DataFrames combineren — stapelen met concat, koppelen met merge

Het probleem dat we oplossen

De gegevens van de winkel staan niet in één bestand. Dat is eigenlijk nooit zo.

De bestellingen van maart staan in orders.csv, de tabel die je al sinds hoofdstuk vier gebruikt. De bestellingen van april kwamen deze week binnen als een aparte export, orders_april.csv. Wat elk product de winkel kost — en welke leverancier het verkoopt — wordt door de inkoopafdeling bijgehouden in hun eigen bestand, products.csv. Niemand heeft de kostprijs in het bestellingenbestand gezet, want kosten horen bij een product, niet bij een bestelling.

Nu stelt de eigenaar één vraag: hoeveel winst heeft elke leverancier opgeleverd in maart en april samen?

Geen enkel bestand kan dat in zijn eentje beantwoorden. Voor de winst heb je het aantal en de verkoopprijs nodig (in de bestellingen), de kostprijs (in het productenbestand) en de leverancier (ook in het productenbestand). En de bestellingen zijn verdeeld over twee maanden.

In een spreadsheet zou je de rijen van april onder die van maart kopiëren, vervolgens in een nieuwe kolom een VLOOKUP schrijven om de kostprijs voor elke rij op te halen, die naar beneden doortrekken en hopen op het beste. Pandas doet diezelfde twee taken in twee regels:

• tabellen met dezelfde soort rijen — maart en april — stapelen tot één langere tabel: pd.concat
• elke rij van de ene tabel koppelen aan de juiste rij van een andere tabel via een gedeelde kolom — elke bestelling aan het bijbehorende product — en de kolommen van de andere tabel meenemen: pd.merge

De regels zijn kort. Wat dit hoofdstuk zo belangrijk maakt, is wat er mis kan gaan zonder enige foutmelding: een merge kan geruisloos bestellingen laten vallen waarvan het product ontbreekt in de opzoektabel, of bestellingen geruisloos dupliceren wanneer de opzoektabel een product twee keer vermeldt. Beide leveren een totaal op dat er volkomen geloofwaardig uitziet, maar toch fout is. Daarom draait het grootste deel van dit hoofdstuk om het vooraf en achteraf weten hoeveel rijen je precies zou moeten hebben — en dat controleren.

Aan het einde van dit hoofdstuk kun je

• Het verschil uitleggen tussen de twee bewerkingen: rijen stapelen (concat) en koppelen op een sleutel (merge)
• Tabellen stapelen met pd.concat, de herhaalde index herstellen met ignore_index=True, en zien wat er gebeurt bij niet-overeenkomende kolommen
• Twee tabellen koppelen op een sleutel met pd.merge, en bewust kiezen tussen how="inner", "left", "right" of "outer"
• Een merge controleren met indicator=True en beveiligen met validate="many_to_one"
• Een rij-explosie door dubbele sleutels herkennen en oplossen
• Samenvoegen op sleutels met verschillende namen via left_on/right_on, en botsende kolomnamen afhandelen met suffixes
• Merge-fouten herkennen — niet-overeenkomende types van sleutels, ontbrekende sleutelkolommen — en de geruisloze fouten ontdekken die geen enkele melding geven

Voorkennis: Kolommen toevoegen.

Maak de bestanden eerst aan

Dit hoofdstuk gebruikt vier kleine bestanden. Plaats ze naast je script.

orders.csv — maart, hetzelfde bestand als in de vorige hoofdstukken:

order_id,date,branch,product,category,quantity,price
1001,2024-03-01,north,pen,stationery,12,15.0
1002,2024-03-01,south,notebook,stationery,5,60.0
1003,2024-03-02,north,bag,accessories,2,850.0
1004,2024-03-02,east,bottle,accessories,7,120.0
1005,2024-03-03,north,eraser,stationery,30,8.0
1006,2024-03-03,south,bag,accessories,1,850.0
1007,2024-03-04,east,pen,stationery,20,15.0
1008,2024-03-04,north,bottle,accessories,4,120.0

orders_april.csv — april, dezelfde kolommen. Let op de stapler: de winkel is die in april gaan verkopen.

order_id,date,branch,product,category,quantity,price
1009,2024-04-01,north,pen,stationery,15,15.0
1010,2024-04-01,east,notebook,stationery,3,60.0
1011,2024-04-02,south,stapler,stationery,6,95.0
1012,2024-04-02,north,bag,accessories,1,850.0

products.csv — één rij per product, bijgehouden door inkoop. Twee dingen zijn hier met opzet "fout", zoals dat ook in het echte leven gebeurt: de nieuwe stapler is nog niet toegevoegd, en er staat een marker in die de winkel nooit heeft verkocht.

product,cost,supplier
pen,9.0,Alpha
notebook,40.0,Alpha
bag,600.0,Bravo
bottle,80.0,Bravo
eraser,5.0,Alpha
marker,25.0,Alpha

branches.csv — wie de leiding heeft over elk van de drie vestigingen van de winkel. De kolom heet branch_name, niet branch, omdat een andere persoon dit bestand heeft gemaakt.

branch_name,manager
north,Mira
south,Omar
east,Lena

Voordat je de code schrijft

Het combineren van tabellen is de stap waarbij een snel regeltje code het vaakst een zelfverzekerd fout antwoord oplevert. Daarom komt de planning op de eerste plaats, en dat zijn vooral vragen die je beantwoordt door simpelweg te kijken.

1. Formuleer de vraag in één zin

"Winst per leverancier, maart en april samen." Die zin vertelt je al hoe de uiteindelijke tabel eruitziet: één rij per leverancier, één getal per rij. Er zijn twee leveranciers, Alpha en Bravo, dus het antwoord moet ongeveer twee rijen hebben. Als het eindigt met drie rijen, of met één, is er onderweg iets misgegaan.

2. Bepaal welke bewerking elk paar tabellen nodig heeft

Er zijn maar twee manieren van combineren, en de toets is eenvoudig: bevatten de twee tabellen dezelfde soort rijen, of verschillende feiten over hetzelfde onderwerp?

• Dezelfde kolommen, verschillende rijen — bestellingen van maart + bestellingen van april. Gebruik pd.concat([a, b]). Het resultaat groeit naar beneden: meer rijen, dezelfde kolommen.
• Een gedeelde sleutelkolom, verschillende feiten — bestellingen + producten, gekoppeld via product. Gebruik pd.merge(a, b, on="product"). Het resultaat groeit in de breedte: dezelfde rijen, meer kolommen.

Maart en april zijn beide "bestellingen" — dezelfde kolommen, meer rijen — dus die worden gestapeld. Bestellingen en producten zijn verschillende zaken die gekoppeld worden via de productnaam, dus die worden gematcht.

3. Bekijk elke tabel voordat je combineert

In hoofdstuk drie heb je geleerd om een bestand direct na het inlezen te controleren. Bij meerdere bestanden is die gewoonte onmisbaar: je hebt de vorm (shape) van elk bestand nodig om de vorm van het resultaat te kunnen voorspellen.

import pandas as pd

march = pd.read_csv("orders.csv")
april = pd.read_csv("orders_april.csv")
products = pd.read_csv("products.csv")

for name, df in [("march", march), ("april", april), ("products", products)]:
    print(name, df.shape, list(df.columns))

march (8, 7) ['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price']
april (4, 7) ['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price']
products (6, 3) ['product', 'cost', 'supplier']

Maart en april hebben exact dezelfde zeven kolommen, in dezelfde volgorde — ideaal om te stapelen. products deelt precies één kolomnaam met hen: product. Dat is de sleutel, de kolom waarop de koppeling wordt gemaakt.

Controleer of de sleutel aan beide kanten hetzelfde type heeft. Een sleutel die in de ene tabel een getal is en in de andere tekst, kan niet overeenkomen:

print(march["product"].dtype, products["product"].dtype)

str str

Beide zijn str. Ze kunnen worden vergeleken.

4. Controleer of de sleutel aan de opzoekkant uniek is

Dit is de allerbelangrijkste controle van het hele hoofdstuk. Elke bestelling heeft één kostprijs nodig. Als products.csv twee keer pen bevat, wordt elke pen-bestelling twee keer gekoppeld en verschijnt deze twee keer in het resultaat.

print(products["product"].is_unique)
print(products["product"].duplicated().sum())

True
0

is_unique is True en er zijn nul duplicaten: één rij per product. Elke bestelling kan met maximaal één productrij overeenkomen.

5. Controleer welke sleutels geen partner vinden

Vraag je vóór het samenvoegen af: zijn er bestellingen waarvan het product ontbreekt in products.csv, en producten die niemand heeft besteld? Met isin (uit het filterhoofdstuk) beantwoord je beide:

orders = pd.concat([march, april], ignore_index=True)

no_cost = ~orders["product"].isin(products["product"])
print(orders.loc[no_cost, ["order_id", "product"]])

never_sold = ~products["product"].isin(orders["product"])
print(products.loc[never_sold, "product"].tolist())

order_id  product
10      1011  stapler
['marker']

Eén bestelling — de stapler, 1011 — heeft geen kostprijs in het bestand. Eén product, de marker, is nooit verkocht. Nu weet je vóór het samenvoegen waar de merge mee te maken krijgt.

6. Voorspel het resultaat en neem een beslissing

Schrijf de voorspelling op in getallen:

• orders heeft 8 + 4 = 12 rijen.
• products heeft één rij per product, dus koppelen kan bestellingen niet vermenigvuldigen.
• Als elke bestelling behouden blijft, heeft de samengevoegde tabel 12 rijen, waarvan één (de stapler) geen kostprijs heeft.
• Als alleen gekoppelde bestellingen behouden blijven, zijn het er 11.

Wat wil je? Voor een winstrapport zou het geruisloos weglaten van de stapler-bestelling de verkopen te laag voorstellen. Het is beter om deze te behouden, te zien dat de kostprijs ontbreekt en dan te beslissen wat je doet — en dat is precies de keuze die het argument how= biedt. Na de merge is het allereerste wat je print de vorm (shape), en die vergelijk je met deze voorspelling.

Dat is het hele plan: de vraag in één zin, de bewerking per paar, elke tabel bekijken, uniekheid van de sleutel, niet-overeenkomende sleutels, het voorspelde aantal rijen. De rest van het hoofdstuk behandelt het gereedschap waarmee je dit uitvoert.

Rijen stapelen met pd.concat

pd.concat accepteert een lijst van tabellen en plaatst ze onder elkaar:

import pandas as pd

march = pd.read_csv("orders.csv")
april = pd.read_csv("orders_april.csv")

both = pd.concat([march, april])
print(both.shape)
print(both[["order_id", "date", "product", "quantity"]])

(12, 7)
   order_id        date   product  quantity
0      1001  2024-03-01       pen        12
1      1002  2024-03-01  notebook         5
2      1003  2024-03-02       bag         2
3      1004  2024-03-02    bottle         7
4      1005  2024-03-03    eraser        30
5      1006  2024-03-03       bag         1
6      1007  2024-03-04       pen        20
7      1008  2024-03-04    bottle         4
0      1009  2024-04-01       pen        15
1      1010  2024-04-01  notebook         3
2      1011  2024-04-02   stapler         6
3      1012  2024-04-02       bag         1

Twaalf rijen, dezelfde zeven kolommen. Pandas lijnde de kolommen uit op naam, niet op positie — als het bestand van april de kolommen in een andere volgorde had staan, zouden ze nog steeds onder de juiste kolomkoppen terechtkomen.

Maar kijk eens langs de linkerrand. De index loopt van 0 tot en met 7, en begint dan weer bij 0. concat heeft de eigen index van elke tabel behouden, waardoor de labels 0 tot en met 3 nu twee keer voorkomen. Dat is geen cosmetisch detail:

print(both.loc[0, ["order_id", "date"]])

order_id        date
0      1001  2024-03-01
0      1009  2024-04-01

Je vroeg om rij 0 en kreeg twee rijen. Alle code die ervan uitgaat dat één label één rij betekent — zoals loc, een latere merge op de index of het aanpassen van een enkele cel — doet nu iets wat je niet bedoelde. Je kunt dit controleren:

print(both.index.is_unique)

False

ignore_index=True — de rijen opnieuw nummeren

Wanneer de oude index puur een rijenteller was (een RangeIndex, zoals bij elke read_csv tot nu toe), heeft deze geen inhoudelijke betekenis. Gooi hem dus weg en laat concat het resultaat vanaf nul nummeren:

orders = pd.concat([march, april], ignore_index=True)
print(orders.index.is_unique)
print(orders[["order_id", "date", "product"]].tail(5))

True
    order_id        date   product
7       1008  2024-03-04    bottle
8       1009  2024-04-01       pen
9       1010  2024-04-01  notebook
10      1011  2024-04-02   stapler
11      1012  2024-04-02       bag

Nu lopen de labels van 0 tot en met 11, elk precies één keer. Maak van ignore_index=True je standaardkeuze bij het stapelen van bestanden; laat het alleen weg als de index betekenis heeft (bijvoorbeeld na set_index("order_id"), waarbij de bestel-id's al uniek zijn).

Bijhouden waar elke rij vandaan kwam

Na het stapelen staat er nergens in de tabel bij welke maand een rij hoort. Hier vertelt de datum het je wel, maar vaak is dat niet zo. Voeg vóór het stapelen een kolom toe — de techniek uit het vorige hoofdstuk — en de informatie reist mee met de rijen:

march["month"] = "March"
april["month"] = "April"
orders = pd.concat([march, april], ignore_index=True)

print(orders["month"].value_counts())

month
March    8
April    4
Name: count, dtype: int64

De tellingen dienen meteen als controle: 8 + 4 = 12, er is niets verloren gegaan.

Wanneer de kolommen niet overeenkomen

Concat lijnt kolommen uit op naam. Een kolom die slechts in enkele tabellen voorkomt, verschijnt toch in het resultaat — en de rijen van tabellen die hem niet hadden, krijgen daar NaN. Stel dat een eenmalige bestelling handmatig werd ingevoerd, met een discount-kolom en zonder date, product, category of price:

manual = pd.DataFrame({
    "order_id": [2001],
    "branch": ["north"],
    "quantity": [3],
    "discount": [0.1],
})

mixed = pd.concat([march.head(2), manual], ignore_index=True)
print(mixed[["order_id", "branch", "price", "discount"]])
print(mixed.isna().sum())

order_id branch  price  discount
0      1001  north   15.0       NaN
1      1002  south   60.0       NaN
2      2001  north    NaN       0.1
order_id    0
date        1
branch      0
product     1
category    1
quantity    0
price       1
month       1
discount    2
dtype: int64

Geen foutmelding. Het resultaat bevat de vereniging van alle kolommen, en de gaten worden opgevuld met NaN. Daarom verdient stapelen dezelfde isna().sum() die je in het hoofdstuk over ontbrekende data hebt geleerd: een kolomnaam die in één bestand anders is gespeld (Quantity tegenover quantity) crasht niet, maar levert twee halflege kolommen op. Als het aantal kolommen na een concat groter is dan in welke invoertabel dan ook, sloot er iets niet goed aan.

axis=1 — en waarom je dat meestal niet wilt

pd.concat([a, b], axis=1) plaatst tabellen juist naast elkaar en koppelt rijen op indexlabel. Het is verleidelijk voor "voeg de productkolommen toe aan de bestellingen", maar het kijkt helemaal niet naar de productnaam — rij 0 van de ene tabel komt naast rij 0 van de andere te staan, ongeacht wat erin staat. Gebruik merge om op basis van een kolom te koppelen.

Rijen koppelen met pd.merge

merge accepteert twee tabellen en een sleutel. Voor elke rij van de linkertabel zoekt het de rijen van de rechtertabel met dezelfde sleutelwaarde, en plakt hun kolommen aan elkaar:

products = pd.read_csv("products.csv")

merged = pd.merge(orders, products, on="product")
print(merged.shape)
print(merged[["order_id", "product", "quantity", "price", "cost", "supplier"]])

(11, 10)
    order_id   product  quantity  price   cost supplier
0       1001       pen        12   15.0    9.0    Alpha
1       1002  notebook         5   60.0   40.0    Alpha
2       1003       bag         2  850.0  600.0    Bravo
3       1004    bottle         7  120.0   80.0    Bravo
4       1005    eraser        30    8.0    5.0    Alpha
5       1006       bag         1  850.0  600.0    Bravo
6       1007       pen        20   15.0    9.0    Alpha
7       1008    bottle         4  120.0   80.0    Bravo
8       1009       pen        15   15.0    9.0    Alpha
9       1010  notebook         3   60.0   40.0    Alpha
10      1012       bag         1  850.0  600.0    Bravo

Elke bestelling heeft nu zijn kostprijs en leverancier naast zich staan, zonder lus en zonder dat de volgorde van een van beide bestanden uitmaakt. De sleutelkolom product verschijnt één keer, omdat deze aan beide kanten gelijk is.

Vergelijk nu de vorm (shape) met het plan. We hadden 12 rijen voorspeld als alles behouden zou blijven — en we hebben er 11. De bestelling van de stapler, 1011, is verdwenen. Geen waarschuwing, geen foutmelding.

Dat is geen bug. Het is het standaardtype merge, how="inner", dat precies doet waarvoor het gemaakt is: alleen de sleutels behouden die in beide tabellen voorkomen. De stapler staat niet in products, dus die bestelling overleeft het niet; de marker staat niet in orders, dus die verschijnt evenmin. Als je die 12 niet had voorspeld, had je die ontbrekende verkoop nooit opgemerkt.

De vier soorten merge

Het argument how= bepaalt wat er gebeurt met rijen waarvan de sleutel aan de andere kant geen partner heeft. Stel je de twee verzamelingen sleutels voor:

• how="inner" (de standaardwaarde) behoudt alleen sleutels die in beide tabellen voorkomen. Niet-gekoppelde rijen aan weerszijden worden weggelaten. Gebruik dit wanneer je alleen complete records wilt.
• how="left" behoudt elke rij van de linkertabel. Linkerrijen zonder partner krijgen NaN in de kolommen van de rechtertabel; niet-gekoppelde rechterrijen vervallen. Gebruik dit om een hoofdtabel te verrijken — de meest voorkomende keuze.
• how="right" is het spiegelbeeld: elke rij van de rechtertabel blijft behouden, niet-gekoppelde linkerrijen vervallen.
• how="outer" behoudt elke sleutel van beide kanten, en vult de ontbrekende plekken met NaN. Gebruik dit om twee lijsten met elkaar te vergelijken en controleren.

De vraag die je moet stellen is: van welke tabel mogen de rijen beslist niet verdwijnen? Hier zijn dat de bestellingen. Elke bestelling is een echte verkoop; het productenbestand is een opzoektabel. De juiste keuze is dus een left merge, met de bestellingen aan de linkerkant:

merged = pd.merge(orders, products, on="product", how="left")
print(merged.shape)
print(merged[["order_id", "product", "cost", "supplier"]])

(12, 10)
    order_id   product   cost supplier
0       1001       pen    9.0    Alpha
1       1002  notebook   40.0    Alpha
2       1003       bag  600.0    Bravo
3       1004    bottle   80.0    Bravo
4       1005    eraser    5.0    Alpha
5       1006       bag  600.0    Bravo
6       1007       pen    9.0    Alpha
7       1008    bottle   80.0    Bravo
8       1009       pen    9.0    Alpha
9       1010  notebook   40.0    Alpha
10      1011   stapler    NaN      NaN
11      1012       bag  600.0    Bravo

Twaalf rijen, zoals voorspeld. De bestelling van de stapler staat er nog steeds tussen, met NaN voor cost en supplier — en dat is eerlijk: de kostprijs is onbekend, en je kunt direct zien dat dat zo is. Een ontbrekende waarde die je kunt zien, is veel beter dan een ontbrekende rij die je niet opmerkt. Vanaf hier zijn de technieken uit het hoofdstuk over ontbrekende data van toepassing: je kunt de gaten tellen met isna().sum(), de kostprijs invullen als inkoop die doorgeeft, of de bestelling apart rapporteren.

Een right merge is het spiegelbeeld — elk product blijft behouden, de marker zonder bestelling:

right = pd.merge(orders, products, on="product", how="right")
print(right.shape)
print(right[["order_id", "product", "cost"]].tail(3))

(12, 10)
    order_id product  cost
9     1008.0  bottle  80.0
10    1005.0  eraser   5.0
11       NaN  marker  25.0

Merk op dat order_id veranderd is van gehele getallen in 1008.0, 1005.0 enzovoort. De rij van de marker heeft immers geen bestel-id, waardoor de kolom nu een NaN bevat — en zoals je in het hoofdstuk over ontbrekende data hebt gezien, verandert een kolom met gehele getallen die een NaN binnenkrijgt in float64. Als id's na een merge plotseling eindigen op .0, is dat de reden: sommige rijen vonden geen partner.

indicator=True — zien waar elke rij vandaan kwam

Een outer merge behoudt alles van beide kanten. Voeg indicator=True toe en pandas voegt een kolom toe, _merge, die voor elke rij aangeeft of de sleutel in beide tabellen voorkwam, alleen links of alleen rechts:

audit = pd.merge(orders, products, on="product", how="outer", indicator=True)
print(audit[["order_id", "product", "cost", "_merge"]])
print(audit["_merge"].value_counts())

order_id   product   cost      _merge
0     1003.0       bag  600.0        both
1     1006.0       bag  600.0        both
2     1012.0       bag  600.0        both
3     1004.0    bottle   80.0        both
4     1008.0    bottle   80.0        both
5     1005.0    eraser    5.0        both
6        NaN    marker   25.0  right_only
7     1002.0  notebook   40.0        both
8     1010.0  notebook   40.0        both
9     1001.0       pen    9.0        both
10    1007.0       pen    9.0        both
11    1009.0       pen    9.0        both
12    1011.0   stapler    NaN   left_only
_merge
both          11
left_only      1
right_only     1
Name: count, dtype: int64

Dit is het rapport van de merge zelf. Elf rijen vonden een match; één bestelling heeft geen productrij (left_only: de stapler); één product heeft geen bestelling (right_only: de marker). Het is dezelfde informatie als stap 5 van het plan, maar nu gegenereerd door de merge zelf.

De outer merge heeft de rijen ook gesorteerd op de sleutel — bag, bottle, eraser, … — in plaats van de volgorde van de bestellingen aan te houden. Inner en left merges behouden de volgorde van de linkertabel; outer garandeert dat niet. Als de volgorde uitmaakt, sorteer dan achteraf, zoals in hoofdstuk zeven.

De _merge-kolom bestaat uit gewone data, dus je kunt erop filteren. Het overzicht van alleen de problemen is één regel code:

problems = audit[audit["_merge"] != "both"]
print(problems[["order_id", "product", "_merge"]])

order_id  product      _merge
6        NaN   marker  right_only
12    1011.0  stapler   left_only

Voer zo'n controle uit telkens wanneer je data samenvoegt die je niet zelf hebt gemaakt. Als de lijst met problemen leeg is, heb je het bewijs dat de twee bestanden op elkaar aansluiten.

Het sluipende gevaar: dubbele sleutels

Tot nu toe had products één rij per product. Stel dat inkoop een tweede leverancier voor pennen toevoegt tegen een iets hogere kostprijs en simpelweg een rij toevoegt, zodat pen twee keer voorkomt:

products_dup = pd.concat(
    [products, pd.DataFrame({"product": ["pen"], "cost": [10.0], "supplier": ["Gamma"]})],
    ignore_index=True,
)
print(products_dup["product"].is_unique)

exploded = pd.merge(march, products_dup, on="product", how="left")
print(march.shape, "->", exploded.shape)
print(exploded[["order_id", "product", "quantity", "cost", "supplier"]])

False
(8, 8) -> (10, 10)
   order_id   product  quantity   cost supplier
0      1001       pen        12    9.0    Alpha
1      1001       pen        12   10.0    Gamma
2      1002  notebook         5   40.0    Alpha
3      1003       bag         2  600.0    Bravo
4      1004    bottle         7   80.0    Bravo
5      1005    eraser        30    5.0    Alpha
6      1006       bag         1  600.0    Bravo
7      1007       pen        20    9.0    Alpha
8      1007       pen        20   10.0    Gamma
9      1008    bottle         4   80.0    Bravo

Maart had 8 bestellingen; de merge leverde 10 rijen op. Bestellingen 1001 en 1007 — de twee pen-bestellingen — verschijnen elk twee keer, één keer per pen-rij. Er ging niets fout. Maar tel nu het aantal (quantity) bij elkaar op en de pennen worden dubbel geteld: 32 extra pennen die nooit zijn verkocht.

print(march["quantity"].sum(), exploded["quantity"].sum())

81 113

81 tegenover 113. Dit heet een rij-explosie (row explosion), en het is de duurste fout bij het samenvoegen van tabellen omdat het resultaat er heel normaal uitziet. De regel erachter is: merge koppelt elke overeenkomende linkerrij aan elke overeenkomende rechterrij. Een een-op-een- of veel-op-een-relatie behoudt het aantal rijen; een sleutel die aan beide kanten wordt herhaald, vermenigvuldigt het.

Een merge die rijen toevoegt geeft nooit een waarschuwing. Het enige symptoom is een totaal dat te hoog uitvalt, en een te hoog totaal valt zelden op. Vergelijk vóór en na elke merge len() — dat is dé controle waarmee je dit ontdekt.

validate= — laat pandas het voor je controleren

Je kunt de relatie aangeven die je verwacht, en pandas zal weigeren samen te voegen als de data daar niet aan voldoet. Veel bestellingen delen één product, dus deze merge moet veel-op-een (many-to-one) zijn:

try:
    pd.merge(march, products_dup, on="product", how="left", validate="many_to_one")
except Exception as error:
    print(type(error).__name__ + ":", error)

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
 product
    pen ...

Een MergeError, en die noemt zelfs de problematische sleutel. De andere waarden zijn "one_to_one", "one_to_many" en "many_to_many". validate= schrijven kost niets en verandert een geruisloos fout antwoord in een duidelijke foutmelding; zet het dus op elke merge met een opzoektabel.

Het oplossen: bepaal welk duplicaat de juiste is

De foutmelding vertelt je dat er iets mis is; bepalen wat juist is is jouw taak. Bekijk eerst de duplicaten:

print(products_dup[products_dup["product"].duplicated(keep=False)])

product  cost supplier
0     pen   9.0    Alpha
6     pen  10.0    Gamma

duplicated(keep=False) markeert elk exemplaar, niet alleen het tweede. Nu moet er een inhoudelijke beslissing worden genomen. Als de nieuwste rij de oude vervangt, behoud dan het laatste exemplaar:

products_clean = products_dup.drop_duplicates(subset="product", keep="last")
fixed = pd.merge(march, products_clean, on="product", how="left", validate="many_to_one")
print(fixed.shape)
print(fixed.loc[fixed["product"] == "pen", ["order_id", "cost", "supplier"]])

(8, 10)
   order_id  cost supplier
0      1001  10.0    Gamma
6      1007  10.0    Gamma

Terug naar 8 rijen, één per bestelling. Als in werkelijkheid beide leveranciers pennen leveren en een bestelling registreert welke van de twee het is, dan hoort supplier ook in de bestellingen te staan, en wordt de sleutel een combinatie van twee kolommen: on=["product", "supplier"]. De code kan die keuze niet maken; je planning wel.

Wanneer de sleutelkolommen verschillende namen hebben

branches.csv noemt de kolom branch_name, terwijl de bestellingen hem branch noemen. on= vereist aan beide kanten dezelfde naam, dus benoem je elke kant apart met left_on= en right_on=:

branches = pd.read_csv("branches.csv")

with_manager = pd.merge(orders, branches, left_on="branch", right_on="branch_name", how="left")
print(list(with_manager.columns))

['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price', 'month', 'branch_name', 'manager']

Beide sleutelkolommen blijven bewaard, omdat pandas niet kan weten dat jij ze als dezelfde kolom beschouwt. branch en branch_name bevatten nu identieke waarden, dus verwijder er een:

with_manager = with_manager.drop(columns="branch_name")
print(with_manager[["order_id", "branch", "manager"]].head(4))

order_id branch manager
0      1001  north    Mira
1      1002  south    Omar
2      1003  north    Mira
3      1004   east    Lena

Het alternatief is om de kolom vóór het samenvoegen te hernoemen — branches.rename(columns={"branch_name": "branch"}) — en daarna gewoon on="branch" te gebruiken. Beide manieren zijn prima; hernoemen houdt het resultaat direct overzichtelijk zonder extra opschoonstap.

Wanneer beide tabellen een kolom met dezelfde naam hebben

Als er een niet-sleutelkolom in beide tabellen voorkomt, kan pandas geen twee kolommen met de naam price in één tabel zetten. Daarom hernoemt pandas ze met de achtervoegsels _x (links) en _y (rechts). Stel je een catalogusprijzentabel voor die haar kolom ook price noemt:

list_prices = pd.DataFrame({"product": ["pen", "bag"], "price": [14.0, 800.0]})

compared = pd.merge(march, list_prices, on="product")
print(compared[["order_id", "product", "price_x", "price_y"]])

order_id product  price_x  price_y
0      1001     pen     15.0     14.0
1      1003     bag    850.0    800.0
2      1006     bag    850.0    800.0
3      1007     pen     15.0     14.0

price_x en price_y werken wel, maar over drie weken weet niemand meer welke wat is. Geef ze daarom zelf een naam met suffixes=:

compared = pd.merge(march, list_prices, on="product", suffixes=("_sold", "_list"))
print(compared[["order_id", "price_sold", "price_list"]])

order_id  price_sold  price_list
0      1001        15.0        14.0
1      1003       850.0       800.0
2      1006       850.0       800.0
3      1007        15.0        14.0

Nu zeggen de kolommen precies wat ze betekenen, en is de korting op elke bestelling simpelweg price_list - price_sold. (Code die later om compared["price"] vraagt, krijgt een KeyError, want na de merge heeft geen enkele kolom meer die exacte naam — nog een reden om de namen bewust te kiezen.)

join — kort over samenvoegen op index

DataFrames hebben ook een .join()-methode. Dat is een merge die de index van de rechtertabel als sleutel gebruikt — handig wanneer de opzoektabel al geïndexeerd is op de sleutel:

by_product = products.set_index("product")

joined = orders.join(by_product, on="product")
print(joined[["order_id", "product", "cost", "supplier"]].head(3))

order_id   product   cost supplier
0      1001       pen    9.0    Alpha
1      1002  notebook   40.0    Alpha
2      1003       bag  600.0    Bravo

join gebruikt standaard how="left". Het doet hetzelfde werk als pd.merge(..., how="left"); merge is het meer algemene gereedschap, en dat is wat we in de rest van deze cursus gebruiken.

Een compleet voorbeeld

Terug naar de vraag van de eigenaar: winst per leverancier over maart en april, waarbij elke bestelling is meegenomen.

supplier_profit.py:

import pandas as pd

march = pd.read_csv("orders.csv")
april = pd.read_csv("orders_april.csv")
products = pd.read_csv("products.csv")

# 1. Stack the two months, remembering where each row came from.
march["month"] = "March"
april["month"] = "April"
orders = pd.concat([march, april], ignore_index=True)
print("orders:", orders.shape)

# 2. The lookup key must be unique, or the merge will duplicate orders.
assert products["product"].is_unique

# 3. Attach cost and supplier to every order. Keep every order.
sales = pd.merge(
    orders, products, on="product", how="left",
    validate="many_to_one", indicator=True,
)
print("after merge:", sales.shape)
assert len(sales) == len(orders)

# 4. Report the orders that found no product row, then set them aside.
unmatched = sales[sales["_merge"] == "left_only"]
print("no cost on file:", unmatched["order_id"].tolist(), unmatched["product"].tolist())
sales = sales[sales["_merge"] == "both"].drop(columns="_merge")

# 5. Profit per row, then per supplier and month.
sales["profit"] = sales["quantity"] * (sales["price"] - sales["cost"])
report = (
    sales.groupby(["supplier", "month"], as_index=False)["profit"]
    .sum()
    .sort_values(["supplier", "profit"], ascending=[True, False])
    .reset_index(drop=True)
)
print()
print(report)
print()
print(sales.groupby("supplier")["profit"].sum().sort_values(ascending=False))

orders: (12, 8)
after merge: (12, 11)
no cost on file: [1011] ['stapler']

  supplier  month  profit
0    Alpha  March   382.0
1    Alpha  April   150.0
2    Bravo  March  1190.0
3    Bravo  April   250.0

supplier
Bravo    1440.0
Alpha     532.0
Name: profit, dtype: float64

Waarom het zo is geschreven:

• Elke combinatiestap wordt gevolgd door een vorm (shape). orders: (12, 8) bevestigt de stapeling (8 + 4 rijen, 7 kolommen + month). after merge: (12, 11) bevestigt dat er niets is weggevallen of gedupliceerd — 3 nieuwe kolommen: cost, supplier, _merge. De afmetingen zijn het snelste en eenvoudigste bewijs dat elke stap deed wat het plan voorschreef.
• De aannames zijn als code vastgelegd. assert products["product"].is_unique en validate="many_to_one" beschermen beide tegen een rij-explosie; assert len(sales) == len(orders) bewaakt de voorspelling uit het plan. Als iemand volgende maand een productenbestand met een duplicaat stuurt, stopt het script in plaats van een kunstmatig opgeblazen winst te printen.
• how="left" behoudt de bestelling van de stapler, en indicator=True noemt deze bij naam. Het script laat deze niet stilletjes vallen; het print [1011] ['stapler'], zodat de eigenaar weet dat er in het totaalaantal van april nog één verkoop ontbreekt totdat inkoop de kostprijs toevoegt. Die regel is onderdeel van het antwoord, geen debug-uitvoer.
• De kolom month is toegevoegd vóór concat. Anders zou de uitsplitsing per maand in het rapport vereisen dat de datums worden ontleed en omgezet — mogelijk, maar meer werk voor hetzelfde resultaat.
• De winst wordt berekend na de merge, omdat daarvoor kolommen uit beide bestanden nodig zijn. Dit is de natuurlijke volgorde: eerst combineren, dan kolommen toevoegen (hoofdstuk negen), dan groeperen (hoofdstuk acht), en daarna sorteren (hoofdstuk zeven).

Het antwoord: Bravo leverde 1.440 aan winst op en Alpha 532, waarbij voor één verkoop in april nog op de kostprijs wordt gewacht. Bravo verkoopt weinig artikelen, maar tassen en flessen hebben een veel grotere marge per stuk dan pennen en gummen.

Wanneer het misgaat

ValueError: You are trying to merge on int64 and str columns for key 'order_id'. If you wish to proceed you should use pd.concat

De sleutel heeft aan beide kanten een ander type. Hier is een leveringsbestand waarvan de bestel-id's zijn geëxporteerd met een # ervoor. Sla dit op als deliveries.csv — het wordt straks ook gebruikt in "Jouw beurt". (Als je het bezorgerslogboek met dezelfde naam uit het hoofdstuk over ontbrekende data nog hebt staan, overschrijf het dan; dit is een ander bestand.)

order_id,status
#1001,delivered
#1002,delivered
#1003,returned
#1005,delivered
#1006,pending

import pandas as pd

march = pd.read_csv("orders.csv")
deliveries = pd.read_csv("deliveries.csv")
print(march["order_id"].dtype, deliveries["order_id"].dtype)

try:
    pd.merge(march, deliveries, on="order_id", how="left")
except ValueError as error:
    print("ValueError:", error)

int64 str
ValueError: You are trying to merge on int64 and str columns for key 'order_id'. If you wish to proceed you should use pd.concat

1001 en "#1001" zijn verschillende waarden, en pandas weigert te gokken. (De hint over pd.concat is bedoeld voor een andere situatie; negeer die.) De oplossing is om de sleutels hetzelfde type en dezelfde tekst te geven — verwijder de #, en converteer daarna:

deliveries["order_id"] = deliveries["order_id"].str.removeprefix("#").astype("int64")
status = pd.merge(march, deliveries, on="order_id", how="left")
print(status[["order_id", "product", "status"]])

order_id   product     status
0      1001       pen  delivered
1      1002  notebook  delivered
2      1003       bag   returned
3      1004    bottle        NaN
4      1005    eraser  delivered
5      1006       bag    pending
6      1007       pen        NaN
7      1008    bottle        NaN

De bestellingen zonder bezorgrecord krijgen NaN voor status — de left merge behoudt ze, wat precies is wat je wilt wanneer de vraag luidt: "welke bestellingen zijn er nog niet bezorgd?"

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge Je hebt validate="many_to_one" gebruikt en de rechtertabel herhaalt een sleutel. Het foutbericht toont de duplicaten. Verwijder validate niet om de foutmelding te laten verdwijnen — bekijk de dubbele rijen met duplicated(keep=False) en bepaal welke de juiste is, zoals uitgelegd in het gedeelte over rij-explosies.

KeyError: 'branch' De kolom die in on= wordt genoemd, ontbreekt in een van de tabellen:

branches = pd.read_csv("branches.csv")

try:
    pd.merge(march, branches, on="branch")
except KeyError as error:
    print("KeyError:", error)

KeyError: 'branch'

branches noemt het branch_name. Print list(df.columns) voor beide tabellen; gebruik left_on="branch", right_on="branch_name", of hernoem een van de kanten. Een spatie aan het einde van een kolomnaam ("branch ") veroorzaakt dezelfde foutmelding en is alleen zichtbaar in de lijstweergave.

De merge is uitgevoerd, maar de nieuwe kolommen zijn allemaal NaN Geen foutmelding, en toch is geen enkele rij gekoppeld. De sleutels lijken hetzelfde maar zijn het niet: "north " met een spatie erachter, "South" met een hoofdletter, of hetzelfde id als tekst in het ene bestand en met een voorvoegsel in het andere. Controleer dit vóór het samenvoegen met isin:

branches_messy = pd.DataFrame({"branch_name": ["north ", "South", "east"], "manager": ["Mira", "Omar", "Lena"]})

print(march["branch"].isin(branches_messy["branch_name"]).sum(), "of", len(march), "orders match")

branches_messy["branch_name"] = branches_messy["branch_name"].str.strip().str.lower()
print(march["branch"].isin(branches_messy["branch_name"]).sum(), "of", len(march), "orders match")

2 of 8 orders match
8 of 8 orders match

Twee van de acht vóór het opschonen, acht van de acht erna. Schoon de sleutel aan beide kanten op dezelfde manier op — str.strip() en één consistente schrijfwijze in hoofd- of kleine letters — voordat je samenvoegt.

Het aantal rijen is toegenomen na een merge De rechtertabel bevat een dubbele sleutel; zie "Het sluipende gevaar" hierboven. Vergelijk len() vóór en na elke merge, en gebruik validate=.

Er zijn rijen verdwenen na een merge Je hebt de standaardwaarde how="inner" gebruikt, en sommige sleutels hadden geen partner. Gebruik how="left" met de hoofdtabel aan de linkerkant, en indicator=True om te zien welke rijen niet gekoppeld konden worden.

TypeError: concat() takes 1 positional argument but 2 were given

try:
    pd.concat(march, deliveries)
except TypeError as error:
    print("TypeError:", error)

TypeError: concat() takes 1 positional argument but 2 were given

concat accepteert één argument: een lijst van tabellen. Schrijf pd.concat([march, april]) — om die vierkante haken draait het.

Een dubbele index na concat, en loc geeft twee rijen terug Elke invoertabel heeft zijn eigen 0, 1, 2, … behouden. Voeg ignore_index=True toe.

Jouw beurt

Het bezorgteam heeft deliveries.csv gestuurd (te zien in "Wanneer het misgaat") — één rij per bestelling waarvan zij een registratie hebben, met bestel-id's geschreven als #1001. De eigenaar wil een bezorgrapport voor maart en april samen.

Schrijf delivery_report.py zodanig dat:

1. Elke bestelling van beide maanden behouden blijft — precies één rij per bestelling, gecontroleerd met een assert op het aantal rijen, en de merge beveiligd met validate= en gecontroleerd met indicator=True.
2. De sleutels echt overeenkomen — de sleutel van de bezorgingen is opgeschoond naar hetzelfde type als order_id, en het script stopt als een bestelling twee keer in het leveringsbestand voorkomt.
3. Ontbrekend betekent "no record" — bestellingen zonder bezorgrij krijgen de status "no record", nooit een geruisloze NaN.
4. Het in deze volgorde print: de vorm na stapelen, de vorm na samenvoegen, de tellingen van _merge, het aantal bestellingen per status, de bestellingen zonder registratie (order_id, branch, product, gesorteerd op order_id), en het aantal bezorgde bestellingen per vestiging.

Wanneer het klopt, is de uitvoer exact:

orders: (12, 7)
after merge: (12, 9)
_merge
left_only     7
both          5
right_only    0
Name: count, dtype: int64

status
no record    7
delivered    3
returned     1
pending      1
Name: count, dtype: int64

    order_id branch   product
3       1004   east    bottle
6       1007   east       pen
7       1008  north    bottle
8       1009  north       pen
9       1010   east  notebook
10      1011  south   stapler
11      1012  north       bag

branch
north    2
south    1
dtype: int64

Voordat je ook maar één regel code schrijft: verklaar elk getal in die uitvoer puur aan de hand van de bestanden: waarom 12 rijen na het stapelen, waarom nog steeds 12 na de merge, en waarom 7 bestellingen zonder registratie. Als je dat niet kunt, is je plan nog niet klaar.

Laat het daarna met opzet twee keer misgaan:

• Verwijder .str.removeprefix("#"). Welke foutmelding krijg je, en welke regel veroorzaakt die?
• Voeg een tweede rij voor #1003 toe aan deliveries.csv (bijvoorbeeld #1003,delivered). Wat doet validate= nu? Wat zou het rapport zonder die controle hebben gezegd?

Oplossing

Het plan. De vraag: "wat is voor elke bestelling in maart en april de bezorgstatus?" — het resultaat heeft dus één rij per bestelling, 12 rijen. Maart en april hebben identieke kolommen: stapel ze met concat en ignore_index=True (8 + 4 = 12). Het leveringsbestand is gekoppeld op bestel-id, maar als tekst met een #, dus de sleutel moet worden opgeschoond voordat deze kan overeenkomen met het gehele getal order_id. Elke bestelling mag er hoogstens één keer in voorkomen, dus de relatie is een-op-een (elke bestelling heeft maximaal één bezorgrij, elke bezorgrij één bestelling). Elke bestelling moet behouden blijven, dus de bestellingen gaan naar links en de merge is how="left". Er zijn vijf bezorgrijen, allemaal voor maart, dus 12 − 5 = 7 bestellingen moeten eindigen met "no record".

delivery_report.py:

import pandas as pd

march = pd.read_csv("orders.csv")
april = pd.read_csv("orders_april.csv")
deliveries = pd.read_csv("deliveries.csv")

# 1. Stack the months.
orders = pd.concat([march, april], ignore_index=True)
print("orders:", orders.shape)

# 2. Clean the key, then check it.
deliveries["order_id"] = deliveries["order_id"].str.removeprefix("#").astype("int64")
assert deliveries["order_id"].is_unique, "an order appears twice in deliveries.csv"

# 3. Keep every order.
report = pd.merge(
    orders, deliveries, on="order_id", how="left",
    validate="one_to_one", indicator=True,
)
print("after merge:", report.shape)
assert len(report) == len(orders)
print(report["_merge"].value_counts())

# 4. Missing status means the delivery team has no record.
report["status"] = report["status"].fillna("no record")

# 5. Orders per status.
print()
print(report["status"].value_counts())

# 6. Orders with no record.
missing = report.loc[report["status"] == "no record", ["order_id", "branch", "product"]]
print()
print(missing.sort_values("order_id"))

# 7. Delivered orders per branch.
delivered = report[report["status"] == "delivered"]
print()
print(delivered.groupby("branch").size())

orders: (12, 7)
after merge: (12, 9)
_merge
left_only     7
both          5
right_only    0
Name: count, dtype: int64

status
no record    7
delivered    3
returned     1
pending      1
Name: count, dtype: int64

    order_id branch   product
3       1004   east    bottle
6       1007   east       pen
7       1008  north    bottle
8       1009  north       pen
9       1010   east  notebook
10      1011  south   stapler
11      1012  north       bag

branch
north    2
south    1
dtype: int64

Elke voorspelling klopt: 12 rijen na het stapelen, 12 na de merge, 7 zonder registratie. De tellingen van _merge bevestigen dit onafhankelijk — 5 both, 7 left_only, 0 right_only (een left merge levert nooit right_only op).

Toelichting bij de gemaakte keuzes:

• validate="one_to_one", niet "many_to_one". De bestellingentabel heeft ook unieke bestel-id's, dus beide kanten zijn uniek. De strengere controle zou ook een dubbel bestel-id bij de bestellingen opmerken — bijvoorbeeld als het bestand van april per ongeluk een bestelling uit maart zou herhalen.
• De assert op is_unique staat vóór de merge, compleet met toelichting, zodat het foutbericht in duidelijke taal zegt wat er mis is in plaats van in merge-termen.
• fillna("no record") wordt toegepast na de merge, nooit ervoor: de ontbrekende statussen ontstaan pas nadat de bestellingen zonder bezorgrij door de left merge behouden zijn gebleven.
• De vestiging east heeft geen bezorgde bestellingen, dus die komt helemaal niet voor in de laatste tabel. Groeperen levert alleen groepen op die daadwerkelijk in de data voorkomen — als de eigenaar een 0 voor east nodig heeft, is daarvoor een merge van de vestigingenlijst met dit resultaat nodig, met how="left" en een fillna(0).

De twee manieren waarop het misging. Zonder removeprefix faalt de regel met astype("int64") als eerste met ValueError: invalid literal for int() with base 10: '#1001', omdat "#1001" niet kan worden omgezet in een getal — de foutmelding wijst naar de opschoonregel, waar de oplossing thuishoort. Laat je ook astype weg, dan geeft de merge zelf ValueError: You are trying to merge on int64 and str columns for key 'order_id'. Met een tweede #1003-rij stopt de assert het script met AssertionError: an order appears twice in deliveries.csv; haal je de assert weg, dan geeft validate="one_to_one" een MergeError: Merge keys are not unique in right dataset; not a one-to-one merge. Zonder een van beide beveiligingen had het rapport 13 rijen getoond voor 12 bestellingen en bestelling 1003 twee keer geteld — eenmaal als returned en eenmaal als delivered — en was elk totaal daaronder geruisloos fout geweest.