How to replace values in Pandas columns only if RegEx condition is matched

Question

I have the following dataframe:

d_test = {
    'c1' : ['31', '421', 'sgdsgd', '523.3'],
    'c2' : ['41', np.nan, '412', '412'],
    'test': [1,2,3,4],
}
df_test = pd.DataFrame(d_test)

I want to replace all values to np.nan if they are not float:

0   31      41   1
1   421     NaN  2
2   NaN     412  3
3   523.3   412  4

here what I do:

df_test[['c1', 'c2']] = df_test[['c1', 'c2']].replace(to_replace=r'^[+-]?([0-9]+([.][0-9]*)?|[.][0-9]+)$', value=np.nan, regex=True)

But result is not what I am looking for:

0   NaN     NaN  1
1   NaN     NaN  2
2   sgdsgd  NaN  3
3   NaN     NaN  4

You have the sense inverted. That .replace() will turn numbers into NaN, but you explained that you want to turn non-numbers into NaN. Just use .to_numeric() and be done with it. — J_H
– J_H, Commented Dec 9, 2022 at 1:12

Timeless · Accepted Answer · 2022-12-09 01:19:50Z

4

IIUC, you can use pandas.to_numeric with errors="coerce":

errors{‘ignore’, ‘raise’, ‘coerce’}, default ‘raise’ :

If ‘raise’, then invalid parsing will raise an exception.

If ‘coerce’, then invalid parsing will be set as NaN.

If ‘ignore’, then invalid parsing will return the input.

df_test = df_test.apply(pd.to_numeric, errors="coerce")

# Output :

print(df_test)
      c1     c2  test
0   31.0   41.0     1
1  421.0    NaN     2
2    NaN  412.0     3
3  523.3  412.0     4

edited Dec 9, 2022 at 1:19

answered Dec 9, 2022 at 0:30

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

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

2 Comments

fsimonjetz Over a year ago

Nice solution! You can make it even more concise with df_test.apply(pd.to_numeric, errors="coerce") since keyword args passed to apply get passed to the function.

Timeless Over a year ago

Thanks fsimonjetz, I updated my answer ;)

Collectives™ on Stack Overflow

How to replace values in Pandas columns only if RegEx condition is matched

1 Answer 1

# Output :

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

# Output :

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related