How to show the labels of the column in a groupby operation-CodePudding

I have a dataframe with columns: Sentiment and Role

Sentiment       Role
positive        manager
positive        worker
negative        leader
positive        director
negative        leader
positive        manager
negative        manager

I want to groupy this dataframe by sentiment and count how many time a role has each sentiment label, like the output below:

Desired output

             worker    leader    director    manager
positive       1         0           1         2
negative       0         2           0         1

I am trying:

df.groupby('Sentiment').agg({'Role':'count'})

But the result is missing the label of each role.

How can I do this?

CodePudding user response：

# use cross tab and provide row and indexes
df2=pd.crosstab(index=df['Sentiment'], columns=df['Role'] ).reset_index().rename_axis(columns=None)

# defie column index sequence
idx=['Sentiment', 'worker', 'leader', 'director','manager']

# reindex the columns, and sort the rows
df2=df2.reindex(idx, axis=1 ).sort_values('Sentiment', ascending=False)
df2

    Sentiment   worker  leader  director    manager
1   positive         1      0         1     2
0   negative         0      2         0     1