Paul's Python CoursePPC

Merging tables

Not met yet

people.merge(trades, on="occupation") lines up two tables by the values in a shared column and gives back one table with the columns of both. The default keeps only rows that match in both tables; how="left" keeps every row of the left table, with NaN where the right had nothing, and how="outer" keeps everything from both. A key that appears twice in the right table repeats the left row once per match, so check a lookup table for repeats, or pass validate="many_to_one" and pandas will refuse to merge if there are any.

Try it

main.py

Output

Press Run and what your program prints shows up here.

Easy to get wrong

  • Thinks merge keeps every row of the left table by default. The default is an inner merge, which drops rows with no match.
  • Thinks merge keeps every row from both tables by default, which is what how="outer" does.
  • Thinks merge raises an error when a row's key has no match in the other table, rather than quietly dropping or filling it.
  • Thinks a key repeated in the lookup table still gives one row per left row. merge makes a row for every matching pair, so the left row is repeated.
  • Thinks pandas refuses to merge on a key that is repeated, when it only checks if asked with validate=.

Where it comes up