Turn a small CSV into a reliable pandas summary
Read, clean, filter and aggregate one complete dataset. Use explicit types, safe assignment and month-end resampling without disconnected snippets.
A useful pandas workflow starts with the shape of the input and ends with an output you can check. This example turns a small sales CSV into totals by region and by month. It is designed for pandas 3.x and also uses APIs available in recent 2.x releases.
The input is embedded so the example runs without a separate file. In an application, pass a file path to read_csv instead of StringIO. Use an existing environment with pandas installed; none of the following code depends on a notebook’s special shell syntax.
Load and clean explicit columns
from io import StringIO
import pandas as pd
source = StringIO("""date,region,sales
2026-01-03, North ,100
2026-01-20,South,50
2026-02-02,North,
2026-02-15,South,75
""")
sales = pd.read_csv(
source,
dtype={"region": "string", "sales": "Float64"},
parse_dates=["date"],
)
sales["region"] = sales["region"].str.strip()
print(sales.dtypes)
string and nullable Float64 express the intended column types. Do not silently replace every missing value with zero: a missing measurement may mean unknown, whereas zero is an observed amount. This example excludes rows with unknown sales from its calculations and leaves the original input available for inspection.
clean = sales.dropna(subset=["sales"]).copy()
positive = clean.loc[clean["sales"] > 0].copy()
positive["sales_with_tax"] = positive["sales"] * 1.20
Use a single .loc assignment when editing a subset of an existing frame. Avoid chained assignment such as sales[mask]["sales"] = 0; pandas 3 uses Copy-on-Write, so that expression does not update the original frame.
Group and verify the result
by_region = (
clean.groupby("region", as_index=False, dropna=False)
.agg(total_sales=("sales", "sum"), orders=("sales", "size"))
.sort_values("region")
)
print(by_region.to_string(index=False))
assert by_region["total_sales"].tolist() == [100.0, 125.0]
assert by_region["orders"].tolist() == [1, 2]
The grouped result should contain:
| region | total_sales | orders |
|---|---|---|
| North | 100 | 1 |
| South | 125 | 2 |
size counts rows, while count counts non-missing values in a column. They agree here because missing sales were removed first. Keeping that decision explicit makes later changes easier to audit.
Resample on a datetime index
monthly = (
clean.set_index("date")["sales"]
.resample("ME")
.sum(min_count=1)
)
print(monthly)
assert monthly.tolist() == [150.0, 75.0]
ME means calendar month end. The old M offset alias was deprecated and removed from newer pandas versions; do not carry it into new examples. min_count=1 keeps an empty month’s aggregate missing instead of inventing a zero.
For a join, choose the expected relationship explicitly. For example, a region lookup with one row per region should use merge(..., on="region", validate="many_to_one"). That validation turns an accidental duplicate lookup key into an error rather than silently multiplying sales rows.
Check the pandas 3 migration notes, indexing guide and resampling reference when adapting this workflow to a larger dataset.