Replace Multiple Values On Python or EXCEL-CodePudding

Seeking for Help. Hi Guys i didnt code yet because i think i need some idea to access the csv and the row. so technically i want to replace the text with the id on the CSV file

import pandas as pd
df = pd.read_csv('replace.csv')
print(df)

Please kindly view the photo. so if you see there is 3 column, so i want to replace the D Column if the D Column is Equal to A Column, then replace with the ID (column B). seeking for i idea if what is the first step or guide.. thanks

Formula in E2:

=MAP(C2:C4,LAMBDA(x,TEXTJOIN(",",,XLOOKUP(TEXTSPLIT(x,","),A2:A4,B2:B4,"",0))))

Or, if all values will be present anyways:

=MAP(C2:C4,LAMBDA(x,TEXTJOIN(",",,VLOOKUP(TEXTSPLIT(x,","),A2:B4,2,0))))

Google-Sheets:

The Google-Sheets equivalent, as per your request, could be:

=MAP(C2:C4,LAMBDA(x,INDEX(TEXTJOIN(",",,VLOOKUP(SPLIT(x,","),A2:B4,2,0)))))

Python/Pandas:

After some trial and error I came up with:

import pandas as pd
df = pd.read_csv('replace.csv', sep=';')
df['Replace'] = df['Replace'].replace(pd.Series(dict(zip(df.name, df.id))).astype(str), regex=True)
print(df)

Prints:

    name  id Replace
0  James   5   5,5,2
1    Tom   2   2,5,5
2  Jerry  10   2,2,2

Note: I used the semi-colon as seperator in the function call to open the CSV.

CodePudding user response：

Nested =substitute functions would make this easy.

=substitute(substitute(substitute(d2, a2, b2),a3,b3),a4,b4)