The Daily Grind opened a second region, and the new region's till exports its orders to a separate file. The weekly report needs both regions in one table, and it needs each loyalty customer counted once however many times they appear. Two jobs, and both are about rows: putting rows together, and taking repeated rows out.
Watch it happen
The east region's export, and the west region's, whose system writes the columns the other way round:
| order_id | drink |
|---|---|
| 1 | oat latte |
| drink | order_id |
|---|---|
| mocha | 2 |
both = east.unionByName(west)
| order_id | drink |
|---|---|
| 1 | oat latte |
| 2 | mocha |
unionByName stacks the rows and matches the columns by name, so the west export's reversed columns land in the right place.
union matches by position
The older union lines the columns up by position and asks no questions. Stack the same two tables with it and you get a table that looks fine and is wrong:
| order_id | drink |
|---|---|
| 1 | oat latte |
| mocha | 2 |
The second row has a drink in the id column and an id in the drink column, and no error was raised. The engine here turned the numbers into text to make it fit; another engine might refuse, which is better, or do the same, which is worse. Use unionByName; the extra word is the safety.
When the columns don't all exist
unionByName refuses a table that is missing a column the other has: stack an export with a city column onto one without, and the run stops with right column not found: city. That is the right default. When the missing column is expected, say so, and the gap is filled with nulls:
east.unionByName(west, allowMissingColumns=True)
| order_id | drink | city |
|---|---|---|
| 1 | oat latte | Leeds |
| 2 | mocha | null |
Repeated rows
distinct() drops rows that are exact repeats of another row, every column the same. dropDuplicates(["customer_id"]) keeps one row per value of the columns you name, whatever the other columns hold.
signups.distinct() # drops exact duplicate rows
signups.dropDuplicates(["customer_id"]) # one row per customer
Those are different jobs. Four signup rows, two of them identical:
| customer_id | source |
|---|---|
| 1 | app |
| 1 | app |
| 1 | web |
| 2 | app |
distinct() returns three rows, because (1, web) is not a repeat of (1, app). dropDuplicates(["customer_id"]) returns two, one per customer, and for customer 1 it keeps one of the three without telling you which.
Which row dropDuplicates keeps is not yours to choose. Which row survives is the engine's choice, and sorting the table beforehand does not change that. Sort six plays by play_id descending, drop duplicates on user_id, and the engine here kept play 1, not play 6. When you need a specific row per key, the latest, the highest, the first by date, that is a window function's job, and the window functions lesson teaches it.
Two problems, in the order they build:
- Combine the Regions — stack two exports by name.
- Unique Listeners — count each person once.