Count distinct values based on conditionals

Robert Calimente 0
Viewed 25

I have a dataset that looks something like this:

Category Score ID
A 96 1
A 95 1
A 95 2
A 95 2
B 96 2
B 95 2
B 96 2
C 97 3
C 96 3
C 97 3

For each category, I want a count of the distinct IDs that have 2 scores (or more) of < 97. So, based on this data, my end goal result would be a dataframe or list that looks like:

Category Count
A 2
B 1
C 0
group-by pandas for-loop python
1 Answers

You can do a nested groupby to get the count of unique ID in each Category group.

out = (df[df['Score'].lt(97)]
       .groupby('Category')
       .apply(lambda g: g.groupby('ID').filter(lambda x: len(x) >= 2)['ID'].nunique())
       .to_frame('Count')
       .reset_index())
# or
out = (df[df['Score'].lt(97)]
       .groupby(['Category', 'ID'])
       .filter(lambda g: len(g) >= 2)
       .groupby(['Category'])['ID'].nunique()
       .reindex(df['Category'].unique(), fill_value=0)
       .to_frame('Count')
       .reset_index())

print(out)

  Category  Count
0        A      2
1        B      1
2        C      0
Ynjxsjmh0
Related