Merging multiple dataframes on different columns

Question

Using Pandas 1.2.1

MRE:

df_a = pd.DataFrame({"A":[1,2,3,4], "B":[33, 44, 55, 66]})
df_b = pd.DataFrame({"B":[33, 44,99], "C":["v", "z", "z"]})
df_c = pd.DataFrame({"A":[3,4,77,55], "D":["aa", "bb", "cc", "dd"]})

Using three dfs created above I want to join all of them together however

df_a, df_b share column "B" therefore they join on column "B"
df_a, df_c share column "A" therefore they join on column "A"

I want to left_join df_b and df_c onto df_a. currently this is my method:

merged_df = pd.merge(df_a, df_b, on=["B"], how="left")
merged_df = pd.merge(merged_df, df_c, on=["A"], how="left")

I know works fine however I cannot stop to think there is a easier and faster way, there are multiple questions on joining multiple dfs on same column using reduce function however could not find solution for my question.

jezrael · Accepted Answer · 2021-02-09 10:26:12Z

3

You can remove on parameter, so it merging by intersection of columns names between DataFrames:

merged_df = pd.merge(df_a, df_b, how="left")
merged_df = pd.merge(merged_df, df_c, how="left")

More dynamic is use reduce, also is removed on parameter:

from functools import reduce
dfList = [df1, df2, df3]
df = reduce(lambda df1,df2: pd.merge(df1,df2,how="left"), dfList)

answered Feb 9, 2021 at 10:26

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

haneulkim Over a year ago

The reason I added col name is because readability, is there an alternative way to do it with more readability?

jezrael Over a year ago

@Ambleu - Hmmm, I think then need your solution, only remove ['A'] to A, similar for B

haneulkim Over a year ago

Could you clarify? not fully understanding it.

jezrael Over a year ago

@Ambleu - It is only small changhe in your solution like merged_df = pd.merge(df_a, df_b, on=["B"], how="left") to merged_df = pd.merge(df_a, df_b, on="B", how="left")

haneulkim Over a year ago

Thanks! I am curious if there is similar method for joining columns however in this case joining columns to not share same name, in such case code like my example is cleanest it could be?

|

Collectives™ on Stack Overflow

Merging multiple dataframes on different columns

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related