Find and replace in dataframe from another dataframe

Question

I have two dataframes, here are snippets of both below. I am trying to find and replace the artists names in the second dataframe with the id's in the first dataframe. Is there a good way to do this?

   id                fullName
0   1           Colin McCahon
1   2  Robert Henry Dickerson
2   3           Arthur Dagley

                                             Artists
0           Arthur Dagley, Colin McCahon, Maria Cruz
1  Fiona Gilmore, Peter Madden, Nicholas Spratt, ...
2                             Robert Henry Dickerson
3                                         Steve Carr

Desired output:

                                             Artists
0                                   3, 1, Maria Cruz
1  Fiona Gilmore, Peter Madden, Nicholas Spratt, ...
2                                                  2
3                                         Steve Carr

BENY · Accepted Answer · 2019-01-22 04:35:28Z

2

You mean check with replace

df1.Artists.replace(dict(zip(df.fullName,df.id.astype(str))),regex=True)
0                                     3, 1, Maria Cruz
1    Fiona Gilmore, Peter Madden, Nicholas Spratt, ...
2                                                    2
3                                           Steve Carr
Name: Artists, dtype: object

answered Jan 22, 2019 at 4:35

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

Primusa · Accepted Answer · 2019-01-22 04:56:55Z

1

Convert your first dataframe into a dictionary:

d = Series(name_df.id.astype(str),index=name_df.fullName).to_dict()

Then use .replace():

artists_df["Artists"] = artists_df["Artists"].replace(d, regex=True)

edited Jan 22, 2019 at 4:56

answered Jan 22, 2019 at 4:35

Primusa

13.5k3 gold badges36 silver badges56 bronze badges

1 Comment

Bimons Over a year ago

That only replaces if, in replace(d), d is the whole string. so, replace("Arthur Dagley","3")) doesnt work. It would have to be replace("Arthur Dagley, Colin McCahon, Maria Cruz","3, Colin McCahon, Maria Cruz"))

Collectives™ on Stack Overflow

Find and replace in dataframe from another dataframe

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related