DT1: Python and pandas for messy data
Joining and reshaping without surprises
🎯You'll be able to
Join tables with merge while checking their relationship, find rows that didn't match, and group and pivot data to answer a question.
🏠The everyday picture
Matching a guest list to a seating plan, you check two things: everyone on the list has a seat, and nobody has been given two seats.
📘What it really is
mergejoins tables on key columns.how='left'keeps every row of the left table, matched or not.-
Check the relationship:
validate='many_to_one'makes pandas raise an error if the right table has a repeated key. Without it, a repeated product in the product list silently turns one order line into two, and revenue doubles for that product. -
Find what didn't match:
indicator=Trueadds a_mergecolumn withboth,left_onlyorright_only. - Count before and after: a left join should keep the row count of the left table. If it grows, a key repeated on the right.
-
Grouping:
df.groupby('store')['amount_paise'].sum()totals per store.df.groupby('store')['order_id'].nunique()counts orders, not lines. -
Reshaping:
pivot_table(index='order_date', columns='store', values='amount_paise', aggfunc='sum')puts stores side by side;meltdoes the reverse.
📝A worked example
Input:
p = pd.read_json('raw/products.json')
m = df.merge(p, on='sku', how='left', validate='many_to_one', indicator=True)
print(len(df), len(m), m['_merge'].value_counts())
Expected output: 317 317, with 316 rows both and 1 left_only. That one line has the code SKU-999, which isn't in the product
list: a row for quarantine, not for revenue.
🌍Where it's used
- Sales reports: order lines joined to products and stores.
- Customer analysis: orders joined to customer areas (with care for personal data, DT5 and DT6).
- Finance: bank statements matched to invoices, with the unmatched ones listed for follow-up.
📝Case scenario
An online bookshop's report showed one author's sales doubling overnight. The product list had gained a second row for the same book
code after a price change. The join copied every order line of that book. Adding validate='many_to_one' turned the silent copy into
an error on the next run.
🖱️Try it
Count orders (not lines) per store for 5 October with nunique, and lines per store with size. Success: you can explain why the two
sets of numbers differ.
⚠️Common mistakes
- Joining without checking keys. Sign: totals grow after a join. Fix:
validate=and a row count before and after. - Inner joins that drop rows quietly. Sign: fewer rows and no message. Fix: left join with
indicator=True, and report the unmatched. - Counting rows when you mean orders. Sign: "orders" match the line count. Fix:
nuniqueon the order id.
🛠️For your project
Project 1 quarantines the unknown products this lesson finds; Project 2 joins orders to customers and must keep one row per order line.
✅Quick check
What does validate='many_to_one' protect you from?
Show the answer
A repeated key on the right side silently copying rows on the left.
How do you count orders in a table of order lines?
Show the answer
Count distinct order ids, for example with nunique().