CODEWITHSUNDEEP
pythonpandas
BKS
BKS

Reputation: 2333

Counting instances in a pandas DF

I have a dataframe that looks like this:

TF  name  
0   A
1   A
0   A
0   A
1   B
1   B
0   B
1   B
1   B

I need to produce a resulting dataframe that would count how many 0's and 1's each person in my dataframe has.

So the result for the above would be:

name  True  False
A     3     1
B     1     4

I don't think groupby would work in this instance. Any solution other than looping and counting?

Upvotes: 1

Views: 70

Answers (2)

Nickil Maveli
Nickil Maveli

Reputation: 29719

You can perform groupby letting TF be the grouped key. Take the corresponding value_counts of the name column to get distinct counts.

Unstack level=0 of the multi-index series so that a dataframe object gets produced. Finally, rename the integer columns by type-casting them as boolean values.

df.groupby('TF')['name'].value_counts().unstack(0).rename(columns=bool)

enter image description here

To have the column names take on string values:

1) Use lambda function:

<...operations on df...>.rename(columns=lambda x: str(x.astype(bool)))

2) Or chain the syntaxes together:

<...operations on df...>.rename(columns=bool).rename(columns=str)

Upvotes: 4

Ted Petrou
Ted Petrou

Reputation: 62037

I would first convert your columns to boolean and then group by both name and TF and then unstack the boolean column TF.

df['TF']=df['TF'].astype(bool)
df.groupby(['name', 'TF']).size().unstack('TF')

TF    False  True 
name              
A         3      1
B         1      4

Upvotes: 2

Related Questions