Data Wrangling with Pandas Cheat Sheet
Core pandas workflows for reading, cleaning, filtering, handling missing values, and merging datasets during everyday data preparation tasks.
2 PagesBeginnerMar 8, 2026
Reading & Inspecting Data
Load a dataset and get an overview before cleaning it.
python
import pandas as pddf = pd.read_csv("data.csv")df.head()df.info()df.describe()df.dtypesdf.shape# Rename and drop columnsdf = df.rename(columns={"old_name": "new_name"})df = df.drop(columns=["unused_col"])# Filter rowsdf_filtered = df[df["amount"] > 100]df_filtered = df.query("amount > 100 and region == 'US'")
Handling Missing & Duplicate Data
Detect, fill, or drop nulls and remove duplicate rows.
python
df.isna().sum() # count nulls per columndf.dropna(subset=["customer_id"]) # drop rows missing a key fielddf["amount"] = df["amount"].fillna(0)df["category"] = df["category"].fillna("Unknown")df["amount"] = df["amount"].fillna(df["amount"].median())df = df.drop_duplicates(subset=["order_id"], keep="first")# Type conversiondf["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
Merging & Transforming
Combine tables and derive new columns.
python
merged = pd.merge(orders, customers, on="customer_id", how="left")combined = pd.concat([df_2023, df_2024], axis=0, ignore_index=True)# apply / map for row-wise transformationsdf["amount_category"] = df["amount"].apply(lambda x: "high" if x > 1000 else "low")df["region_code"] = df["region"].map({"US": 1, "EU": 2, "APAC": 3})
Key Concepts
Ideas that come up in almost every wrangling task.
- df.info()- Shows column dtypes, non-null counts, and memory usage at a glance
- NaN vs None- Pandas represents missing numeric data as NaN (float); use isna()/notna() to test, not ==
- how='left'/'inner'/'outer'- Controls which rows survive a merge based on matches in the join key
- apply vs vectorized ops- Vectorized operations (df['a'] + df['b']) are much faster than .apply() with a Python function
- loc vs iloc- .loc selects by label, .iloc selects by integer position
Method Chaining with .pipe()
Compose multi-step cleaning pipelines readably instead of reassigning df repeatedly.
python
import pandas as pddef drop_bad_rows(df): return df.dropna(subset=["customer_id"])def add_amount_bucket(df, cutoff=1000): return df.assign( amount_bucket=lambda d: pd.cut(d["amount"], bins=[0, cutoff, float("inf")], labels=["low", "high"]) )clean = ( df .pipe(drop_bad_rows) .pipe(add_amount_bucket, cutoff=500) .query("amount > 0") .reset_index(drop=True))# assign() for chained column creation referencing prior columns in the same chainresult = df.assign( net=lambda d: d["amount"] - d["discount"], net_pct=lambda d: d["net"] / d["amount"])
Categorical, Nullable & Sparse Dtypes
Use memory-efficient dtypes and pandas' nullable integer/boolean types.
python
# Categorical dtype - saves memory and speeds up groupby on low-cardinality columnsdf["region"] = df["region"].astype("category")# Nullable integer dtype (Int64, capital I) supports pd.NA alongside intsdf["quantity"] = df["quantity"].astype("Int64")# Nullable boolean dtypedf["is_active"] = df["is_active"].astype("boolean")# Downcast numeric columns to reduce memory footprintdf["amount"] = pd.to_numeric(df["amount"], downcast="float")# Inspect memory usage per columndf.memory_usage(deep=True)
Vectorized String Cleaning with .str
Clean and extract text data without row-by-row Python loops.
python
df["email"] = df["email"].str.strip().str.lower()# Extract with a regex capture group into new columnsdf[["area_code", "number"]] = df["phone"].str.extract(r"\((\d{3})\)\s*(\d+)")# Boolean masks from string testsmask = df["email"].str.contains("@gmail.com", na=False)# Split a delimited column into multiple columnsdf[["first", "last"]] = df["full_name"].str.split(" ", n=1, expand=True)# Replace using regexdf["sku"] = df["sku"].str.replace(r"[^A-Za-z0-9]", "", regex=True)
Merge Validation & Indicator Columns
Catch silent join bugs (duplicate keys, unmatched rows) before they corrupt downstream analysis.
python
# validate= raises if the join cardinality assumption is wrongmerged = pd.merge( orders, customers, on="customer_id", how="left", validate="many_to_one" # errors if customers has duplicate customer_id)# indicator=True adds a _merge column showing match sourcecompare = pd.merge(orders, customers, on="customer_id", how="outer", indicator=True)only_in_orders = compare[compare["_merge"] == "left_only"]# merge_asof for nearest-key (e.g. timestamp) joins instead of exact matchtrades = trades.sort_values("time")quotes = quotes.sort_values("time")aligned = pd.merge_asof(trades, quotes, on="time", direction="backward")
Advanced Wrangling Terms
Techniques that separate production-grade cleaning pipelines from ad-hoc scripts.
- .pipe()- Passes the whole DataFrame through a function, enabling readable method-chained pipelines instead of nested calls
- validate= in merge()- Asserts join cardinality ('one_to_one', 'one_to_many', 'many_to_one', 'many_to_many') and raises MergeError if violated
- indicator=True- Adds a _merge column labeling each row as left_only/right_only/both, useful for auditing join coverage
- merge_asof- Joins on the nearest key rather than an exact match, typically used for time-ordered data like trades and quotes
- Int64 / boolean (nullable dtypes)- Capitalized pandas extension dtypes that support pd.NA for missing values, unlike NumPy's native int64/bool
- category dtype- Stores repeated string values as integer codes internally, cutting memory use and speeding up groupby/merge on that column
- .str accessor- Vectorized string methods (.str.contains, .str.extract, .str.split) that avoid slow row-wise Python loops
- df.explode()- Turns a column of list-like values into multiple rows, one per list element, replicating the other columns
Pro Tip
Avoid looping over rows with iterrows() for transformations - it's orders of magnitude slower than a vectorized operation or .apply() on a Series for anything beyond a few thousand rows.
Was this cheat sheet helpful?
Explore Topics
#DataWranglingWithPandas#DataWranglingWithPandasCheatSheet#DataScience#Beginner#ReadingInspectingData#Handling#Missing#Duplicate#MachineLearning#Git#CheatSheet#SkillVeris
