Why Pandas Eliminates More Candidates Than They Expect

import pandas as pd

orders = pd.read_csv('orders.csv')
customers = pd.read_csv('customers.csv')

print('orders rows:', len(orders))
print('dup customer ids:', customers['customer_id'].duplicated().sum())

# Fail loudly if customers has duplicate keys
merged = orders.merge(customers, on='customer_id',
                      how='left', validate='many_to_one',
                      indicator=True)

print('merged rows:', len(merged))          # must equal orders rows
print(merged['_merge'].value_counts())      # left_only = no customer

assert len(merged) == len(orders)

import pandas as pd

# If validate fails, inspect the duplicates first
dups = customers[customers['customer_id'].duplicated(keep=False)]
print(dups.sort_values('customer_id').head(10))

# Decide a rule and write it down, for example:
# keep the most recently updated record per customer
customers = (customers
             .sort_values('updated_at')
             .drop_duplicates(subset='customer_id', keep='last'))

# In the notebook, add a markdown note:
# 'Customers had duplicate IDs. Kept the latest
#  record per ID, based on updated_at.'

df1 = df[df['status'] != 'cancelled']
df2 = df1.merge(cust, on='cid')
for i, r in df2.iterrows():
    if r['amount'] > 5000:
        df2.loc[i, 'big'] = 1
    else:
        df2.loc[i, 'big'] = 0
df3 = df2.groupby('city').sum()
df3

# Reviewer questions:
# Which join type? What happened to unmatched rows?
# Why sum every column, including IDs?
# Why a loop for a simple flag?

import numpy as np

active = orders.query("status != 'cancelled'")
print('active orders:', len(active))

with_cust = active.merge(customers, on='customer_id',
                         how='left', validate='many_to_one')
assert len(with_cust) == len(active)

with_cust['is_big'] = np.where(with_cust['amount'] > 5000, 1, 0)

city_summary = (with_cust
                .groupby('city', as_index=False)
                .agg(orders=('order_id', 'count'),
                     revenue=('amount', 'sum'),
                     big_orders=('is_big', 'sum')))
city_summary.sort_values('revenue', ascending=False)

import pandas as pd

df = pd.read_csv('sales_messy.csv')
print(df.dtypes)

df['amount'] = pd.to_numeric(
    df['amount'].astype(str).str.replace(r'[^0-9.]', '', regex=True),
    errors='coerce')

df['order_date'] = pd.to_datetime(df['order_date'],
                                  format='%d/%m/%Y', errors='coerce')

df['city'] = df['city'].str.strip().str.title()

print(df[['amount', 'order_date']].isna().sum())
print(df['order_date'].min(), df['order_date'].max())

# Q: Total sales per month

monthly = (df.dropna(subset=['amount', 'order_date'])
             .groupby(df['order_date'].dt.to_period('M'))['amount']
             .sum())
print(monthly)

# What to say out loud:
# 'I dropped rows where amount or date could not be
#  parsed. There were N of them, which I would report
#  to the business rather than hide.'

# Why the format matters: without format='%d/%m/%Y',
# a date like 05/03/2025 can be read as 3 May
# instead of 5 March.

TAKE-HOME PREPARATION

Pick a public dataset with 2-3 related tables.
Write 5 business questions yourself.

For every merge:
[ ] rows before and after printed
[ ] validate= argument used
[ ] indicator=True checked for unmatched rows

For every headline number:
[ ] recomputed a second way (e.g. pivot_table)
[ ] written in a markdown cell with the logic

Final: Restart & Run All. Fix anything that fails.

BEFORE SHARING ANY NOTEBOOK

[ ] Title cell: question, data, key findings
[ ] Imports and file paths in the first cell
[ ] Variable names describe the stage
    raw_orders -> clean_orders -> monthly_sales
[ ] No iterrows for simple logic
[ ] Shape or null check after each major step
[ ] Markdown note for every assumption
[ ] Charts have titles and axis labels
[ ] Runs top to bottom on a fresh kernel

20-MINUTE DRILL (do it three times)

Minute 0-2   df.shape, df.dtypes, df.head()
Minute 2-4   df.isna().sum(), df.duplicated().sum()
Minute 4-10  Fix numbers, dates, text columns
Minute 10-12 Count values lost to coercion
Minute 12-17 Answer the business question
Minute 17-20 Say the answer and the caveats

Make your own messy file: add currency signs,
mixed date formats, extra spaces and 'NA' text.