CODEWITHSUNDEEP
pythonpandasstringdataframereplace
AnalysisChamp
AnalysisChamp

Reputation: 13

replace a word in a column with values from another column in pandas dataframe

In below table, I want to create a new column or update the Comment column that can replace the placeholder word "NameTag" with the value in column "Name" and the word "IDTag" with the value in column "ID"

ID Name Comment
A1 Alex the name NameTag belonging to IDTag is a common name
A2 Alice Judging the NameTag of IDTag, I feel its a girl's name

I want the output like this...

ID Name Comment
A1 Alex the name Alex belonging to A1 is a common name
A2 Alice Judging the Alice of A2, I feel its a girl's name

I tried pandas replace function but it didn't work to replace word with column name... getting below error

ValueError: Series.replace cannot use dict-value and non-None to_replace

Upvotes: 1

Views: 639

Answers (6)

Shivai chaudhari
Shivai chaudhari

Reputation: 21

try :

import pandas as pd

df = pd.DataFrame({
     'ID':['A1','A2'],
     'Name':['Alex','Alice'],
     'Comment':["the name NameTag belonging to IDTag is a common name","Judging the NameTag of IDTag, I feel its a girl's name"]
})

for i  in range(len(df['Comment'])):
    df.loc[i,'Comment'] = df.loc[i,'Comment'].replace('IDTag',df.loc[i,'ID']).replace('NameTag',df.loc[i,'Name'])

output dataframe

Upvotes: 1

mozway
mozway

Reputation: 260640

For a generic method that works with any number of columns you can use a regex and a list comprehension:

import re

cols = ['ID', 'Name']

pattern = '|'.join([f'{x}Tag' for x in cols]) # 'IDTag|NameTag'
r = re.compile(fr"\b({pattern})\b")

df['Comment'] = [r.sub(lambda m: d.get(m.group(1)), s)
                 for d, s in zip(df[cols].add_suffix('Tag').to_dict('index').values(),
                                 df['Comment'])]

Output:

   ID   Name                                            Comment
0  A1   Alex     the name Alex belonging to A1 is a common name
1  A2  Alice  Judging the Alice of A2, I feel its a girl's name

Upvotes: 0

Jamie Dormaar
Jamie Dormaar

Reputation: 71

given the duplicated dataFrame "test":

test = pd.DataFrame({
    'ID': ['A1', 'A2'], 
    'Name': ['Alex', 'Alice'], 
    'Comment': ["the name NameTag belonging to IDTag is a common name", "Judging the NameTag of IDTag, I feel its a girl's name"]})

test

output:


ID  Name    Comment
0   A1  Alex    the name NameTag belonging to IDTag is a commo...
1   A2  Alice   Judging the NameTag of IDTag, I feel its a gir...

This for loop does what you need I think,

for row in range(test.shape[0]):
    name = test['Name'].loc[row]
    id = test['ID'].loc[row]
    test['Comment'].loc[row] = test['Comment'].loc[row].replace('NameTag', name)
    test['Comment'].loc[row] = test['Comment'].loc[row].replace('IDTag', id)

test

output:

    ID  Name    Comment
0   A1  Alex    the name Alex belonging to A1 is a common name
1   A2  Alice   Judging the Alice of A2, I feel its a girl's name

Upvotes: 0

jezrael
jezrael

Reputation: 862661

Use list comprehension:

df["Comment"] = [c.replace("NameTag", n).replace("IDTag", i) 
                 for c, n, i in zip(df['Comment'], df['Name'], df['ID'])]
print (df)
    ID    Name                                            Comment
0  A1    Alex    the name Alex  belonging to A1  is a common name
1  A2   Alice   Judging the Alice  of A2 , I feel its a girl's...

Upvotes: 1

hlidka
hlidka

Reputation: 2345

import pandas as pd

data = {'ID': ['A1', 'A2'], 'Name': ['Alex', 'Alice'], 'Comment': ['the name NameTag belonging to IDTag is a common name', 'Judging the NameTag of IDTag, I feel its a girl\'s name']}

df = pd.DataFrame(data)
df['Personalized'] = df.Comment.replace(['NameTag'] * len(df), df.Name, regex=True)

print(df.Personalized)

prints

0    the name Alex belonging to IDTag is a common name
1    Judging the Alex of IDTag, I feel its a girl's...

Upvotes: 1

Jason Baker
Jason Baker

Reputation: 3706

Using .apply and lambda:

df["Comment"] = df.apply(lambda x: x["Comment"].replace("NameTag", x["Name"]), axis=1)

Upvotes: 0

Related Questions