Skip to content
Bamboo Weekly

pandas concat

Stack data frames that are already aligned — and attach them by index when they are not.

You have downloaded the same table for four states, or twelve months, or every year since 1975. Now you have a dictionary of data frames, and one question. How do you get them into a single data frame?

merge is the wrong tool here, and that is the whole point of this page. There is no key to match on. The frames already agree about what a row means; they are just in separate objects. pd.concat glues them together — end to end down the rows, or side by side across the columns — and lines up whatever axis you are not concatenating along. .join does something narrower and more convenient: it attaches other frames to this one by index, which is what you want when the frames represent the same rows measured different ways.

Here is the decision, and it fits in three sentences. If the thing that connects your two frames is a shared column of values — country names, airport codes, dates stored as a column — you want merge. If it is a shared index, you want join, which is merge with the index assumed and less typing. If nothing needs matching at all, because the frames are already the same shape in the direction you care about, you want concat.

Official documentation: pandas.concat.

The arguments that earn their keep

pd.concat(objs,                   # a list or dict of data frames or series
          axis='index',           # stack rows (default) — or 'columns', side by side
          join='outer',           # keep all labels on the other axis; 'inner' keeps the shared ones
          ignore_index=False,     # True to throw away the old labels and renumber
          keys=['a', 'b'],        # label each input, producing a MultiIndex
          names=['source', ...])  # names for those new index levels

Only two of these decide the shape of the result. axis says which direction you are growing in, and join says what to do with the axis you are not growing in. Everything else is about labels.

That second one is the part people skip. When you stack rows, Pandas takes the union of the columns by default, inventing NaN wherever an input did not have that column. When you stack columns, it takes the union of the row index, with the same consequence. join='inner' switches to the intersection instead. Neither default is wrong, but both are silent.

One Pandas 3 wrinkle to know: concatenating along columns when every input has a DatetimeIndex emits a warning saying that sorting by default is deprecated. Pass sort=False — or sort=True if you want the old behavior — and it goes away.

A worked example, on real data

FRED publishes a house price index for every US state as its own CSV, which is exactly the situation concat exists for. This is the data behind Bamboo Weekly #9, where the solution globs one downloaded CSV per state and stacks the lot in a single call:

import pandas as pd

states = ['NY', 'CA', 'TX', 'FL']

all_dfs = {
    state: pd.read_csv(
        f'https://fred.stlouisfed.org/graph/fredgraph.csv?id={state}STHPI',
        parse_dates=['observation_date'],
        index_col='observation_date')
    for state in states
}

{state: df.shape for state, df in all_dfs.items()}

{'NY': (205, 1), 'CA': (205, 1), 'TX': (205, 1), 'FL': (205, 1)}

Four frames, 205 quarters each, every one indexed by the same dates. Because the indexes already agree, concatenating along the columns gives a wide table with nothing to reconcile:

pd.concat(all_dfs.values(), axis='columns', sort=False).tail(4)

                  NYSTHPI  CASTHPI  TXSTHPI  FLSTHPI
observation_date
2025-04-01        1126.94   968.26   524.87   817.08
2025-07-01        1144.50   967.57   526.03   815.20
2025-10-01        1149.60   971.20   527.10   822.02
2026-01-01        1157.14   976.79   529.31   827.88

Now stack the same four frames the other way, along the rows, and watch what happens:

pd.concat(all_dfs.values()).tail(3)

                  NYSTHPI  CASTHPI  TXSTHPI  FLSTHPI
observation_date
2025-07-01            NaN      NaN      NaN   815.20
2025-10-01            NaN      NaN      NaN   822.02
2026-01-01            NaN      NaN      NaN   827.88

820 rows, four columns, three quarters of them empty. Nothing failed. FRED names each column after its series, so NYSTHPI and CASTHPI are different columns as far as Pandas is concerned, and the outer join dutifully kept all four. The frames were never aligned in the direction I stacked them.

The fix is to make the columns agree, and then to record the state somewhere that survives:

hpi = {state: df.set_axis(['hpi'], axis='columns')
       for state, df in all_dfs.items()}

stacked = pd.concat(hpi, names=['state', 'date'])
stacked.head(3)

                    hpi
state date
NY    1975-01-01  78.08
      1975-04-01  75.65
      1975-07-01  77.64

Passing a dictionary rather than a list is the shortcut here: the dictionary keys become keys=, and names= gives the two index levels their titles. One column of numbers, and the label that used to live in a column name is now an index level you can group on:

stacked.groupby('state')['hpi'].last().round(1)

state
CA     976.8
FL     827.9
NY    1157.1
TX     529.3
Name: hpi, dtype: float64

join, which has its own page

.join attaches frames by index rather than stacking them, and on aligned frames it does the same job as concat(axis='columns') with a signature that reads as attaching rather than combining. It has enough of its own arguments, and enough of its own traps, to deserve a page: join covers on=, how=, the suffixes, and the list-of-frames form, and settles the merge-versus-join question properly.

Where it shows up in Bamboo Weekly

Of the 185 Bamboo Weekly solutions, 34 use pd.concat. Two worth studying, both free to read:

Bamboo Weekly #76: Aging legislators uses concat in both directions in the same solution. It stacks the historical and current rosters of Congress with ignore_index=True, then flattens several columns of nested JSON with pd.json_normalize and concatenates those results back on with axis='columns'. The detail to notice is the .reset_index() on the last one — that is the alignment problem below, caught before it happened.

Bamboo Weekly #75: Refugees is the clearest keys= example I have. Three UNHCR tables go in side by side with keys=['population', 'destination', 'origin'], and the result is a data frame whose columns are a MultiIndex, so a single cell is addressed as df[('destination', 2000)].

Three mistakes people make

Concatenating along columns when the indexes do not line up. This is the one that costs the most time, because the output looks like missing data rather than a bug. Here are the five biggest quarterly jumps in the house price index for New York and for California:

top_ny = all_dfs['NY']['NYSTHPI'].pct_change().mul(100).nlargest(5).round(1).to_frame('NY')
top_ca = all_dfs['CA']['CASTHPI'].pct_change().mul(100).nlargest(5).round(1).to_frame('CA')

pd.concat([top_ny, top_ca], axis='columns', sort=False)

                    NY    CA
observation_date
1979-01-01        15.7   NaN
1982-01-01         8.2   NaN
1980-07-01         7.1   NaN
1980-04-01         7.1   NaN
1981-07-01         6.8   NaN
1982-10-01         NaN  10.6
2004-07-01         NaN  10.2
1977-04-01         NaN   8.5
1977-07-01         NaN   7.9
1976-07-01         NaN   6.5

Ten rows from two five-row frames, and half of every column empty. Pandas did exactly what I asked: it aligned on the dates, and the two states boomed in different quarters. But I did not want date alignment. I wanted a leaderboard, first against first. Throw the dates away and say so:

pd.concat([top_ny.reset_index(drop=True), top_ca.reset_index(drop=True)],
          axis='columns')

     NY    CA
0  15.7  10.6
1   8.2  10.2
2   7.1   8.5
3   7.1   7.9
4   6.8   6.5

Whenever an axis='columns' concat returns more rows than any of its inputs, this is what happened.

Reaching for join='inner' to clean up the NaN. When stacking rows produces the four-column mess from the worked example, join='inner' looks like the tidy-up. It is not. It keeps only the columns present in every input, and if the inputs have no column names in common, that is none of them:

pd.concat(all_dfs.values(), join='inner').shape

(820, 0)

820 rows and zero columns, returned without complaint. join='inner' is a real answer when you are stacking frames that mostly agree and you want to drop the odd extra column. It is never the answer to columns that should have had the same name and did not. Fix the names.

Forgetting ignore_index=True after a stack. When the frames you stack carry meaningful indexes, concat keeps them, and you end up with a data frame whose labels repeat:

flat = pd.concat([df.reset_index() for df in hpi.values()])
flat.index.is_unique

False

flat.loc[0]

  observation_date    hpi
0       1975-01-01  78.08
0       1975-01-01  41.66
0       1975-01-01  55.99
0       1975-01-01  66.04

flat.loc[0] returned four rows. Code downstream that expected one row now gets a data frame, and the failure surfaces somewhere else entirely. Either pass ignore_index=True to renumber from zero, or pass keys= to make the duplication explicit as a second index level. Choose one; do not leave it undecided.

Practice it

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

Go deeper

If the two frames are connected by a shared column of values rather than a shared index, you want merge, which also has the longer treatment of how merge and join differ. The Pandas user guide on merge, join, concatenate and compare is the canonical reference, and it is unusually good on the alignment rules that make all three of these methods behave the way they do.

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

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