Concat multiindex pandas into single one

user_4730164 0
Viewed 64

I have 3 pandas multiindex groupby(['location','date'])

print(a)
location date         hosp               
976      2020-10-02     9    
         2020-10-03    10    
         2020-10-04    10    

print(b)         
                     incid_hosp  
location date                                                  
976      2020-10-02           1        
         2020-10-03           1        
         2020-10-04           0    
    
print(c)
                      P   T
location date                  
978      2020-10-02   5   60
         2020-10-02   4   52
         2020-10-03   4   2

I want to concat them to get:

print(result)
                      hosp      incid_hosp   P  T
location date
976   2020-10-02       9            1        NaN   NaN
      2020-10-03      10            1        NaN   NaN
      2020-10-04      10            0        NaN  NaN
978   2020-10-02      NaN           NaN       5   60
      2020-10-03      NaN           NaN       4   52
      2020-10-04      NaN           NaN       4   2

I have tried

result = pd.concat([a,b,c], axis=1, sort=False)

But It produces to much NaN values ...

pandas concat python
2 Answers

Try this using combine_first and reduce:

from functools import reduce
reduce(lambda x, y: x.combine_first(y), [a,b,c])

Output:

                       P     T  hosp  incid_hosp
location date                                   
976      2020-10-02  NaN   NaN   9.0         1.0
         2020-10-03  NaN   NaN  10.0         1.0
         2020-10-04  NaN   NaN  10.0         0.0
978      2020-10-02  5.0  60.0   NaN         NaN
         2020-10-02  4.0  52.0   NaN         NaN
         2020-10-03  4.0   2.0   NaN         NaN
user_63615310

For three dataframes, you can use chain join:

a.join(b,how='outer').join(c, how='outer')

Output:

                     hosp  incid_hosp    P     T
location date                                   
976      2020-10-02   9.0         1.0  NaN   NaN
         2020-10-03  10.0         1.0  NaN   NaN
         2020-10-04  10.0         0.0  NaN   NaN
978      2020-10-02   NaN         NaN  5.0  60.0
         2020-10-02   NaN         NaN  4.0  52.0
         2020-10-03   NaN         NaN  4.0   2.0
user_42384080
Related