Counting along columns in a Pandas Dataframe?

user_10404120 0
Viewed 67

I have a data frame which contains a column with Names, and then columns containing either the value Yes, No or NaN next to these names. What steps would I take to count the amount of times Yes, No or NaN appears beside each name across each column? To give you a better example of what I mean:

col1    col2  col3  col4
Bob       yes   yes   no
Tim       no     no   yes
Susan     yes   Nan   yes

Thanks!

pandas python
1 Answers

One possible idea using DataFrame.stack, Series.value_counts along with DataFrame.unstack:

counts = (
    df.set_index('col1').astype(str).stack()
    .groupby(level=0, sort=False).value_counts().unstack(fill_value=0)
)

Another possible solution using DataFrame.stack and Series.str.get_dummies:

counts = df.set_index('col1').astype(str).stack().str.get_dummies().sum(level=0)

Result:

# print(counts)
       nan  no  yes
col1               
Bob      0   1    2
Tim      0   2    1
Susan    1   0    2
user_128331660
Related