Joining Tables
Joining Tables: Joining Tables with merge, Inner, Left, Right and Outer Joins, When a Join Goes Wrong.
Updated
- Joining Tables with merge (Apprentice): merge lines up the rows of two tables by a key column they share, the job a spreadsheet's VLOOKUP does, with on= when the key has the same name in both, left_on and right_on when it doesn't, and suffixes to tell apart other columns the two tables share.
- Inner, Left, Right and Outer Joins (Journeyman): A join has to decide what happens to rows that find no match, and merge's how= says which ones to keep (inner only the matches, left every row of the left table, right every row of the right one, outer every row of both), while indicator=True and a row count before and after show what the join did.
- When a Join Goes Wrong (Journeyman): A join can run without an error and still give wrong answers, when a key that repeats in the lookup table copies rows (which validate= catches), when the key is a number in one table and text in the other, or when spaces hide in the keys, and each has a check and a fix.