# pandas

Docs for TrueQuery 0.1.6. The pandas code the checker looks for, which checks are on by default, and what it can't follow.

A `.py` file that imports pandas is read with Python's own parser; pandas
is never imported and your code never runs. The checks look for pandas
code that runs without an error and gives the wrong data:

- a write through a chain (`df[mask]["col"] = v`,
  `df["col"].fillna(0, inplace=True)`), which never reaches the DataFrame
  under pandas 3 (checked on every edit);
- a write to the row of an `iterrows()` loop, which changes a copy;
- `groupby(...).first()` / `.last()`, which can build a row from values of
  different input rows;
- `sort_values` on one column (an unstable sort) deciding which row
  `drop_duplicates` keeps;
- `to_sql(if_exists="replace")`, which drops the table's keys, constraints
  and column types;
- off by default (they fire on most code and depend on data the checker
  can't see): `merge`/`join` with no `validate=`, and `groupby` with no
  `dropna=`.

A DataFrame is a name the file builds from pandas (`pd.read_*`,
`pd.DataFrame(...)`, `pd.concat`, `pd.merge`, a method of another known
DataFrame) or annotates as `pd.DataFrame`. A name the checker can't follow
(a parameter with no annotation, a value from another module) isn't
reported on. The receipt counts these files as `pandas in N Python files`.
Checks that need to know what a column means (money, units, time zones)
come with contracts.

## An example

A small example file, written for these docs:

```python
import pandas as pd

orders = pd.read_csv("orders.csv")

# Set VAT on EU orders.
orders[orders.region == "EU"]["vat"] = 0.2

# Latest order per customer.
latest = orders.sort_values("ordered_at").drop_duplicates("customer_id", keep="last")

# First value of each column, per customer.
summary = orders.groupby("customer_id").first()

summary.to_sql("customer_summary", engine, if_exists="replace")
```

A real run on it:

```output
$ truequery review etl/clean_orders.py
etl/clean_orders.py:6:1 [error] py/pandas-no-chained-assignment — chained assignment: the write lands on a temporary copy, so the DataFrame never changes
    fix: assign in one step on the DataFrame itself: df.loc[rows, "col"] = value
etl/clean_orders.py:9:43 [warning] py/pandas-dedupe-unstable-sort — drop_duplicates keeps a row picked by sort_values on line 9: one key, default (unstable) sort, so rows that tie on the key come out in any order
    fix: sort by the key plus a unique tiebreaker column, or pass kind="stable" if the input order is the intended tiebreaker
etl/clean_orders.py:12:41 [warning] py/pandas-groupby-first-mixes-rows — groupby(...).first() takes the first non-null value of each column separately, so one output row can mix values from different input rows
    fix: to keep whole rows: sort, then .nth(0) / .nth(-1) (or drop_duplicates(subset=keys)); or pass skipna=False
etl/clean_orders.py:14:9 [warning] py/pandas-to-sql-replace — to_sql(if_exists="replace") drops the table and creates a new one with types pandas guesses: its keys, constraints, indexes, column types and grants are lost without an error
    fix: empty the table (DELETE or TRUNCATE) and write with if_exists="append", so the table's definition stays

TrueQuery receipt
Checked      0 SQL files · pandas in 1 Python file
Found        1 error · 3 warnings
Not checked  your data: TrueQuery reads files and never connects to your database
Not checked  pandas: only DataFrames a file builds or annotates itself are followed; what a column means (money, units, time zones) needs contracts
```

See every check, on or off, in [all 32 checks](https://analytically.ai/truequery/checks).