Merge two dataframes by index

Choose your join type

Need tailored merging to align the data? Adjust the join operations with how=... parameter in pd.merge.

# Outer join, like a humanitarian who invites everyone
outer_merged_df = pd.merge(df1, df2, left_index=True, right_index=True, how='outer')

# Left join, where df1 is the exclusive party and df2 tries to sneak in
left_merged_df = pd.merge(df1, df2, left_index=True, right_index=True, how='left')


Remember to ensure match of index types -- a panda 😅 (pd) should play with a panda, use apply(pd.to_numeric) to do the trick if needed.

Dressing up the index

Get your indexes ready for the grand merging! If not set already, make use of DataFrame.set_index:

df1 = df1.set_index('key_column')
df2 = df2.set_index('key_column')


Too much merging messes up the DataFrame? Move index to a column using reset_index:

df1.reset_index(inplace=True)


Keep dimensions in check

Before using pd.concat, ensure you're not mixing apples 🍎 and oranges 🍊 -- column counts of DataFrames should match:

# Checking if you're comparing apples to apples
assert df1.shape[1] == df2.shape[1], "I told you not to mix apples with oranges!"


A mismatch can lead to a fruit salad, not the best when it comes to data merging 😂.

Special note on pivot tables

Especially with pivot tables, keep an eye on index data types — they can be as slippery as a banana peel 🍌. Use pd.to_numeric to save your slip.

Save to excel

After meeting, greeting, and merging, consider saving the reunion in a souvenir (Excel using .to_excel())—useful for a stroll down the memory lane later and sharing with folks who don't speak pandas.