Create dataframe with (for each cell) averages of other dataframes

user_16363897 0
Viewed 109

I have a list of about 20 dataframes, all with the same structure (same rows and columns). I want to create a new df, where each cell is equal to the average of the corresponding (same row/column) cells of the listed dfs.

So, for example, if we have just 2 dfs (A and B), I need the following:

A=
   A  B  C  D
0  7  6  8  7
1  7  0  7  6
2  9  2  7  0

B=
   A  B  C  D
0  6  9  2  7
1  4  4  5  7
2  6  8  5  4

Average=
     A    B    C    D
0  6.5  7.5  5.0  7.0
1  5.5  2.0  6.0  6.5
2  7.5  5.0  6.0  2.0

I tried this code, but it's pretty slow (the real dfs are quite large) and messes up the order of columns:

dfs = [A,B]

Average = pd.concat([each.stack() for each in dfs],axis=1)\
             .apply(lambda x:x.mean(),axis=1)\
             .unstack()

Is there a better alternative? Thanks

pandas dataframe python
1 Answers

Use -

(A+B) / 2

Output

     A    B    C    D
0  6.5  7.5  5.0  7.0
1  5.5  2.0  6.0  6.5
2  7.5  5.0  6.0  2.0

For scaling up to more dfs, put all of them in a list and just use sum(list). Edit: Based on @younggoti's reco-

list_of_df = [A,B]
sum(list_of_df)/len(list_of_df)
user_40980130
Related