Putting a Join to Work
Overview
Two files, one question, and the checks that keep the answer honest.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Take two files through to one summarised answer.
- Put the checks in the right places.
- Summarise last, after the joining and the cleaning.
- Report the size of what you summarised.
Why This Matters
This is the unit assembled: read, check the keys, join, check the row count, then summarise. The order matters, because every check is cheap before the join and expensive afterwards — once a duplicate key has multiplied your rows, the total is wrong and nothing in the output says so.
Step 1: The shape of a real answer
Read both. Check the keys. Join with the how the question implies. Check the row count. Only then summarise.
Read, and look
Two files, four checks each — the habit from unit 5. The key columns get one extra look, because they are what the join depends on.
import pandas as pd
from io import StringIO
people = pd.read_csv(StringIO("id,team\n1,red\n2,red\n3,blue\n"))
scores = pd.read_csv(StringIO("id,score\n1,90\n2,80\n3,70\n"))
print(people.shape, scores.shape)
print(people["id"].dtype, scores["id"].dtype)
print("dupes:", int(people["id"].duplicated().sum()), int(scores["id"].duplicated().sum()))Join, and check the count
You predicted one row per person. If the join gives more, a key was duplicated; if fewer, some did not match. Either way you want to know now.
import pandas as pd
from io import StringIO
people = pd.read_csv(StringIO("id,team\n1,red\n2,red\n3,blue\n"))
scores = pd.read_csv(StringIO("id,score\n1,90\n2,80\n3,70\n"))
out = pd.merge(people, scores, on="id", how="left")
print(len(out), "rows for", len(people), "people")Mini Practice #1: Check before joining
Try It YourselfPrint the dtypes and the duplicate counts for both key columns before you join anything.
Step 2: Summarise last
Group only once the table is the one you meant. A mean over multiplied rows is wrong in a way that looks entirely reasonable.
The answer, with its size
The team means, and the number of people behind each — because a team of one is not a comparison.
import pandas as pd
from io import StringIO
people = pd.read_csv(StringIO("id,team\n1,red\n2,red\n3,blue\n"))
scores = pd.read_csv(StringIO("id,score\n1,90\n2,80\n3,70\n"))
out = pd.merge(people, scores, on="id", how="left")
print(out.groupby("team")["score"].agg(["mean", "size"]))What a duplicate would have done
The same code with one duplicated score row. The red mean has not changed much, the size has doubled, and nothing raised.
import pandas as pd
from io import StringIO
people = pd.read_csv(StringIO("id,team\n1,red\n2,red\n3,blue\n"))
bad = pd.read_csv(StringIO("id,score\n1,90\n1,90\n2,80\n3,70\n"))
out = pd.merge(people, bad, on="id", how="left")
print(len(out), "rows for 3 people")
print(out.groupby("team")["score"].agg(["mean", "size"]))Mini Practice #2: The answer and its size
Try It YourselfJoin these and produce the mean and the count per team in one result.
Step 3: When to Use It
Answers that need two files
Scores in one file, teams in another: read, clean, join, then summarise.
out = pd.merge(people, scores, on="id")Reports you will rerun
A function over the file paths runs the same checks on next month’s files.
When Not to Use It
Do not join before cleaning the keys. An id kept as "007" in one file and turned into "7" in the other matches nothing, and the result is empty rather than an error.
End-of-Lesson Exercises
Exercise 1: Mean score per team, from two files
Two files are supplied: people.csv with id and team, and scores.csv with id and score. Write team_means(people_path, scores_path) returning a dictionary of team to mean score, rounded to one decimal place.
Read both, merge on id, group by team, take the mean of score, round it, then to_dict(). The second file set is different, so a typed-in answer fails.
Exercise 2: Report the join, not just its result
Write join_report(people_path, scores_path) returning a three-element list of [people read, rows after a left join, people with no score]. The middle number differing from the first is how a duplicated key announces itself.
Read both, left join, and report len(people), len(joined) and the count of NaN in the score column. A left join cannot lose rows, so more rows than people means a key was duplicated.