python - 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.

question from:https://stackoverflow.com/questions/65617204/pandas-replacing-value-with-string-from-other-dataframe

Answer 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