Merge/Join pandas DataFrames

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

Reference¶

https://pandas.pydata.org/pandas-docs/stable/merging.html

http://stackoverflow.com/questions/22676081/pandas-the-difference-between-join-and-merge

Comment¶

You are able to specify (via left_on and right_on) which columns to join in each data frame.
Columns that appear in both data frames but not used in joining are distinguished using suffixes.

import pandas as pd

df1 = pd.DataFrame({"x": [1, 2, 3], "y": [5, 4, 3]})
print(df1)

df2 = pd.DataFrame({"x": [10, 20, 30], "z": ["a", "b", "c"]})
print(df2)

Default Join¶

Columns (x in this case) appear in both data frames are used for joining.

df1.merge(df2)

Join on Index¶

df1.merge(df2, left_index=True, right_index=True)

Join on Specified Columns¶

import pandas as pd

df1 = pd.DataFrame({"id": [1, 2, 3], "v": [5, 4, 3]})
print(df1)

df2 = pd.DataFrame({"x": [1, 2, 3], "y": ["a", "b", "c"]})
print(df2)

df1.merge(df2, left_on="id", right_on="x")

Cartesion/Cross Join¶

https://apassionatechie.wordpress.com/2018/01/15/pandas-equivalent-of-sql-cross-join-cartesian-product/

import pandas as pd

df1 = pd.DataFrame({"id": [1, 2], "v": [5, 4]})
df1

df2 = pd.DataFrame({"x": [10, 20], "y": ["a", "b"]})
df2

df1.assign(key=1).merge(df2.assign(key=1))

df1.assign(key=1).merge(df2.assign(key=1)).drop("key", axis=1)