Kurosaki Kurosaki - 1 month ago 6
Python Question

How to use groupby method combine several (use slice?) columns or rows?

I know that using group by method can combine several columns or rows by using theirs label, but if I wanna combine several columns by calculate theirs mean or standard deviation like this:

a1 a2 a3 b1 b2
A1 5 8 9 5 0
A2 0 1 7 6 9
A3 2 4 5 2 4
B1 2 4 7 7 9
B2 1 7 0 6 9


But this now work at all:

df.loc[:, 'a'] = df.groupby(['a1': a3']).mean()


It says cannot using ":", but it's really hard to list all the labels like this:

df.loc[:, 'a'] = df.groupby(['a1', 'a2', a3']).mean()

a b
A1 x x
A2 x x
A3 x x
B1 x x
B2 x x


In the same way, how about the rows?

a1 a2 a3 b1 b2
A x x x x x
B x x x x x


So what's the best way to solve this?

Answer
df = pd.DataFrame([[5,8,9,5,0], [0,1,7,6,9], [2,4,5,2,4], [2,4,7,7,9], [1,7,0,6,9]],
                  index=['A1', 'A2', 'A3', 'B1', 'B2'],
                  columns=['a1', 'a2', 'a3', 'b1', 'b2'])
a_cols = [col for col in df.columns if col.startswith('a')]  # or use any other criterion to select the columns
# e.g. to select columns a51:a100, you can use something like:
# a_cols = [col for col in df.columns if col.startswith('a') and int(col[1:]) >= 51]
df['a'] = df[a_cols].mean(axis=1)
print df

    a1  a2  a3  b1  b2         a 
A1   5   8   9   5   0  7.333333      
A2   0   1   7   6   9  2.666667      
A3   2   4   5   2   4  3.666667      
B1   2   4   7   7   9  4.333333      
B2   1   7   0   6   9  2.666667          

Another method by using groupby:

df.groupby(lambda col: col[0], axis=1).mean()  # or use any other function to select columns
# e.g. to select columns a51:a100, you can use something like:
# lambda col: col if col.startswith('a') and int(col[1:]) >= 51] else 'other'
           a    b
A1  7.333333  2.5
A2  2.666667  7.5
A3  3.666667  3.0
B1  4.333333  8.0
B2  2.666667  7.5

Rows:

df['prefix'] = df.index.map(lambda x: x[0])  # or any other criterion 
print df

    a1  a2  a3  b1  b2         a prefix
A1   5   8   9   5   0  7.333333      A
A2   0   1   7   6   9  2.666667      A
A3   2   4   5   2   4  3.666667      A
B1   2   4   7   7   9  4.333333      B
B2   1   7   0   6   9  2.666667      B

df.groupby('prefix').mean()              

              a1        a2   a3        b1        b2         a
prefix                                                       
A       2.333333  4.333333  7.0  4.333333  4.333333  4.555556
B       1.500000  5.500000  3.5  6.500000  9.000000  3.500000    
Comments