Pandas Cheat Sheet: The Complete Quick Reference

The pandas operations you look up every time — from reading a CSV to groupby aggregations, merge joins, and time series. This reference covers the full data analysis workflow.

Quick reference

The 25 patterns that cover 90% of daily pandas work.

Creating DataFrames

import pandas as pd
import numpy as np

# From a dict — most common
df = pd.DataFrame({
    "name": ["Alice", "Bob", "Carol"],
    "age":  [30, 25, 35],
    "score": [88.5, 92.0, 79.3],
})

# From a list of dicts (e.g. API response)
records = [{"id": 1, "val": "a"}, {"id": 2, "val": "b"}]
df = pd.DataFrame(records)

# From a NumPy array
df = pd.DataFrame(np.random.randn(5, 3), columns=["A", "B", "C"])

# Empty DataFrame with schema
df = pd.DataFrame(columns=["name", "age", "score"])

# Series — a single column
s = pd.Series([10, 20, 30], name="count")

Reading and writing data

# CSV
df = pd.read_csv("data.csv")
df = pd.read_csv("data.csv",
    sep=";",             # delimiter
    encoding="utf-8",   # character encoding
    parse_dates=["date"],# auto-parse date columns
    dtype={"id": str},  # force column type
    nrows=1000,         # read only first 1000 rows
    skiprows=2,         # skip header rows
)
df.to_csv("out.csv", index=False)   # index=False — don't write row numbers

# Excel
df = pd.read_excel("data.xlsx", sheet_name="Sales")
df.to_excel("out.xlsx", sheet_name="Results", index=False)

# JSON
df = pd.read_json("data.json")
df.to_json("out.json", orient="records", indent=2)

# Parquet (fast columnar format — use for large data)
df = pd.read_parquet("data.parquet")
df.to_parquet("out.parquet", index=False)

# SQL
import sqlite3
conn = sqlite3.connect("db.sqlite3")
df = pd.read_sql("SELECT * FROM users WHERE active = 1", conn)
df.to_sql("results", conn, if_exists="replace", index=False)

Viewing and inspecting data

df.shape         # (rows, columns)
df.dtypes        # column data types
df.info()        # types + null counts + memory
df.describe()    # count/mean/std/min/percentiles for numeric cols
df.head(5)       # first 5 rows
df.tail(5)       # last 5 rows
df.sample(10)    # 10 random rows
df.columns       # Index of column names
df.index         # Row index

df.isnull().sum()   # null count per column
df.duplicated().sum()  # duplicate row count
df["col"].nunique()    # number of unique values
df["col"].value_counts()           # frequency table
df["col"].value_counts(normalize=True)  # as percentages

Selecting and filtering

# Single column → Series
df["name"]

# Multiple columns → DataFrame
df[["name", "age"]]

# Boolean filter
df[df["age"] > 30]
df[(df["age"] > 30) & (df["score"] >= 80)]   # AND
df[(df["age"] < 20) | (df["score"] > 95)]    # OR
df[~df["name"].str.startswith("A")]           # NOT

# query() — readable syntax
df.query("age > 30 and score >= 80")
df.query("city in @city_list")   # reference a Python variable with @

# isin()
df[df["status"].isin(["active", "pending"])]

# loc — label-based (rows and columns by name)
df.loc[0:5, "name"]              # rows 0–5, column "name"
df.loc[df["age"] > 30, "name"]  # filter rows, select column

# iloc — position-based (integer index)
df.iloc[0:5, 0:2]     # first 5 rows, first 2 columns
df.iloc[-1]           # last row

# at / iat — single cell (faster than loc/iloc for scalars)
df.at[3, "name"]
df.iat[3, 0]

Modifying DataFrames

# Add / replace column (immutable style — assign returns new df)
df2 = df.assign(tax=df["price"] * 0.2)
df2 = df.assign(
    tax=lambda x: x["price"] * 0.2,
    total=lambda x: x["price"] + x["tax"],
)

# Rename columns
df = df.rename(columns={"old_name": "new_name", "qty": "quantity"})

# Drop columns or rows
df = df.drop(columns=["unnecessary_col"])
df = df.drop(index=[0, 5, 10])

# Reset row index (after filtering)
df = df.reset_index(drop=True)

# Sort
df = df.sort_values("score", ascending=False)
df = df.sort_values(["group", "score"], ascending=[True, False])

# Remove duplicates
df = df.drop_duplicates()
df = df.drop_duplicates(subset=["email"])  # dedup by column

Handling missing data

df.isnull().sum()              # nulls per column
df.dropna()                    # drop rows with any null
df.dropna(subset=["email"])    # drop only if this column is null
df.dropna(thresh=3)            # keep rows with at least 3 non-null values

df.fillna(0)                   # fill all nulls with 0
df.fillna({"age": 0, "name": "Unknown"})   # per-column fill
df["col"].fillna(df["col"].mean())         # fill with mean
df["col"].fillna(method="ffill")           # forward-fill
df["col"].fillna(method="bfill")           # backward-fill

# Detect and replace
df.replace({"status": {"yes": True, "no": False}})
df["score"].replace(-1, np.nan)    # mark sentinel as NaN

Data types and conversion

df["age"].astype(int)
df["price"].astype(float)
df["id"].astype(str)

pd.to_numeric(df["value"], errors="coerce")  # invalid → NaN
pd.to_datetime(df["date"])
pd.to_datetime(df["date"], format="%d/%m/%Y")  # explicit format

# Categorical — saves memory and speeds up groupby
df["status"] = df["status"].astype("category")

# Check
df.dtypes
df["col"].dtype

String operations (str accessor)

All str methods are vectorised (no loop needed):

df["name"].str.lower()
df["name"].str.upper()
df["name"].str.strip()                     # trim whitespace
df["name"].str.replace("Inc.", "", regex=False)
df["email"].str.split("@").str[0]          # take part before @
df["name"].str.contains("alice", case=False)  # boolean mask
df["name"].str.startswith("A")
df["name"].str.len()                       # character count
df["col"].str.extract(r"(\d{4})")          # capture group → column
df["col"].str.extractall(r"(\d+)")         # all matches → multi-index

DateTime operations (dt accessor)

df["date"] = pd.to_datetime(df["date"])

df["date"].dt.year
df["date"].dt.month
df["date"].dt.day
df["date"].dt.day_name()       # "Monday", "Tuesday" …
df["date"].dt.hour
df["date"].dt.weekday          # 0=Monday … 6=Sunday
df["date"].dt.is_month_end
df["date"].dt.floor("H")       # round down to hour

# Date arithmetic
df["date"] + pd.Timedelta(days=7)
df["end"] - df["start"]                     # → Timedelta
(df["end"] - df["start"]).dt.total_seconds()

Groupby and aggregation

# Single aggregation
df.groupby("region")["sales"].sum()
df.groupby("region")["sales"].agg(["sum", "mean", "count"])

# Multiple columns, multiple functions
df.groupby("region").agg(
    total_sales=("sales", "sum"),
    avg_sales=("sales", "mean"),
    orders=("order_id", "count"),
)

# transform — keeps original shape (great for adding group stats back)
df["group_mean"] = df.groupby("category")["sales"].transform("mean")
df["rank"] = df.groupby("category")["sales"].transform("rank", ascending=False)

# apply — custom function (slower; use agg/transform first)
df.groupby("region").apply(lambda g: g.nlargest(3, "sales"))

# Named aggregation shorthand (pandas 0.25+)
result = df.groupby(["region", "quarter"]).agg(
    revenue=("price", "sum"),
    units=("qty", "sum"),
    customers=("customer_id", "nunique"),
).reset_index()

Merging and joining

# merge() is SQL JOIN
# INNER JOIN (default) — only matching rows
pd.merge(orders, customers, on="customer_id")

# LEFT JOIN — all orders, null if no customer match
pd.merge(orders, customers, on="customer_id", how="left")

# Different column names
pd.merge(orders, customers, left_on="cust_id", right_on="id")

# On index
pd.merge(df1, df2, left_index=True, right_index=True)

# Concatenate rows (stack vertically)
pd.concat([df_jan, df_feb, df_mar], ignore_index=True)

# Concatenate columns (side by side)
pd.concat([df_left, df_right], axis=1)

# Update df1 with values from df2 (fills NaN only)
df1.update(df2)

# Merge indicator — see which side each row came from
pd.merge(df1, df2, on="id", how="outer", indicator=True)
# "_merge" column: "left_only", "right_only", "both"

Reshaping

# pivot_table — Excel-style pivot
df.pivot_table(
    values="sales",
    index="region",
    columns="quarter",
    aggfunc="sum",
    fill_value=0,
)

# melt — wide → long (unpivot)
pd.melt(df,
    id_vars=["id", "name"],      # columns to keep
    value_vars=["q1", "q2", "q3"], # columns to unpivot
    var_name="quarter",
    value_name="sales",
)

# stack / unstack — pivot index ↔ columns
df.set_index(["region", "quarter"]).stack().reset_index()

# crosstab — frequency table of two categorical columns
pd.crosstab(df["region"], df["status"])
pd.crosstab(df["region"], df["status"], normalize="index")  # row %

Window functions

# Rolling (sliding window)
df["sales_7d_avg"] = df["sales"].rolling(window=7).mean()
df["sales_7d_sum"] = df["sales"].rolling(window=7).sum()

# Expanding (cumulative)
df["cumulative_sales"] = df["sales"].expanding().sum()
df["cumulative_max"] = df["sales"].expanding().max()

# Exponentially weighted moving average
df["ewma"] = df["sales"].ewm(span=7).mean()

# Shift (lag values)
df["prev_sales"] = df["sales"].shift(1)
df["next_sales"] = df["sales"].shift(-1)
df["pct_change"] = df["sales"].pct_change()

Common mistakes

Performance tips

# Use categorical for low-cardinality string columns (saves 5–10× memory)
df["status"] = df["status"].astype("category")

# Check memory usage
df.memory_usage(deep=True).sum() / 1024**2  # in MB

# Avoid iterrows — vectorise instead
# SLOW:
for i, row in df.iterrows():
    df.at[i, "tax"] = row["price"] * 0.2
# FAST:
df["tax"] = df["price"] * 0.2

# Read only needed columns
df = pd.read_csv("big.csv", usecols=["id", "date", "sales"])

# Read in chunks for very large files
for chunk in pd.read_csv("huge.csv", chunksize=100_000):
    process(chunk)

# Use query() for readable boolean filters (also avoids chained indexing)
df.query("age > 30 and score >= 80")

FAQ

How do I filter rows where a column contains a substring? Use df[df["col"].str.contains("pattern")]. Add na=False to ignore NaN: df["col"].str.contains("pattern", na=False).

What's the difference between loc and iloc? loc selects by label (column name, index label); iloc selects by integer position. Use loc most of the time; iloc when you need positional slicing like df.iloc[0:5].

Why does df["col"] = value sometimes raise SettingWithCopyWarning? You are modifying a copy of a slice, not the original DataFrame. Call df = df.copy() after slicing, or use df.assign() which always returns a new DataFrame.

How do I concatenate DataFrames with different columns? pd.concat([df1, df2]) fills missing columns with NaN by default. Add join="inner" to keep only shared columns.

How do I read specific sheets from an Excel file? pd.read_excel("file.xlsx", sheet_name="Sales") for one sheet, or sheet_name=None to get a dict of all sheets: dfs = pd.read_excel("file.xlsx", sheet_name=None).

When should I use apply() vs vectorised operations? Prefer vectorised methods (arithmetic, str accessor, dt accessor, groupby().agg()) — they're 10–1000× faster. Only fall back to apply() when the operation can't be expressed as a vectorised call.