Replace values in DataFrame column when they start with string using lambda

Question

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

does your dataframe has just the 1 column? and apply isnt a preffered way bdw — anky
– anky, Commented Aug 22, 2019 at 17:32
and each column has values starting with XXX which you want to replace with np.nan or is it just 1 column? — anky
– anky, Commented Aug 22, 2019 at 17:51

Roushan · Accepted Answer · 2019-08-22 18:08:55Z

16

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

edited Aug 22, 2019 at 18:08

answered Aug 22, 2019 at 17:34

Roushan

4,4914 gold badges25 silver badges39 bronze badges

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

1 Comment

McRae Over a year ago

Excellent. This answer helps me understand lambda better for this sort of thing.

anky · Accepted Answer · 2019-08-22 18:35:55Z

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:

edited Aug 22, 2019 at 18:35

answered Aug 22, 2019 at 17:47

anky

75.3k11 gold badges46 silver badges76 bronze badges

2 Comments

McRae Over a year ago

I like the np.where option you presented. How does the apply lambda test against it?

anky Over a year ago

@McRae check this

Valdi_Bo · Accepted Answer · 2019-08-22 17:37:04Z

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.

edited Aug 22, 2019 at 17:37

answered Aug 22, 2019 at 17:29

Valdi_Bo

31.1k4 gold badges29 silver badges45 bronze badges

2 Comments

McRae Over a year ago

Thanks very much for your answer. It looks like I kind of fell on the right path anyway??

Valdi_Bo Over a year ago

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.

Collectives™ on Stack Overflow

Replace values in DataFrame column when they start with string using lambda

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related