Pandas Merging and Joining

Data often comes in several tables, and Pandas can combine them into one.

Stacking Tables With concat

Use pd.concat() when two tables have the same columns and you want to put one under the other, for example January and February sales:

Example

python

import pandas as pd

jan = pd.DataFrame({"product": ["Tea", "Rice"], "sold": [40, 60]})
feb = pd.DataFrame({"product": ["Tea", "Sugar"], "sold": [55, 30]})

both = pd.concat([jan, feb], ignore_index=True)
print(both)

Output

  product  sold
0     Tea    40
1    Rice    60
2     Tea    55
3   Sugar    30

ignore_index=True numbers the rows again from 0. Without it, you would get the index 0, 1, 0, 1.

Joining Tables With merge

Use pd.merge() when two tables share a key column and you want to bring their information side by side. Here, one table has students and another has their fee payments:

Example

python

import pandas as pd

students = pd.DataFrame({
    "roll_no": [1, 2, 3, 4],
    "name": ["Ali", "Ayesha", "Hamza", "Sana"]
})
payments = pd.DataFrame({
    "roll_no": [1, 2, 4, 5],
    "paid": [15000, 18000, 12000, 9000]
})

print(pd.merge(students, payments, on="roll_no"))

Output

   roll_no    name   paid
0        1     Ali  15000
1        2  Ayesha  18000
2        4    Sana  12000

Hamza (roll number 3) has no payment, and roll number 5 has no student, so both are left out. This is an inner join, the default.

Types of Join

how=	Keeps	Use it to
`"inner"`	Only keys found in both tables	See matching records only
`"left"`	All rows of the left table	Keep every student, paid or not
`"right"`	All rows of the right table	Keep every payment
`"outer"`	All rows from both tables	See everything, including mismatches

Example

python

print(pd.merge(students, payments, on="roll_no", how="left"))

Output

   roll_no    name     paid
0        1     Ali  15000.0
1        2  Ayesha  18000.0
2        3   Hamza      NaN
3        4    Sana  12000.0

Now Hamza is in the result with NaN for paid, so you can see who has not paid yet:

Example

python

result = pd.merge(students, payments, on="roll_no", how="left")
print(result[result["paid"].isna()]["name"].tolist())

Output

['Hamza']

The paid column became a float (15000.0) because NaN is a float value. Integers cannot hold missing values in a plain NumPy column.

An Outer Join

Example

python

print(pd.merge(students, payments, on="roll_no", how="outer"))

Output

   roll_no    name     paid
0        1     Ali  15000.0
1        2  Ayesha  18000.0
2        3   Hamza      NaN
3        4    Sana  12000.0
4        5     NaN   9000.0

Different Column Names

If the key has a different name in each table, use left_on and right_on:

Example

python

import pandas as pd

products = pd.DataFrame({"id": [10, 20], "item": ["Charger", "Earbuds"]})
orders = pd.DataFrame({"product_id": [20, 10, 20], "qty": [1, 2, 3]})
print(pd.merge(orders, products, left_on="product_id", right_on="id"))

Output

   product_id  qty  id     item
0          20    1  20  Earbuds
1          10    2  10  Charger
2          20    3  20  Earbuds

Exercise

You want to keep every row of the left table, even if it has no match in the right table. Which join do you use?

how="inner"how="left"how="right"pd.concat()