Skip to content

pandas Essentials

The workhorse of tabular data in Python: load, clean, transform, aggregate, and merge datasets with expressive, vectorized operations.

Table of Contents

Installation / Setup

pip install pandas pyarrow     # or: uv add pandas pyarrow

Use pandas ≥ 2.x and install pyarrow (the modern engine for Parquet and string data).

import pandas as pd

Core Syntax

Series & DataFrame

s = pd.Series([10, 20, 30], index=["a", "b", "c"], name="values")

df = pd.DataFrame({
    "name": ["ada", "lin", "sam"],
    "score": [92, 85, 78],
})
df = pd.DataFrame({"a": [1, 2], "b": [3, 4]}, index=["x", "y"])

Reading & writing files

Format Read Write
CSV pd.read_csv("f.csv") df.to_csv("f.csv", index=False)
Parquet pd.read_parquet("f.parquet") df.to_parquet("f.parquet", index=False)
Excel pd.read_excel("f.xlsx", sheet_name=0) df.to_excel("f.xlsx", index=False)
JSON pd.read_json("f.json") df.to_json("f.json", orient="records")
SQL pd.read_sql("SELECT * FROM t", conn) df.to_sql("t", conn, if_exists="replace")
Clipboard/HTML pd.read_clipboard(), pd.read_html(url) — 
pd.read_csv("data.csv",
            sep=",", encoding="utf-8",          # delimiters & text encoding
            index_col="id",                     # use a column as index
            usecols=["a", "b", "c"],            # read only some columns
            parse_dates=["date"],               # parse datetime columns
            dtype={"zip": str},                 # force dtypes (keep leading zeros)
            na_values=["", "n/a", "NULL"],      # treat as missing
            skiprows=1, nrows=10_000)           # skip/limit rows

For very large files, read in chunks or use Polars: for chunk in pd.read_csv("big.csv", chunksize=100_000): ....

Inspection

df.head() / df.tail(3)     # first / last rows
df.info()                  # dtypes, non-null counts, memory
df.describe()              # summary stats of numeric columns
df.shape                   # (rows, cols)
df.columns                 # column labels
df.index                   # row labels
df.dtypes                  # per-column dtype
df["col"].value_counts(normalize=True)   # distribution
df["col"].nunique()
df.memory_usage(deep=True) # real memory incl. strings

Common Operations

Selection & filtering

df["col"]                    # Series
df[["a", "b"]]               # DataFrame (list = columns)
df.loc["row_label"]          # label-based
df.loc[df["score"] > 80, ["name"]]   # rows + columns by label
df.iloc[0]                   # first row, position-based
df.iloc[1:4, 0:2]            # positional slice
df.at["x", "a"]              # single value, fast
df.iat[0, 1]

mask = df["score"].between(60, 90)
mask = df["name"].isin(["ada", "sam"])
mask = df["name"].str.contains("^a", regex=True)
df[mask]                     # boolean filtering
df.query("score > 80 and name != 'sam'")   # query syntax

Indexing

df = df.set_index("id")       # move column -> index
df = df.reset_index()         # index -> column
df.sort_index()               # sort by row labels
df.set_axis(new_names, axis=1)  # rename columns positionally
df.reindex(["a", "b", "c"])   # conform to new index (fills/aligns)

.loc is label-inclusive, .iloc is position-exclusive on the right edge.

Missing values

df.isna().sum()                    # missing count per column
df.notna()
df.dropna()                        # drop rows with any NaN
df.dropna(subset=["score"])        # drop only if key col missing
df.dropna(how="all")               # drop fully-empty rows
df.fillna(0)                       # constant
df.fillna(df["col"].median())      # per-column value
df["col"].ffill()                  # forward fill
df["col"].bfill()                  # backward fill
df["col"].interpolate()            # linear interpolation
df.replace({"old": "new", -1: None})

df.isna().sum() after every load is a reflex worth keeping.

Duplicates

df.duplicated()                      # boolean Series
df.duplicated(subset=["email"])      # only these columns
df.drop_duplicates()                 # keep first by default
df.drop_duplicates(subset=["user"], keep="last")

Sorting

df.sort_values("score", ascending=False)
df.sort_values(["score", "name"], ascending=[False, True])
df.sort_values("score", na_position="first")
df.sort_index()

groupby & aggregation

df.groupby("region")["sales"].sum()
df.groupby("region")["sales"].agg(["sum", "mean", "count"])
df.groupby(["region", "year"]).agg(
    total=("sales", "sum"),
    avg_score=("score", "mean"),
)
df.groupby("region")["sales"].transform("mean")   # same length as df
df.groupby("region").filter(lambda g: g["sales"].sum() > 1000)
df.groupby("region").apply(custom_fn)             # flexible, slower

df.groupby(pd.Grouper(key="date", freq="ME"))["sales"].sum()  # monthly

Merge, join, concat

pd.merge(df_a, df_b, on="user_id")                    # shared column
pd.merge(df_a, df_b, on="user_id", how="left")        # keep all of df_a
pd.merge(df_a, df_b, left_on="id", right_on="user", suffixes=("", "_b"))

df_a.join(df_b, on="user_id")      # join on index or column, left by default
df_a.join(df_b.set_index("id"), on="user_id")

pd.concat([df_jan, df_feb], axis=0, ignore_index=True)   # stack rows
pd.concat([df_x, df_y], axis=1)                           # side by side
how Meaning
inner Only matching keys (default)
left / right All rows of one side, fill the other
outer Union of keys, NaN where missing

Watch for duplicated keys multiplying rows in merges — check with df_a["key"].duplicated().any() first.

Pivot & reshape

df.pivot(index="date", columns="city", values="temp")       # long -> wide
df.pivot_table(index="region", columns="year",
               values="sales", aggfunc="sum", margins=True)
pd.crosstab(df["region"], df["churned"])                    # counts table

df.melt(id_vars=["user"], value_vars=["q1", "q2"],          # wide -> long
        var_name="quarter", value_name="score")
df.stack() / df.unstack()          # index <-> columns
df.wide_to_long(df, stubnames=["val"], i="id", j="time")

Keep data tidy: one row per observation, one column per variable — reshape with melt/pivot before analysis.

Datetime

df["date"] = pd.to_datetime(df["date"])          # parse
df["date"].dt.year / .dt.month / .dt.day         # extract parts
df["date"].dt.strftime("%Y-%m-%d")               # format
df["date"].dt.dayofweek                          # 0=Monday
df["date"].dt.to_period("M")                     # month period

idx = pd.date_range("2024-01-01", periods=12, freq="ME")
df.set_index("date").resample("W").sum()         # weekly totals
df.set_index("date").resample("D").ffill()       # daily forward fill
df["date"].shift(1)                              # previous value (lag)
Resample alias Meaning
D, W, ME, QE, YE day, week, month/quarter/year end
H, T, S hour, minute, second
W-MON weekly anchored to Monday

String operations (str accessor)

df["name"].str.strip().str.lower()
df["email"].str.contains("@", na=False)
df["code"].str.replace(r"\s+", "", regex=True)
df["full"].str.split("_", expand=True)            # -> multiple columns
df["text"].str.extract(r"(?P<num>\d+)")           # regex groups
df["name"].str.startswith("a") / .str.len()
df["name"].str.cat(sep=", ")                      # join rows

String ops are elementwise Python (slower than numeric) — vectorize with regex carefully; for huge text columns use str dtype with pyarrow or Polars.

Categorical data

df["cat"] = df["cat"].astype("category")
df["cat"].cat.categories          # unique categories
df["cat"].cat.codes               # integer codes
df["cat"].cat.rename_categories(["low", "high"])
df["cat"].cat.reorder_categories(["low", "med", "high"], ordered=True)
df["cat"].cat.set_categories(["a", "b", "c", "d"])   # add unseen

Categoricals cut memory (repeated strings stored once) and give ordered semantics.

apply / map

df["score"].map(lambda x: x * 2)        # Series elementwise
df["grade"] = df["score"].map({90: "A", 80: "B"})   # dict lookup
df["col"].apply(custom_func)            # Series, elementwise
df.apply(func, axis=1)                  # per row (SLOW — prefer vectorized)
df["col"].map(fn)                       # preferred alias since pandas 2.1

Vectorized ops and groupby.transform beat apply(axis=1) by orders of magnitude on big frames.

Performance tips

  • Vectorize: prefer df["a"] + df["b"] over apply.
  • Use query / boolean masks over Python-side row loops.
  • Downcast dtypes: df["c"] = df["c"].astype("float32"); categoricals for repeated strings.
  • Choose engine="pyarrow" for Parquet I/O and string dtype.
  • Chain operations lazily — pandas 3.x makes copy-on-write default (no more SettingWithCopy).
  • Read only what you need: usecols, dtype, parse_dates, chunksize.
  • Keep the index simple (default RangeIndex) unless you need lookups; set_index speeds label-based reads.
  • Profile big pipelines with .to_numpy() handoffs to NumPy where pandas overhead dominates.

Practical Examples

1. End-to-end EDA workflow

df = pd.read_csv("sales.csv", parse_dates=["date"])

# Clean
df = df.drop_duplicates(subset=["order_id"])
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df = df.dropna(subset=["amount"])

# Inspect
print(df.info()); print(df.describe())

# Aggregate
monthly = (df.set_index("date")
             .resample("ME")["amount"]
             .agg(["sum", "count"]))
top_customers = df.groupby("customer_id")["amount"].sum().nlargest(10)

2. Feature engineering

X = pd.read_csv("users.csv", parse_dates=["signup_date"])

X["signup_year"] = X["signup_date"].dt.year
X["active_days"] = (pd.Timestamp("today") - X["signup_date"]).dt.days
X["email_provider"] = X["email"].str.split("@").str[1]
X["is_premium"] = X["plan"].isin(["pro", "enterprise"]).astype(int)
X["plan"] = X["plan"].astype("category")
X = pd.get_dummies(X, columns=["plan"], dtype=int)   # or keep categorical

3. Merge features with labels for modeling

features = pd.read_parquet("features.parquet")
labels = pd.read_csv("labels.csv")

data = features.merge(labels, on="user_id", how="inner", validate="one_to_one")
X = data.drop(columns=["user_id", "churned"])
y = data["churned"]

Common Patterns

# Missing-value report
missing = df.isna().sum()
missing = missing[missing > 0].sort_values(ascending=False)

# First/last per group (top row per group)
df.sort_values("date").groupby("user_id").tail(1)

# Rolling mean
df.set_index("date")["sales"].rolling(7).mean()

# Relative frequency table
df.groupby("region")["churned"].mean()          # churn rate by region

# Z-score column
df["z"] = (df["score"] - df["score"].mean()) / df["score"].std()

# Check merge cardinality before joining
assert not df["key"].duplicated().any(), "duplicate keys would multiply rows"

Best Practices

  • Always check df.isna().sum() and dtypes right after loading.
  • Prefer vectorized operations and groupby.transform over apply(axis=1).
  • Write files with index=False unless the index is meaningful.
  • Pass errors="coerce" when converting messy columns, then handle NaN explicitly.
  • Use how="left" merges deliberately and validate cardinality (validate=...).
  • Keep data tidy (long format) and reshape only for display/features.
  • Set copy-on-write explicitly (pd.set_option("mode.copy_on_write", True)) on pandas 2.x for safety.
  • Use Polars or chunking when DataFrames exceed memory.

Common Mistakes

Mistake Consequence Correct approach
df[df.col == 5] chain after chain SettingWithCopyWarning, silent misses Assign to a copy or use .loc
dropna() without subset Loses rows with any missing cell dropna(subset=[...])
Chained df["a"]["b"] assignment SettingWithCopy df.loc[mask, "col"] = value
fillna(0) before aggregation Biased means Fill with median or drop
Merge without checking duplicates Rows multiply silently validate="one_to_one"
read_csv on large file Slow, memory-heavy dtype, usecols, chunksize, or Polars
to_csv(index=True) Mysterious Unnamed: 0 column index=False

Troubleshooting

Symptom Cause Fix
KeyError: 'col' Column name mismatch (spaces/case) df.columns.tolist() to inspect
SettingWithCopyWarning Chained assignment on a slice Use .loc or enable copy-on-write
Dates read as strings parse_dates not set Pass parse_dates=[...]; pd.to_datetime
PerformanceWarning Slicing non-monotonic index df.sort_index() or use sort_values first
Merge output grew huge Duplicate keys Deduplicate keys before merge
MemoryError Whole file in RAM Chunks, dtypes, categoricals, or Polars
Column of object dtype slow Mixed/text columns astype("string") with pyarrow backend

Official Documentation

Last Updated

2026-09-06 — Initial version.