Replace Multiple Values On Python or EXCEL

Question

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

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 — nidiv
– nidiv, Commented Jan 11, 2023 at 8:20
@JvdV this is the wrong formula im using =VLOOKUP(A3,$A$1:$B$3,2,FALSE) — nidiv
– nidiv, Commented Jan 11, 2023 at 8:24

JvdV · Accepted Answer · 2023-01-11 09:31:37Z

1

Excel 365:

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

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.

edited Jan 11, 2023 at 9:31

answered Jan 11, 2023 at 8:34

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

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

8 Comments

nidiv Over a year ago

Hello thank you im getting error text slip how to fix that?

nidiv Over a year ago

it is possible to upload the file? this is for excel right? not google sheet?

JvdV Over a year ago

@nidiv, I also included a Pandas solution that seems to work fine too.

nidiv Over a year ago

it is possible to avoid #N/A when the "name" is missing?

JvdV Over a year ago

For GS: =MAP(C2:C4,LAMBDA(x,INDEX(TEXTJOIN(",",,IFERROR(VLOOKUP(SPLIT(x;","),A2:B4,2,0),"Missing"))))) I suppose. For Excel you should use the XLOOKUP() variant @nidiv

|

Rycliff · Accepted Answer · 2023-01-11 08:44:31Z

0

Nested =substitute functions would make this easy.

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

edited Jan 11, 2023 at 8:44

answered Jan 11, 2023 at 8:37

Rycliff

1271 silver badge9 bronze badges

Collectives™ on Stack Overflow

Replace Multiple Values On Python or EXCEL

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related