<!-- skill: Pandas Frames | version: 1.0.0 | description: Master DataFrames: loading, indexing, groupby, merges, datetimes, vectorization | category: Python & Data -->
## Instructions
- Always inspect first: `df.shape`, `df.dtypes`, `df.head()`, `df.isna().sum()` before any transform.
- Load correctly: `read_csv(path, parse_dates=[...], dtype={...}, usecols=[...])`; use `chunksize=` for files larger than RAM.
- Prefer `loc` for label-based and `iloc` for positional indexing; never chain-assign (`df[a][b] = x`) — use single `loc`.
- Use vectorized ops over `apply`/`iterrows`: prefer built-in methods, `np.where`, `mask`, string/date accessors.
- Groupby discipline: `groupby(...).agg({...})` with named aggregations; `reset_index()` before merging results back.
- Merge safely: always specify `on`, `how`, and `validate` (`1:1`, `m:1`, `1:m`); check `indicator=True` on risky joins.
- Handle datetimes with `pd.to_datetime(errors='coerce', utc=True)`, then `tz_convert`; use `dt` accessor, `Grouper(freq=...)`.
- Set index deliberately for time series (`set_index('date').sort_index()`); use `resample`, `rolling(min_periods=1)`, `shift/diff`.
- Deduplicate early: `drop_duplicates(subset=[...], keep='first')`; assert uniqueness on keys with `assert df[key].is_unique`.
- Clean strings once: `str.strip().str.lower()`, `str.replace` regex, `astype('category')` for low-cardinality columns.
- Track every cleaning decision in code comments and print a before/after null-count summary.
- Never invent data: if a merge drops rows or dates fail to parse, report counts instead of silently filling.

## Examples
- Sales ledger → monthly revenue by region with `groupby + Grouper(freq='ME')`, top-10 customers table.
- Web logs → parse timestamps, resample to hourly hits, flag gaps, join with user metadata via validated merge.
- Survey dump → strip headers, coerce dtypes, categorical encoding, pivot_table cross-tab with margins.

## Workflows
1. Load + inspect: shape, dtypes, nulls, date range, duplicate-key check.
2. Clean: headers, dtypes, datetimes, strings, missing values — log each choice.
3. Reshape: filter with `query`/boolean masks, groupby-agg, merge, pivot.
4. Validate: row-count reconciliation pre/post merge, null re-check, spot-check 5 rows.
5. Persist: `to_parquet('clean.parquet')` as working format; export CSV only for delivery.

## Templates
```python
# frames.py — chunked load + groupby + validated merge + datetime handling
import pandas as pd
orders = pd.read_csv("orders.csv", parse_dates=["ordered_at"], dtype={"region": "string"})
users = pd.read_csv("users.csv", dtype={"user_id": "int64", "tier": "string"})
orders["ordered_at"] = pd.to_datetime(orders["ordered_at"], utc=True, errors="coerce")
print("bad dates:", orders["ordered_at"].isna().sum())
orders["month"] = orders["ordered_at"].dt.tz_convert("Europe/Berlin").dt.to_period("M")
monthly = (
    orders.groupby(["month", "region"], observed=True)
    .agg(revenue=("amount", "sum"), orders=("order_id", "nunique"))
    .reset_index()
)
merged = monthly.merge(users_summary, on="region", how="left", validate="m:1", indicator=True)
print(merged["_merge"].value_counts())
merged.to_parquet("monthly.parquet", index=False)
```
```python
# chunks.py — process a 10 GB CSV without blowing RAM
import pandas as pd
out = {}
for chunk in pd.read_csv("huge.csv", chunksize=200_000, usecols=["user_id", "amount"], dtype={"amount": "float64"}):
    g = chunk.groupby("user_id")["amount"].sum()
    for k, v in g.items():
        out[k] = out.get(k, 0.0) + float(v)
result = pd.Series(out, name="total").reset_index().rename(columns={"index": "user_id"})
result.to_parquet("totals.parquet", index=False)
```

## Tools
- Core: `pip install pandas pyarrow` — pyarrow gives fast Parquet I/O and string dtypes.
- Inspect: `df.info(memory_usage='deep')`, `df.describe(include='all', datetime_is_numeric=True)`.
- SARAH Run executes `frames.py` in project folder; Parquet outputs save beside it.

## Advanced
- Avoid `apply(axis=1)` — rewrite with vectorized ops; if truly row-wise, use `itertuples()` (100x faster than `iterrows`).
- MultiIndex: `groupby([a, b]).agg(...)` → `unstack()` for wide tables; `stack()` to go long; `swaplevel().sort_index()`.
- Rolling done right: `set_index('ts').rolling('7D').mean()` on sorted datetime index; `ewm(span=7)` for decayed averages.
- Memory: downcast numerics (`pd.to_numeric(downcast='integer')`), `category` dtype, `usecols` + `chunksize`, Parquet over CSV.
- Merge explosions: pre-check `df.duplicated(subset=key).sum()` on both sides; use `validate` to fail fast.
- Troubleshooting: `SettingWithCopyWarning` → use `.loc[row, col] =` on the original or `.copy()` explicitly.

## Documentation
- pandas user guide: https://pandas.pydata.org/docs/user_guide/index.html
- 10 minutes to pandas: https://pandas.pydata.org/docs/user_guide/10min.html
- Cookbook: https://pandas.pydata.org/docs/user_guide/cookbook.html