How to merge all data-frames in a dictionary in Python

user_5016119 0
Viewed 8363

I have a dictionary dict contains many (more than 100) dataframes. Each dataframe contains two variable name and 'value_i'. For example, the first dataframe in this dictionary dict[1] looks like the following:

name  value_1
A      1
B      1.1 
C      2

Similarly, the second dataframe in this dictionary dict2 looks like the following:

name  value_2
A      1
B      1.1 
D      1.3

I want to merge all dataframes within this dictionary by common variable name.

The expected outcome should looks like the following: 

name  value_1   value_2
A      1         1
B      1.1       1.1
C      2         nan
D     nan           1.3

I know I can do pd.merge[dict[i], dict[i-1], how = 'outer', on = 'name' many times to merge all dataframes together. But this is too inefficient.

I tried pd.concat(dict.values(), axis = 1, join='outer' But concat dont allow me to merge by key variable.

Can anyone teach me how to do it more efficiently please?

pandas dataframe dictionary python
2 Answers

Given

>>> df1                                                                                                                
  name  value_1
0    A      1.0
1    B      1.1
2    C      2.0
>>> df2                                                                                                                
  name  value_2
0    A      1.0
1    B      1.1
2    D      1.3

and

>>> d = {1: df1, 2: df2}

you can issue

>>> from functools import partial, reduce                                                                              
>>>                                                                                                                    
>>> my_reduce = partial(pd.merge, on='name', how='outer')                                                              
>>> reduce(my_reduce, d.values())                                                                                      
  name  value_1  value_2
0    A      1.0      1.0
1    B      1.1      1.1
2    C      2.0      NaN
3    D      NaN      1.3
user_36200030

Consider you have multiple items in dict 

d={i : j.set_index('name') for i,j in d.items()}
yourdf=pd.concat(d,axis=1)
user_79645270
Related