How to split data rows into cells with calculations

Question

I have a dataframe like

df = pd.DataFrame({'x':[1,1,3,7,8,8,9], 'y':[1,0,1,1,1,0,1]})
df
   x  y
0  1  1
1  1  0
2  3  1
3  7  1
4  8  1
5  8  0
6  9  1

which I would like to break into boxes [(1,3), (3,5), (5,7), (7,9), (9,11)] (included on the left, exclusive on the right), For each group I I would like to get the number of rows that fell into the basket, and part of 1 in each group.

The result should be a different data framework, e.g.

result = pd.DataFrame({'LB':[1,3,5,7,9], 'RB':[2,4,6,8,10], 'N':[2,1,0,3,1], 'Pcnt1':[.5,1,np.nan,2/3,1]})
print(result)
   LB  N     Pcnt1  RB
0   1  2  0.500000   2
1   3  1  1.000000   4
2   5  0       NaN   6
3   7  3  0.666667   8
4   9  1  1.000000  10

How to do it?

Answer 1

You can do something like

bin_edges = [1,3,5,7,9,11]
bins = pd.cut(df.x, bin_edges, right=False)

df_new = pd.DataFrame({"LB": bin_edges[:-1], "RB": bin_edges[1:]})
binned = df.groupby(bins.values.codes)["y"]
df_new["N"] = binned.count()
df_new["N"] = df_new["N"].fillna(0)
df_new["Pcnt1"] = binned.mean()

which gives

>>> df_new
   LB  RB  N     Pcnt1
0   1   3  2  0.500000
1   3   5  1  1.000000
2   5   7  0       NaN
3   7   9  3  0.666667
4   9  11  1  1.000000

(This uses the exclusive RB agreement.)

---

Here all the hard work is done pd.cut, which returns a series of dtype categories:

>>> bins
0     [1, 3)
1     [1, 3)
2     [3, 5)
3     [7, 9)
4     [7, 9)
5     [7, 9)
6    [9, 11)
Name: x, dtype: category
Categories (5, object): [[1, 3) < [3, 5) < [5, 7) < [7, 9) < [9, 11)]

Since we want to align, I went down to the basic bin indices:

>>> bins.values.codes
array([0, 0, 1, 3, 3, 3, 4], dtype=int8)

, , , 100, NaN -1, () , df_new.