Beyond value_counts()

Question

I have a pandas dataframe including some values and count for each:

df = pd.DataFrame({'value':[1,2,3,11,12,13,21,22,23], 'count':[100,200,300, 1100,1200,1300, 2100,2200,2300]})

value count
1     100
2     200
3     300
11    1100
12    1200
13    1300
21    2100
22    2200
23    2300

what I want is to adding up the counts within a given equal size windows of value. For example, for a window_size=10, I want to have:

value     count
[0,  10)  600
[10, 20)  3600
[20, 30)  6600

what is the best way to do that?

Does this answer your question: [Pandas Groupby Range of Values](https://stackoverflow.com/questions/21441259/pandas-groupby-range-of-values), [Pandas groupby how to compute counts in ranges](https://stackoverflow.com/questions/25010215/pandas-groupby-how-to-compute-counts-in-ranges), — wwii, Sep 26 '20 at 16:04

score 3 · Accepted Answer · answered Sep 26 '20 at 15:58

3

You can use pd.cut to categorize the column value into discrete intervals based on window size, then groupby the column count using this binned column and aggregate using sum:

w = 10
g = pd.cut(df['value'], np.r_[0:df['value'].max() + w : w], right=False)
s = df['count'].groupby(g).sum()

value
[0, 10)      600
[10, 20)    3600
[20, 30)    6600
Name: count, dtype: int64

answered Sep 26 '20 at 15:58

Shubham Sharma

68,127
6
24
53

1

I was also thinking about using pd.cut to get the categorization so we could groupby it. very succinct – Joe Ferndz Sep 26 '20 at 16:17

Beyond value_counts()

1 Answers1