Pandas: one problem shape at a time

Look for

• A file too big to load at once
• Read, check shape and types first
• IDs or zip codes that start with zero

Smallest shape

import io
import pandas as pd

csv = "id,amount\n1,10\n2,20\n3,30\n4,40\n"
total = 0
for chunk in pd.read_csv(io.StringIO(csv), chunksize=2):
    total += chunk["amount"].sum()
result = pd.DataFrame({"total": [total]})

The usual trap

pandas guesses types, and a guess can lose data. A zip code 01234 is read as the number 1234. Passing dtype={'zip': str} keeps 01234. Name the types of identifier columns when you read.

Not this

Chunking is for data bigger than memory. If it fits, read it once and use the normal DataFrame operations.

Look for

• Dates stored as text
• Names with extra spaces or mixed case
• A numeric code inside a string

Smallest shape

import pandas as pd

df = pd.DataFrame({"when": ["05/03/2024", "15/03/2024", "bad"], "name": ["  Asha  ", "ben", None]})
df["when"] = pd.to_datetime(df["when"], format="%d/%m/%Y", errors="coerce")
df["name"] = df["name"].str.strip().str.title()
result = df

The usual trap

Without a format, 05/03/2024 is read as May 3rd (month first). In the check, pd.to_datetime('05/03/2024').month is 5, and with format='%d/%m/%Y' it is 3. Always state the format for dates written as day/month.

Not this

Casting changes the type of a value. Cleaning fixes the text itself. A column often needs cleaning first (trim, lowercase) and casting second.

Look for

• 1 row per group
• Each row's share of its group's total
• Compare a row with its group's average

Smallest shape

import pandas as pd

sales = pd.DataFrame({"region": ["US", "US", "EU"], "amount": [10.0, 30.0, 5.0]})
sales["share_of_region"] = (
    sales["amount"] / sales.groupby("region")["amount"].transform("sum")
).round(2)
result = sales

The usual trap

agg shrinks the table and transform does not. On 3 rows in 2 regions, groupby().agg() returns 2 rows (EU 5, US 40), while transform('sum') returns a value for each of the 3 rows, so you can divide each row by its group's total.

Not this

If you only need 1 row per group, agg is enough. Use transform when you still need every original row next to its group value.

Look for

• Add columns from another table
• Keep customers who have no orders
• Match each row with the closest earlier row (an as-of join)

Smallest shape

import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3], "cust": ["a", "b", "z"]})
cust = pd.DataFrame({"cust": ["a", "b", "b"], "tier": ["gold", "silver", "silver"]})
result = orders.merge(cust, on="cust", how="left")

The usual trap

A duplicate key on the right side repeats the left row. Customer b appears twice in cust, so order 2 appears twice in the result (4 rows from 3 orders). Adding validate='m:1' makes pandas raise MergeError instead of repeating silently.

Not this

Concatenating stacks rows from tables with the same columns. A merge adds columns by matching keys.

Look for

• The most recent record of each customer
• 1 row per key
• Keep the newest version

Smallest shape

import pandas as pd

df = pd.DataFrame({
    "cust": ["a", "a", "b"],
    "updated": ["2024-01-01", "2024-03-01", "2024-02-01"],
    "city": ["Pune", "Goa", "Agra"],
})
result = (
    df.sort_values("updated")
    .drop_duplicates("cust", keep="last")
    .sort_values("cust")
    .reset_index(drop=True)
)

The usual trap

drop_duplicates keeps the first row it meets. Without sorting by date first, customer a keeps the January row (Pune), not the March row (Goa). Sort, then keep='last'.

Not this

groupby().last() also gives 1 row per key, but it takes the last value of each column separately and skips missing values. Sorting then drop_duplicates keeps whole rows.

Look for

• Long data that should be wide
• Days or months as columns
• Wide columns that should become rows

Smallest shape

import pandas as pd

long = pd.DataFrame({"day": ["d1", "d1", "d2"], "item": ["x", "y", "x"], "qty": [1, 2, 3]})
result = long.pivot_table(index="day", columns="item", values="qty", aggfunc="sum").reset_index()

The usual trap

pivot needs each day and item to appear once. With 2 rows for d1 and x, long.pivot(...) raises: Index contains duplicate entries, cannot reshape. pivot_table with an aggfunc combines the duplicates instead.

Not this

melt goes the other way, from wide to long. If a prompt starts from many columns and wants one value column, it is melt, not pivot.

Look for

• Some readings are missing
• Fill from the previous value
• Drop rows that cannot be used

Smallest shape

import pandas as pd

df = pd.DataFrame({"device": ["a", "a", "b", "b"], "reading": [1.0, None, None, 4.0]})
df["reading"] = df.groupby("device")["reading"].ffill()
result = df

The usual trap

A fill that ignores the group borrows from another one. A plain ffill on the whole column gives device b's first row the value 1.0, which belongs to device a. Filling inside groupby('device') leaves it missing.

Not this

Dropping removes the row. Filling invents a value. Neither is neutral, so say in the answer which one you chose and why.

Look for

• Return the rows that break a rule
• Check a count or a bound
• A gate that must fail the pipeline

Smallest shape

import pandas as pd

df = pd.DataFrame({"id": [1, 2, 3], "reading": [0.5, None, 2.0]})
result = df[df["reading"].isna()]

The usual trap

count() and len() disagree when values are missing. For 3 rows with one missing reading, len(df) is 3 and df['reading'].count() is 2. An empty result means the check passed, so test it once on data you know is bad.

Not this

Cleaning fixes bad rows. A quality gate returns or reports them, and in a real pipeline stops the run when the result is not empty, so a person can decide.