merge multiple dataframes with different columns names and different rows number

Question

I want to merge many dataframes with 2 columns each: one common column teams, rows numbers in each dataframe is different, and teams can be present in different number of dataframe or all of them.

#df1
team_col    pattern_A
team_1      1
team_3      1
team_4      1

#df2
teams_col   pattern_B
team_1      1
team_2      1

#df3
team_col    pattern_C
team_2      1
team_4      1
team_5      1

#df4
team_col    pattern_D
team_1      1
team_3      1
team_5      1

I want a single dataframe with the column teams and all other columns. I want to associate each team with his patterns. Like below

team_col    pattern_A   pattern_B   pattern_C   pattern_D
team_1      1           1           0           1
team_2      0           1           1           0
team_3      1           0           0           1
team_4      1           0           1           0
team_5      0           0           1           1

adir abargil · Accepted Answer · 2021-01-03 21:24:06Z

2

dfs = [df2,df3,df5]
df1 = df1.set_index('team_col')
for df in dfs:
   df1 = df1.add(df.set_index('team_col'),fill_values=0)
>>>df1.reset_index(drop=True)

team_col    pattern_A   pattern_B   pattern_C   pattern_D
team_1      1           1           0           1
team_2      0           1           1           0
team_3      1           0           0           1
team_4      1           0           1           0
team_5      0           0           1           1

edited Jan 3, 2021 at 21:24

answered Jan 3, 2021 at 20:49

adir abargil

5,7453 gold badges23 silver badges29 bronze badges

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

5 Comments

Khaled Koubaa Over a year ago

return error: "TypeError: can only concatenate str (not "float") to str"

adir abargil Over a year ago

This probebly has to do with your dataframe dtypes.. can you tey to print each df as so df.dtypes ?

Khaled Koubaa Over a year ago

all dataframes has the "teams" column as object, and the second columns as int64

adir abargil Over a year ago

Im not in front of computer i will check in the morning, (its evening here) sorry for that one

adir abargil Over a year ago

Edited accordingly.. try now... forgot to reset index inplace

Collectives™ on Stack Overflow

merge multiple dataframes with different columns names and different rows number

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related