Were bank holidays quieter?
The bike shop owner has one table of hires and a separate list of bank holidays. She asks whether holidays are quieter.
Neither table can answer alone. The hires have no holiday column, and the holiday list has no hires. The answer needs both tables joined on the date they share.
By the end of this lesson
Note
I can join two tables on a shared column and check the join did what I meant.
Key words
| Word | What it means | Example |
|---|---|---|
| Join | put the rows of two tables side by side where a column matches | merge |
| Key | the column both tables share, used to match rows | date |
| Left join | keep every row of the first table, matched or not | |
| Inner join | keep only the rows that match in both tables | |
| Unmatched | a row with no partner in the other table | |
| Unique | appearing only once in a column |
The cells students fill in are left blank in this preview.
Two tables, one shared column
Data is often kept in several small tables, so a fact is written once. A holiday list is written once, not copied onto 365 rows.
To use both, you join them. Pick a column both tables share, the key. Each row of one table finds the rows of the other with the same key value.
Make the second table
Press Start on the lesson bar and wait until the lab is running. The terminal has no Python, so each Run starts one command in the lab with docker exec jupyter.
The lab holds only hires.csv. The first Run writes a second file, holidays.csv, with eight bank holidays of 2025.
Write the holiday list
docker exec jupyter sh -c "printf '%s\n' 'date,holiday' '2025-01-01,New Year' '2025-04-18,Good Friday' '2025-04-21,Easter Monday' '2025-05-05,Early May' '2025-05-26,Spring' '2025-08-25,Summer' '2025-12-25,Christmas' '2025-12-26,Boxing Day' > holidays.csv"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Read it back
docker exec jupyter cat holidays.csvShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A left join keeps every row
A left join starts from the first table. Every row stays. If a row finds a partner, the new columns are filled in. If not, they are left empty.
This is the safe choice for adding information to a table. You cannot lose a day by adding a column.
Worked example: three days joined
| date | hires | holiday |
|---|---|---|
| 2025-04-18 | 166 | Good Friday |
| 2025-04-19 | 247 | (empty) |
| 2025-04-21 | 232 | Easter Monday |
The cells students fill in are left blank in this preview.
Join and show the holidays
docker exec jupyter python3 -c "import pandas as pd; h=pd.read_csv('hires.csv'); b=pd.read_csv('holidays.csv'); j=h.merge(b, on='date', how='left'); print(len(h), len(b), len(j)); print(j['holiday'].notna().sum()); print(j[j['holiday'].notna()][['date','weekday','holiday','hires']].to_string(index=False))"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A join worked out by hand in Python
# Join by hand: each hires row looks for its date in the holidays.
hires = [["2025-04-18", 166], ["2025-04-19", 247], ["2025-04-21", 232]]
holidays = {"2025-04-18": "Good Friday", "2025-04-21": "Easter Monday"}
for row in hires:
name = "none"
if row[0] in holidays:
name = holidays[row[0]]
print(row[0], row[1], name)
# Add "2025-04-19": "Saturday" to holidays and run it again.
Left, inner and outer
An inner join keeps only the rows that match on both sides. An outer join keeps every row from both tables, matched or not.
Which one you pick changes the row count, and so every number you work out afterwards. Say which join you used and why.
Count the rows of each kind of join
docker exec jupyter python3 -c "import pandas as pd; h=pd.read_csv('hires.csv'); b=pd.read_csv('holidays.csv'); print(len(h.merge(b, on='date', how='inner')), len(h.merge(b, on='date', how='left')), len(h.merge(b, on='date', how='outer')))"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Group, then read each group's size
Once joined, ask the question. Mark each day as a bank holiday or not, keep the weekdays, and take the mean of hires for each.
Look at how many days are in each group. A mean over eight days is much weaker evidence than a mean over 253.
Compare holiday weekdays with ordinary ones
docker exec jupyter python3 -c "import pandas as pd; h=pd.read_csv('hires.csv'); b=pd.read_csv('holidays.csv'); j=h.merge(b, on='date', how='left'); j['bank']=j['holiday'].notna(); w=j[j['weekday'].isin(['Mon','Tue','Wed','Thu','Fri'])]; print(w.groupby('bank')['hires'].agg(['count','mean']).round(1))"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A repeated key multiplies rows
If the key repeats in the second table, a row of the first matches each copy and is written once for each. The table grows, and every total after it is too big.
Pandas can check for this. Pass validate="one_to_one" and it stops with an error if a key is not unique.
Repeat a holiday and join again
docker exec jupyter python3 -c "import pandas as pd; h=pd.read_csv('hires.csv'); b=pd.read_csv('holidays.csv'); b=pd.concat([b, b.iloc[[0]]]); print(len(h.merge(b, on='date', how='left')), b['date'].is_unique)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Ask pandas to refuse it
docker exec jupyter python3 -c "import pandas as pd; h=pd.read_csv('hires.csv'); b=pd.read_csv('holidays.csv'); b=pd.concat([b, b.iloc[[0]]]); h.merge(b, on='date', how='left', validate='one_to_one')" 2>&1 | tail -n 1Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think a join cannot change the number of rows if it is a left join. It can grow. A left join never loses a row of the first table, but a repeated key makes copies of it. Compare the row counts before and after.
Going further: the day after
In your notebook, join the holidays to hires.csv again. Then work out the mean hires on the day after each holiday.
Hint: add one day to each holiday date with pd.to_datetime(...) + pd.Timedelta(days=1) before you join. Say what you expect first.