menu

Questions & Answers

Replace Multiple Values On Python or EXCEL

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

Photo

In The Photo

name  | id | Replace
james | 5  | James,James,Tom
tom   | 2  | Tom,James,James
jerry | 10 | Tom,Tom,Tom

What Im Expected Result:

name  | id | Replace
james | 5  | 5,5,2
tom   | 2  | 2,5,5
jerry | 10 | 2,2,2
Comments:
2023-01-11 09:10:47
Just to be clear, can you include the expected results?
2023-01-11 09:10:47
Hi i added sir what i want to do
2023-01-11 09:10:47
@JvdV Can you help me?
2023-01-11 09:10:47
I can't advise on the Python/Pandas side of things as I have to little knowledge about it, but if you can do this in Excel I could help. Does it have to be done in place? If so, use PowerQuery or VBA. If it's ok to do in a new column, then it's rather easy with ms365 functions.
2023-01-11 09:10:47
Can you help me with excel or google sheet i think that a good i dea? where is the simple way to do it? please help me to acheive that on google sheet or exel
2023-01-11 09:10:47
@JvdV this is the wrong formula im using =VLOOKUP(A3,$A$1:$B$3,2,FALSE)
Answers(2) :

Nested =substitute functions would make this easy.

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

As per my comment, if it's ok to get data in a new column and with ms365, try:

enter image description here

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))))

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

=MAP(C2:C4,LAMBDA(x,INDEX(TEXTJOIN(",",,VLOOKUP(SPLIT(x,","),A2:B4,2,0)))))
Comments:
2023-01-11 09:10:47
Hello thank you im getting error text slip how to fix that?
2023-01-11 09:10:47
That means nothing to me @nidiv
2023-01-11 09:10:47
it is possible to upload the file? this is for excel right? not google sheet?
2023-01-11 09:10:47
WOW ITS WORK ON GOOGLE SHEET THANK YOU SO MUCH!!!!!