How to count value in column and groupby

October 25, 2022

I want to to get the count of OK & NOK for each ITCD & Indicateur RDV,
here a sample of my table :

_ITCD_ | _Indicateur RDV_ | Week | Workers
OK         OK               41      John
OK         NOK              41      John
NOK        NOK              40      Liam

I want to get the count of each workers for each week so here the output result that i want achieve

Workers | Week | _ITCD_  | _Indicateur RDV_|
               | OK| NOK | OK | NOK        |
------------------------------------------ |
John      41   | 2 |  0  | 1  |  1         |  
------------------------------------------ |
Liam      40   | 0 |  1  | 0  |  1         |

So far i tried melt & crosstab but i didnt achieve to include the other column, same with pivot table & groupby

Thanks in advance !

>Solution :

You can use lists in crosstab, combine it with melt:

df2 = df.melt(['Week', 'Workers'])

pd.crosstab([df2['Week'], df2['Workers']], [df2['variable'], df2['value']])

output:

variable     _ITCD_    _Indicateur RDV_   
value           NOK OK              NOK OK
Week Workers                              
40   Liam         1  0                1  0
41   John         0  2                1  1