How to merge data in pandas, R and SQL
By the Analistable team · Updated · 12 min read
pandas, R and SQL all do the same two operations. Joining matches rows on a key: df1.merge(df2, on='id', how='left') in pandas, left_join(df1, df2, by = join_by(id)) in R, LEFT JOIN … ON in SQL. Stacking appends rows: pd.concat, bind_rows and UNION ALL. Pick the join type by which unmatched rows you want to keep.
The same join in three languages
| Language | Code |
|---|---|
| pandas | customers.merge(orders, on='customer_id', how='left') |
| R (base) | merge(customers, orders, by = 'customer_id', all.x = TRUE) |
| R (dplyr) | left_join(customers, orders, by = join_by(customer_id)) |
| SQL | SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id |
Join types mapped across languages
| Join | pandas how= | base R merge() | dplyr | SQL |
|---|---|---|---|---|
| Inner | 'inner' (default) | default | inner_join | INNER JOIN |
| Left | 'left' | all.x = TRUE | left_join | LEFT JOIN |
| Right | 'right' | all.y = TRUE | right_join | RIGHT JOIN |
| Full outer | 'outer' | all = TRUE | full_join | FULL OUTER JOIN |
| Anti | indicator=True, then filter | — | anti_join | LEFT JOIN … WHERE b.key IS NULL |
Stacking (appending) rows
- pandas:
pd.concat([jan, feb, mar], ignore_index=True)aligns columns by name. - R:
dplyr::bind_rows(jan, feb, mar)aligns by name and fills missing columns with NA. - SQL:
SELECT … FROM jan UNION ALL SELECT … FROM febaligns by position;UNIONalso removes duplicates.
Checks that prevent wrong results
- Confirm key uniqueness before joining. In pandas,
validate='one_to_many'raises an error if the left keys repeat. - Compare row counts before and after. Unexpected growth means duplicate keys.
- Match types: an integer
idnever equals a string'id'. - Rename clashing columns (pandas adds
_xand_ysuffixes).
Language guides
Without writing code
If the data starts in spreadsheets, Analistable loads it into an in-browser SQL engine (DuckDB) and writes the join for you from a plain-language question. You can read the SQL it generates, so it doubles as a way to learn the syntax.
Worked example: the same data, the same answer
Customers (C-101 Bakery Lune, C-102 Hart & Co, C-103 Nordic Supply) and Orders (O-9001 C-101 120, O-9002 C-103 75, O-9003 C-101 80, O-9004 C-104 60). A left join from customers, summed per customer, gives the same table in every language:
| name | orders | total |
|---|---|---|
| Bakery Lune | 2 | 200 |
| Hart & Co | 0 | 0 |
| Nordic Supply | 1 | 75 |
(customers.merge(orders, on="customer_id", how="left")
.groupby("name", as_index=False)
.agg(orders=("order_id", "count"), total=("amount", "sum")))customers |>
left_join(orders, by = join_by(customer_id)) |>
summarise(orders = sum(!is.na(order_id)), total = sum(amount, na.rm = TRUE), .by = name)SELECT c.name, COUNT(o.order_id) AS orders, COALESCE(SUM(o.amount), 0) AS total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;Order O-9004 (customer C-104) isn't in the result because the join starts from customers. Start from orders, or use a full outer join, to see it.
Which should you use?
- SQL when the data already lives in a database, or when the logic should be readable by analysts across teams.
- pandas for data pipelines in Python, files of any size that fit in memory, and when the next step is modelling or plotting in Python.
- R / dplyr for statistical analysis and reporting, especially with the tidyverse.
- None of them when the question is one-off and the data is a few spreadsheets: a tool that writes the SQL for you is faster.
Performance notes
All three handle millions of rows on a laptop, but in different ways: pandas and R hold the data in memory, so very large joins need enough RAM; databases join on disk and use indexes on the key columns. In any language, joining on clean, typed keys (integers or trimmed text) is faster and safer than joining on free text.
Prepare the keys before you join
Most joins that return nothing, or return too little, fail on the key rather than the join. Data from spreadsheets is the usual culprit: IDs typed with a trailing space, codes stored as numbers in one file and text in the other, upper and lower case mixed. Normalise both keys the same way first:
for df in (customers, orders):
df["customer_id"] = df["customer_id"].astype(str).str.strip().str.upper()customers <- customers |> mutate(customer_id = toupper(trimws(as.character(customer_id))))
orders <- orders |> mutate(customer_id = toupper(trimws(as.character(customer_id))))SELECT *
FROM customers c
LEFT JOIN orders o ON UPPER(TRIM(o.customer_id)) = UPPER(TRIM(c.customer_id));Leading zeros need attention when reading files: pandas reads "00742" as the number 742 unless you pass dtype={"customer_id": str} to read_csv or read_excel, and readr can be told the same with col_types = cols(customer_id = col_character()). In a database, wrapping a key in a function in the ON clause usually stops an index on that column from being used, so for large tables clean the column once and store the result.
Different key names and keys made of several columns
| Case | pandas | dplyr | SQL |
|---|---|---|---|
| Key has a different name in each table | left_on="cust", right_on="customer_id" | join_by(cust == customer_id) | ON a.cust = b.customer_id |
| Key is two columns | on=["store", "sku"] | join_by(store, sku) | ON a.store = b.store AND a.sku = b.sku |
| Key is the row index | left_index=True, right_index=True, or df.join() | Turn row names into a column first | Not applicable — use a column |
With left_on and right_on, pandas keeps both key columns in the result; drop one afterwards. For a two-column key, joining on only one of the columns is a classic mistake: an SKU joined without its store matches every store's row for that SKU and multiplies the result.
Duplicate keys and row explosion
A join returns one output row for every pair of matching rows. If the lookup table has a key twice, every row that matches it is doubled. Here a price list accidentally contains SKU A1 twice, at an old and a new price:
| order_id | sku | qty | price |
|---|---|---|---|
| 1 | A1 | 2 | 4 |
| 1 | A1 | 2 | 4.5 |
| 2 | A1 | 1 | 4 |
| 2 | A1 | 1 | 4.5 |
| 3 | B2 | 5 | 10 |
Three order lines became five rows, and any total of qty is now inflated from 8 to 11. Each language has a way to catch this before it reaches a report:
- pandas:
lines.merge(prices, on="sku", validate="many_to_one")raises a MergeError saying the merge keys are not unique in the right dataset. - dplyr (1.1 and later):
left_join(lines, prices, by = join_by(sku), relationship = "many-to-one")stops with an error when a line matches more than one price; without the argument, dplyr warns about an unexpected many-to-many relationship. - SQL: check the lookup table first with
SELECT sku, COUNT(*) FROM prices GROUP BY sku HAVING COUNT(*) > 1.
Fixing it is a business decision, not a coding one: keep the latest price by date, or add the effective date to the key so each order matches the price that applied at the time.
Anti joins and semi joins: what didn't match
Using the customers and orders from the worked example, two questions come up straight after any join: which customers have no orders, and which orders point to a customer that doesn't exist?
m = customers.merge(orders, on="customer_id", how="left", indicator=True)
no_orders = m.loc[m["_merge"] == "left_only", ["customer_id", "name"]]
m2 = orders.merge(customers, on="customer_id", how="left", indicator=True)
orphans = m2.loc[m2["_merge"] == "left_only", ["order_id", "customer_id", "amount"]]
with_orders = customers[customers["customer_id"].isin(orders["customer_id"])] # semi joinno_orders <- anti_join(customers, orders, by = join_by(customer_id))
orphans <- anti_join(orders, customers, by = join_by(customer_id))
with_orders <- semi_join(customers, orders, by = join_by(customer_id))SELECT c.customer_id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
SELECT o.order_id, o.customer_id, o.amount FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);All three return C-102 Hart & Co as the customer with no orders, and O-9004 (C-104, 60) as the orphan order. The semi join returns Bakery Lune and Nordic Supply once each, even though Bakery Lune has two orders — that is the difference between a semi join and an inner join. In SQL, prefer NOT EXISTS to NOT IN (SELECT …): if the subquery returns a NULL, NOT IN returns no rows at all.
Stacking tables with different columns
Monthly exports drift: February gains a discount column that January doesn't have. pandas and dplyr handle this by name, SQL by position:
combined = pd.concat(
[jan.assign(source="jan"), feb.assign(source="feb")],
ignore_index=True,
) # columns missing from a file are filled with NaNcombined <- bind_rows(jan = jan, feb = feb, .id = "source") # missing columns become NASELECT order_id, amount, NULL AS discount, 'jan' AS source FROM jan
UNION ALL
SELECT order_id, amount, discount, 'feb' AS source FROM feb;
-- DuckDB can match columns by name instead of position:
SELECT * FROM jan UNION ALL BY NAME SELECT * FROM feb;Adding a source column at the stacking step costs nothing and saves a lot of guessing later, when a total looks wrong and you need to know which file a row came from.
Reading many spreadsheets in one go
import pandas as pd
from pathlib import Path
sheets = pd.read_excel("sales.xlsx", sheet_name=None) # dict of all sheets
by_sheet = pd.concat([df.assign(sheet=name) for name, df in sheets.items()], ignore_index=True)
files = sorted(Path("exports").glob("*.csv"))
by_file = pd.concat([pd.read_csv(f).assign(file=f.name) for f in files], ignore_index=True)library(readxl); library(purrr); library(dplyr)
path <- "sales.xlsx"
by_sheet <- excel_sheets(path) |>
set_names() |>
map(\(s) read_excel(path, sheet = s)) |>
list_rbind(names_to = "sheet")-- DuckDB reads a folder of CSV files as one table
SELECT * FROM read_csv('exports/*.csv', union_by_name = true, filename = true);Once the files are one table, every join in this guide applies unchanged. For the spreadsheet-only route, see merging CSV files and merging Excel files.
Choosing the join type
| Question | Join | Starts from |
|---|---|---|
| Every order with its customer's details | Left join | Orders |
| Every customer, including those with no orders | Left join | Customers |
| Only customers who have ordered, with order detail | Inner join | Either |
| Customers who have ordered, one row each | Semi join | Customers |
| Customers with no orders | Anti join | Customers |
| Orders whose customer is missing | Anti join | Orders |
| Everything from both, matched where possible | Full outer join | Either |
When in doubt, start with a left join from the table whose rows you are reporting on, then count the unmatched rows. An inner join hides unmatched rows silently, which is fine when you mean it and a source of quiet errors when you don't. Right joins are just left joins written the other way round; most people swap the table order instead.
Worked example: aggregate before you join
Add a refunds table — R-1 C-101 20, R-2 C-101 10, R-3 C-103 15 — and ask for sales, refunds and net per customer. Joining the raw tables first gives the wrong answer:
-- Wrong: C-101 has 2 orders and 2 refunds, so the join makes 4 rows
SELECT o.customer_id, SUM(o.amount) AS sales, SUM(r.refund) AS refunds
FROM orders o JOIN refunds r ON r.customer_id = o.customer_id
GROUP BY o.customer_id;
-- C-101: sales 400, refunds 60 (true values: 200 and 30)Summarise each table to one row per customer, then join the summaries:
WITH s AS (SELECT customer_id, SUM(amount) AS sales FROM orders GROUP BY customer_id),
r AS (SELECT customer_id, SUM(refund) AS refunds FROM refunds GROUP BY customer_id)
SELECT s.customer_id, s.sales, COALESCE(r.refunds, 0) AS refunds,
s.sales - COALESCE(r.refunds, 0) AS net
FROM s LEFT JOIN r ON r.customer_id = s.customer_id
ORDER BY s.customer_id;| customer_id | sales | refunds | net |
|---|---|---|---|
| C-101 | 200 | 30 | 170 |
| C-103 | 75 | 15 | 60 |
| C-104 | 60 | 0 | 60 |
The pandas version follows the same shape: groupby("customer_id") on each table, then merge(..., how="left") and fillna(0) for customers with no refunds. The inner join in the wrong version also dropped C-104, who had no refunds — two mistakes from one join.
Joining on dates and ranges
Some matches aren't equal keys: an order should pick up the exchange rate or price that applied on its date. Each tool has a way to join to the nearest earlier row:
- pandas:
pd.merge_asof(orders, rates, on="date", by="currency")takes the last rate on or before each order date. Both tables must be sorted by the date column. - dplyr 1.1+:
left_join(orders, rates, by = join_by(currency, closest(date >= rate_date)));between()andoverlaps()handle date ranges. - SQL: join on a range,
ON o.order_date BETWEEN p.valid_from AND p.valid_to. DuckDB also offers anASOF JOINfor the nearest-earlier case.
Check the result after every join
merged = orders.merge(customers, on="customer_id", how="left",
validate="many_to_one", indicator=True)
assert len(merged) == len(orders)
print(merged["_merge"].value_counts()) # both 3, left_only 1For a many-to-one left join — orders looking up their customer — the row count must not change, so assert it. The indicator column then says how many rows found a match: with the example data, 3 orders matched and 1 (O-9004) didn't. The R equivalent is stopifnot(nrow(merged) == nrow(orders)) followed by sum(is.na(merged$name)); in SQL, run COUNT(*) on the source and the joined query and compare.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| More rows after the join than before | Duplicate keys in the lookup table | Find duplicates, deduplicate, or use validate / relationship |
| Every joined column is NaN, NA or NULL | Keys differ in type, spaces or case | Normalise both keys before joining |
| pandas error about merging int64 and object (or str) columns | The key is a number in one table and text in the other | Convert both with astype(str) or astype(int) |
| Columns called amount_x and amount_y | A non-key column exists in both tables | Rename first, or pass suffixes=("_order", "_refund") |
| SQL error: ambiguous column name | An unqualified column exists in both tables | Prefix it with the table alias, such as c.customer_id |
| UNION fails on the number of columns | The SELECTs return different column counts | Add NULL AS column to the shorter one |
| Totals too high after join and sum | A one-side value repeated by a one-to-many join | Aggregate each table before joining |
| Leading zeros missing from IDs | The file was read with numeric types | Read the key column as text |
Every guide in this topic
- How to combine two tables in SQL
Combine two tables in SQL side by side with JOIN or stacked with UNION and UNION ALL: syntax, examples, column rules and how to choose.
- How to join multiple tables in SQL
Copy-paste examples for joining 3 or more tables in SQL: chain JOIN … ON, mix INNER and LEFT joins safely and avoid double-counted totals.
- How to merge CSV files in Python
Merge CSV files in Python: stack a folder of CSVs with glob and pandas.concat or the csv module, or join two CSVs on a shared key column.
- How to merge data frames in R
Merge data frames in R with base merge() or dplyr joins: inner, left, full and anti joins, different key names, and stacking with rbind or bind_rows.
- How to merge DataFrames in pandas
Merge two pandas DataFrames on a key with merge(): inner, left, outer and anti joins, different key names, validate, indicator, and when to use concat.
- How to merge datasets in SAS
Merge datasets in SAS with DATA step MERGE and BY: sorting, IN= flags for inner, left and anti joins, one-to-many merges, and PROC SQL alternatives.
- How to merge datasets in Stata
Merge datasets in Stata with merge 1:1, m:1 and 1:m, read the _merge variable, keep only matches, and stack datasets with append. Examples included.
- How to use MERGE (upsert) in BigQuery and Snowflake
Use MERGE in BigQuery and Snowflake to upsert: WHEN MATCHED and NOT MATCHED clauses, deletes, and why you must dedupe the source table first.
- Python vs Excel for merging files: when pandas is worth it
When pandas beats Excel and Power Query for merging files, and when it does not: size, repeatability, sharing and the skills each route needs.
- Why a SQL join returns too many rows, and how to fix it
A SQL join returns too many rows when keys repeat or a join condition is missing. Count keys first, then aggregate in a CTE before joining.
- Why pandas merge creates duplicate rows, and how to stop it
pandas merge multiplies rows when a key repeats on both sides. Find the culprit with validate= and indicator=, then dedupe or aggregate before you merge.
- Why R merge() drops rows unexpectedly, and how to keep them
R's merge() is an inner join by default, so unmatched rows vanish. Fix it with all.x, consistent key types, trimmed text and careful NA and factor handling.
Frequently asked questions
- What is the difference between merge and concat in pandas?
- merge joins tables side by side on key columns. concat stacks tables on top of each other (or side by side by index) without matching keys.
- Is a SQL JOIN the same as pandas merge?
- Yes, conceptually. pandas merge supports the same inner, left, right and full outer joins, plus a cross join.
- What does an anti join do?
- It returns rows from the first table that have no match in the second, for example customers who never placed an order.
- Why does my merge return more rows than the original table?
- The key repeats in the other table, so each original row matches several rows. Check for duplicate keys, and in pandas pass validate='many_to_one' to make the merge fail instead.
- How do I join on two columns?
- Pass a list in pandas (on=["store", "sku"]), list both columns in dplyr (join_by(store, sku)), or combine conditions with AND in SQL.
- What is the difference between UNION and UNION ALL?
- UNION ALL keeps every row from both queries. UNION also removes rows that are exact duplicates, which takes extra work and can drop rows you meant to keep, such as two identical sales on the same day.
- How do I join a CSV file to an Excel file?
- Read both into data frames (pd.read_csv and pd.read_excel, or readr and readxl) and merge them as usual. For SQL, load both into tables first; DuckDB can query CSV files directly. The spreadsheet route is covered in joining two CSV files by a column.