Kurosaki - 1 year ago 76
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?

``````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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download