Chapter 7 · 6 levels

Merging tables

Combine Harbor & Pine's tables with merge, handle unmatched rows with left joins and indicator, and avoid the duplicate-row fan-out trap.

Start

Real questions cross tables. “Revenue by category” needs order lines (for revenue) and products (for the category). “Customers who never ordered” needs customers and orders. “Refunds by channel” needs returns, order lines and orders. The data is split up on purpose, so each fact is stored once, and merge is how you bring it back together.

Merging is also where the most expensive mistakes happen. Join on the wrong key, or join a table where one row matches many, and your totals quietly double. Nothing errors. The numbers just look a bit high. This chapter teaches the mechanics (how=, on=, suffixes, indicator) and, more importantly, the habits that keep merges honest: know each table’s grain, check row counts before and after, and use validate= to make pandas check your assumptions for you.

Levels

  1. Add product names to order lines

    from Angela Rivera, Head of Customer Support

    merge, inner join, on, suffixes, row count check

    Code

  2. Customers who never ordered

    from Logan Brooks, Email Marketing Specialist

    left join, indicator, anti join, isin

    Code

  3. Revenue by category, completed orders only

    from Laura Bennett, Head of Finance

    merge, multi table, groupby, filtering

    Code

  4. Why did shipping revenue double?

    from Laura Bennett, Head of Finance

    fan out, grain, merge

    Quiz

  5. Shipping fees on lighting orders

    from Daniel Brooks, Head of Operations

    fan out, grain, isin, unique, merge

    Code

  6. Boss: where do returns come from?

    from Angela Rivera, Head of Customer Support

    merge, multi table, groupby, rates, validate

    Boss · 3 steps