Skip to content
Bamboo Weekly

pandas merge

Combine two data frames by matching values in their columns, SQL-style.

What do you do when the answer to your question lives in two different files? Real data sets are almost always split up this way: one file has the measurements, and a second, smaller file explains what the codes in the first one mean. The airport is 12892, the indicator is SE.PRM.ENRR, the country is 356. On its own, neither file answers anything.

merge is how you put them back together. You tell Pandas which column on the left matches which column on the right, and it hands you a new data frame whose rows contain the columns from both. If you know SQL, this is a join, and it behaves the way you expect. If you do not, it is a lookup performed on every row at once.

Official documentation: DataFrame.merge

The arguments that earn their keep

left.merge(right,
           on='country',              # same column name on both sides
           left_on='Country',         # ... or different names, one per side
           right_on='name',
           how='inner',               # inner, left, right, outer, cross
           suffixes=('_x', '_y'),     # what to call columns that collide
           indicator=True,            # add a _merge column: both / left_only / right_only
           validate='many_to_one')    # raise if the key is not as unique as you think

The data frame you invoke merge on is the left one, and the argument is the right one. That sounds obvious until you chain three merges together, at which point the result of the previous merge becomes the new left, and it stops being obvious very quickly.

how decides what happens to rows that find no partner. An inner join, the default, keeps only the rows that matched on both sides. A left join keeps every row from the left and fills the right-hand columns with NaN where nothing matched. A right join does the mirror image, and an outer join keeps everything from both.

suffixes handles the case where both data frames have a column of the same name that is not the join key. Column names in a data frame have to be unique, so Pandas renames the collisions rather than silently dropping one:

a.merge(b, on='country', suffixes=('_2023', '_2024'))

  country  total_2023  total_2024
0   India           1           9
1  Poland           2           8

A worked example, on real data

Here is the Global Coal Plant Tracker, the same workbook I used in Bamboo Weekly #64, joined to the ISO 3166 country list so that we can group plants by world sub-region:

import pandas as pd

coal_url = ('https://www.bambooweekly.com/content/files/wp-content/uploads/2024/02/'
            'global-coal-plant-tracker-january-2024.xlsx')
iso_url = ('https://raw.githubusercontent.com/lukes/'
           'ISO-3166-Countries-with-Regional-Codes/master/all/all.csv')

units = pd.read_excel(coal_url, sheet_name='Units',
                      usecols=['Country', 'Capacity (MW)', 'Status'])
countries = pd.read_csv(iso_url, usecols=['name', 'alpha-3', 'sub-region'])

len(units), len(countries)

(13906, 249)

The two files disagree about what the column is called, so on will not work; we need left_on and right_on:

len(units.merge(countries, left_on='Country', right_on='name'))

11555

13,906 rows went in, and 11,555 came out. Pandas did not warn me about that; it never does. Some 2,351 generating units quietly fell out of the analysis, and if I had gone straight to a groupby I would have plotted a chart that was wrong and looked fine.

The way to find out what went missing is to ask for a left join and let Pandas label each row:

(
    units
    .merge(countries, left_on='Country', right_on='name',
           how='left', indicator=True)
    .loc[lambda df_: df_['_merge'] == 'left_only', 'Country']
    .value_counts()
    .head()
)

Country
United States     1218
Russia             429
Vietnam            197
South Korea        104
United Kingdom     102

Nothing is broken in the data. The ISO list simply calls those places United States of America, Russian Federation, and Viet Nam. The join key was fine as a concept and wrong as a string, and the fix is to normalize the country names before merging, not to reach for a different how.

It is worth seeing what each how does to the row count on this same pair of data frames:

{how: len(units.merge(countries, left_on='Country', right_on='name', how=how))
 for how in ['inner', 'left', 'right', 'outer']}

{'inner': 11555, 'left': 13906, 'right': 11715, 'outer': 14066}

The left join is the only one that preserves all 13,906 original units, which is why it is the right choice when the left-hand frame is your data and the right-hand frame is a lookup table. The outer join is larger than either input, because it adds back the countries that have no coal plants at all.

Where it shows up in Bamboo Weekly

Bamboo Weekly #118: Flight delays is the clearest example of left_on and right_on I have. The Bureau of Transportation Statistics gives you one enormous file of flights plus two tiny lookup tables, so the solution merges three times: once against the airport codes for the origin, once again against the same table for the destination, and once against the carrier codes. Both airport merges bring along a column called Code, which is exactly what suffixes is for.

Bamboo Weekly #81: School uses World Bank education data, which arrives as three CSV files: the measurements plus metadata about countries and about indicators. Merging in the country metadata dropped 149 rows, and the solution stops to explain why — that is the inner join doing its job, on data where not every country code appears in both files.

Bamboo Weekly #165: Artemis II pulls the positions of the Orion capsule and of the Moon from NASA's Horizons API into two separate data frames, then merges them on datetime so that each row holds both bodies at the same instant. Since the two frames have identical column names, suffixes=['_orion', '_moon'] is what makes the result readable.

Bamboo Weekly #182: Surveillance technology shows a pattern worth stealing: run a groupby, take the top five results, then merge that summary back onto the original data frame to recover the underlying rows. The key there is two columns, on=['State', 'City'], because neither identifies a place by itself.

Four mistakes people make

An inner join loses rows without telling you. This is the big one, and it is what the coal example above is about. The default how='inner' keeps only rows that matched, so any imperfection in your join key — a renamed country, a discontinued code, a trailing space — removes data from your analysis silently. Compare len() before and after every merge. If the number changed and you cannot say why, stop and find out.

Duplicate keys multiply rows instead of adding columns. A merge is not a lookup that adds columns to the rows you already have; it produces one row for every matching pair. If a key appears twice on the left and three times on the right, you get six rows out, not two. On a real data set, that is how a 40,000-row data frame becomes 400,000 and the notebook stops responding. Pass validate='one_to_one' or validate='many_to_one' and Pandas will raise a MergeError the moment the key is less unique than you assumed, which is far better than discovering it from a memory error.

Join keys that look identical but are not. Merging an integer column against a string column raises a loud ValueError, which is the friendly case. The dangerous one is when both sides are strings and only the formatting differs: FIPS code '06' will never match '6', and 'India ' will never match 'India'. Check .dtype on both keys and strip and pad before you merge.

Forgetting which frame is the left one. left_on refers to the frame you called .merge on, and right_on to the argument. When you chain merges, the left frame changes at each step, and the arguments have to follow. If a merge returns zero rows or an unexpectedly enormous number, swapping left_on and right_on is worth trying before anything more elaborate.

merge or join?

Pandas gives you two methods for this, and the documentation describes them as near-equivalents. In practice they differ in three ways that matter, and the first one has cost me more debugging time than the other two combined.

merge defaults to an inner join. join defaults to a left join. Same data, same intent, different answers:

left  = pd.DataFrame({'v': [1, 2, 3]}, index=['a', 'b', 'c'])
right = pd.DataFrame({'w': [10, 20]},  index=['a', 'b'])

left.join(right)                                        # 3 rows
left.merge(right, left_index=True, right_index=True)    # 2 rows

     v     w                    v   w
a    1  10.0               a    1  10
b    2  20.0               b    2  20
c    3   NaN

The join keeps row c and fills it with NaN. The merge drops it and says nothing. Neither is wrong, but if you switch between the two methods without thinking about it, your row count changes underneath you.

Second, join matches on the index by default, while merge matches on columns. That is the difference people usually name first, and it is the least important one, because any column can become an index with set_index and merge accepts left_index=True when you want it to behave like join.

Third, join will take a list of data frames and combine all of them at once, which merge will not:

left.join([right, third])     # works — columns v, w, z
left.merge([right, third])    # TypeError

The suffix arguments differ too: merge takes a suffixes tuple, join takes separate lsuffix and rsuffix strings.

My rule is simple. If both frames are already indexed on what I want to match, or I am attaching several lookup tables to one frame, I use join. Everything else is merge, because naming the columns explicitly makes the intent visible to whoever reads the code next — including me, six months later.

Practice it

Work through a merge exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/merge/

Go deeper

If the columns you want to match on are already the indexes of your two data frames, join says the same thing with less typing. It is the index-based cousin of merge, and the two overlap almost completely, since any column can be made into an index. If you are not matching keys at all, but stacking data frames that share a shape, you want concat instead.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 13 Bamboo Weekly exercises that use merge on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.