pandas merge, join and concat: Combine DataFrames Without Losing Rows

Data · By the Loop & Branch editorial team · Published 2026-11-06

Real data rarely arrives in one table. Customers are in one file, orders in another, country names in a third. pandas gives you three tools to put them together: merge() matches rows on key values like a SQL join, join() does the same using the index, and concat() stacks tables on top of each other. Most bugs come not from the syntax but from the keys: duplicates that multiply rows, and types that silently refuse to match.

All examples were run with Python 3.13 and pandas 3.0. The functions shown have been stable for many versions, but error messages may be worded slightly differently in older releases.

The example data

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "name": ["Ada", "Grace", "Linus", "Guido"],
    "country": ["UK", "US", "FI", "NL"],
})
orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105],
    "customer_id": [1, 1, 2, 4, 5],
    "amount": [30.0, 12.5, 99.0, 45.0, 20.0],
})

Two details matter for what follows: customer 3 (Linus) has no order, and order 105 belongs to a customer 5 who does not exist in customers.

merge: the basic join

pd.merge(customers, orders, on="customer_id")
# same thing, method style:
customers.merge(orders, on="customer_id")

   customer_id   name country  order_id  amount
0            1    Ada      UK       101    30.0
1            1    Ada      UK       102    12.5
2            2  Grace      US       103    99.0
3            4  Guido      NL       104    45.0

By default merge performs an inner join: only keys present in both tables survive. Linus disappeared because he has no order, and order 105 disappeared because its customer is unknown. Ada appears twice because she has two orders; each match produces a row. If you leave out on, pandas joins on every column the two tables share, which is convenient until someone adds a column with the same name to one of them. Naming the key explicitly is safer.

The four join types

`how=`	Keeps	In our example
`"inner"` (default)	keys found in both tables	4 rows: Linus and order 105 dropped
`"left"`	every row of the left table	5 rows: Linus kept with empty order columns
`"right"`	every row of the right table	5 rows: order 105 kept with empty customer columns
`"outer"`	every key from either table	6 rows: both kept

A left join is the usual choice when the left table is your "main" list and the right one adds information:

customers.merge(orders, on="customer_id", how="left")

   customer_id   name country  order_id  amount
0            1    Ada      UK     101.0    30.0
1            1    Ada      UK     102.0    12.5
2            2  Grace      US     103.0    99.0
3            3  Linus      FI       NaN     NaN
4            4  Guido      NL     104.0    45.0

Notice that order_id became 101.0: a missing value in an integer column forces pandas to switch to floats. If that bothers you, convert the column to the nullable integer type afterwards with .astype("Int64") (capital I).

There is also how="cross", which pairs every row of one table with every row of the other (4 × 5 = 20 rows here). It is useful for building grids of combinations, and a mistake almost everywhere else.

Find the rows that did not match: indicator=True

The most useful argument you have probably never used. It adds a _merge column telling you where each row came from:

check = customers.merge(orders, on="customer_id", how="outer", indicator=True)
print(check)

   customer_id   name country  order_id  amount      _merge
0            1    Ada      UK     101.0    30.0        both
1            1    Ada      UK     102.0    12.5        both
2            2  Grace      US     103.0    99.0        both
3            3  Linus      FI       NaN     NaN   left_only
4            4  Guido      NL     104.0    45.0        both
5            5    NaN     NaN     105.0    20.0  right_only

Filtering on it gives you an instant data-quality report, for example orders pointing to unknown customers:

check.loc[check["_merge"] == "right_only", ["order_id", "customer_id"]]

   order_id  customer_id
5     105.0            5

Running an outer merge with indicator=True once, before the merge you actually want, is a cheap habit that catches most join problems early.

Different column names: left_on and right_on

When the key is called customer_id in one table and client in the other, name both sides:

ref = orders.rename(columns={"customer_id": "client"})
customers.merge(ref, left_on="customer_id", right_on="client")

   customer_id   name country  order_id  client  amount
0            1    Ada      UK       101       1    30.0
1            1    Ada      UK       102       1    12.5
2            2  Grace      US       103       2    99.0
...

Both key columns are kept. Drop the redundant one with .drop(columns="client"). To join on several columns at once, pass lists: on=["year", "region"].

Overlapping column names: suffixes

If both tables have a non-key column with the same name, pandas renames them _x and _y:

crm = pd.DataFrame({"customer_id": [1, 2], "name": ["Ada L.", "Grace H."]})
customers.merge(crm, on="customer_id")

   customer_id name_x country    name_y
0            1    Ada      UK    Ada L.
1            2  Grace      US  Grace H.

Names like name_x are easy to mix up later. Choose meaningful suffixes; an empty string keeps the left column's original name:

customers.merge(crm, on="customer_id", suffixes=("", "_crm"))

   customer_id   name country  name_crm
0            1    Ada      UK    Ada L.
1            2  Grace      US  Grace H.

Duplicate keys and validate=

A merge returns one row for every matching pair of rows. If a key appears twice on each side, you get four rows for it. This is how a lookup table with an accidental duplicate quietly inflates totals:

dup = pd.concat([customers, customers.iloc[[0]]])   # Ada listed twice
len(orders.merge(dup, on="customer_id"))             # 6, not 4

Ada's two orders are now counted twice each, and any sum over amount is wrong. The validate argument turns this silent problem into an error. Use "one_to_one", "one_to_many", "many_to_one" or "many_to_many" to state what you expect:

orders.merge(customers, on="customer_id", validate="many_to_one")   # fine
customers.merge(orders, on="customer_id", validate="one_to_one")

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

Adding validate to every merge in a pipeline costs one argument and protects every figure downstream. To find the offending keys, use df[df.duplicated("customer_id", keep=False)].

When a merge returns nothing: key types

An empty or half-empty result usually means the keys look the same but are not. Three common causes:

Numbers against strings. If one file was read with IDs as text, pandas refuses to merge:
```
ValueError: You are trying to merge on int64 and str columns for key 'customer_id'. If you wish to proceed you should use pd.concat
```
Convert one side, for example orders["customer_id"] = orders["customer_id"].astype("int64"), or read the file with an explicit dtype=. Leading zeros ("007") are a reason to keep IDs as strings on both sides instead.
Invisible whitespace and case. "A1 " does not match "A1", and "b2" does not match "B2". In a test with two such codes, the merge returned 0 rows; after df["code"] = df["code"].str.strip().str.upper() on both sides it returned 2.
Missing keys. Unlike SQL, pandas matches NaN keys with each other, so rows with an unknown ID on both sides get paired. Drop them first with dropna(subset=["customer_id"]) if that is not what you want.

Encoding problems when reading the files in the first place are a separate issue, covered in fixing UnicodeDecodeError when reading a CSV in pandas.

join: merging on the index

DataFrame.join() is a shortcut for merges that involve the index. It does a left join by default. Its on argument names a column of the calling table to match against the index of the other one:

countries = pd.DataFrame(
    {"country_name": ["United Kingdom", "United States", "Finland"]},
    index=["UK", "US", "FI"],
)
customers.join(countries, on="country")

   customer_id   name country    country_name
0            1    Ada      UK  United Kingdom
1            2  Grace      US   United States
2            3  Linus      FI         Finland
3            4  Guido      NL             NaN

It pairs naturally with aggregation, because groupby puts the group keys in the index. Total spend per customer, attached to the customer table:

totals = orders.groupby("customer_id")["amount"].sum()
customers.set_index("customer_id").join(totals)

              name country  amount
customer_id
1              Ada      UK    42.5
2            Grace      US    99.0
3            Linus      FI     NaN
4            Guido      NL    45.0

Our guide to pandas groupby covers the aggregation side in detail. With merge, the equivalent is left_index=True and/or right_index=True.

concat: stacking tables

pd.concat() does not match keys at all. It glues tables together, by default one under the other, aligning columns by name. It is the right tool for monthly files with the same structure:

jan = pd.DataFrame({"order_id": [1, 2], "amount": [10, 20]})
feb = pd.DataFrame({"order_id": [3], "amount": [15]})
pd.concat([jan, feb], ignore_index=True)

   order_id  amount
0         1      10
1         2      20
2         3      15

Without ignore_index=True you keep the original row labels, so the index would read 0, 1, 0. Use keys=["jan", "feb"] instead if you want to remember which file each row came from; it adds an outer index level. Columns that exist in only some of the tables are filled with NaN, and axis=1 places tables side by side, aligned on the index.

To combine many files, build a list and call concat once: pd.concat([pd.read_csv(p) for p in paths], ignore_index=True). Calling it inside a loop, one file at a time, copies the growing table on every iteration and gets slow quickly. The same pattern works for a folder of yearly downloads, as in our property-price analysis.

Which one should I use?

You want to…	Use
add columns by matching key values	`merge(on=…, how=…)`
add columns by matching the index	`join()` or `merge(left_index=True, right_index=True)`
add rows from tables with the same columns	`concat()`
check which rows matched	`merge(how="outer", indicator=True)`
guarantee keys are unique	`merge(validate="one_to_one")` or `"many_to_one"`

Summary

Name your key with on, choose the join type deliberately (inner drops unmatched rows without warning), check unmatched rows with indicator=True, protect totals with validate, give overlapping columns meaningful suffixes, and make sure keys have the same type and formatting on both sides. Use join when the key is in the index, and concat when you are adding rows rather than columns.

Sources

pandas documentation, Merge, join, concatenate and compare (user guide).
pandas documentation, pandas.merge (API reference: how, indicator, validate, suffixes).