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...


Answer: 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
    

