[FIXED] Row-wise Cumulative Mean Across Grouped Columns using Pandas

Issue

I would like to create multiple columns which show the row-wise cumulative mean for grouped columns. Here is some sample data:

import pandas as pd

data = [[1, 4, 6, 10, 15, 40, 90, 100], [2, 5, 3, 11, 25, 50, 90, 120], [3, 7, 9, 14, 35, 55, 100, 120]]
df = pd.DataFrame(data, columns=['a1', 'a2', 'a3', 'a4', 'b1', 'b2', 'b3', 'b4'])

   a1  a2  a3  a4  b1  b2   b3   b4
0   1   4   6  10  15  40   90  100
1   2   5   3  11  25  50   90  120
2   3   7   9  14  35  55  100  120

What I want is to generate new columns like this:

• New column a1_2 is calculated by the mean of columns a1 and a2 row-wise.
• New column a1_3 is calculated by the mean of columns a1, a2 and a3 row-wise.
• New column a1_4 is calculated by the mean of columns a1, a2, a3 and a4 row-wise.

The same should happen for the grouped columns with b. Of course you can do this manually, but this is not ideal when you have too many variables. Here is the expected output:

df['a1_2'] = df[['a1', 'a2']].mean(axis=1)
df['a1_3'] = df[['a1', 'a2', 'a3']].mean(axis=1)
df['a1_4'] = df[['a1', 'a2', 'a3', 'a4']].mean(axis=1)
df['b1_2'] = df[['b1', 'b2']].mean(axis=1)
df['b1_3'] = df[['b1', 'b2', 'b3']].mean(axis=1)
df['b1_4'] = df[['b1', 'b2', 'b3', 'b4']].mean(axis=1)

   a1  a2  a3  a4  b1  b2   b3   b4  a1_2      a1_3  a1_4  b1_2       b1_3   b1_4
0   1   4   6  10  15  40   90  100   2.5  3.666667  5.25  27.5  48.333333  61.25 
1   2   5   3  11  25  50   90  120   3.5  3.333333  5.25  37.5  55.000000  71.25 
2   3   7   9  14  35  55  100  120   5.0  6.333333  8.25  45.0  63.333333  77.50  

So I was wondering if there is some automatic way of doing this?

Solution

expanding.mean

for c in ('a', 'b'):
    m = df.filter(like=c).expanding(axis=1).mean().iloc[:, 1:]
    df[m.columns.str.replace(r'(\d+)$', r'1_\1', regex=True)] = m

Result

   a1  a2  a3  a4  b1  b2   b3   b4  a1_2      a1_3  a1_4  b1_2       b1_3   b1_4
0   1   4   6  10  15  40   90  100   2.5  3.666667  5.25  27.5  48.333333  61.25
1   2   5   3  11  25  50   90  120   3.5  3.333333  5.25  37.5  55.000000  71.25
2   3   7   9  14  35  55  100  120   5.0  6.333333  8.25  45.0  63.333333  77.50

Another option:

out = [value.expanding(axis=1).mean()
            .rename(columns = lambda col: f"{col[0]}1_{col[1]}") 
       for _, value in df.groupby(df.columns.str[0], axis = 1)]

pd.concat([df]+out, axis = 1)
   a1  a2  a3  a4  b1  b2   b3   b4  a1_1  a1_2      a1_3  a1_4  b1_1  b1_2       b1_3   b1_4
0   1   4   6  10  15  40   90  100   1.0   2.5  3.666667  5.25  15.0  27.5  48.333333  61.25
1   2   5   3  11  25  50   90  120   2.0   3.5  3.333333  5.25  25.0  37.5  55.000000  71.25
2   3   7   9  14  35  55  100  120   3.0   5.0  6.333333  8.25  35.0  45.0  63.333333  77.50

Answered By - Shubham Sharma