⚙️ Setup¶
import numpy as np
import pandas as pd
rng = np.random.default_rng(232)
print("pandas", pd.__version__)
pandas 3.0.3
products = pd.DataFrame({"product_id":[1,2,3], "product":["Pen","Mug","Hat"]})
orders = pd.DataFrame({"order":[10,11,12,13], "product_id":[1,2,2,99]})
inner = orders.merge(products, on="product_id", how="inner")
left = orders.merge(products, on="product_id", how="left")
print(f"orders: {len(orders)} | inner: {len(inner)} | left: {len(left)}")
print("\nleft join (order 13 has product_id 99, no match -> product is NaN):")
print(left)
orders: 4 | inner: 3 | left: 4 left join (order 13 has product_id 99, no match -> product is NaN): order product_id product 0 10 1 Pen 1 11 2 Mug 2 12 2 Mug 3 13 99 NaN
Answer: The inner join keeps only orders whose product_id exists in products, so order 13 (product 99) is dropped (3 rows). The left join keeps all 4 orders and fills the missing product with NaN for order 13. NaN appears on the right-side columns wherever the left key has no match, which is exactly how a left join flags orphaned rows instead of silently losing them.
wide = pd.DataFrame({"store":["A","B"], "Q1":[10,7], "Q2":[12,9], "Q3":[15,8]})
long = wide.melt(id_vars="store", var_name="quarter", value_name="sales")
print("WIDE:"); print(wide)
print("\nLONG:"); print(long)
WIDE: store Q1 Q2 Q3 0 A 10 12 15 1 B 7 9 8 LONG: store quarter sales 0 A Q1 10 1 B Q1 7 2 A Q2 12 3 B Q2 9 4 A Q3 15 5 B Q3 8
Answer: melt(id_vars="store", var_name="quarter", value_name="sales") collapses the quarter columns into two: a quarter column of names and a sales column of values, keeping store as the identifier. The 2x3 wide grid becomes 6 tidy rows, each one observation, which is the shape plotting and modeling tools expect (build on the tidy-data idea from Chapter 18).
logs = pd.DataFrame({
"region":["N","N","S","S","N"],
"month": ["Jan","Jan","Jan","Feb","Feb"],
"amount":[10, 5, 8, 12, 7],
})
grid = logs.pivot_table(index="region", columns="month", values="amount", aggfunc="sum", margins=True)
print(grid)
month Feb Jan All region N 7 15 22 S 12 8 20 All 19 23 42
Answer: pivot_table(..., aggfunc="sum", margins=True) sums the duplicate region-month entries (North/Jan has two rows, 10 and 5, which combine to 15) and margins=True adds the All totals. Plain pivot would raise a ValueError here because the region-month pairs are not unique; pivot_table is the version that aggregates, so reach for it whenever combinations can repeat.
raw = pd.Series(["2022-01-05","15/02/2022","2022/03/20","oops","2022-04-01"])
d = pd.to_datetime(raw, errors="coerce", format="mixed")
print(f"failed to parse (NaT): {d.isna().sum()}")
out = pd.DataFrame({"date":d, "month":d.dt.month_name(), "weekday":d.dt.day_name()})
print(out)
failed to parse (NaT): 1
date month weekday
0 2022-01-05 January Wednesday
1 2022-02-15 February Tuesday
2 2022-03-20 March Sunday
3 NaT NaN NaN
4 2022-04-01 April Friday
Answer: to_datetime(..., errors="coerce", format="mixed") parses the mixed formats and turns the unparseable "oops" into NaT (1 failure, which you count with .isna().sum(), tying back to Chapters 19 and 20). From the valid datetimes the .dt accessor extracts features: dt.month_name() and dt.day_name(). Those extracted parts are exactly the kind of features you engineer in Chapter 24.
s = pd.Series(["Order #1042 - Austin, TX", "Order #88 - Reno, NV", "Order #730 - Miami, FL"])
order_id = s.str.extract(r"#(\d+)").astype(int)
place = s.str.split(" - ").str[1] # the part after " - "
city_state = place.str.split(",", expand=True)
out = pd.DataFrame({"order_id": order_id[0],
"city": city_state[0].str.strip(),
"state": city_state[1].str.strip()})
print(out)
order_id city state 0 1042 Austin TX 1 88 Reno NV 2 730 Miami FL
import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(6, 3))
ax.bar(out["city"], out["order_id"], color="#0891b2")
ax.set(title="Parsed from one messy text column: order id by city", ylabel="order id")
plt.tight_layout(); plt.show()
Answer: str.extract(r"#(\d+)") pulls the digits after the hash into the order_id; str.split(" - ").str[1] grabs the location, and str.split(",", expand=True) splits it into city and state columns (remember to .str.strip() the pieces, the part after the comma keeps its leading space). The .str accessor lets you do all of this across the whole column at once, no loop required.