Combine dictionary of dataframes that contain some of the same and different column names?

user_4343563 0
Viewed 90

I have a dictionary of dataframes like:

  {'A_all4':             region     A_all4_count  total  A_all4_percent
0                            AP             0        40             0.0
1                            ESA            0        21             0.0
2                            CA             0        16             0.0
3                            LA             0        33             0.0
4                            NA             0        20             0.0
5                            WA             0        25             0.0
6                            EA             0        39             0.0,
'PA_all4':               region      PA_all4_count  total  PA_all4_percent
0                            AP             0        40             0.0
1                            ESA            0        21             0.0
2                            CA             0        16             0.0
3                            LA             0        33             0.0
4                            NA             0        20             0.0
5                            WA             0        25             0.0
6                            EA             0        39             0.0,
'NA_all4':              region       NA_all4_count  total  NA_all4_percent
0                          AP               0       40              0.0
1                          SA               4       21              19.0
2                          CA               1       16              6.2
3                          LA               0       33              0.0
4                          NA               2       20              10.0
5                          WA               2       25              8.0
6                          EA               0       39              0.0}

I want to combine all the different dataframes within the dictionary so that I have one final dataframe that combines all the different count and percent columns but only has one region and total column.

For example:

     region      total       A_all4_count.   A_all4_percent     P_all4_count      P_all4_perct       NA_all4_count           NA_all4_perc
       AP          40             0               0.0                 0                0.0                0                  0
       ESA         21             0               0.0                 0                 0.0               4                  19.0
       CA          16             0               0.0                 0                 0.0               1                  6.2
       LA          33.            0               0.0                 0                 0.0               0                  0.0
       NA          20             0.              0.0                 0                 0.0               2                  10.0

etc...

I have tried using

  pd.concat(d, axis=1).sum(axis=1, level=0)

but that doesn't give me what I want.

pandas dataframe dictionary python
2 Answers

To successively merge you can run a for loop or reduce:

from functools import reduce

result = reduce(lambda df_1, df_2: pd.merge(df_1, df_2), dict_of_dfs.values())

where first argument is a function that takes 2 df's and merges them. The values of the dictionary i.e, the dataframes are reduced in this fashion.

user_93321870

You can define the columns you want for the 3 dataframes in lists and then concat with the dictionary entries for the dataframes each with the selected column list, as follows:

Assuming your dict of dataframe is called d:

A_all4_col = ['region', 'total',  'A_all4_count', 'A_all4_percent']
PA_all4_col = ['PA_all4_count', 'PA_all4_percent']
NA_all4_col = ['NA_all4_count', 'NA_all4_percent']

pd.concat([d['A_all4'][A_all4_col], 
           d['PA_all4'][PA_all4_col], 
           d['NA_all4'][NA_all4_col]], axis=1)

Result:

     region  total  A_all4_count  A_all4_percent  PA_all4_count  PA_all4_percent  NA_all4_count  NA_all4_percent
0        AP     40             0             0.0              0              0.0              0              0.0
1       ESA     21             0             0.0              0              0.0              4             19.0
2        CA     16             0             0.0              0              0.0              1              6.2
3        LA     33             0             0.0              0              0.0              0              0.0
4       NaN     20             0             0.0              0              0.0              2             10.0
5        WA     25             0             0.0              0              0.0              2              8.0
6        EA     39             0             0.0              0              0.0              0              0.0
0  Globally    194             0             0.0              0              0.0              9              4.6
user_150706970
Related