A faster way to replace multiple string value in a column in Pandas

Question

I am trying to replace multiple string values in a column and I understand that I can use the replace() to do it one by one. Given I need to replace more than 10 string values, I am just wondering if there's a faster way to replace a number of string values to the same value.

df = pd.DataFrame({'a':["US", "Japan", "UK", "China", "Peru", "Germany"]})
df.replace({'a' : { 'Japan' : 'Germany', 'UK' : 'Germany', 'China' : 'Germany' }})

Expected output:

         a
0       US
1  Germany
2  Germany
3  Germany
4     Peru
5  Germany

Try df.replace('Japan|UK|China', 'Germany', regex=True). The df.replace() can handel regualr expressions, there you can combine multiple strings/groups. — mosc9575
– mosc9575, Commented Oct 22, 2021 at 11:48
How many different string values do you have in your column? — Dani Mesejo
– Dani Mesejo, Commented Oct 22, 2021 at 11:48

jezrael · Accepted Answer · 2021-10-22 12:00:36Z

4

Use numpy.where with Series.isin:

#60k rows
df = pd.DataFrame({'a':["US", "Japan", "UK", "China", "Peru", "Germany"] * 10000})

In [161]: %timeit df['a'] = df.a.map({ 'Japan' : 'Germany', 'UK' : 'Germany', 'China' : 'Germany' }).fillna(df.a)
12.4 ms ± 501 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [162]: %timeit df['a'] = np.where(df.a.isin(['Japan','UK','China']), 'Germany', df.a)
4.27 ms ± 379 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)   

#assignment raise error in test
In [1632]: %timeit df.replace({'a' : { 'Japan' : 'Germany', 'UK' : 'Germany', 'China' : 'Germany' }})
7.85 ms ± 462 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Slowier solution:

In [157]: %timeit df.replace('Japan|UK|China', 'Germany', regex=True)
218 ms ± 842 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)

edited Oct 22, 2021 at 12:00

answered Oct 22, 2021 at 11:51

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

3 Comments

sammywemmy Over a year ago

I think a more thorough test should include the reassignment, which should be as a Series, either as a direct assignment or with assign method.

jezrael Over a year ago

@sammywemmy - good idea.

codedancer Over a year ago

You always have some nice solutions!

mosc9575 · Accepted Answer · 2021-10-22 11:52:47Z

1

Use:

df = df.replace('Japan|UK|China', 'Germany', regex=True)

answered Oct 22, 2021 at 11:52

mosc9575

6,3922 gold badges12 silver badges36 bronze badges

2 Comments

jezrael Over a year ago

added to my answer.

mosc9575 Over a year ago

I see your point. It is a slow way to do it. Thank you.

Collectives™ on Stack Overflow

A faster way to replace multiple string value in a column in Pandas

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related