Count values in a column conditional on another column

Question

I have a dataset from which I am trying to count the number of 1's in a column and group them depending on another column and return this as a value (to use within a Class).

Example data

import pandas as pd
Current = {'Item':  ['Chocolate', 'Chocolate', 'Sweets', 'Chocolate', 'Sweets', 'Pop'],
        'Order': [0, 1, 1, 1, 1, 0], 
        }
Current = pd.DataFrame (Current, columns = ['Item','Order'])

I want to then count the number of 1s by each item (the real table has 25 columns) and return this value.

I have managed to do that when there are values using this code:

choc = Current[Current["Item"] == "Chocolate"]
print(choc["Order"].value_counts()[1])

returns: 2

(in reality I would use the bit inside the print to return it in my Class, not just print it)

This works if there is a count, such as for chocolate, but if there is no count, it returns an error.

pop = Current[Current["Item"] == "Pop"]
print(pop["Order"].value_counts()[1])

Returns: KeyError: 1.0

My questions are:

Is there a better way to do this? If not, how do I get the value to return 0 if there isn't a count, e.g. in the case of pop?

IIUC: Current.assign(Counts=Current.groupby('Item').Order.transform('sum')) — piRSquared
– piRSquared, Commented Feb 27, 2020 at 16:28
Or Current.groupby('Item').Order.sum(). I'm just not sure what you want as the final result. I think your example is incomplete. — piRSquared
– piRSquared, Commented Feb 27, 2020 at 16:29

intan · Accepted Answer · 2020-02-27 17:14:38Z

1

If you want to check the items individually, you can do something like this:

Current[Current.Item=='Pop'].Order.sum()

This will return 0 for no count items.

If you expect summary as your end result, you can do:

Current.groupby('Item').agg({'Order':sum}).reset_index()

It will return a dataframe with count values of each item

answered Feb 27, 2020 at 17:14

intan

383 bronze badges

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

1 Comment

PharmDataSci Over a year ago

Perfect, the first solution is exactly what I was after, and the second solution is interesting for other applications - thank you :)

Collectives™ on Stack Overflow

Count values in a column conditional on another column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related