Skip to main content
MahiDev — Learn. Build. Become AI-ready.

Lesson 20

Combining DataFrames

Stack tables with concat and match them up on a shared column with merge, choosing inner, left, or outer to decide which rows survive.

Intermediate25 min
Combining DataFrames hero Module 24 Lesson 20Combining DataFrames hero Module 24 Lesson 20

Two tables meet in one of two ways. concat stacks them, on top of each other or side by side. merge matches rows through a shared column, the way a database join does.

Two tables sharing a track column, ready to be merged on itTwo tables sharing a track column, ready to be merged on it

Stack rows with concat

Pass a list of tables. With matching columns the rows pile up, and ignore_index=True renumbers them so no label repeats. A column that only one table has is filled with NaN for the others.

python
import pandas as pd

autumn = pd.DataFrame({"name": ["Avery", "Jordan"], "hours": [42, 31]})
winter = pd.DataFrame({"name": ["Casey", "Riley"], "hours": [55, 24]})
print(pd.concat([autumn, winter]))
print(pd.concat([autumn, winter], ignore_index=True))
print(pd.concat([autumn, winter], keys=["autumn", "winter"]))

Output:

text
     name  hours
0   Avery     42
1  Jordan     31
0   Casey     55
1   Riley     24
     name  hours
0   Avery     42
1  Jordan     31
2   Casey     55
3   Riley     24
            name  hours
autumn 0   Avery     42
       1  Jordan     31
winter 0   Casey     55
       1   Riley     24

keys labels each block, so the result remembers which table every row came from.

Side by side with axis=1

axis=1 puts the columns of one table next to the other. pandas lines the rows up on the index, not on the order, so mismatched labels give you gaps rather than a silent mistake.

python
import pandas as pd

hours = pd.DataFrame({"hours": [42, 31, 55]}, index=["Avery", "Jordan", "Casey"])
scores = pd.DataFrame({"score": [88, 74]}, index=["Avery", "Casey"])
print(pd.concat([hours, scores], axis=1))
print(pd.concat([hours, scores], axis=1, join="inner"))

Output:

text
        hours  score
Avery      42   88.0
Jordan     31    NaN
Casey      55   74.0
       hours  score
Avery     42     88
Casey     55     74

Match on a column with merge

on names the column both tables share. The default how="inner" keeps only the values found in both, which is why a merge can quietly shrink your data.

python
import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley"],
    "track": ["Python", "NumPy", "Python", "SQL"],
})
mentors = pd.DataFrame({
    "track": ["Python", "NumPy", "pandas"],
    "mentor": ["Ines", "Devi", "Tomas"],
})
print(pd.merge(learners, mentors, on="track"))

Output:

text
     name   track mentor
0   Avery  Python   Ines
1  Jordan   NumPy   Devi
2   Casey  Python   Ines

Choose which rows survive

how="left" keeps every left row and leaves NaN where the right table had nothing. outer keeps everything from both sides. indicator=True adds a column saying where each row came from, which is the fastest way to see what failed to match.

python
import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley"],
    "track": ["Python", "NumPy", "Python", "SQL"],
})
mentors = pd.DataFrame({
    "track": ["Python", "NumPy", "pandas"],
    "mentor": ["Ines", "Devi", "Tomas"],
})
print(learners.merge(mentors, on="track", how="left"))
print(learners.merge(mentors, on="track", how="outer", indicator=True))

Output:

text
     name   track mentor
0   Avery  Python   Ines
1  Jordan   NumPy   Devi
2   Casey  Python   Ines
3   Riley     SQL    NaN
     name   track mentor      _merge
0  Jordan   NumPy   Devi        both
1   Avery  Python   Ines        both
2   Casey  Python   Ines        both
3   Riley     SQL    NaN   left_only
4     NaN  pandas  Tomas  right_only

When the columns have different names

left_on and right_on match columns that mean the same thing but are spelled differently. If both tables have a column of the same name that you are not matching on, suffixes keeps them apart.

python
import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan"],
    "track": ["Python", "NumPy"],
    "score": [88, 74],
})
targets = pd.DataFrame({
    "course": ["Python", "NumPy"],
    "score": [80, 80],
})
joined = learners.merge(
    targets, left_on="track", right_on="course", suffixes=("", "_target")
)
print(joined)
print(joined["score"] - joined["score_target"])

Output:

text
     name   track  score  course  score_target
0   Avery  Python     88  Python            80
1  Jordan   NumPy     74   NumPy            80
0    8
1   -6
dtype: int64

Key takeaways

  • concat stacks tables: rows by default, columns with axis=1

  • concat on axis=1 lines rows up on the index, not on their order

  • merge matches rows through a shared column

  • how chooses the survivors: inner keeps matches, left keeps the left table

  • Next: Reading and Writing Files

  • Previous: agg, transform, and filter

Loading playground…

Check your understanding

Combining DataFrames Quiz

What does concat do by default?

1 / 10

Previous

← agg, transform, and filter

Next

Reading and Writing Files →