conditionally Replace string in a Pandas series with another string

Question

Take the below example. To replace one string in one particular column I have done this and it worked fine:

df = pd.DataFrame({'key': ['A', 'B', 'C', 'A', 'B', 'C'],
                   'data1': range(6),
                   'data2': ['A1', 'B1', 'C1', 'A1', 'B1', 'C1']},
                   columns = ['key', 'data1', 'data2'])

  key  data1 data2
0   A      0    A1
1   B      1    B1
2   C      2    C1
3   A      3    A1
4   B      4    B1
5   C      5    C1



df['data2']= df['data2'].str.strip().str.replace("A1","Bad")

  key  data1 data2
0   A      0    Bad
1   B      1    B1
2   C      2    C1
3   A      3    Bad
4   B      4    B1
5   C      5    C1

Q(1) How can we conditionally replace one string? Meaning that, in column data2, I would like to replace A1 but only if "key==A" and "data1">1. How can I do that?

Q(2) Can the conditional replacement be applied to multiple replacement (i.e., replacing A1 and A2 at the same time with "Bad" but only under similar conditions?

asongtoruin · Accepted Answer · 2018-03-22 09:37:13Z

3

You can use numpy and a regex-based replacement to cover A1, A2 and more. if we extend your data to include an example with A3:

import pandas as pd
import numpy as np

df = pd.DataFrame({'key': ['A', 'B', 'C', 'A', 'B', 'C', 'A'],
                   'data1': range(7),
                   'data2': ['A1', 'B1', 'C1', 'A1', 'B1', 'C1', 'A3']},
                   columns=['key', 'data1', 'data2'])

df['data2'] = np.where((df['key'] == 'A') & (df['data1'] > 1),
                       df['data2'].str.replace(r'A\d+','Bad'),
                       df['data2'])

This returns:

  key  data1 data2
0   A      0    A1
1   B      1    B1
2   C      2    C1
3   A      3   Bad
4   B      4    B1
5   C      5    C1
6   A      6   Bad

edited Mar 22, 2018 at 9:37

answered Mar 22, 2018 at 9:30

asongtoruin

10.4k3 gold badges42 silver badges52 bronze badges

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

Comments

jezrael · Accepted Answer · 2018-03-22 09:34:31Z

I think need filter column in both sides with replace only for filtered rows:

mask = (df['key']=="A") &  (df['data1'] > 1)
df.loc[mask, 'data2']= df.loc[mask, 'data2'].str.strip().str.replace("A1","Bad")  

print (df)
  key  data1 data2
0   A      0    A1
1   B      1    B1
2   C      2    C1
3   A      3   Bad
4   B      4    B1
5   C      5    C1

If need multiple replace use replace with dict:

df = pd.DataFrame({'key': ['A', 'A', 'C', 'A', 'B', 'C'],
                   'data1': range(6),
                   'data2': ['A1', 'A2', 'C1', 'A1', 'B1', 'C1']},
                   columns = ['key', 'data1', 'data2'])

mask = (df['key']=="A") &  (df['data1'] > 0)
df.loc[mask, 'data2']= df.loc[mask, 'data2'].str.strip().replace({"A1":"Bad", "A2":'Bad1'})

Or use regex:

df.loc[mask, 'data2']= df.loc[mask, 'data2'].str.strip().str.replace(r'^A.*',"Bad")


print (df)
  key  data1 data2
0   A      0    A1
1   A      1  Bad1
2   C      2    C1
3   A      3   Bad
4   B      4    B1
5   C      5    C1

SBad · Accepted Answer · 2018-03-22 13:10:26Z

0

If we want to extend the example above in the following way:

df = pd.DataFrame({'key': ['A', 'B', 'C', 'A', 'B', 'C'],
                   'data1': range(6),
                   'data2': ['A1', 'B1', 'C1', 'A1', 'B1', 'C1']},
                   columns = ['key', 'data1', 'data2'])  

mask = (df['data1'] > 1)
df.loc[mask, 'data2']= df.loc[mask, 'data2'].str.strip().str.replace("A1",df['key']) 

  key  data1 data2
0   A      0    A1
1   B      1    B1
2   C      2   NaN
3   A      3   NaN
4   B      4   NaN
5   C      5   NaN

I am very surprised by the answer I thought the content of data2 would be replaced by content of column "key" (at the condition data1>1). any idea?

answered Mar 22, 2018 at 13:10

SBad

1,3456 gold badges26 silver badges37 bronze badges

4 Comments

jezrael Over a year ago

Do you need df.loc[mask, 'data2']= df.loc[mask].apply(lambda x: x['data2'].strip().replace('A1', x['key']), axis=1) ?

SBad Over a year ago

@jezrael ..thats exactly what I need...thank you and thank you again..... iam trying to apply it to my data and I get an error saying "raise IndexingError('Unalignable boolean Series key provided') pandas.core.indexing.IndexingError: Unalignable boolean Series key provided"

jezrael Over a year ago

Hmmm, solution is same only data are changed?

jezrael Over a year ago

Sometimes help df.loc[mask.values, 'data2']= df.loc[mask.values], but without data hard to know.

Collectives™ on Stack Overflow

conditionally Replace string in a Pandas series with another string

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related