grouping rows python pandas

Question

say I have the following dataframe and, the index represents ages, the column names is some category, and the values in the frame are frequencies...

Now I would like to group ages in various ways (2 year bins, 5 year bins and 10 year bins)

>>> table_w
      1    2    3    4
20  1000   80   40  100
21  2000   40  100  100
22  3000   70   70  200
23  3000  100   90  100
24  2000   90   90  200
25  2000  100   80  200
26  2000   90   60  100
27  1000  100   30  200
28  1000  100   90  100
29  1000   60   70  100
30  1000   70  100  100
31   900   40  100   90
32   700  100   30  100
33   700   30   50   90
34   600   10   40  100

I would like to end up with something like...

           1    2    3    4
20-21    3000  ...  ...  ...
22-23    6000  ...  ...  ...
24-25    4000  ...  ...  ...
26-27    3000  ...  ...  ...
28-29    2000  ...  ...  ...
30-31    1900  ...  ...  ...
32-33    1400  ...  ...  ...
34        600  ...  ...  ...

Is there a simple and efficient way to do this?

Any help is greatly appreciated...

Alicia Garcia-Raboso · Accepted Answer · 2016-07-11 15:55:04Z

Use pd.cut() to create the age bins and group your dataframe with them.

import io

import numpy as np
import pandas as pd

data = io.StringIO("""\
       1    2    3    4
20  1000   80   40  100
21  2000   40  100  100
22  3000   70   70  200
23  3000  100   90  100
24  2000   90   90  200
25  2000  100   80  200
26  2000   90   60  100
27  1000  100   30  200
28  1000  100   90  100
29  1000   60   70  100
30  1000   70  100  100
31   900   40  100   90
32   700  100   30  100
33   700   30   50   90
34   600   10   40  100
""")
df = pd.read_csv(data, delim_whitespace=True)

bins = np.arange(20, 37, 2)
df.groupby(pd.cut(df.index, bins, right=False)).sum()

Output:

             1    2    3    4
[20, 22)  3000  120  140  200
[22, 24)  6000  170  160  300
[24, 26)  4000  190  170  400
[26, 28)  3000  190   90  300
[28, 30)  2000  160  160  200
[30, 32)  1900  110  200  190
[32, 34)  1400  130   80  190
[34, 36)   600   10   40  100

Collectives™ on Stack Overflow

grouping rows python pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related