Skip to content
Bamboo Weekly

pandas join

Attach other data frames to this one by index — including several of them in a single call, which merge will not do.

You have monthly unemployment in one data frame and quarterly GDP in another, and both are indexed by date. What is the shortest, clearest way to put them in one table?

The answer is .join, and it is the method people reach for last, because merge gets all the attention. That is backwards; I generally prefer to use join, although I admit that might be an artifact of my years working with SQL.

When the thing connecting two frames is their index, join says it in one short line, it keeps the left frame's rows and their order, and it will attach a whole list of frames at once. merge can be talked into doing the same job with left_index=True, right_index=True, but you have to say so every time, and its defaults are different in a way that changes your row count without telling you.

Official documentation: DataFrame.join

merge, join, or concat?

Pandas gives you three ways to combine data frames, and choosing between them is the actual question most people have. Here is the whole decision.

Use merge when what connects the two frames is a shared column of values — country names, airport codes, dates stored as an ordinary column. You name the columns, Pandas matches them.

Use join when what connects them is the index. Either they are already indexed the same way, or one of them is a lookup table indexed by a code that appears as a column in the other. That second case is what on= is for, and it is the shape almost every lookup in real life takes.

Use concat when nothing needs matching at all, because the frames are already the same shape in the direction you care about and you just want them stacked.

The tell that you have picked wrong is reset_index. If you are calling it so that merge can see a key, you wanted join. If you are calling set_index on both frames so that join can see a key, you wanted merge.

The arguments that earn their keep

left.join(other,                  # a data frame, a named series, or a list of them
          on='key',               # match this column of left against other's index
          how='left',             # left (default), right, inner, outer, cross
          lsuffix='_a',           # rename collisions coming from the left
          rsuffix='_b',           # ... and from the right
          validate='many_to_one') # raise if the key is not as unique as you think

Five arguments, and two of them will decide whether your analysis is right.

how defaults to 'left'. Read that again, because merge defaults to 'inner'. Same two frames, same intent, different number of rows, no warning either way.

on is the one that reads strangely until it clicks. It names a column on the left and matches it against the index on the right. That asymmetry is not a wart; it is precisely the shape of a lookup table, where the codes repeat on the left and are unique on the right.

validate works here exactly as it does on merge. Pass 'many_to_one' and Pandas raises a MergeError the moment your lookup table turns out to have duplicate keys, which is much nicer than discovering it from a frame that came back longer than it went in.

If you pass a series rather than a data frame, it has to have a name — an unnamed one gets you ValueError: Other Series must have a name — because that name becomes the new column.

A worked example, on real data

FRED publishes each economic indicator as its own CSV, indexed by date. That is join territory: four separate downloads, one shared index, no key to name.

import pandas as pd

series = {
    s: pd.read_csv(f'https://fred.stlouisfed.org/graph/fredgraph.csv?id={s}',
                   parse_dates=['observation_date'],
                   index_col='observation_date')
    for s in ['UNRATE', 'FEDFUNDS', 'CPIAUCSL', 'GDP']
}

unrate, fedfunds, cpi, gdp = (series[s] for s in
                              ['UNRATE', 'FEDFUNDS', 'CPIAUCSL', 'GDP'])

{s: df.shape for s, df in series.items()}

{'UNRATE': (943, 1), 'FEDFUNDS': (865, 1), 'CPIAUCSL': (955, 1), 'GDP': (318, 1)}

Four frames of different lengths, because the series start in different years and GDP is quarterly rather than monthly. Attaching one to another is a single call with nothing to configure:

unrate.join(fedfunds).tail(3)

                  UNRATE  FEDFUNDS
observation_date
2026-05-01           4.3      3.63
2026-06-01           4.2      3.63
2026-07-01           4.1      3.63

And here is the argument for join that nothing else in Pandas answers. Hand it a list, and it attaches all of them at once:

unrate.join([fedfunds, cpi, gdp]).tail(4)

                  UNRATE  FEDFUNDS  CPIAUCSL       GDP
observation_date
2026-04-01           4.3      3.64   332.407  32475.21
2026-05-01           4.3      3.63   333.979       NaN
2026-06-01           4.2      3.63   332.568       NaN
2026-07-01           4.1      3.63   332.813       NaN

merge cannot do this at all; you would write three chained calls. concat can, but concat treats every input as a peer and takes the union of their indexes, which is a different result: 955 rows rather than 943, the extra twelve being months of 1947 for which no unemployment figure exists. join keeps unemployment as the subject of the sentence and everything else as an attachment, so the result still has unemployment's index, unemployment's length, and unemployment's order.

One limitation to know before you rely on the list form. Suffixes are not allowed with it:

unrate.join([fedfunds, cpi], lsuffix='_a', rsuffix='_b')

ValueError: Suffixes not supported when joining multiple DataFrames

So every column name across the whole list has to already be distinct. FRED names each column after its series, so this happens to work; frames you built yourself often will not.

Now the row counts, which is where how earns its place at the top of this page:

{how: len(unrate.join(gdp, how=how))
 for how in ['left', 'inner', 'right', 'outer']}

{'left': 943, 'inner': 314, 'right': 318, 'outer': 947}

The default keeps all 943 monthly observations and leaves GDP as NaN in the two months out of every three where there is no quarterly figure. That is almost always what you want when the left frame is your data and the right frame is extra context.

on=: a column on the left, an index on the right

The other half of join is the lookup. World Bank population arrives with an ISO three-letter code in a column, and the ISO 3166 table is naturally indexed by that code:

population = pd.read_csv('https://raw.githubusercontent.com/datasets/'
                         'population/main/data/population.csv')
countries = pd.read_csv('https://raw.githubusercontent.com/lukes/'
                        'ISO-3166-Countries-with-Regional-Codes/master/all/all.csv',
                        usecols=['alpha-3', 'region', 'sub-region']).set_index('alpha-3')

pop2024 = population.loc[population['Year'] == 2024]
len(pop2024), len(countries)

(265, 249)

One argument turns the code into a region:

(
    pop2024
    .join(countries, on='Country Code')
    .groupby('sub-region')['Value'].sum()
    .nlargest(5)
)

sub-region
Southern Asia                      2062868926
Eastern Asia                       1622936147
Sub-Saharan Africa                 1241764723
South-eastern Asia                  695353901
Latin America and the Caribbean     662186388
Name: Value, dtype: int64

No set_index on the left, no reset_index on the right, no left_on/right_on pair. And notice what the left default bought us: 265 rows went in and 265 came out. The 50 that found no region are World Bank aggregates — Arab World, Africa Eastern and Southern, Central Europe and the Baltics — and they are still in the frame, visible as NaN, which is how I found out they were there. The merge spelling of the same operation returns 215 rows and never mentions the other 50.

Four mistakes people make

Switching between merge and join without passing how. This is the expensive one. merge defaults to an inner join and join defaults to a left join, so translating one into the other silently changes your data:

len(unrate.join(gdp))                                    # 943
len(unrate.merge(gdp, left_index=True, right_index=True))  # 314

Two spellings of what looks like the same instruction, 629 rows apart. Neither is wrong. But if you rewrite a merge as a join to tidy up the code, and you do not say how='inner', you have just changed the answer. Compare len() before and after, every time.

Forgetting on= — or the set_index that would have replaced it. join matches on the index. If you meant to match on a column and did not say so, Pandas does not complain; it matches your default RangeIndex against a table of country codes and finds nothing:

pop2024.join(countries)[['Country Name', 'Country Code', 'sub-region']].head(3)

                    Country Name Country Code sub-region
64                         Aruba          ABW        NaN
129  Africa Eastern and Southern          AFE        NaN
194                  Afghanistan          AFG        NaN

Same 265 rows, the new columns present, every value missing. No error, no warning. Either pass on='Country Code' or call .set_index('Country Code') first — but pass on=, because the version that keeps the code as an ordinary column is the one you can chain a groupby onto.

Indexes that look equal and are not. Two indexes match on their values, not on how they print. Read the same GDP file without parse_dates and the index becomes strings:

gdp_str = pd.read_csv('https://fred.stlouisfed.org/graph/fredgraph.csv?id=GDP',
                      index_col='observation_date')

print(unrate.index.dtype, gdp_str.index.dtype)

datetime64[us] str

unrate.join(gdp_str)['GDP'].notna().sum()

0

943 rows, a GDP column, and not one value in it. Merging an integer key against a string key raises a loud ValueError in Pandas 3; joining a DatetimeIndex against a string index does not, and neither does 'IND ' against 'IND'. Check the indexes before you join them, not after:

unrate.index.intersection(gdp.index).size, unrate.index.intersection(gdp_str.index).size

(314, 0)

Zero, or a number far below what you expected, is your answer before you have written the join at all.

Treating columns overlap but no suffix specified as an obstacle. Rename each FRED frame's column to value and join refuses to continue:

value = {s: df.set_axis(['value'], axis='columns') for s, df in series.items()}
value['UNRATE'].join(value['FEDFUNDS'])

ValueError: columns overlap but no suffix specified: Index(['value'], dtype='str')

I like this error, and it is worth seeing what the alternative looks like. concat does the same combination without a word of complaint:

pd.concat([value['UNRATE'], value['FEDFUNDS']], axis='columns', sort=False).columns.tolist()

['value', 'value']

A data frame with two columns called value, where df['value'] now hands back a two-column frame instead of a series and everything downstream misbehaves in a way that has nothing to do with this line. join made me name the sides instead:

value['UNRATE'].join(value['FEDFUNDS'], lsuffix='_unrate', rsuffix='_fedfunds').tail(3)

                  value_unrate  value_fedfunds
observation_date
2026-05-01                 4.3            3.63
2026-06-01                 4.2            3.63
2026-07-01                 4.1            3.63

Note that merge spells this as a single suffixes tuple and join as two separate strings.

Where it shows up in Bamboo Weekly

Of the 185 Bamboo Weekly solutions, 39 use .join — about one in five. Four worth reading, all of them free:

Bamboo Weekly #39: WeWork is the reference for both of this page's headline arguments. It compares WeWork's stock against the S&P 500, two frames with identical column names, so lsuffix='_we' and rsuffix='_spy' are doing real work. Then it attaches construction spending and gross rents — written first as two chained joins, and then as we_df.join([construction_spending_df, gross_rents_df], how='outer'), so you can see exactly what the list form replaces.

Bamboo Weekly #36: Nobel Prize is the best on= example in the archive. The Nobel API hands you three linked tables, and the solution walks all three in one chain: .join(prizes_laureates_df.set_index('prize_id')).join(laureates_df, on='laureate_id'). The first join matches index to index; the second matches a column of the growing frame against the index of the lookup. The same post also joins a series — the output of a value_counts — and uses how='right' to keep only the laureates who won more than once.

Bamboo Weekly #26: Hot weather shows the pattern I use most. A groupby result is already indexed by the thing you grouped on, which means a lookup table indexed the same way attaches with nothing more than .join(stations_df) — no key named anywhere. The solution does it both ways, once after .set_index('station_id') on raw rows and once straight off the groupby.

Bamboo Weekly #60: Iceland is the tidiest small example, and it shows why set_index then join often beats merge. Two Wikipedia tables give the country name in columns called Location and Country / dependency; once each is set as the index, the names stop mattering and the whole thing is population_df.join(area_df) followed by an assign dividing one by the other. The same post also has the .reset_index().set_index('Country') dance you need when the frame you want to attach to is indexed by something else.

Practice it

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

Go deeper

If the two frames are connected by a shared column of values rather than an index, you want merge, which also covers indicator= and the row-count arithmetic of duplicate keys. If nothing needs matching and you are stacking frames that already agree, you want concat. The Pandas user guide on merge, join, concatenate and compare is the canonical reference for all three, and it is unusually good on the index-alignment rules underneath them.

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 51 Bamboo Weekly exercises that use join on real-world data — try each one, then study the worked solution.

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