Adding dataframes and dividing resultant based on availability

Question

I want to add two dataframes which I can achieve by add function. Now I want to divide each value of resultant dataframe based on whether respective value was present in initial dataframes(df1,df2,df3). For eg.

df1 = pd.DataFrame([[1,2],[3,4]], index =['A','B'], columns = ['C','D'])
df2 = pd.DataFrame([[11,12], [13,14]], index = ['A','B'], columns = ['D','E'])
df3 = df1.add(df2, fill_value=0)

This would result in a df like

     C   D     E
A  1.0  13  12.0
B  3.0  17  14.0

I require a df like:

     C    D     E
A  1.0  6.5  12.0
B  3.0  8.5  14.0

because D column is found in both dataframes, I divide those values by 2. Can anyone please provide a generic solution, assuming I need to add more than 2 dataframes (so the division factor also changes) and have more than 100 columns in each dataframe.

Answer 1

We can concatenate all DFs horizontally in one step:

In [13]: df = pd.concat([df1,df2], axis=1).fillna(0)

this yields:

In [15]: df
Out[15]:
   C  D   D   E
A  1  2  11  12
B  3  4  13  14

now we can group by columns, calculating average (mean):

In [14]: df.groupby(df.columns, axis=1).mean()
Out[14]:
     C    D     E
A  1.0  6.5  12.0
B  3.0  8.5  14.0

or we can do it in one step (thanks @jezrael):

In [60]: pd.concat([df1,df2], axis=1).fillna(0).groupby(level=0, axis=1).mean()
Out[60]:
     C    D     E
A  1.0  6.5  12.0
B  3.0  8.5  14.0

Timing:

In [38]: df1 = pd.concat([df1] * 10**5, ignore_index=True)

In [39]: df2 = pd.concat([df2] * 10**5, ignore_index=True)

In [40]: %%timeit
    ...: df = pd.concat([df1,df2], axis=1).fillna(0)
    ...: df.groupby(df.columns, axis=1).mean()
    ...:
63.4 ms ± 2.39 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [41]: %%timeit
    ...: s = pd.Series(np.concatenate([df1.columns, df2.columns])).value_counts()
    ...: df1.add(df2, fill_value=0).div(s)
    ...:
28.7 ms ± 712 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [42]: %%timeit
    ...: pd.concat([df1,df2]).mean(level = 0)
    ...:
65.5 ms ± 555 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [43]: df1.shape
Out[43]: (200000, 2)

In [44]: df2.shape
Out[44]: (200000, 2)

Current winner: @jezrael (28.7 ms ± 712 µs) - congratulations!

Answer 2

It looks like you are trying to compute a mean. Don't do too many operations with the dataframe methods and individual columns if you can help it, as it's slow.

df = pd.concat([df1,df2]) # concatenate all your dataframes together
df.mean(level = 0)

The second line computes the mean along the vertical axis (axis = 0 by default), and level = 0 tells pandas to get the mean of each unique index.