How to create this kind of crosstab by Python?

Question

The data looks like:

bad score1 score2
1    80-90  70-80
0    90-100 80-90
1    70-80  90-100
1    70-80 70-80
0    70-80 70-80
1    80-90  70-80

The result should be like the total number of 'the bad flag is 1 when it is in the corresponding range of socre1 and score2'. For example:

       70-80 80-90 90-100 (score2)
70-80   1     0       1
80-90   2     0       0
90-100  0     1       0
(score1)

I know the pd.crosstab has the similar function, but it can not solve my issue.

pd.crosstab(df.score1, df.score2)

mozway · Answer 1 · 2022-11-28T10:54:09.760

0

Use values andaggfunc, combined with fillna:

out = (pd.crosstab(df.score1, df.score2, values=df['bad'], aggfunc='sum')
         .fillna(0, downcast='infer')
)

Output:

score2  70-80  80-90  90-100
score1                      
70-80       1      0       1
80-90       2      0       0
90-100      0      0       0

edited Nov 28 '22 at 10:54

answered Nov 28 '22 at 10:49

mozway

@jezrael is it? Op asked about a `crosstab` – mozway Nov 28 '22 at 10:51
@jezrael In the end, I think crosstab uses a `pivot_table` internally – mozway Nov 28 '22 at 10:53
@jezrael fair enough. I'm also adding the `pivot` one for clarity – mozway Nov 28 '22 at 10:53

1 Answers1