Counting Total Rows In Pandas Dataframe With The Same String Value In Multiple Columns

Thank you very much for your help! Question: How can I count the number of rows that contain '9999-Don't Know' in multiple columns? I have been able to find solutions that take me

Solution 1:

This will give you number of rows equal to "9999-Don't Know" per column

df.astype(object).eq("9999-Don't Know").sum()

This will give you total count of "9999-Don't Know", thanks @Mitch

df.astype(object).eq("9999-Don't Know").values.sum()

This will give you total number of rows with at least one

df.astype(object).eq("9999-Don't Know").any(1).sum()

Solution 2:

You can also use this:

df.stack().str.contains("9999-Don't Know").sum()

Although this is slower than @piRSquared solution:

In [38]: timeit df.astype(str).eq("9999-Don't Know").values.sum() 
1000 loops, best of 3: 182 us per loop

In [39]: timeit df.stack().str.contains("9999-Don't Know").sum()
1000 loops, best of 3: 467 us per loop

Solution 3:

Another solution is:

df.eq("9999-Don't Know").sum().sum()

also you've mentioned the type error:

TypeError: Could notcompare ["9999-Don't Know"] with block values. 

this means you have a list like an element of DataFrame. It can be transformed to string with the code:

df = df.applymap(lambda x: x[0] iftype(x) == list else x)