Level 8 · Products that sold in only one quarter

Four kinds of merge

how= decides which keys survive a merge:

• "inner" (the default): only keys found on both sides.
• "left": every key from the left table, with NaN where the right has no match.
• "right": the mirror image, every key from the right table. a.merge(b, how="right") keeps the same rows as b.merge(a, how="left"), only the column order differs, so most people just write the left version.
• "outer": every key from either side, with NaN wherever one side is missing.

Thomas wants products missing from either quarter, so only an outer merge keeps everything he needs.

Units per quarter, then outer

First reduce each quarter to one row per product. q1[["order_id"]] keeps only the order ids, so merging with order_items brings in the lines of Q1 orders and nothing else:

q1_units = q1[["order_id"]].merge(order_items, on="order_id").groupby(
    "product_id", as_index=False
).agg(q1_units=("quantity", "sum"))

107 products sold in Q1 and 111 in Q2. The outer merge gives 116 rows:

both = q1_units.merge(q2_units, on="product_id", how="outer", indicator=True)
both["_merge"].value_counts()   # both 102, right_only 9, left_only 5

indicator=True adds the _merge column, which says where each row came from. It’s the easiest way to filter the “only on one side” rows, and it doubles as a check: 102 + 9 + 5 = 116.

Reading the result

The five Q1-only products include the Sable Oak Daybed, the Elm Cashmere-Blend Area Rug and the Cedar Ceramic Candle, all discontinued. The nine Q2-only products include the Rowan Walnut Dresser (launched 2025-04-21) and the Pacifica Black Metal Arc Lamp (2025-04-05), plus garden beds, grill tools and planters that only sell once outdoor season starts. A product missing from Q2 shows NaN in q2_units, not 0: pandas can’t know whether “no row” means zero sales or missing data, so it leaves that decision to you.

Common mistakes

• Using the default inner merge, which throws away exactly the one-sided products.
• Merging the raw order lines of the two quarters instead of per-product totals, which fans out.
• Filtering on q2_units == 0 to find missing products. Missing shows up as NaN, not 0.
• Forgetting to drop the _merge column from the final table.