Lesson · 16 min

Join two tables

Two tables

One file rarely has everything. trips.csv has the start station of each trip. stations.csv has the zone of each station. The question "how many trips start in each zone" needs both files. You join the two tables: for each trip, you find the row of its station.

The column that links two tables is the key of the join. Here, the key is the name of the station. Its name is start in one file and name in the other file.

A lookup dictionary

You can search the whole list of stations for each trip, but that is slow, and the code is long. Make a dictionary from the smaller table first. The key of the join becomes the key of the dictionary.

zone_of = {}
for station in stations:
    zone_of[station["name"]] = station["zone"]

print(zone_of["Old Mill"])   # river

Python has a short form for this loop. It looks like a list comprehension, and its name is dictionary comprehension.

zone_of = {station["name"]: station["zone"] for station in stations}

Add the value to each row

for trip in trips:
    trip["zone"] = zone_of[trip["start"]]

After this loop, each trip has its zone. You can count and group by zone with the tools of lesson 6.

Keys with no match

zone_of[trip["start"]] stops with a KeyError when a trip starts at a station that is not in the station file. This occurs in real data. A station closed, or a name has a typing mistake. You must decide what to do. There are two usual answers:

Leave these trips out. Your totals are then too small, and nobody sees it.
Keep these trips with a clear label, and report them.

trip["zone"] = zone_of.get(trip["start"], "unknown")

The second answer is almost always better.

If you know SQL: the first answer is an INNER JOIN, and the second answer is a LEFT JOIN.

Check both sides

After a join, ask two questions.

Which keys of the trips have no station? These are the unknown names.
Which stations have no trip? A count by station that you make from the trips never shows a station with 0 trips, because that station is not in the trips.

used = set([trip["start"] for trip in trips])
no_trips = [station["name"] for station in stations if station["name"] not in used]

The key must also be the same text on both sides. Old Mill and old mill do not match. Clean the keys first, as you did in lesson 8.

Your turn

main.py loads 16 trips and 8 stations. Every value of a station is text, also the number of docks. Do the tasks in order.

Tasks

5 to do

Task 1, not checked yet. Make a dictionary zone_of. Each key is the name of a station in stations, and its value is the zone of that station.
Task 2, not checked yet. Add the key zone to each trip in trips. Its value is the zone of the start station. When the start station is not in zone_of, the value is "unknown".
Task 3, not checked yet. Make a dictionary trips_by_zone with the number of trips for each zone. The zone unknown is one of the keys.
Task 4, not checked yet. Check both sides of the join. Make a list unknown_starts with the start stations of the trips that are not in zone_of, each name one time, in alphabetical order. Make a list unused with the names of the stations in stations where no trip starts.
Task 5, not checked yet. Write a function with_zone(trips, zone_of). It returns a list with one row for each trip. Each row has all the keys of the trip, and the key zone. For a start station that is not in zone_of, the zone is "unknown".

Task 1 of 5

Make a dictionary zone_of. Each key is the name of a station in stations, and its value is the zone of that station.

All tasks