Normalise the key before joining
Trailing spaces, mixed case, and leading zeros make keys that look identical and are not. Strip, casefold, and pad on both sides before the join, and the misses drop to the real ones.
Joining two datasets
Real analysis needs more than one file, and joining them is where rows get lost or duplicated. A join is a dictionary lookup on a key, and the failure modes are the same in every language: keys that do not match, and keys that match more than once.
What you will see
1 3 - one row (b2) had no match, and you now know it before you use the numbers.
About 2 min of reading
Try It
Join two lists and report the match rate.
1
Build both sides
base = [{'id': 'a1', 'v': 3}, {'id': 'b2', 'v': 9}, {'id': 'c3', 'v': 1}]
other = [{'id': 'a1', 'name': 'alpha'}, {'id': 'c3', 'name': 'gamma'}]
index = {r['id']: r for r in other}
print(len(index), len(other))
Before you run it, say out loud what it will print. Then open the answer.
What it prints
2 2 - the key is unique, so the index is safe.
2
Join and count misses
miss = 0
for r in base:
o = index.get(r['id'])
if o is None:
miss += 1
else:
r.update(o)
print(miss, len(base))
Before you run it, say out loud what it will print. Then open the answer.
What it prints
1 3 - one row (b2) had no match, and you now know it before you use the numbers.
Put it in order
Before you write anything, put these lines back in the right order. Click a line to move it up one step. This support step is easier than writing code from scratch, and it is here to make the last example stick.
Data Flow
One dataset indexes the other, rows look up.
1
Normalise the key on both sides
↓
2
Index the smaller dataset by key
↓
3
Look up each base row
↓
4
Report matched, missing, and duplicate counts
Line by line
Read the first example one line at a time. A line that prints shows what appears on screen; the rest set things up quietly.
base = [{'id': 'a1', 'v': 3}, {'id': 'b2', 'v': 9}, {'id': 'c3', 'v': 1}]
sets nothing on screen
other = [{'id': 'a1', 'name': 'alpha'}, {'id': 'c3', 'name': 'gamma'}]
sets nothing on screen
index = {r['id']: r for r in other}
sets nothing on screen
print(len(index), len(other))
2 2 - the key is unique, so the index is safe.
Why this works
When you have a choice
Choose the join direction deliberately
A left join keeps the base rows and loses data; a right join answers a different question. Say which side is the population you are making claims about, and the other side is just attributes.
Examples
Index one side, look up
index = {r['id']: r for r in other} then for each row attach index.get(row['id']). This is a left join, and .get() makes the misses visible instead of crashing.
Duplicate keys silently multiply
If the index has two rows with the same key, the dictionary keeps the last one and you lose the first without a word. Count the keys before you index: len(index) must equal len(other) or the key is not unique.
Report the match rate
'joined 1,204 of 1,310 rows' is the number you must publish. A 92 percent match rate changes what the answer means, and a 100 percent rate is often the sign that the wrong key was used.
Words this lesson uses
Each one is defined here on this site. Open the entry for the full definition and the other words it points at.
← Missing values
Previous Lesson
Missing values