Pandas Analysis skill for Claude Code

An analysis is done when every number in the conclusion can be regenerated by re-running the code top to bottom, row counts are accounted for at every join and filter, dtypes are deliberate, and assertions guard the assumptions the result depends on.

Ground rules

• Look before transforming: shape, dtypes, nulls, duplicates, key uniqueness.
• Specify dtypes and date parsing on load; never trust inference on messy data.
• Track row counts after every filter and merge; explain any change.
• Vectorize. iterrows/apply(axis=1) are a last resort.
• Write results to files (Parquet/CSV) from code, not by copying printed output.
• Never overwrite the raw input file.

Workflow

- [ ] 1. Load with explicit dtypes, record raw shape
- [ ] 2. Profile: dtypes, nulls, cardinality, duplicates, ranges
- [ ] 3. Clean: types, missing values, outliers, duplicates (log each step's count)
- [ ] 4. Join with validate= and indicator=, check counts
- [ ] 5. Aggregate/reshape to answer the question
- [ ] 6. Assert invariants, sanity-check totals against a source
- [ ] 7. Save outputs, summarize findings with caveats

1. Load

import pandas as pd

df = pd.read_csv(
    "orders.csv",
    usecols=["order_id", "customer_id", "created_at", "amount", "currency", "status"],
    dtype={"order_id": "string", "customer_id": "string", "currency": "category", "status": "category"},
    parse_dates=["created_at"],
    na_values=["", "NA", "null", "-"],
)
print(df.shape)

Source	Loader notes
CSV	dtype=, usecols=, parse_dates=; dtype_backend="pyarrow" for lower memory
Parquet	pd.read_parquet(path, columns=[...], filters=[...]) reads only what you need
Excel	pd.read_excel(path, sheet_name=..., dtype=...); check merged header rows
SQL	pd.read_sql(query, engine, params=...); aggregate in SQL when data is large
Large CSV	chunksize= and aggregate per chunk, or switch to Polars/DuckDB

Messy files (encodings, delimiters, broken rows) belong to csv-cleaning first.

2. Profile

df.info(memory_usage="deep")
df.describe(include="all").T
df.isna().mean().sort_values(ascending=False).head(20)     # null share per column
df.nunique().sort_values()
df["status"].value_counts(dropna=False)
df.duplicated(subset=["order_id"]).sum()                    # key uniqueness
df["created_at"].agg(["min", "max"])

3. Clean

before = len(df)
df = (
    df
    .assign(
        amount=lambda d: pd.to_numeric(d["amount"], errors="coerce"),
        created_at=lambda d: pd.to_datetime(d["created_at"], utc=True),
        status=lambda d: d["status"].astype("string").str.strip().str.lower().astype("category"),
    )
    .drop_duplicates(subset=["order_id"], keep="last")
    .query("status != 'test'")
)
print(f"cleaning removed {before - len(df)} rows")
bad_amount = df["amount"].isna().sum()   # rows that failed numeric conversion

Missing-value decisions:

Situation	Action
Missing means zero (no purchases)	fillna(0) and say so
Missing at random, small share	Drop rows, report the count
Missing in key/join columns	Investigate source; never fill silently
Integer column with nulls	Use nullable Int64, not float
Category with nulls	Keep as explicit “unknown” if it matters for counts

4. Merge safely

merged = orders.merge(
    customers, on="customer_id", how="left",
    validate="many_to_one",      # raises if customers has duplicate keys
    indicator=True,
)
print(merged["_merge"].value_counts())
assert len(merged) == len(orders), "left join changed row count: duplicate keys on the right"
unmatched = merged.loc[merged["_merge"] == "left_only", "customer_id"].unique()

Normalize key dtypes and formatting (strip, case, leading zeros) on both sides before merging; "00123" vs 123 silently fails to match.

5. Aggregate and reshape

summary = (
    merged
    .groupby(["region", pd.Grouper(key="created_at", freq="MS")], observed=True, dropna=False)
    .agg(
        orders=("order_id", "nunique"),
        revenue=("amount", "sum"),
        avg_order=("amount", "mean"),
        customers=("customer_id", "nunique"),
    )
    .reset_index()
)

# share within group without collapsing rows
merged["share_of_customer_spend"] = merged["amount"] / merged.groupby("customer_id")["amount"].transform("sum")

wide = summary.pivot_table(index="created_at", columns="region", values="revenue", aggfunc="sum", fill_value=0)
long = wide.reset_index().melt(id_vars="created_at", var_name="region", value_name="revenue")

# time series: sort first, then resample/rolling
daily = merged.set_index("created_at").sort_index()["amount"].resample("D").sum()
rolling_7d = daily.rolling(7, min_periods=7).mean()

Need	Use
One row per group	groupby().agg(name=(col, func))
Keep row count, add group stat	groupby().transform()
Filter groups	groupby().filter(lambda g: len(g) >= 5) or transform + mask
Top N per group	sort_values().groupby().head(N)
Ratios	Aggregate numerator and denominator separately, then divide (never average ratios)

6. Assert and sanity-check

import math

assert df["order_id"].is_unique
assert (df["amount"].dropna() >= 0).all(), "negative amounts present"
total_raw = df["amount"].sum()
total_summary = summary["revenue"].sum()
assert math.isclose(total_raw, total_summary, rel_tol=1e-9), (total_raw, total_summary)

Cross-check one headline number against an independent source (dashboard, finance report, a SQL query). If it differs, explain why before reporting.

Performance and memory

Symptom	Fix
apply(axis=1) over rows	Vectorized column ops, np.where, np.select, .map(dict)
String columns using GBs	category for low cardinality; string[pyarrow] otherwise
Repeated pd.concat in a loop	Collect frames in a list, concat once
Huge CSV re-parsed every run	Convert once to Parquet
Doesn’t fit in memory	Read only needed columns, chunk, or move to Polars/DuckDB
Slow groupby on object keys	Convert keys to category with observed=True

Measure: %timeit in notebooks, df.memory_usage(deep=True).sum() / 1e6 for MB.

Gotchas

• Chained assignment (df[df.x > 0]["y"] = 1) does not modify df. With Copy-on-Write (default in pandas 3), it never works; use df.loc[mask, "y"] = 1.
• pandas 3 infers a dedicated string dtype for text; code checking dtype == object for strings needs updating.
• groupby drops NaN keys unless dropna=False; categoricals produce empty groups unless observed=True.
• Averaging per-group averages weights small groups like big ones.
• Timezone-naive and aware datetimes cannot be compared; normalize to UTC on load.
• value_counts() excludes NaN by default; mean() skips NaN silently.
• Integer columns become float when a NaN appears; IDs like 1234567890123456789 lose precision. Load IDs as strings.
• read_csv with mixed types per column emits DtypeWarning and guesses per chunk; set dtypes.

Output

**Question:** Monthly revenue by region, Jan–Sep 2026
**Data:** orders.csv 1,204,331 rows -> 1,198,902 after removing 5,429 test/duplicate orders
**Joins:** orders -> customers (many_to_one), 312 orders with unknown customer kept as "unknown"
**Result:** EMEA revenue +18% vs. H1; APAC flat (table saved to out/revenue_by_region.parquet)
**Checks:** totals match raw sum; September total within 0.2% of finance dashboard
**Caveats:** amounts in original currency; FX conversion not applied