Make table with bins without unstack

user13948 0
Viewed 16

I have a dataframe with two columns:

df=[[5,3,1,9],[0,6,2,3],[1,5,1,8],[5,6,2,8],[2,9,11,3],[0,1,1,17]]
df=pd.DataFrame(df1, columns = ['A_june', 'A_july', 'B_june', 'B_july'])

I want to group these columns all into the same bins, namely 0-2, 2-4,4-6,6-8,8-10 and 10+ and display them in a table with counts filling these rows and columns:

Bins      A_june   A_july   B_june   B_july

0-2
2-4
4-6
6-8
...

Where I'm having trouble is if I use pd.cut, I don't know how to display it like this. I tried. basically

df=df(pd.cut(A_june,bins),pd.cut(A_july,bins))
df=df.groupby(pd.cut(A_june,bins),pd.cut(A_july,bins)).size().unstack()

But then I end up with groupby objects and similar objects that I can't do what I want with, and that don't have the right rows and columns anyway.

Thanks for any suggestions! :)

jupyter group-by dataframe
1 Answers

Try:

x = pd.cut(
    df.stack(),
    pd.IntervalIndex.from_breaks([*range(0, 11, 2), np.inf], closed="left"),
).droplevel(0)

print(pd.crosstab(x, x.index))

Prints:

col_0        A_july  A_june  B_july  B_june
row_0                                      
[0.0, 2.0)        1       3       0       3
[2.0, 4.0)        1       1       2       2
[4.0, 6.0)        1       2       0       0
[6.0, 8.0)        2       0       0       0
[8.0, 10.0)       1       0       3       0
[10.0, inf)       0       0       1       1
Andrej Kesely0
Related