7

I have a DataFrame:

import pandas as pd
import numpy as np
x = {'Value': ['Test', 'XXX123', 'XXX456', 'Test']}
df = pd.DataFrame(x)

I want to replace the values starting with XXX with np.nan using lambda.

I have tried many things with replace, apply and map and the best I have been able to do is False, True, True, False.

The below works, but I would like to know a better way to do it and I think the apply, replace and a lambda is probably a better way to do it.

df.Value.loc[df.Value.str.startswith('XXX', na=False)] = np.nan
McRae
  • 177
  • 1
  • 2
  • 12

3 Answers3

15

use the apply method

In [80]: x = {'Value': ['Test', 'XXX123', 'XXX456', 'Test']}
In [81]: df = pd.DataFrame(x)
In [82]: df.Value.apply(lambda x: np.nan if x.startswith('XXX') else x)
Out[82]:
0    Test
1     NaN
2     NaN
3    Test
Name: Value, dtype: object

Performance Comparision of apply, where, loc enter image description here

Roushan
  • 4,074
  • 3
  • 21
  • 38
5

np.where() performs way better here:

df.Value=np.where(df.Value.str.startswith('XXX'),np.nan,df.Value)

Performance vs apply on larger dfs:

enter image description here

anky
  • 74,114
  • 11
  • 41
  • 70
  • 1
    I like the np.where option you presented. How does the apply lambda test against it? – McRae Aug 22 '19 at 17:52
  • @McRae check [this](https://stackoverflow.com/questions/41166348/why-is-np-where-faster-than-pd-apply) – anky Aug 22 '19 at 18:00
1

Use of .loc is not necessary. Write just:

df.Value[df.Value.str.startswith('XXX')] = np.nan

Lambda function could be necessary if you wanted to compute some expression to be substituted. In this case just np.nan is enough.

Valdi_Bo
  • 30,023
  • 4
  • 23
  • 41
  • Thanks very much for your answer. It looks like I kind of fell on the right path anyway?? – McRae Aug 22 '19 at 17:46
  • I thouhgt actually about applying a lambda function, which returns some value to be substituted. In this case the value to substitute is just *np.nan*, so there is no need to apply any lambda function. – Valdi_Bo Aug 22 '19 at 17:49