Analistable

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

Left join of customers and orders on customer_id
LanguageCode
pandascustomers.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))
SQLSELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id

Join types mapped across languages

Equivalent join arguments
Joinpandas how=base R merge()dplyrSQL
Inner'inner' (default)defaultinner_joinINNER JOIN
Left'left'all.x = TRUEleft_joinLEFT JOIN
Right'right'all.y = TRUEright_joinRIGHT JOIN
Full outer'outer'all = TRUEfull_joinFULL OUTER JOIN
Antiindicator=True, then filter—anti_joinLEFT 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 feb aligns by position; UNION also 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 id never equals a string 'id'.
  • Rename clashing columns (pandas adds _x and _y suffixes).

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:

Total spend per customer (left join + group by)
nameorderstotal
Bakery Lune2200
Hart & Co00
Nordic Supply175
(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

Joining on differently named or multi-column keys
CasepandasdplyrSQL
Key has a different name in each tableleft_on="cust", right_on="customer_id"join_by(cust == customer_id)ON a.cust = b.customer_id
Key is two columnson=["store", "sku"]join_by(store, sku)ON a.store = b.store AND a.sku = b.sku
Key is the row indexleft_index=True, right_index=True, or df.join()Turn row names into a column firstNot 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 lines joined to a price list with a duplicate key
order_idskuqtyprice
1A124
1A124.5
2A114
2A114.5
3B2510

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 join
no_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 NaN
combined <- bind_rows(jan = jan, feb = feb, .id = "source")  # missing columns become NA
SELECT 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

Which join answers which question
QuestionJoinStarts from
Every order with its customer's detailsLeft joinOrders
Every customer, including those with no ordersLeft joinCustomers
Only customers who have ordered, with order detailInner joinEither
Customers who have ordered, one row eachSemi joinCustomers
Customers with no ordersAnti joinCustomers
Orders whose customer is missingAnti joinOrders
Everything from both, matched where possibleFull outer joinEither

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;
Net sales per customer
customer_idsalesrefundsnet
C-10120030170
C-103751560
C-10460060

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() and overlaps() handle date ranges.
  • SQL: join on a range, ON o.order_date BETWEEN p.valid_from AND p.valid_to. DuckDB also offers an ASOF JOIN for 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 1

For 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

Join problems in pandas, R and SQL
SymptomLikely causeFix
More rows after the join than beforeDuplicate keys in the lookup tableFind duplicates, deduplicate, or use validate / relationship
Every joined column is NaN, NA or NULLKeys differ in type, spaces or caseNormalise both keys before joining
pandas error about merging int64 and object (or str) columnsThe key is a number in one table and text in the otherConvert both with astype(str) or astype(int)
Columns called amount_x and amount_yA non-key column exists in both tablesRename first, or pass suffixes=("_order", "_refund")
SQL error: ambiguous column nameAn unqualified column exists in both tablesPrefix it with the table alias, such as c.customer_id
UNION fails on the number of columnsThe SELECTs return different column countsAdd NULL AS column to the shorter one
Totals too high after join and sumA one-side value repeated by a one-to-many joinAggregate each table before joining
Leading zeros missing from IDsThe file was read with numeric typesRead the key column as text

Every guide in this topic

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.