Pandas Guide: DataFrames, Cleaning and Aggregation

Real data arrives messy: stray spaces, inconsistent capitals, numbers stored as text, duplicated rows, and blanks that mean "unknown". pandas has a tool for each problem. The skill is applying them in an order that never hides a problem from you. This guide works through one small but realistic sales export from raw file to summary tables, checking the data at every step.

Load it, then look before you touch

import io
import pandas as pd

raw = io.StringIO("""order_id,order_date,city,category,amount
1001,2026-08-01, hyderabad ,Books,"1,250.00"
1002,2026-08-01,Pune,books,480
1003,2026-08-02,HYDERABAD,Electronics,
1003,2026-08-02,HYDERABAD,Electronics,
1004,2026-08-03,Chennai,Electronics,15999
1005,2026-08-03,pune,Toys,n/a
1006,2026-08-04,Chennai,Books,899.5
""")
df = pd.read_csv(raw, parse_dates=["order_date"])
print(df.shape)
print(df.dtypes)
print(df.isna().sum())

(7, 5)
order_id               int64
order_date    datetime64[ns]
city                  object
category              object
amount                object
dtype: object
order_id      0
order_date    0
city          0
category      0
amount        3
dtype: int64

io.StringIO stands in for a file here; with a real export you would pass the path. Two findings already matter:

• The amount column has dtype object, meaning text, not a number. One value, "1,250.00", has a thousands separator, so pandas could not parse the column as numeric.
• The amount column has three missing values, not two. The two blank fields are missing, and so is n/a: read_csv treats a list of common markers, including n/a, NA and the empty string, as missing by default (pandas docs: read_csv). That is usually helpful, but check the list against your data. A product literally named "NA" would vanish.

parse_dates turned order_date into real datetimes on the way in, which is always easier than converting later. For large files, also pass dtype= for every column, so pandas does not have to guess.

Clean, one problem at a time

df = df.drop_duplicates()
df["city"] = df["city"].str.strip().str.title()
df["category"] = df["category"].str.title()
df["amount"] = pd.to_numeric(df["amount"].str.replace(",", ""), errors="coerce")
print(df)
print("missing amounts:", df["amount"].isna().sum())

   order_id order_date       city     category   amount
0      1001 2026-08-01  Hyderabad        Books   1250.0
1      1002 2026-08-01       Pune        Books    480.0
2      1003 2026-08-02  Hyderabad  Electronics      NaN
4      1004 2026-08-03    Chennai  Electronics  15999.0
5      1005 2026-08-03       Pune         Toys      NaN
6      1006 2026-08-04    Chennai        Books    899.5
missing amounts: 2

Each line fixes one thing:

• drop_duplicates() removed the repeated order 1003 (row 3). It compares whole rows by default; pass subset=["order_id"] when the same key should never appear twice, even if other columns differ.
• .str.strip().str.title() turned " hyderabad ", "HYDERABAD" and "pune" into consistent values. Without it, a groupby would report three different Hyderabads.
• pd.to_numeric(..., errors="coerce") converted the cleaned text to numbers and turned anything unparseable into NaN instead of raising (pandas docs: to_numeric). Here the separator could also have been handled at load time with read_csv(..., thousands=",").

The last line is the important habit: count what you coerced. errors="coerce" is a quiet operation, and "2 missing" is a fact to report, not to paper over.

Decide what missing means

There is no universally right way to handle missing values; there is only the right way for the question. Two orders have no amount. For a revenue report, dropping them and saying so is honest. For an order count, they still count. Filling them with 0 would claim those orders were free, and filling with the mean would invent revenue. Keep missing values missing until a specific calculation needs a decision, and make that decision visibly in that calculation.

Summarise with groupby and named aggregations

groupby splits the rows into groups, applies a function to each, and combines the results (pandas docs: group by). Named aggregations give each output column a clear name:

summary = (df.dropna(subset=["amount"])
             .groupby("city")
             .agg(orders=("order_id", "count"),
                  revenue=("amount", "sum"),
                  avg_order=("amount", "mean"))
             .sort_values("revenue", ascending=False))
print(summary.round(2))

           orders  revenue  avg_order
city                                 
Chennai         2  16898.5    8449.25
Hyderabad       1   1250.0    1250.00
Pune            1    480.0     480.00

The dropna is the missing-value decision made explicitly, for this table only: revenue per city over orders with a known amount. A reader of this table should be told that two orders were excluded.

Pivot tables, and how fill_value can hide a gap

A pivot table puts one grouping in the rows and another in the columns (pandas docs: pivot_table):

print(df.pivot_table(index="city", columns="category", values="amount", aggfunc="sum", fill_value=0))

category    Books  Electronics  Toys
city                                
Chennai     899.5      15999.0   0.0
Hyderabad  1250.0          0.0   0.0
Pune        480.0          0.0   0.0

Look at Hyderabad's electronics: 0.0. There was an electronics order in Hyderabad; its amount is unknown. fill_value=0 makes "no sales", "no data" and "unknown amount" look identical. Leave fill_value out while you explore, so gaps show as NaN, and add it only for a final report where zeros genuinely mean zero.

Add reference data with a merge that checks itself

Joining a lookup table is where rows silently multiply or disappear. merge can check both problems for you (pandas docs: merging):

regions = pd.DataFrame({"city": ["Hyderabad", "Chennai", "Pune"], "region": ["South", "South", "West"]})
merged = df.merge(regions, on="city", how="left", validate="many_to_one", indicator=True)
print(merged["_merge"].value_counts().to_dict())
print(merged.groupby("region")["order_id"].count().to_dict())

{'both': 6, 'left_only': 0, 'right_only': 0}
{'South': 4, 'West': 2}

• validate="many_to_one" raises an error if a city appears twice in the lookup table, which would otherwise duplicate orders.
• indicator=True adds a _merge column; any left_only rows are orders whose city had no match, perhaps a spelling the cleaning step missed.

Because of the earlier str.title(), every city matched. Without it, "HYDERABAD" would have come through as left_only.

Turn your assumptions into checks

Cleaning code encodes assumptions: order IDs are unique, amounts are never negative, every city is one you know. Write them down as checks that run every time, so next month's file cannot quietly break them:

• df["order_id"].is_unique should be True after deduplication.
• (df["amount"].dropna() >= 0).all() catches refunds or sign errors that slipped into a sales column.
• set(df["city"]) <= known_cities flags a new spelling before it becomes a separate row in every report.
• The row count after a merge should equal the row count before it.

A failed check should stop the script with a clear message rather than print a warning nobody reads. In scheduled pipelines, the same idea grows into data quality tests; see data quality testing and validation in pipelines.

The workflow, in order

1. Load with explicit dates and types; inspect shape, dtypes and isna().sum().
2. Remove exact duplicates; standardise text columns.
3. Convert numbers with errors="coerce", then count what failed.
4. Decide about missing values per calculation, not globally.
5. Aggregate with named agg; pivot without fill_value until the final report.
6. Merge with validate and indicator, and check the counts.

When the data grows to millions of rows, the same steps need attention to speed and memory; pandas performance covers that. pandas is built on NumPy arrays, explained in the NumPy guide, and the wider set of data libraries is in Python for data science: the essential libraries. NumPy basics, pandas DataFrames and data cleaning are part of the data handling module of the Python Programming course.

Sources

• pandas.read_csv — pandas documentation
• pandas.to_numeric — pandas documentation
• Group by: split-apply-combine — pandas documentation
• Merge, join, concatenate and compare — pandas documentation
• pandas.pivot_table — pandas documentation