Retrieve the count of the number of different values from a pandas DF based on condition

Question

Dummy df:

columns = ['id', 'answer', 'is_correct']
data = [['1','hello','1.0'],
       ['1','hi', '1.0'],
       ['1','bye', '0.0'],
        ['2', 'dog', '0.0'],
        ['2', 'cat', '1.0'],
        ['2', 'dog', '0.0'],
        ['3', 'Milan', '1.0'],
        ['3', 'Paris', '0.0'],
        ['3', 'Paris', '0.0'],
        ['3', 'Milannnn', '1.0']]
df = pd.DataFrame(columns=columns, data=data)

I want to create a new df with the following columns:

headers= ['id', 'number_of_different_correct_answers', 'number_of_different_incorrect_answers']

id should equal id from the dummy df.

Consequently, I want to retrieve the number of different correct answers (is_correct == 1.0) for each id and likewise for is_correct == 0.0 (incorrect answers). With different I mean that within id 2 we have dog twice within is_correct == 0.0 thus it should only count as 1.

Based on the dummy df, the new df would look like this

id  number_of_different_correct_answers number_of_different_incorrect_answers
1   2                                   1
2   1                                   1
3   2                                   1

score 2 · Accepted Answer · answered Mar 10 '21 at 21:06

you can drop duplicates, groupby by id and count distinct values:

(df.drop_duplicates(['id','answer'])
   .groupby('id')['is_correct']
   .value_counts()
   .unstack(level=1)
   .rename(columns = {'0.0':'number_of_different_incorrect_answers', 
                      '1.0':'number_of_different_correct_answers'})
)

produces


is_correct  number_of_different_incorrect_answers   number_of_different_correct_answers
id      
1           1                                       2
2           1                                       1
3           1                                       2

score 2 · Answer 2 · answered Mar 10 '21 at 21:08

this was answered before Python Pandas : pivot table with aggfunc = count unique distinct But it is using old versions of pandas so needs some update

df.pivot_table(values='answer',index='id', columns='is_correct',aggfunc=lambda x: len(x.unique())).rename(columns={'1.0':'number_of_different_correct_answers','0.0':'number_of_different_incorrect_answers'})

Retrieve the count of the number of different values from a pandas DF based on condition

2 Answers2