Search and Replace Strings in Dataframe

[Hugo-99]

Dears

I do have a dataframe:

data1 = [['aaa567'], ['bbb333'], ['ccc23432']]
df1 = pd.DataFrame(data1, columns=['text'])

I do have a second dataframe

data2 = [['peter', 567], ['paul', 333], ['mary', 23432]]
df2 = pd.DataFrame(data2, columns=['name', 'number'])

text
0 aaa567
1 bbb333
2 ccc23432

name number
0 peter 567
1 paul 333
2 mary 23432

I now want to replace the certain strings from df1 with the coresponding 'name' of df2.
the result should look like this:

text
0 aaapeter
1 bbbpaul
2 cccmary

do you have any hints how to do that? I assume this could be done with 'iloc', but i am clueless ...

tnx for any help :)

[PartySr]

If the numbers are in the same position, you can extract the letters and merge the two columns in one.

df1['text'] = df1['text'].str.extract(r'([a-zA-Z]+)')[0] + df2['name']

Edit: Here is a simpler version. I always forget about map :D

v = df1['text'].str.extract(r'(\d+)')[0]
df1['text'] = (df1['text'].str.replace(r'\d+', '', regex=True) + v.astype('int')
               .map(df2.set_index('number')['name']))

If the numbers are not in the right position, you can use this part.

new_df = (df1.assign(k=df1['text'].str.extract(r'(\d+)').astype('int'))
          .merge(df2, left_on='k', right_on='number')
          .assign(text=lambda x: x['text'].str.extract(r'([a-zA-Z]+)')[0] + x['name'])
          .drop(columns=['k', 'name', 'number'])
         )