Week 7: Joining tables

STAT163 — Data Manipulation Essentials

Oleh Omelchenko

2026-10-12

Pre-lecture quiz

Today

  • 1. Joining two tables
  • 2. When joins drop rows
  • 3. When joins add rows
  • 4. Keys and identity

1. Joining two tables

The plan is in another table

members: the gym's member list

member_id name plan status
M-01 O. Symonenko pro active
M-02 I. Danylko basic active
M-03 K. Fedorak pro frozen
M-04 Y. Herasym basic active
M-05 L. Kostiuk day active
M-06 P. Zinchuk pro cancelled
M-07 H. Melnychuk basic active
M-08 R. Oliynyk pro active

visits: the entrance gate log, June

visit_id member_id date minutes
V-901 M-01 2026-06-01 75
V-902 M-03 2026-06-01 40
V-903 M-01 2026-06-03 80
V-904 M-05 2026-06-03 55
V-905 M-77 2026-06-04 60
V-906 M-02 2026-06-05 35
V-907 M-01 2026-06-08 70
V-908 M-05 2026-06-09 50
V-909 M-02 2026-06-10 45
V-910 M-08 2026-06-11 90

Your answer

How long is a visit on each plan, on average? What do you need from each table to answer it?

A join by member_id

visits members visit_id minutes member_id V-901 75 M-01 V-903 80 M-01 V-902 40 M-03 V-904 55 M-05 V-910 90 M-08 member_id plan M-01 pro M-02 basic M-03 pro M-04 basic M-05 day M-06 pro M-07 basic M-08 pro visits.merge(members, on="member_id") visit_id minutes member_id plan V-901 75 M-01 pro V-903 80 M-01 pro V-902 40 M-03 pro V-904 55 M-05 day V-910 90 M-08 pro

Each row of the result is a visit with its member's plan added. Grouped by plan, all June visits average 71 minutes on pro, 40 on basic and 52.5 on day.

Three questions to the same tables

  1. How long is a visit on each plan, on average?
  2. Which members did not come at all in June?
  3. Do all visits belong to known members?

Your answers

For each question: which rows must you keep in the result, and which rows can you lose?

Four ways to keep rows

visits (left): M-77 M-01 M-05 members (right): M-01 M-05 M-04 M-77 only in visits, M-01 and M-05 in both, M-04 only in members how="inner" only rows with a match M-01 M-01 M-05 M-05 2 rows: M-77 and M-04 are gone how="left" every left row stays M-77 NaN M-01 M-01 M-05 M-05 3 rows: M-04 is gone how="right" every right row stays M-01 M-01 M-05 M-05 NaN M-04 3 rows: M-77 is gone how="outer" nothing is lost M-77 NaN M-01 M-01 M-05 M-05 NaN M-04 4 rows, with NaN on both sides

Choose how= before you write the join, by asking which rows you must not lose. With inner you get matched rows only, with left or right every row of one table, and with outer every row of both.

Fact tables and lookup tables

In a database, most data is stored in two kinds of tables. A fact table has one row per event: a visit, a payment, an order. A lookup table has one row per thing the events refer to: a member, a product, a shop.

visits: a fact table members: a lookup table visit_id member_id V-901 M-01 V-903 M-01 V-907 M-01 V-906 M-02 V-902 M-03 V-904 M-05 member_id plan M-01 pro M-02 basic M-03 pro M-05 day no match: the visit drops out V-905 M-77 M-05 pro M-05 changed plan: V-904 is counted twice

A join is safe when every key in the fact table is in the lookup table exactly once.

2. When joins drop rows

Members with no visits

visits member_id M-01 M-03 M-01 M-05 M-77 M-02 M-01 M-05 M-02 M-08 count rows per member visits per member member_id visits M-01 3 M-02 2 M-03 1 M-05 2 M-08 1 M-77 1 join with members joined member_id plan visits M-01 pro 3 M-02 basic 2 M-03 pro 1 M-05 day 2 M-08 pro 1 M-77 is not in members 5 members, 9 visits

The average is 9 / 5 = 1.8 visits per member in June.

Your guess

The gym has 8 members. Is 1.8 the average number of visits per member?

member_id visits _merge
M-01 3 both
M-02 2 both
M-03 1 both
M-04 NaN left_only
M-05 2 both
M-06 NaN left_only
M-07 NaN left_only
M-08 1 both

With how="left" from members, the three members who never came stay. With 0 visits for them, the average is 9 / 8 = 1.125.

A visit with no member

members

member_id name
M-01 O. Symonenko
M-02 I. Danylko
M-03 K. Fedorak
M-04 Y. Herasym
M-05 L. Kostiuk
M-06 P. Zinchuk
M-07 H. Melnychuk
M-08 R. Oliynyk

visits

visit_id member_id minutes
V-901 M-01 75
V-902 M-03 40
V-903 M-01 80
V-904 M-05 55
V-905 M-77 60
V-906 M-02 35
V-907 M-01 70
V-908 M-05 50
V-909 M-02 45
V-910 M-08 90

Your ideas

V-905 belongs to M-77, who is not in the member list. Where could this row come from? What would you do with it?

A typo at the front desk, a deleted account, a test record from the gate. Find the rows and look at them first: whether you delete, fix or keep them depends on the cause.

Active members only

Somebody exported the member list again, with a filter: active members only.

members: the filter removes the two red rows

member_id plan status
M-01 pro active
M-02 basic active
M-03 pro frozen
M-04 basic active
M-05 day active
M-06 pro cancelled
M-07 basic active
M-08 pro active

visits of pro members

visit_id member_id minutes
V-901 M-01 75
V-902 M-03 40
V-903 M-01 80
V-907 M-01 70
V-910 M-08 90

average visit, pro plan

member list minutes
all members 71
active members 78.75

Discuss

Which visits dropped out of the join? Should they count in the average?

V-902, the 40-minute visit of M-03, whose membership is frozen. The answer depends on the question: time spent in the gym, or time spent by paying members.

3. When joins add rows

Revenue from orders and parcels

In the analyst's report, June revenue is 7,257.20 €. In the finance department's report, it is 3,571.80 €, about half.

orders: one row per order

order_id placed order_total
O-501 06-01 611.00
O-502 06-03 129.90
O-503 06-05 1264.00
O-504 06-08 89.50
O-505 06-10 432.00
O-506 06-14 55.00
O-507 06-17 780.40
O-508 06-21 210.00

the analyst's code

report = orders.merge(shipments, on="order_id")
revenue = report["order_total"].sum()

Your task

The report has two errors. Find them.

shipments: one row per parcel

shipment_id order_id parcel carrier
S-701 O-501 1/1 DHL
S-702 O-502 1/1 NovaPost
S-703 O-503 1/3 DHL
S-704 O-503 2/3 DHL
S-705 O-503 3/3 UPS
S-706 O-504 1/1 NovaPost
S-707 O-505 1/2 UPS
S-708 O-505 2/2 UPS
S-709 O-507 1/2 DHL
S-710 O-507 2/2 DHL
S-711 O-508 1/1 NovaPost

Two errors in one join

orders shipments after the join: one row per parcel O-503, 1264.00 O-506, 55.00 S-703, O-503 S-704, O-503 S-705, O-503 no parcels O-503, S-703, 1264.00 O-503, S-704, 1264.00 O-503, S-705, 1264.00 ×3 no row in the result 1264.00 went into the sum three times 55.00 is missing from the revenue report 8 orders → 11 result rows, and the sum goes from 3,571.80 to 7,257.20

The code runs and the table looks normal. You see the two errors only in the totals. Rows repeated by a join like this are called fan-out: when the row count grows, the sums grow with it.

Plan changes in the member list

The member list now has a row for every change of plan or status. M-02 moved from basic to pro on 7 June, and M-03 came back from a freeze on 28 May.

members: one row per change

member_id plan status since
M-01 pro active 2026-01-12
M-02 basic active 2026-02-03
M-02 pro active 2026-06-07
M-03 pro frozen 2026-04-15
M-03 pro active 2026-05-28
M-04 basic active 2026-03-05
M-05 day active 2026-04-22
M-06 pro cancelled 2026-05-01
M-07 basic active 2026-05-14
M-08 pro active 2026-06-02

visits joined with members: M-02 and M-03

visit_id date member_id plan status
V-902 06-01 M-03 pro frozen
V-902 06-01 M-03 pro active
V-906 06-05 M-02 basic active
V-906 06-05 M-02 pro active
V-909 06-10 M-02 basic active
V-909 06-10 M-02 pro active

9 visits go into the join, and 12 rows come out.

Discuss

Nothing in the member list is wrong. What does one of its rows stand for now?

The grain of the member list changed: one row is now a member from a date on. We wrote the join as if there were one row per member. For each visit we need the row that was valid on its date: V-902 active, V-906 basic, V-909 pro.

4. Keys and identity

Two lists with no shared key

A company pays for its employees' gym membership. Its HR office sends the gym a list of names, and the gym's manager has to find which employees are members.

members: from the gym

member_id name plan
M-11 Oksana Kovalenko pro
M-12 Oleksiy Bondar basic
M-13 Ivan Melnyk pro
M-14 Iryna Savchuk day

employees: from the company

name department
Kovalenko Oksana Finance
Oleksii Bondar IT
Ivan Melnyk Sales
Ivan Melnyk Logistics
Iryna Savchuk HR

Your answer

Can you join these lists on name? What goes wrong?

Names as keys

  • Oksana Kovalenko and Kovalenko Oksana are one person, with the names in a different order. Oleksiy and Oleksii are two spellings of one name. If you join on name, both pairs are missed.
  • The company has two employees named Ivan Melnyk. If you join on name, member M-13 gets both of them.

That is why each person gets an identifier: a tax number, an employee number, member_id. It has no meaning, so a new name or address does not change it. A name and an address are attributes that come with the key.

Summary

  • Before a join, find out the grain of each table: what one row stands for.
  • Keep track of your rows: count them before and after the join, and look at the rows with no match.

Sources

Data: the gym, the company, the shop and their tables are made up for this lecture.

McKinney, Python for Data Analysis, 3rd ed. — Ch. 8.2. McGregor, Practical Python Data Wrangling and Data Quality — Ch. 6–7.

Questions