Applying counter on pandas on unique columns values

Question

My data looks like this

Same user can have multiple shift IDs. So in nurse_id column I have repeating IDs.

I want to create counter on new_state for unique nurse.

Currently when I do

Counter(df["new_stat"])

It gives

Counter({'D': 15123, 'R': 29300, 'not_active': 2581, 'N': 21455})

But it counts duplicate values of IDs as well.

How can I create counter on new_stat for unique nurse_id?

Answer 1

Pandas solution is SeriesGroupBy.value_counts:

df.groupby('nurse_id')['new_stat'].value_counts().reset_index(name='count')

Or DataFrameGroupBy.size:

df.groupby(['nurse_id','new_stat']).size().reset_index(name='count')

If need Counters objects per groups is possible use dictionary comprehension with Series constructor:

pd.Series({k: Counter(v['new_stat']) for k, v in df.groupby('nurse_id')})