Replace string with corresponding string from another column with pandas

Question

I have a data frame called df that looks something like this:

pd.DataFrame({
    'column1' : ['client#1 is #name#', 'client#2 is #name#'], 
    'column2': ['josh', 'max']}
)

              column1 column2
0  client#1 is #name#    josh
1  client#2 is #name#     max

I am trying to replace the phrase "#name" in column1 with the value of column2. I want the end result to look like this:

I have tried a few approaches like the following:

df['column1'] = df['column1'].replace(["#name#"], df['column2'])

But I am not sure of how to grab the specific phrase '#name#' in column1 and replace it with the value of column2. Any suggestions on how to approach this would be greatly appreciated!

cs95 · Accepted Answer · 2019-01-11 17:25:46Z

2

If it's strings, and if there are no NaNs, I would recommend calling str.replace inside a list comprehension for speed:

df['column1'] = [
    x.replace('#name#', y) for x, y in zip(df.column1, df.column2)]

df
            column1 column2
0  client#1 is josh    josh
1   client#2 is max     max

Why are list comprehensions worth it for string operations? You can read more at For loops with pandas - When should I care?.

Another interesting option you can consider is str.replace with iter:

it = iter(df.column2)
df['column1'] = df.column1.str.replace('#name#', lambda x: next(it))

df
            column1 column2
0  client#1 is josh    josh
1   client#2 is max     max

Should handle NaNs and mixed dtypes just fine (but will be slower).

A simpler replace option by @Vaishali, which will work if the "#name#" substring is always at the end of the string.

df['column1'] = df.column1.add(df.column2).str.replace('#name#', '')
df
            column1 column2
0  client#1 is josh    josh
1   client#2 is max     max

edited Jan 11, 2019 at 17:25

answered Jan 11, 2019 at 17:14

cs95

406k106 gold badges744 silver badges797 bronze badges

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

4 Comments

user3116949 Over a year ago

Thanks @coldspeed this really helps and clarifies what I am trying to do!

cs95 Over a year ago

@user3116949 Word of advice, try to make sure your questions conform to the site guidelines. This means all data should be present as runnable code in your question.

Vaishali Over a year ago

Or simply df.column1.add(df.column2).str.replace('#name#', '') :)

cs95 Over a year ago

@Vaishali Ah that is a nice one and will work, assuming "#name#" is always at the end of the string.

Collectives™ on Stack Overflow

Replace string with corresponding string from another column with pandas

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related