Introduction
Knowing basic Python is one thing. Turning a messy file into a useful answer is another.
Most data-analysis tasks follow the same broad sequence: load the data, inspect it, clean errors, transform columns, calculate summaries, combine related tables and validate the result. This cheat sheet brings the most useful Pandas and NumPy commands into that workflow, with examples you can adapt instead of isolated syntax you may forget.
The examples use a simple orders dataset with columns such as order_id, customer_id, region, category, revenue and order_date.
Python Data Analysis Workflow at a Glance
| Stage | Main question | Useful commands |
| Load | How do I bring data into Python? | pd.read_csv(), pd.read_excel() |
| Inspect | What is inside the dataset? | head(), shape, info(), describe() |
| Clean | What needs correcting? | isna(), fillna(), dropna(), drop_duplicates(), astype() |
| Transform | How do I create analysis-ready fields? | assign(), map(), pd.to_datetime(), np.where() |
| Summarise | What patterns exist by group? | groupby(), agg(), transform() |
| Combine | How do I connect related datasets? | merge(), join(), concat() |
| Validate | Can I trust the output? | duplicated(), value_counts(), validate=, indicator=True |
1. Set Up Pandas and NumPy
Start by importing both libraries with their standard aliases:
import pandas as pd
import numpy as np
Load a CSV or Excel file:
orders = pd.read_csv("orders.csv")
orders = pd.read_excel("orders.xlsx", sheet_name="Orders")
These two lines are alternatives. Use the one that matches your file type.
2. Inspect the Dataset Before Changing It
Do not begin cleaning until you understand the structure. A quick inspection can reveal unexpected column names, incorrect data types, missing values and duplicate records.
orders.head() # first five rows
orders.sample(5) # five random rows
orders.shape # number of rows and columns
orders.columns # column names
orders.dtypes # data type of each column
orders.info() # structure and non-null counts
orders.describe() # summary of numeric columns
For a focused quality check:
orders.isna().sum()
orders.duplicated().sum()
orders["region"].value_counts(dropna=False)
orders["customer_id"].nunique()
shape tells you how large the dataset is, while info() helps identify columns stored in the wrong format. Record the row count before major cleaning or merging steps. This gives you a baseline for checking whether records were removed or multiplied unexpectedly.
3. Select, Filter and Sort Data with Pandas
Select one column as a Series:
orders["revenue"]
Select multiple columns as a DataFrame:
orders[["order_id", "region", "revenue"]]
Use loc to filter rows and choose columns by name:
west_orders = orders.loc[
orders["region"] == "West",
["order_id", "category", "revenue"]
]
Use iloc when you need positions instead of labels:
orders.iloc[0:5, 0:3]
For multiple conditions, place each condition inside parentheses and use & for AND or | for OR:
high_value_west = orders.loc[
(orders["region"] == "West") & (orders["revenue"] >= 1000)
]
Sort and inspect common values:
orders.sort_values("revenue", ascending=False)
orders["category"].value_counts()
Rename columns when the original names are unclear:
orders = orders.rename(columns={"sales_value": "revenue"})
4. Clean Missing, Duplicate and Inconsistent Data
Cleaning is not simply deleting anything incomplete. First decide whether the problem affects an identifier, measurement, category or non-essential field.
| Situation | Possible action | Check before applying it |
| Required identifier is missing | Investigate or remove the row | Confirm the ID cannot be recovered |
| Numeric value is missing | Fill with a justified value or keep it missing | Check distribution and business meaning |
| Category label is inconsistent | Standardise text or map known variants | Confirm that variants mean the same thing |
| Exact duplicate exists | Remove the duplicate | Define which columns make a record unique |
| Date or number is stored as text | Convert the data type | Inspect values that fail conversion |
Standardise column names and text
orders.columns = (
orders.columns
.str.strip()
.str.lower()
.str.replace(" ", "_", regex=False)
)
orders["region"] = orders["region"].str.strip().str.title()
Detect and handle missing values
orders.isna().sum()
orders["revenue"] = orders["revenue"].fillna(
orders["revenue"].median()
)
orders = orders.dropna(subset=["order_id", "customer_id"])
Median filling is shown as a coding example, not a universal rule. It may be suitable when a numeric field is skewed and a defensible estimate is needed, but it can distort the analysis if the missing values carry meaning.
Always investigate why values are missing before filling them.
Remove duplicates
orders = orders.drop_duplicates()
orders = orders.drop_duplicates(subset=["order_id"], keep="first")
The second command assumes order_id should be unique. Verify that assumption before applying it.
Convert data types safely
orders["revenue"] = pd.to_numeric(
orders["revenue"],
errors="coerce"
)
orders["order_date"] = pd.to_datetime(
orders["order_date"],
errors="coerce"
)
orders["customer_id"] = orders["customer_id"].astype("string")
With errors="coerce", values that cannot be converted become missing values. Check those rows instead of assuming the conversion worked perfectly:
orders.loc[orders["revenue"].isna()]
orders.loc[orders["order_date"].isna()]
5. Use NumPy for Numerical Operations and Conditions
NumPy works with homogeneous multidimensional arrays called ndarray objects. It is useful for array calculations, reshaping, conditional logic and numerical operations.
values = np.array([120, 250, 180, 310, 400, 150])
values.shape
values.ndim
values.dtype
values.reshape(2, 3)
Filter and summarise an array:
values[values >= 200]
np.sum(values)
np.mean(values)
np.median(values)
np.max(values)
np.std(values)
Use NumPy’s nan functions when an array contains missing numerical values:
values_with_na = np.array([120, 250, np.nan, 310])
np.nanmean(values_with_na)
np.nanmedian(values_with_na)
Create a conditional column in Pandas with np.where():
orders["order_band"] = np.where(
orders["revenue"] >= 1000,
"High value",
"Standard"
)
For more than two categories, pd.cut() or a mapping table can be clearer than several nested np.where() calls.
6. Understand Pandas GroupBy
The Pandas GroupBy guide describes the process as split, apply and combine:
- Split rows into groups.
- Apply a calculation to each group.
- Combine the results.
Calculate total revenue by region:
orders.groupby("region")["revenue"].sum()
Return a clean DataFrame with several useful metrics:
regional_summary = (
orders.groupby("region", as_index=False)
.agg(
orders=("order_id", "nunique"),
total_revenue=("revenue", "sum"),
average_revenue=("revenue", "mean")
)
.sort_values("total_revenue", ascending=False)
)
Group by more than one column:
category_summary = (
orders.groupby(["region", "category"], as_index=False)
.agg(total_revenue=("revenue", "sum"))
)
Use transform() when you want a group-level calculation returned against every original row:
orders["regional_average"] = (
orders.groupby("region")["revenue"].transform("mean")
)
orders["vs_regional_average"] = (
orders["revenue"] - orders["regional_average"]
)
Remember that size() counts rows in each group, including rows with missing values in other columns. count() returns non-missing counts for the selected columns. Choose the command based on the question you are answering.
7. Merge DataFrames Without Creating Incorrect Results
Suppose orders contains transaction data and customers contains one row per customer:
customers = pd.read_csv("customers.csv")
The main join types are:
| Join type | Rows retained | Typical use |
| inner | Keys present in both tables | Analyse matched records only |
| left | Every row from the left table | Enrich a primary dataset without intentionally dropping rows |
| right | Every row from the right table | Preserve the right-hand dataset |
| outer | All keys from both tables | Reconcile coverage and find unmatched records |
Merge customer attributes into orders:
analysis = orders.merge(
customers,
how="left",
on="customer_id",
validate="many_to_one",
indicator=True
)
Here, validate="many_to_one" checks the expected relationship. Many orders can belong to one customer, but customer_id should not be duplicated in the customer table.
If that assumption is broken, Pandas raises an error instead of silently multiplying rows.
Use the indicator column to inspect match quality:
analysis["_merge"].value_counts()
analysis.loc[
analysis["_merge"] == "left_only"
]
analysis = analysis.drop(columns="_merge")
If key columns have different names, use left_on= and right_on=. Before merging, ensure both keys use compatible data types and formats:
orders["customer_id"] = (
orders["customer_id"]
.astype("string")
.str.strip()
)
customers["customer_id"] = (
customers["customer_id"]
.astype("string")
.str.strip()
)
One important difference from typical SQL behaviour is that Pandas can match null merge keys with other null keys. The official Pandas merge documentation flags this behaviour because it can create unexpected matches.
Inspect or remove missing keys when those matches would be invalid.
8. Put the Commands Together in One Workflow
The following example connects the main steps:
import pandas as pd
import numpy as np
# Load
orders = pd.read_csv("orders.csv")
customers = pd.read_csv("customers.csv")
# Inspect
starting_rows = len(orders)
orders.info()
print(orders.isna().sum())
# Clean
orders.columns = (
orders.columns
.str.strip()
.str.lower()
.str.replace(" ", "_", regex=False)
)
orders["revenue"] = pd.to_numeric(
orders["revenue"],
errors="coerce"
)
orders["order_date"] = pd.to_datetime(
orders["order_date"],
errors="coerce"
)
orders = orders.drop_duplicates(subset="order_id")
orders = orders.dropna(
subset=["order_id", "customer_id", "revenue"]
)
# Transform
orders["order_band"] = np.where(
orders["revenue"] >= 1000,
"High value",
"Standard"
)
# Merge and validate
analysis = orders.merge(
customers,
how="left",
on="customer_id",
validate="many_to_one",
indicator=True
)
print(analysis["_merge"].value_counts())
# Group and summarise
regional_summary = (
analysis.groupby("region", as_index=False)
.agg(
customers=("customer_id", "nunique"),
orders=("order_id", "nunique"),
total_revenue=("revenue", "sum"),
average_revenue=("revenue", "mean")
)
.sort_values("total_revenue", ascending=False)
)
# Final checks
print("Starting order rows:", starting_rows)
print("Rows after cleaning:", len(orders))
print("Rows after merge:", len(analysis))
print(regional_summary)
The printed row counts are not decoration. They help you explain what changed during cleaning and confirm that a merge did not unexpectedly increase or reduce the number of order records.
9. Common Mistakes to Check Before Trusting the Output
Treating missing values as automatically equal to zero
A missing value may mean unavailable, not collected or not applicable. Zero is a real measurement. Replacing one with the other can change totals and averages.
Merging before checking key uniqueness
Duplicate keys on both sides can create a many-to-many merge and multiply rows. Use duplicated(), value_counts() and the validate argument before accepting the result.
Merging columns with different data types
A numeric customer ID in one table and a string ID in another can prevent correct matching. Standardise the type and remove unwanted spaces first.
Confusing count() with size()
size() counts group rows, while count() excludes missing values from the selected column. The two answer different questions.
Editing a filtered subset without making the intention clear
When creating an independent subset that you plan to modify, use .copy():
west_orders = orders.loc[
orders["region"] == "West"
].copy()
west_orders["revenue_share"] = (
west_orders["revenue"] /
west_orders["revenue"].sum()
)
Skipping validation after each major step
Recheck shape, missing values, duplicates, category values and merge status after transformations. A script that runs without an error can still produce the wrong analytical result.
Move from Remembering Commands to Solving Data Problems
A cheat sheet helps you recall syntax, but practical ability develops when you use those commands on unfamiliar data and explain why each step is appropriate.
You can explore structured Python and data-analysis challenges on CompeteX to practise debugging, data manipulation and analytical reasoning.
As your skills develop, AuthenX provides AI-led portfolio screening and conversational skill verification. ConnectX gives data professionals a place to learn, discuss problems and connect with the wider community.
Together, these products form part of the PangaeaX data ecosystem, helping learners move from practice to verified skill development and professional participation.
Final Takeaway
Effective Python analysis is not about memorising every Pandas or NumPy function. It is about following a reliable process:
- Inspect before changing data.
- Clean according to the meaning of each field.
- Use NumPy for efficient numerical operations and conditions.
- Use GroupBy to answer a defined analytical question.
- Validate merge keys, relationships and row counts.
- Check the final output before communicating a conclusion.
Keep this cheat sheet nearby while practising, but make the reasoning behind each command part of your workflow. That is what turns Python syntax into dependable data-analysis skill.
Technical References
Reference note: The commands and version-sensitive behaviours in this guide were checked against the official Pandas and NumPy documentation on 26 August 2026. Check the documentation for changes when working with a different or later library version.