check if pandas data frame column (string/object) is numeric (ignore empty/NULL/NAN)

Question

I tried this inspired by the accepted answer here:

df = pd.DataFrame({'col1':['1', '']})
all_numeric = pd.to_numeric(df['col1'], errors='coerce').notnull().all().item()
print(all_numeric)

to detect that a columns is numeric (ignoring blanks + NULLs + NANs).

In the above code all_numeric is False (Python bool), which does not make sense or maybe it does? I thought I try to impute nan as the reason might be the empty value:

df = pd.DataFrame({'col1':['1', '']})
df = df.apply(lambda x: x.str.strip()).replace('', np.nan)
all_numeric = pd.to_numeric(df['col1'], errors='coerce').notnull().all().item()
print(all_numeric)

Same outcome. Maybe my way of checking if all values of a column are numeric (ignoring NULL/NAN/empty strings) is wrong? Thanks!

@anon01 ??? is df = pd.DataFrame({'col1':['1', '']}) not data? — cs0815
– cs0815, Commented Apr 1, 2022 at 15:41

score 0 · Accepted Answer · 2022-04-01 16:57:47Z

0

You could strip white spaces and convert empty string to NaN, then drop it; then do the test:

out = pd.to_numeric(df['col1'].str.strip().replace('', pd.NA).dropna(), errors='coerce').notna().all().item()

Output:

True

This test throws False for the following input:

df = pd.DataFrame({'col1':['1', 's']})

edited Apr 1, 2022 at 16:57

answered Apr 1, 2022 at 15:42

user7864386

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

11 Comments

cs0815 Over a year ago

I am sorry I do not follow. Are you saying I should remove notnull()?

cs0815 Over a year ago

thanks. so how do I check if all values of a column are numeric (type object/string) ignoring empty entries, NULLs and NANs?

cs0815 Over a year ago

thanks but I want the logic for numeric detection ignore this and nulls and nans

cs0815 Over a year ago

no I do not - sorry

cs0815 Over a year ago

this is actually why I have df = df.apply(lambda x: x.str.strip()).replace('', np.nan)

|

Muntakimur Rahaman · Accepted Answer · 2022-04-01 15:53:14Z

-1

According to the official pandas doc, you can check a series of data if it's numeric or not.

>>df = pd.DataFrame({'col1':['1', '']})
>>df.col1.str.isnumeric()

0     True
1    False

answered Apr 1, 2022 at 15:53

Muntakimur Rahaman

1891 silver badge9 bronze badges

5 Comments

cs0815 Over a year ago

df['col1'].str.isnumeric().mean() might work ...

Muntakimur Rahaman Over a year ago

Do you want to retrieve those cols which contain numeric values?

cs0815 Over a year ago

no I just want to transform them to numeric if there cardinality is also above threshold. long story

Muntakimur Rahaman Over a year ago

Can you add some sample output?

cs0815 Over a year ago

My example is pretty self explanatory I think?

Collectives™ on Stack Overflow

check if pandas data frame column (string/object) is numeric (ignore empty/NULL/NAN)

2 Answers 2

11 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related