Pandas replacing value with string from other dataframe

Question

i want to replace all cell values(int)[dataset1] with the name(str)[dataset2] where the index of [dataset2] is matching with the values from [dataset1].

enter image description here

It looks so simple ... but I'm completely stuck.

Start by pasting your actual data into the question and formatting it as code, that way we can copy/paste and help you out. Images of data are not very useful. — Chris
– Chris, Commented Jan 7, 2021 at 17:35
use merge() twice and you have your answer. Can't show with data as images not text — Rob Raymond
– Rob Raymond, Commented Jan 7, 2021 at 19:08

Rob Raymond · Accepted Answer · 2021-01-07 19:22:04Z

1

merge() the names dataframe onto each of the value columns then cleanup.

df1 = pd.DataFrame({"id":[1111,1122,1133,1144,1155,1166],
              "name":["red","blue","green","yellow","magenta","black"]})
df2 = pd.DataFrame({"value1":[1122,1144,1166,1111,1111,1155],
             "value2":[np.nan,np.nan,1133,np.nan,1144,np.nan]})

output = (df2
 .merge(df1, left_on="value1", right_on="id", how="left")
 .rename(columns={"name":"name1"})
 .merge(df1, left_on="value2", right_on="id", how="left")
 .rename(columns={"name":"name2"})
 .drop(columns=["id_x","id_y","value1","value2"])
)

print(output.to_string())

output

     name1   name2
0     blue     NaN
1   yellow     NaN
2    black   green
3      red     NaN
4      red  yellow
5  magenta     NaN

answered Jan 7, 2021 at 19:22

Rob Raymond

31.5k3 gold badges19 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Pandas replacing value with string from other dataframe

1 Answer 1

output

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

output

Comments

Your Answer

Sign up or log in

Post as a guest

Related