pandas

Files (1)

• SKILL.md

SKILL.md (first lines)

# pandas

Rules and patterns for pandas DataFrame work in data-processing code, including pandas↔numpy bridges.
Builds on `python-style.instructions.md`; do not restate general Python or import-alias rules.

Cross-references:

- OHLC bars, indicators, signals, forecasting → load the `financial-timeseries` skill.
- `pandas_udf`, `applyInPandas`, `toPandas` bridges → load the `pyspark` skill.

## Load Patterns and Solutions

Read the following files for named, ready-to-use implementations with when/replaces/gotchas:

- `../../catalogs/pandas-patterns.md`
- `../../catalogs/pandas-solutions.md`

---

## Baseline

Target pandas 3.0 behavior. The dated, metadata-compatible pandas/NumPy/TensorFlow
reference and interpreter fallback live in the
[TensorFlow baseline](../tensorflow/SKILL.md#baseline); they are not an integrated
runtime guarantee. The pandas 2.2-to-3.0 API table below remains migration guidance,
not a separate current combined environment.

---

## Datetime Index

Use when a numeric epoch column must drive time-series slicing or resampling.

- Convert epoch seconds with `pd.to_datetime(col, unit="s")`, sort, then reassign `set_index(...)`.
- Slice a sorted `DatetimeIndex` with `.loc` and ISO-8601 labels (inclusive on both ends) — not boolean masks.
- tz-aware sources: parse with `utc=True`, `tz_convert` to the target zone, then `tz_localize(None)`.
  Never add a fixed `Timedelta` offset — it breaks on DST.
- Do not assume `datetime64[ns]`: pandas 3.0 infers resolution (`unit="s"` → `datetime64[s]`).

```python
window = df.loc["2024-03-01":"2024-04-01"]
```

Catalog: `unix-timestamp-to-datetimeindex`, `timezone-aware-to-naive-local-index`.

---

## Loading Partitioned Files

Use when data is split across many files sharing a name prefix.

- Track each row's source path so duplicates resolve deterministically.
- Concatenate once, then sort + dedup keeping the most recent source.

Catalog: `multi-file-parquet-concat`, `sort-dedup-by-source`.

---

## Cleaning

Use after any load, merge, or concat that may introduce duplicates or wrong dtypes.

- Sort → `drop_duplicates(subset=[...])` → `reset_index(drop=True)` in one chain; agents often omit the reset.
- Cast many columns in one call with a dtype mapping; use `pd.to_numeric(errors="coerce")` for dirty input.
- Rename with a module-level dict constant, never positional `df.columns = [...]`.
- Replace out-of-range values with `Series.where(cond, other)`; clamp to bounds with `Series.clip`.
- Write through `.loc[mask, col]` — chained `df[col][mask] = v` is a silent no-op under Copy-on-Write (pandas 3.0).

```python
df = (
    df.sort_values("sort_col")
    .drop_duplicates(subset=["key_col"], keep="last")
    .reset_index(drop=True)
    .astype(dict.fromkeys(NUMERIC_COLS, "float64"))
    .rename(columns=RENAME)
)

df["amount"] = df["amount"].where(df["amount"] >= threshold, 0)
df.loc[df["amount"] > cap, "flag"] = True
```

---

## Construction

Use when building a DataFrame from records or bridging to/from dicts.

- Accumulate dict records, then `pd.DataFrame(records)` once → `loop-accumulate-then-dataframe`.
- Lookup dict: `drop_duplicates(subset=[key]).set_index(key)[val].to_dict()` makes the keep-first/last
  choice explicit; `dict(zip(...))` silently keeps the last duplicate → `csv-read-strip-dedup-to-dict`.
- Dict to two-column frame: `pd.DataFrame(mapping.items(), columns=[...])` → `dict-to-dataframe`.
- Nested dicts / JSON records: `pd.json_normalize(records, sep="_")` → `nested-dict-flatten-to-dataframe`.

---

## Feature Engineering

Use when deriving columns from a time-ordered or grouped DataFrame.

- Rolling windows: `rolling(window=n).sum()` / `.mean()`; drop warm-up rows or pass `min_periods`.
  Never `bfill()` the leading NaNs in features — it copies future values backward (look-ahead leak).
- Per-row group size: `groupby(key)[key].transform("size")` — keeps row count; no merge.
- Categorical keys: pass `observed=True` to `groupby` (the pandas 3.0 default; 2.x warns).
- `groupby().apply`: select value columns first or pass `include_groups=False`; prefer `agg`/`transform`.
- Date-axis regressor: integer day offsets from the first date; sklearn rejects raw datetimes.
- sklearn transformers: `set_output(transform="pandas")` (scikit-learn >= 1.2) keeps index and columns.

```python
df["roll_3"] = df["value"].rolling(window=3).sum()

df["group_n"] = df.groupby("key")["key"].transform("size")
df = df[df["group_n"] > threshold]

dates = pd.to_datetime(df["date"])
df["day_offset"] = (dates - dates.min()).dt.days

df_scaled = StandardScaler().set_output(transform="pandas").fit_transform(df)
```

From mabrosim/aet-content-pack, MIT.

More from mabrosim/aet-content-pack