How to count number of rows with a specific string value in a column using pandas?

Question

I have a pandas column with dtype 'object' that contains numeric values and the value '?'.

How should I proceed to count the number of rows that have the value '?' ?

I'm trying to run:

question_mark_count = df['column'].str.contains('\?').sum()

in a column that has numeric value and some question marks '?', but I'm getting the error:

AttributeError: Can only use .str accessor with string values!

When I run df.dtypes, I can see that the column is 'object' type.

I've also tried to convert the column to string:

df["column"] = df["column"].astype("string")

But I'm still getting the same error.

it won't work because I also have 0.0 values. It throws ValueError: invalid literal for int() with base 10: '0.0' — jimmy
– jimmy, Commented Oct 22, 2021 at 21:44

eliu · Accepted Answer · 2021-10-22 21:52:09Z

1

to further explore possibilities:

df["column"].str.contains('\?').value_counts()

immune to np.nan pd.NA ints floats or whatever you have in your df['column']

answered Oct 22, 2021 at 21:52

eliu

2,4892 gold badges19 silver badges31 bronze badges

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

Comments

Danail Petrov · Accepted Answer · 2021-10-22 21:40:40Z

1

how about this?

>>> (df["column"].str.contains('\?')).astype('int').sum()

answered Oct 22, 2021 at 21:40

Danail Petrov

1,90512 silver badges12 bronze badges

4 Comments

jimmy Over a year ago

I got a ValueError: cannot convert float NaN to integer. Is is possible to concatenate a command to get rid of the NaN values as well in your proposed solution?

Danail Petrov Over a year ago

This is because you probably have NaN in ‘column’. Get rid of it first & then apply the solution above. It should work

jimmy Over a year ago

df_concatenated['column'].isnull().any() returns false .

Danail Petrov Over a year ago

NaN != null. Also chec eliu’s solution with value_counts()

Morita Ichika · Accepted Answer · 2022-10-10 09:25:43Z

0

In my case the previous answer is almost correct. Try to add na=False in the call to the contains function:

df["column"].str.contains('\?', na=False).astype('int').sum()

answered Oct 10, 2022 at 9:25

Morita Ichika

214 bronze badges

Collectives™ on Stack Overflow

How to count number of rows with a specific string value in a column using pandas?

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related