Pandas dataframe how to groupby bins of numerical column and then count other binary column-CodePudding

I have a datafrmae

c1 c2  SED f
1  2   0.2 1
3  3   0.7 1
3  1   0.1 0
8  1   0.6 0
9  2   1   1
4  9   8.3 1

I want to group SED to bins of width 0.5 and foreach bin, count the number of rows the column f is 1 and the number of rows it is 0.

So for this example I will get:

SED_bin   cou_0   cou_1     
  0-0.5     1       1
  0.5-1     1       2
  8-8.5     0       1

What is the best way to do it? Please note this is just an example of SED values and there could be more below to above this range so I need the binning to be generic

CodePudding user response：

One option is to use cut crosstab:

out = (pd.crosstab(pd.cut(df['SED'], np.arange(int(df['SED'].min()), int(df['SED'].max()) 1, 0.5)), df['f'])
       .add_prefix('count_').rename_axis(index='SED_bins').reset_index())

Output:

f    SED_bins  count_0  count_1
0  (0.0, 0.5]        1        1
1  (0.5, 1.0]        1        2
2  (8.0, 8.5]        0        1