How to count values in rows across multiple columns with Pandas?

Question

I am new in Python. I would appreciate any advice regarding this method.

I am using pandas in python and have a dataframe (csv file) like this one but with 195 columns and ~300 individuals.

Index  IID    Sex    Disease 1   Disease 2  Disease 3

0      001     F     Absent        Absent   Present  
1      002     M     Absent        Absent   Present   
2      003     M     Present       Absent   Present

I want to count the number of individuals with every disease, that means that I need to count the value "Present" across the 195 columns. Then I would like to have the counts grouped by sex. How I can do it?

The best I was able to do was: GROUP=df1.loc[:,["SEX","Disease1","Disease2", "Disease3"].groupby('SEX') GROUP.count() but this just counted all the entries across the specified columns grouped by sex. I don't know how to make the same but counting only entries with the "Present" value on each row or at least to count the number of entries for each of the values on the rows ("Present", "Absent", "Unable_to_Code").

Try df[(df.filter(like='Disease')=='Present').all(1)].groupby('Sex')['IID'].size() If I had more data, I could to valid and verify. — Scott Boston
– Scott Boston, Commented Jan 10, 2019 at 22:28
Thanks so much for your response! I got this: "Series([], Name: IID, dtype: int64)" but don't know why. What's the difference between "size()" and "count()"? — Karla
– Karla, Commented Jan 11, 2019 at 3:23

Felix · Accepted Answer · 2019-01-10 23:18:35Z

1

This is the solution I came up with:

pd.merge(df['Sex'].to_frame(), df.filter(like='Disease')[df.filter(like='Disease') == 'Present'], left_index=True, right_index=True).groupby('Sex').count()

answered Jan 10, 2019 at 23:18

Felix

1,90513 silver badges31 bronze badges

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

1 Comment

Karla Over a year ago

Thanks so much feliks! This solved the problem that had me hitting my head against the wall for so long!!! I can finally sleep tonight! Thank you!

Collectives™ on Stack Overflow

How to count values in rows across multiple columns with Pandas?

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