Find distinct value from dataframe column and create new column in datafrase and set frequency in new column

user_5662881 0
Viewed 36

I have sample dataframe like

_id    Dept    Result
1234    dept1   NaN
1235    dept2   Good
1235    dept2   Bad
1235    dept2   Good
1235    dept2   none
1235    dept2   none

I want to convert it into

_id     Name   Good Bad none
1234    dept1   0   0   5
1235    dept2   2   1   2

I am able to do it with for loop. As I have too much data so using for loop is slow, need some optimised solution. Can it be achieved by using some dataframe function

pandas dataframe python
1 Answers

Use crosstab and for prevent remove None column replace NaN and Nones by same value, e.g. non:

df = pd.crosstab([df['_id'], df['Dept']], df['Result'].fillna('non'))
print (df)
Result      Bad  Good  non
_id  Dept                 
1234 dept1    0     0    1
1235 dept2    1     2    2
user_29010020
Related