Pandas - merge multiple Datasrames with different indexes and some Identical column names existing

user_8487230 0
Viewed 42

I have multiple dataframes with different index and different columns. I want to merge them and hope to combined those columns with same names into an one. I have tried outer join but it would not merge columns with same names. Also for pd.update, but since I have data with different indexes this seems not working well. Could anyone give me some suggestions? Thanks.

INPUT>

ID    Cat1    Cat2  
1     0       75  
2     61      0 


ID    Cat2    Cat3  
3     5       75  

ID    Cat2   Cat4
4     54      40 
5     20      38

Desired Output> 

ID    Cat1    Cat2  Cat3 Cat4
1     0       75     0    0
2     61      0      0    0
3     0       5      75   0
4     0       54     0    40 
5     0       20     0    38
pandas python
2 Answers

You can first set_index to ID and then chain combine_first:

df1 = pd.DataFrame({'ID': {0: 1, 1: 2}, 'Cat1': {0: 0, 1: 61}, 'Cat2': {0: 75, 1: 0}}).set_index("ID")
df2 = pd.DataFrame({'ID': {0: 3}, 'Cat2': {0: 5}, 'Cat3': {0: 75}}).set_index("ID")
df3 = pd.DataFrame({'ID': {0: 4, 1: 5}, 'Cat2': {0: 54, 1: 20}, 'Cat4': {0: 40, 1: 38}}).set_index("ID")

print (df1.combine_first(df2).combine_first(df3).fillna(0))

    Cat1  Cat2  Cat3  Cat4
ID                        
1    0.0  75.0   0.0   0.0
2   61.0   0.0   0.0   0.0
3    0.0   5.0  75.0   0.0
4    0.0  54.0   0.0  40.0
5    0.0  20.0   0.0  38.0
user_92844230

A sequential merge of all the dataframes would one solution

df_all = pd.merge(df1,df2,how='outer', on=['ID'])
df_all = pd.merge(df_all,df3,how='outer', on=['ID'])
df_all = df_all.fillna(0)

Alternatively, use pd.concat

df_all = pd.concat([df1, df2, df3], join='outer', axis=1, sort=False).fillna(0)
user_51300780
Related