Python count number of occurrence of a value in a dataframe column

Question

I have a dataframe like this:

This is the final dataframe that I want:

I know I can use groupby to count, but it only gives me the total number. How can I break down into the count per 'True' and 'False'. and arrange like this?

df.groupby(['ID', 'PASS'])['PASS'].size().unstack(fill_value=0).assign(Total=lambda x: x.sum(axis=1)) stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe — Alexander
– Alexander, Commented Dec 10, 2019 at 3:07

mustnot · Accepted Answer · 2019-12-10 03:03:27Z

2

import pandas as pd

data = [['a', 'TRUE'], ['a', 'FALSE'], ['a', 'TRUE'], ['b', 'TRUE'], ['b', 'TRUE'], ['b', 'TRUE'],
    ['b', 'FALSE'], ['c', 'TRUE'], ['c', 'TRUE']]
df = pd.DataFrame(data, columns=['ID', 'PASS'])


df['value'] = 1
result = df.pivot_table(values='value', index='ID', columns='PASS', aggfunc='sum', fill_value=0)
result['Total'] = result.agg(sum, axis=1)
result

PASS    FALSE   TRUE    Total
ID          
a   1   2   3
b   1   3   4
c   0   2   2

answered Dec 10, 2019 at 3:03

mustnot

1596 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

adhg · Accepted Answer · 2019-12-10 03:50:34Z

0

Another way to do it is by groupby and unstack, such that:

df = df.groupby(["ID","PASS"])['PASS'].count().unstack(fill_value=0)
df['total'] = df['FALSE']+df['TRUE']

desired result:

PASS    FALSE   TRUE    Total
ID          
a         1       2      3
b         1       3      4
c         0       2      2

answered Dec 10, 2019 at 3:50

adhg

11k13 gold badges63 silver badges99 bronze badges

Collectives™ on Stack Overflow

Python count number of occurrence of a value in a dataframe column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related