CODEWITHSUNDEEP
pythonpython-2.7python-3.xpandasdataframe
Kurosaki
Kurosaki

Reputation: 65

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?

Upvotes: 1

Views: 200

Answers (1)

Dennis Golomazov
Dennis Golomazov

Reputation: 17339

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

Upvotes: 1

Related Questions