Python Intermediate Lesson 5: pandas in Depth – Merge, GroupBy and Pivot Tables

📎 This article includes 3 downloadable practice files ↓

📘 Python Intermediate Course · Lesson 5 of 10

1. Clean types first

sales["date"] = pd.to_datetime(sales["date"], format="%Y-%m-%d", errors="coerce")
sales["qty"]  = pd.to_numeric(sales["qty"], errors="coerce")
sales = sales.dropna(subset=["date", "qty"])     # the 2 bad rows become NaT/NaN, then go

2. Merge (the pandas VLOOKUP)

prod = prod.rename(columns={"name": "product"})   # customers also have "name"!
df = sales.merge(cust, on="customer_id", how="left").merge(prod, on="code", how="left")

3. GroupBy with named aggregations

df.groupby("region").agg(invoices=("invoice", "count"),
                         amount=("amount", "sum"),
                         avg=("amount", "mean")).round(0)

4. Pivot tables

df["month"] = df["date"].dt.strftime("%Y-%m")
df.pivot_table(index="category", columns="month", values="amount", aggfunc="sum", fill_value=0)

Practice

Unzip the practice data into a folder, put this lesson’s script in the same folder and run it (python pyi-NN.py). Compare with the expected output file. Then change one thing (a filter, a column, a format) and predict the new output before you run it.