# pandas

Source: https://codewiki.com/datascience/pandas-guide/

> - **what**: pandas represents tabular data with labeled `Series` and `DataFrame` objects and provides selection, cleaning, aggregation, joins, and input/output operations.
> - **when**: Use pandas when data fits in memory and you need to inspect and transform heterogeneous columns in Python; use another execution engine for streaming scans or out-of-memory data.
> - **how**: Define the index, column `dtype` values, missing-data policy, and key cardinality first; then compose `.loc`, vectorized expressions, named aggregations, and validated joins.

## What it is and why it exists

pandas is Python's tabular data processing library. Its central object is the DataFrame, a two-dimensional labeled structure made of rows, columns, and data types. A single column is usually a Series, which carries values, a data type, and an index.

Python lists and dictionaries can hold records, but they don't automatically provide column-wise operations, label alignment, missing-value propagation, grouped aggregation, or relational joins. pandas puts those operations behind one interface, so you can begin with CSV data, a database query, or in-memory objects and produce a new table or statistic through inspectable transformations.

pandas is best suited to structured data that fits in one machine's memory and to cleaning or analysis that benefits from interactive inspection. You'll meet it in feature engineering, experiment analysis, report preparation, and small-to-medium ETL jobs. It isn't a database and doesn't preserve constraints, transactions, or query plans automatically; once data enters pandas, code must express those guarantees again.

A table is more than a two-dimensional matrix of values. Row index alignment determines how objects pair up, each column's dtype determines how values are represented, and key uniqueness determines whether a join expands the row count. Reliable pandas code treats these properties as a data contract instead of checking only column names.

## How it works

### Two labeled containers

A `Series` is a one-dimensional sequence of values with one row index. A `DataFrame` is a collection of columns sharing a row index, with column names forming a second labeled axis. `df["revenue"]` returns a `Series`, while `df[["revenue"]]` preserves two dimensions and returns a `DataFrame`; that distinction affects later selection, joins, and return types.

Every column has its own `dtype`, so one `DataFrame` can hold integers, floating-point values, strings, booleans, and dates. `object` says only that a column stores Python objects; it doesn't mean “this is a string column.” Specify important column types at the input boundary and inspect `df.dtypes` after cleaning.

### Selection is a coordinate rule

`.loc` selects by label, while `.iloc` selects by integer position. A label slice includes its ending label, while a positional slice excludes the stop just like an ordinary Python slice. `.at` and `.iat` are useful for reading or writing one scalar, but they don't change the meaning of labels and positions.

A Boolean condition is also an indexed `Series`. Put parentheses around every comparison when combining conditions with `&`, `|`, and `~`, because Python's operator precedence isn't natural-language precedence. Put the final selection in one `.loc[rows, columns]` operation so the assignment target and result shape are both explicit.

| Expression | Selection rule | Result shape |
|---|---|---|
| `df["revenue"]` | One column by label | `Series` |
| `df[["revenue"]]` | A list of column labels | `DataFrame` |
| `df.loc["a":"c"]` | Inclusive label slice | Depends on matching rows |
| `df.iloc[0:3]` | Stop-exclusive position slice | At most three rows |

### Operations align labels

When two `Series` objects participate in arithmetic, pandas pairs their index labels before calculating matching positions. A label that appears on only one side remains in the result with a missing value. This rule safely aligns data from different sources, but it can also make code intended to be positional silently produce extra rows.

Convert to arrays or reset indexes only when the domain truly defines correspondence by position. Conversion to a NumPy array discards label protection, so validate lengths and ordering first. When unexpected `NaN` values appear, compare indexes before filling them with zero.

### Missing values are part of the type contract

pandas uses several missing markers, including `NaN` in floating-point columns, `NaT` in date columns, and `pd.NA` in nullable extension types. Nullable dtypes such as `Int64`, `boolean`, and `string` represent missing values without abandoning the domain type. Detect missing values consistently with `isna()` or `notna()` instead of equality comparisons or truth conversion.

Cleaning policy belongs to each column: an unknown customer ID usually can't be replaced by `0`, and a missing amount isn't necessarily zero. Whether a parse failure is rejected, corrected, or converted to missing should be expressed and tested next to the `errors` argument of `to_numeric()` or `to_datetime()`.

### Grouping and joining change shape

`groupby()` implements split-apply-combine: it forms groups from keys, then applies an aggregation or transformation to each group. `agg()` usually reduces the row count, while `transform()` returns a result on the original index and suits within-group shares or centered values. Named aggregation fixes the output name, input column, and aggregation function together, making the result easier to review.

`merge()` implements database-style joins on keys, but uniqueness constraints from the input systems aren't inherited automatically. Parameters such as `validate="many_to_one"` turn expected cardinality into a runtime check, while `indicator=True` records whether each row came from the left, right, or both tables. After a join, still inspect row counts, unmatched keys, and important amount totals.

### Input and output are boundaries

Readers such as `read_csv()` perform parsing and schema inference at the same time. Use `usecols`, `dtype`, `na_values`, and date parsing deliberately for fields whose meaning is known. A sample that looks clean isn't evidence that a production column has only one representation.

Output choices carry semantics too. `to_csv(index=False)` drops the row index, so a meaningful business index must first become a column. Formats differ in which dtypes and metadata they preserve; reload a small artifact and compare its schema instead of assuming a round trip is lossless.

A maintainable pipeline makes its boundary checks visible:

1. Select and rename expected columns.
2. Parse values into declared dtypes and record failures.
3. Enforce key, null, range, and cardinality rules.
4. Transform data, then verify output shape and invariant totals.

## Examples

The four examples use the same kind of order and customer data, progressing through selection, cleaning, aggregation, and joining. Every output below was produced with Python 3.14.3 and pandas 3.0.5.

### 1. Build a table and select by label

First, make the stable business key the index, then select paid orders with one Boolean condition and an explicit column list. Rows with missing revenue don't pass the `notna()` condition.

<!-- quick -->

```python
# file: select_orders.py
import pandas as pd

orders = pd.DataFrame(
    {
        "order_id": [1001, 1002, 1003, 1004],
        "region": ["East", "West", "East", "West"],
        "revenue": [120.0, 75.5, None, 210.0],
        "paid": [True, False, True, True],
    }
).set_index("order_id")

paid_orders = orders.loc[
    orders["paid"] & orders["revenue"].notna(),
    ["region", "revenue"],
]

print(orders.dtypes.astype(str).to_dict())
print(paid_orders.to_string())
```

```text
{'region': 'str', 'revenue': 'float64', 'paid': 'bool'}
         region  revenue
order_id                
1001       East    120.0
1004       West    210.0
```

<!-- /quick -->

`set_index()` returns a new object labeled by `order_id`; it doesn't use `inplace=True`, so the ownership change is visible in the assignment. The two dimensions in `.loc` describe the row condition and output columns, and the result remains a two-dimensional `DataFrame`.

The output also exposes the inferred types. Because the amount column contains `None`, it has the `float64` dtype, while the Boolean column remains `bool`. If order IDs must preserve leading zeros, declare them as strings while reading instead of trying to reconstruct them from integers later.

### 2. Clean types at the input boundary

CSV inference would normally turn `001` into the integer `1`, so the customer ID is declared as `string` during the read. Each step in the method chain returns an inspectable new result, and the amount ends with an explicit nullable floating-point type.

```python
# file: clean_customers.py
from io import StringIO

import pandas as pd

raw_csv = StringIO(
    """customer_id,segment,spend
001, retail ,19.50
002,,unknown
002,retail,24.00
003,SMB,31.25
"""
)

customers = pd.read_csv(
    raw_csv,
    dtype={"customer_id": "string"},
    na_values=["unknown"],
)
cleaned = (
    customers.assign(
        segment=lambda frame: frame["segment"].str.strip().str.lower().fillna("unassigned"),
        spend=lambda frame: pd.to_numeric(frame["spend"], errors="coerce"),
    )
    .drop_duplicates(subset="customer_id", keep="last")
    .reset_index(drop=True)
)
cleaned["spend"] = cleaned["spend"].astype("Float64")

print(cleaned.to_string(index=False))
print(cleaned.dtypes.astype(str).to_dict())
```

```text
customer_id segment  spend
        001  retail   19.5
        002  retail   24.0
        003     smb  31.25
{'customer_id': 'string', 'segment': 'str', 'spend': 'Float64'}
```

`na_values` maps a domain sentinel to a missing value, and the string accessor normalizes the category labels. `errors="coerce"` also converts any other unparseable amount to missing; production code must count those values so parse failures aren't silently swallowed.

Keeping the last row for each customer is merely an explicit example policy. Real data needs a timestamp or version column proving that “last” means newest, followed by an assertion that the key is unique. Otherwise, an input-order change alters the result.

### 3. Aggregate and return group-level results

Named aggregation creates a one-row-per-region summary, while `transform("sum")` returns the region total for every original order. These operations serve different shapes and don't need to be simulated with `apply()`.

```python
# file: summarize_orders.py
import pandas as pd

orders = pd.DataFrame(
    {
        "region": ["East", "East", "West", "West", "East"],
        "channel": ["web", "store", "web", "store", "web"],
        "revenue": [120.0, 80.0, 150.0, 50.0, 100.0],
    }
)

summary = (
    orders.groupby("region", as_index=False)
    .agg(
        orders=("revenue", "size"),
        revenue=("revenue", "sum"),
        avg_order=("revenue", "mean"),
    )
    .sort_values("revenue", ascending=False)
)
orders["region_share"] = orders["revenue"] / orders.groupby("region")["revenue"].transform("sum")

print(summary.to_string(index=False, formatters={"avg_order": "{:.2f}".format}))
print(orders[["region", "channel", "region_share"]].round(3).to_string(index=False))
```

```text
region  orders  revenue avg_order
  East       3    300.0    100.00
  West       2    200.0    100.00
region channel  region_share
  East     web         0.400
  East   store         0.267
  West     web         0.750
  West   store         0.250
  East     web         0.333
```

`as_index=False` keeps the grouping key as an ordinary column, which helps with later export or joining. Every named aggregation can be traced from its output column to its source column and function, without producing a hard-to-read hierarchical column index.

`transform()` preserves the original row index, so its result can be assigned safely to `orders["region_share"]`. If a group total can be zero, the code also needs a zero-denominator policy and a check that the result is finite.

### 4. Validate join cardinality

Orders may reference the same customer many times, while `customer_id` must be unique in the customer dimension. That relationship is `many_to_one` and should be checked with `validate`, not assumed from historically clean data.

```python
# file: join_customers.py
import pandas as pd

orders = pd.DataFrame(
    {
        "order_id": [1001, 1002, 1003, 1004],
        "customer_id": [10, 11, 10, 13],
        "total": [80.0, 125.0, 45.0, 60.0],
    }
)
customers = pd.DataFrame(
    {
        "customer_id": [10, 11, 12],
        "tier": ["gold", "silver", "bronze"],
    }
)

joined = orders.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)
joined["tier"] = joined["tier"].fillna("unmatched")
report = joined.groupby("tier", as_index=False).agg(
    orders=("order_id", "size"),
    revenue=("total", "sum"),
)

print(joined[["order_id", "customer_id", "tier", "_merge"]].to_string(index=False))
print(report.to_string(index=False))
```

```text
 order_id  customer_id      tier    _merge
     1001           10      gold      both
     1002           11    silver      both
     1003           10      gold      both
     1004           13 unmatched left_only
     tier  orders  revenue
     gold       2    125.0
   silver       1    125.0
unmatched       1     60.0
```

Customer `10` being referenced by two orders is valid, while customer `13` has no match. `indicator` retains that fact in the result so the business rule can reject, quarantine, or label it instead of letting a missing tier flow silently into a report.

If customer `10` appeared twice in the customer table, `validate="many_to_one"` would raise `MergeError` immediately. Without that check, both orders would match two rows, doubling the order count and revenue total.

## Pitfalls

> **Pitfall:** Assigning after chained selection looks plausible but has no unambiguous single write target. Under pandas 3.0 Copy-on-Write semantics, `df[df["paid"]]["status"] = "ready"` doesn't update the original `DataFrame`.

**Fix:** specify rows and columns in one `.loc` call: `df.loc[df["paid"], "status"] = "ready"`. If you intend to create an independent result, call `.copy()` explicitly before changing it. Don't disable Copy-on-Write or ignore warnings to patch over ambiguous ownership.

> **Pitfall:** Equal object lengths don't mean operations are positional. When index labels differ or arrive in another order, automatic alignment can create missing values or pair values with the wrong business entities.

**Fix:** check index uniqueness and set relationships before the operation. When domain meaning follows labels, let pandas align and inspect unmatched labels; when it follows positions, verify ordering and length before using arrays explicitly. Don't apply `fillna(0)` directly to a surprising result.

> **Pitfall:** Plain `bool` and integer dtypes can't represent every missing state, so inference may promote a column to floating point or generic objects. The truth value of `pd.NA` is also ambiguous, and `if value:` raises an exception.

**Fix:** choose suitable dtypes such as `boolean`, `Int64`, `Float64`, or `string` at the input boundary, and detect missing values with `isna()`. Define missing-data policy per column, then assert the resulting `dtype` and missing count.

> **Pitfall:** A join without a cardinality check can expand as a Cartesian product when the right table has duplicate keys. pandas also matches null keys on both sides, unlike the null join behavior of common SQL databases.

**Fix:** validate key uniqueness and null policy before joining, call `merge(..., validate=...)`, and audit sources with `indicator=True`. Afterward, compare row counts, unmatched-key counts, and amount totals that shouldn't change.

> **Pitfall:** By default, `groupby()` drops rows whose grouping key is missing. The report still runs and looks tidy, but the grouped amount can be smaller than the input amount.

**Fix:** count rows with missing group keys first, then label them, reject the data, or write `dropna=False` explicitly according to the business rule. Run conservation checks after aggregation, such as comparing input and grouped order counts and revenue totals.

<!-- deep -->

## Labels, types, and ownership contracts

### Alignment precedes calculation

A pandas binary operation doesn't simply take two underlying arrays and calculate position by position. It first constructs a label relationship: `Series` aligns row indexes, while `DataFrame` aligns both row indexes and column labels. The result usually contains the union of labels, so a coordinate missing from one side produces a missing value.

Duplicate indexes complicate that relationship because one label may identify several rows. An interface that depends on unique keys should check `index.is_unique` at its boundary instead of waiting for a downstream surprise. To compare two indexes, inspect `equals()`, set differences, and order explicitly rather than comparing only lengths.

Indexes also have types. The integer label `1` isn't equal to the string label `"1"`, and time indexes with different time zones may not represent the same time semantics. Before resetting an index to default integers, confirm it isn't the only copy of a business key.

### Types define valid states

A `dtype` constrains representation range, missing-value capability, and operation behavior. Binary floating point for money may require a tolerance or integers in the smallest currency unit; identifiers should be strings to preserve leading zeros; dates need an explicit time-zone policy. No universal “optimize dtypes” function is correct for every column.

Nullable extension dtypes express unknown values with `pd.NA` and follow three-valued logic. For example, combining an unknown Boolean with `True` can remain unknown. Before filtering, decide whether an unknown condition is excluded, retained, or rejected instead of relying on implicit truth.

Type conversion is also a validation boundary. `errors="raise"` suits input that must be valid, while `errors="coerce"` can isolate bad values for further inspection. If you choose the latter, retain or count the newly missing values so cleaning doesn't erase the source of an error.

### Copy-on-Write changes assignment

Copy-on-Write is enabled by default in pandas 3.0. An object derived from another pandas object behaves as a copy from the user's perspective: changing the derived object doesn't also mutate the source. The implementation may share underlying data while it remains read-only and copy before the first write, but application code shouldn't depend on whether internal blocks are shared.

This model removes many uncertain “view or copy” side effects, but it doesn't make chained assignment correct. A chained expression first creates an intermediate object and then writes only to that intermediate object; it can't send the update back to the original `DataFrame`. A single `.loc` assignment identifies the target object explicitly.

A shallow `copy(deep=False)` is also protected by Copy-on-Write, so later writes to one side don't alter the other. This isn't a recursive copy of an object graph when a column contains mutable Python objects. Ownership boundaries should still avoid hiding mutable containers in `object` columns.

### Cardinality is a join schema

Relational databases usually store unique-key and foreign-key rules in a schema, but an in-memory `DataFrame` doesn't inherit them automatically. State the expected join relationship as `one_to_one`, `one_to_many`, `many_to_one`, or `many_to_many`, then enforce it with `validate`.

`many_to_many` can be legitimate, such as a bridge between orders and promotions. Without estimating matches first, however, the result can be far larger than either input. Count matches by key before joining and define how later aggregation avoids double counting.

Join validation checks cardinality but doesn't prove that domain keys are correct. Case, surrounding whitespace, time zones, and different units can all make keys appear to match or fail to match. A reliable pipeline normalizes keys before the join and checks source distribution and business invariants afterward.

### Shape and invariants close the loop

Shape is an observable consequence of every pandas operation. Selection may preserve or reduce axes, aggregation reduces groups, `transform()` preserves the source index, and joins can increase or decrease rows according to their type. State the expected relationship rather than hard-coding one fixture's exact row count.

Useful invariants come from the domain. A left join should preserve every left-side order, a partitioned revenue report should conserve total revenue, and a deduplicated customer table should have a unique customer key. These checks catch valid pandas programs that implement the wrong data rule.

Small adversarial fixtures expose more than large random samples. Include shuffled labels, a duplicate key, an unmatched key, one missing grouping value, and a value near each range boundary. The expected result should be simple enough to calculate by hand.

Assertions turn those contracts into executable checks. Use ordinary assertions for key counts and totals, and `pandas.testing.assert_frame_equal()` when index, columns, dtypes, missing values, and data must all match an expected table.

Keep expected tables small and literal. A test that reconstructs its expectation with the same grouping or joining steps can repeat the implementation error instead of detecting it.

<!-- /deep -->

[Checkpoint: datascience/pandas-guide](https://codewiki.com/datascience/pandas-guide/#checkpoint)

## Further reading

- [pandas user guide: 10 minutes to pandas](https://pandas.pydata.org/docs/user_guide/10min.html)
- [pandas user guide: indexing and selecting data](https://pandas.pydata.org/docs/user_guide/indexing.html)
- [pandas user guide: working with missing data](https://pandas.pydata.org/docs/user_guide/missing_data.html)
- [pandas user guide: group by](https://pandas.pydata.org/docs/user_guide/groupby.html)
- [pandas user guide: merge, join, and concatenate](https://pandas.pydata.org/docs/user_guide/merging.html)
- [pandas user guide: Copy-on-Write](https://pandas.pydata.org/docs/user_guide/copy_on_write.html)