Binning one column and cross tabulating occurrences according to another column

Question

My data looks like that:

        CPB% Bin
0   0.011368   A
1   0.011397   A
2   0.011946   A
3   0.011353   A
4   0.011382   A
5   0.016643   A
6   0.018974   A
7   0.011828   A
8   0.010999   A
9   0.008970   B
10  0.008988   B
11  0.009070   B
12  0.009089   A
13  0.009089   A
14  0.008978   B
15  0.009951   A
16  0.011174   A
17  0.008976   B
18  0.010339   A
19  0.012273   A
20  0.009694   A
21  0.007221   B
22  0.015916   A
23  0.007943   B
24  0.008711   B

And what I need to have is that:

           CPB% Bin-A Bin-B
0.01       20    15    5
0.02       30    15    15
0.03       75    50    25
0.04       67    50    17

What I tried is the following:

bins = np.linspace(0, 1, num=1000)
df_b = pd.DataFrame(pd.cut(df['CPB%'], bins=bins).value_counts()).sort_index(ascending = True)

But then I do not know how to spread and count the number of CPB% for a specific cluster (like 0.01) between A and B. Any idea? Thanks!

Answer 1

Use pd.crosstab to crosstabulate counts:

pd.crosstab(pd.cut(df['CPB%'], bins=bins), df.Bin)

---

Example using pd.qcut (not to be confused with pd.cut) with the bins as quantiles:

pd.crosstab(pd.qcut(df['CPB%'], 4), df.Bin)

Bin                 A  B
CPB%                    
(0.00622, 0.00899]  0  7
(0.00899, 0.0103]   5  1
(0.0103, 0.0114]    6  0
(0.0114, 0.019]     6  0

If you want the left of the interval as the index label, you can do something like

import operator
pd.crosstab(pd.qcut(df['CPB%'], 4).map(operator.attrgetter('left')), df.Bin)
# On v0.24,
# pd.crosstab(pd.qcut(df['CPB%'], 4).arrays.left, df.Bin)

Bin      A  B
CPB%         
0.00622  0  7
0.00899  5  1
0.01030  6  0
0.01140  6  0