Count the number of zeros with Pandas

user_10932908 0
Viewed 367

I have the following dataframe:

import pandas as pd

data = {
    'State': ['EU','EU','EU','US','US','US','UK','UK','UK'],
    'Var': [4,1,6,2,1,6,2,0,1],
    'Mark': [0,1,1,1,0,0,0,0,0]
}

Table = pd.DataFrame(data)
Table

I want to count the number of zeros in the "Mark" column for each country. the output should be a table like this:

State  #ofZeros
EU       1
US       2
UK       3

I managed to count the number of "1s" in the "Mark" column for each country with groupby:

Table.groupby('State')['Mark'].sum()

and it would be great to know if it is possible to count also the zeros (or anyother value) with groupby.

pandas-groupby pandas python
3 Answers

Group the dataframe by States then call sum on boolean masking for Mark==0.

>>> Table.groupby('State', sort=False)['Mark'].agg(lambda x:x.eq(0).sum())

State
EU    1
US    2
UK    3
Name: Mark, dtype: int64

You can also call to_frame to convert it to a dataframe, then reset the index if needed:

Table.groupby('State', sort=False)['Mark'].agg(lambda x:x.eq(0).sum()).to_frame('#of Zeros').reset_index()

  State  #of Zeros
0    EU          1
1    US          2
2    UK          3

On a side note, as you have mentioned in the question: I managed to count the number of "1s" in the "Mark" column for each country with groupby: Table.groupby('State')['Mark'].sum(),

No, you are not actually counting the number of 1s, you are just getting the sum of the values in Mark column for each groups. For the sample data you have, Mark column has only 0 and 1 values, that is why sum and count of 1s, both are equal. If it had some other values as well in addition to 0, and 1, the sum of the values would be different than the count of 1s.

user_91363480

You can actually check the occurrences of value 0 in the column "Mark" using the code below.

Table[['State', 'Mark']][Table.Mark == 0].value_counts()

Table[['State', 'Mark']] narrows the columns that are required to be shown.

The output should be

State  Mark
UK     0       3
US     0       2
EU     0       1
dtype: int64
user_84059020

You could use value_counts when you filter the dataframe on state. Then you just lookup the count of value 0.

states = set(Table.State)
count_data = [[state, Table[Table.State == state].Mark.value_counts()[0]] for state in states]
df = pd.DataFrame(count_data, columns=['State', 'zeros'])
df
print(df)
>> 
    State   zeros
0   US      2
1   UK      3
2   EU      1
user_128816040
Related