How to use pandas.Grouper to group integers in intervals?

user_2876079 0
Viewed 391

Is pandas.Grouper only thought to be used for dates? Or can it also be used for integers?

I would like to use pandas.Grouper in combination with pandas.pivot_table. Here is an example on how to use pandas.Grouper for a column containing dates:

import pandas
import numpy
from datetime import datetime

date_data_frame = pandas.DataFrame(
            {                
                "date": [
                    datetime(2019, 9, 1, 13, 0),
                    datetime(2019, 9, 1, 13, 5),
                    datetime(2019, 10, 1, 20, 0),
                    datetime(2019, 10, 3, 10, 0),
                    datetime(2019, 12, 2, 12, 0),
                    datetime(2019, 9, 2, 14, 0),
                ],
                "name": "Maria Maria Maria Maria Jane Carlos".split(),
                "value": [25, 9, 4, 3, 2, 8],
            }
        )

grouped_pivot_table = pandas.pivot_table(
    date_data_frame,
    index=[pandas.Grouper(key="date", freq="M")], #grouped entries to show as row headers
    columns='name',               #entries to show as column headers
    values='value',               #entries to aggregate and show as cells
    aggfunc=numpy.sum,            #aggregation function(s)
)

print(grouped_pivot_table)

Now lets assume I do not have dates, but integers between 1 and 100 and I would like to group them in intervals of 10 (1-10, 11-20, ...). How can I specify the intervals for the grouping using pandas.Grouper?

I tried freq="10" but that did not work:

import pandas
import numpy
from datetime import datetime
    
date_data_frame = pandas.DataFrame(
            {                
                "param": [
                    1,
                    5,
                    10,
                    15,
                    22,
                    33,
                ],
                "name": "Maria Maria Maria Maria Jane Carlos".split(),
                "value": [25, 9, 4, 3, 2, 8],
            }
)
    
grouped_pivot_table = pandas.pivot_table(
    date_data_frame,
    index=[pandas.Grouper(key="param", freq="10")], #grouped entries to show as row headers
    columns='name',               #entries to show as column headers
    values='value',               #entries to aggregate and show as cells
    aggfunc=numpy.sum,            #aggregation function(s)
)
    
print(grouped_pivot_table)

If that is not possible with pandas.Grouper what should I use instead to group the param index of my pivot table?

pandas pivot-table grouping
1 Answers

Possible idea is use integer division, I think Grouper working only with datetimes:

grouped_pivot_table = pandas.pivot_table(
    date_data_frame,
    index= (date_data_frame["param"] - 1) // 10, #grouped entries to show as row headers
    columns='name',               #entries to show as column headers
    values='value',               #entries to aggregate and show as cells
    aggfunc=numpy.sum,            #aggregation function(s)
)
    
print(grouped_pivot_table)
name   Carlos  Jane  Maria
param                     
0         NaN   NaN   34.0
1         NaN   NaN    7.0
2         NaN   2.0    NaN
3         8.0   NaN    NaN

Or use cut with closing interval from right side:

bins = range(0, date_data_frame["param"].max() // 10 * 10 + 20, 10)
labels = ['{}-{}'.format(i + 1, j) for i, j in zip(bins[:-1], bins[1:])]

grouped_pivot_table = pandas.pivot_table(
    date_data_frame,
    #grouped entries to show as row headers
    index= pd.cut(date_data_frame["param"], bins=bins, labels=labels), 
    columns='name',               #entries to show as column headers
    values='value',               #entries to aggregate and show as cells
    aggfunc=numpy.sum,            #aggregation function(s)
)
    
print(grouped_pivot_table)
name   Carlos  Jane  Maria
param                     
1-10      NaN   NaN   38.0
11-20     NaN   NaN    3.0
21-30     NaN   2.0    NaN
31-40     8.0   NaN    NaN

or not (right=False parameter):

bins = range(0, date_data_frame["param"].max() // 10 * 10 + 20, 10)
labels = ['{}-{}'.format(i + 1, j) for i, j in zip(bins[:-1], bins[1:])]

grouped_pivot_table = pandas.pivot_table(
    date_data_frame,
    #grouped entries to show as row headers
    index= pd.cut(date_data_frame["param"], bins=bins, labels=labels, right=False), 
    columns='name',               #entries to show as column headers
    values='value',               #entries to aggregate and show as cells
    aggfunc=numpy.sum,            #aggregation function(s)
)
    
print(grouped_pivot_table)
name   Carlos  Jane  Maria
param                     
1-10      NaN   NaN   34.0
11-20     NaN   NaN    7.0
21-30     NaN   2.0    NaN
31-40     8.0   NaN    NaN
user_29010020
Related