Comma Separated String of ids map to values in python Pandas-CodePudding

I have a comma separated string in a columns in pandas DataFrame:

I have a mapping Dataframe

I want the final Dataframe:

I want a new column and replace the ids with values from the mapping dataframe, what is the write pythonic pandas way to implement this?

CodePudding user response：

You can use pd.Series.str.split, replace and join back into str:

df = pd.DataFrame({"Index":[0,1], "ids":["1,2,3", "1,2,3"]})
df2 = pd.DataFrame({"id":[1,2,3], "value":["value1", "value2", "value3"]})

df["value"] = (df["ids"].str.split(",", expand=True).astype(int)
                        .replace(df2.set_index("id")["value"])
                        .agg(lambda d: ",".join(i for i in d if isinstance(i, str)), axis=1))

print (df)

   Index    ids                 value
0      0  1,2,3  value1,value2,value3
1      1  1,2,3  value1,value2,value3