Select, clean, aggregate, and join labeled tables while preserving types and row meaning.

Pandas for tabular data

Pandas is designed for labeled, heterogeneous tabular data. Its power comes from aligned indexes, column operations, grouping, reshaping, and joins. That same implicit alignment can create subtle bugs unless row identity and data types are explicit.

Install and load data

uv add pandas

from pathlib import Path

import pandas as pd


def load_orders(path: Path) -> pd.DataFrame:
    return pd.read_csv(
        path,
        dtype={
            "order_id": "string",
            "customer_id": "string",
            "status": "category",
        },
        parse_dates=["ordered_at"],
        usecols=[
            "order_id",
            "customer_id",
            "status",
            "amount",
            "ordered_at",
        ],
    )

Provide dtypes when identifiers might contain leading zeroes. Select only needed columns to reduce memory and prevent accidental dependencies on unrelated data.

Inspect before transforming

orders = load_orders(Path("orders.csv"))

print(orders.shape)
print(orders.dtypes)
print(orders.head())
print(orders.isna().sum())
print(orders["status"].value_counts(dropna=False))

Do not infer data quality from head() alone. Check missing values, uniqueness, ranges, categories, and duplicate keys.

Select with `loc` and `iloc`

loc selects by labels; iloc selects by integer position:

paid = orders.loc[
    orders["status"].eq("paid"),
    ["order_id", "customer_id", "amount"],
]

first_five = orders.iloc[:5, :3]

Use .at[row_label, column] and .iat[row_position, column_position] for one scalar.

Avoid chained assignment:

# Ambiguous and may not update the original frame.
orders[orders["status"] == "pending"]["status"] = "open"

# Explicit assignment.
orders.loc[orders["status"].eq("pending"), "status"] = "open"

When a filtered frame must be independently mutated, call .copy().

Clean data with explicit failure policy

orders = orders.rename(columns=str.strip)
orders["order_id"] = orders["order_id"].str.strip()
orders["amount"] = pd.to_numeric(orders["amount"], errors="coerce")
orders["ordered_at"] = pd.to_datetime(
    orders["ordered_at"],
    errors="coerce",
    utc=True,
)

errors="coerce" converts invalid values to missing values. That is only safe when you inspect, quarantine, or reject those rows afterward:

invalid = orders.loc[
    orders["order_id"].isna()
    | orders["amount"].isna()
    | orders["ordered_at"].isna()
]

if not invalid.empty:
    raise ValueError(f"{len(invalid)} invalid order rows")

Handle duplicates according to key semantics:

duplicate_ids = orders["order_id"].duplicated(keep=False)
if duplicate_ids.any():
    duplicates = orders.loc[duplicate_ids].sort_values("order_id")
    raise ValueError(f"duplicate order IDs:\n{duplicates}")

Blind drop_duplicates() can discard conflicting facts.

Missing data is not one concept

orders["status"] = orders["status"].fillna("unknown")
complete_amounts = orders.dropna(subset=["amount"])

Before filling or dropping, distinguish:

unknown values;
values that do not apply;
parse failures;
values not yet collected;
values intentionally withheld.

Use nullable extension dtypes when absence is meaningful:

orders["item_count"] = orders["item_count"].astype("Int64")
orders["customer_id"] = orders["customer_id"].astype("string")

Group and aggregate

customer_summary = (
    orders.loc[orders["status"].eq("paid")]
    .groupby("customer_id", as_index=False)
    .agg(
        order_count=("order_id", "nunique"),
        revenue=("amount", "sum"),
        latest_order=("ordered_at", "max"),
    )
    .sort_values("revenue", ascending=False)
)

Named aggregations produce stable, meaningful column names. Decide whether missing group keys should be retained with dropna=False.

Use transform when output must align with original rows:

orders["customer_total"] = orders.groupby("customer_id")["amount"].transform("sum")

Join with cardinality checks

customers = pd.read_csv(
    "customers.csv",
    dtype={"customer_id": "string"},
)

enriched = orders.merge(
    customers[["customer_id", "country"]],
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)

unmatched = enriched.loc[enriched["_merge"].eq("left_only")]
if not unmatched.empty:
    raise ValueError(f"{len(unmatched)} orders have no customer")

enriched = enriched.drop(columns="_merge")

validate turns an accidental many-to-many explosion into an error. Available expectations include one_to_one, one_to_many, many_to_one, and many_to_many.

Join keys must have compatible dtypes and normalization. Trimming only one side or mixing integers and strings creates false misses.

Index alignment can surprise

Pandas aligns Series by label:

left = pd.Series([10, 20], index=["a", "b"])
right = pd.Series([1, 2], index=["b", "c"])
result = left + right

Only label "b" overlaps; "a" and "c" become missing. This behavior is useful, but it differs from positional NumPy arithmetic. Reset indexes or convert to arrays only when positional behavior is truly intended.

Method chains and testable transforms

Write transformations as functions:

def paid_order_summary(orders: pd.DataFrame) -> pd.DataFrame:
    required = {"order_id", "customer_id", "status", "amount"}
    missing = required.difference(orders.columns)
    if missing:
        raise ValueError(f"missing columns: {sorted(missing)}")

    return (
        orders.loc[orders["status"].eq("paid")]
        .groupby("customer_id", as_index=False)
        .agg(
            order_count=("order_id", "nunique"),
            revenue=("amount", "sum"),
        )
        .sort_values("customer_id")
        .reset_index(drop=True)
    )

Pure input-to-output functions are easy to test with small frames. Keep file and database I/O in separate adapters.

Scale and storage

CSV loses type metadata and can be expensive to parse. Parquet preserves column types better and supports column-oriented reads:

uv add pyarrow

customer_summary.to_parquet("customer-summary.parquet", index=False)
summary = pd.read_parquet(
    "customer-summary.parquet",
    columns=["customer_id", "revenue"],
)

For data larger than memory or workloads dominated by joins and aggregations, push work into a database or consider an out-of-core engine. Chunked CSV processing helps only when the calculation can be combined correctly across chunks.

Common pitfalls

Losing leading zeroes by inferring identifiers as integers.
Chained assignment and unclear view/copy ownership.
Silent coercion without inspecting rejected rows.
Many-to-many joins that multiply rows.
Assuming row order is stable without sorting.
Using .apply(axis=1) where vectorized column operations are clearer and faster.
Treating DataFrames as a transactional data store.
Comparing floating aggregates with exact equality.

Practice

Load a CSV with explicit identifier, category, numeric, and UTC datetime types.
Produce an invalid-row report before cleaning.
Build a named aggregation grouped by two keys.
Perform a many_to_one join and fail on unmatched rows.
Test a pure transformation with pandas.testing.assert_frame_equal.

Pandas for tabular data

Pandas for tabular data

Install and load data

Inspect before transforming

Select with `loc` and `iloc`

Clean data with explicit failure policy

Missing data is not one concept

Group and aggregate

Join with cardinality checks

Index alignment can surprise

Method chains and testable transforms

Scale and storage

Common pitfalls

Practice

Finish this lesson

Pandas for tabular data

On this page