.replace() is not working when applied to multiple columns in pandas

Question

I have a few columns that have numerical data with commas (eg. the number is stored as '4,200' and hence not being read as a number) in base file. To able to process the data I need to remove these commas from multiple columns of data.

import pandas as pd
import numpy as np
df = {'INR': ['4,200','5,000',0,'4,353','6,000',1],
'USD':['4,100','3,000','1,000','4,353','6,000',1]}
df = pd.DataFrame(df)

If I write the following line of code it works:

df['INR']=df['INR'].replace(',','').astype(int)

But the following line of code doesn't:

df[['INR','USD']]=df[['INR','USD']].replace(',','').astype(int)

Would be great if someone can help understand why

Use df[['INR','USD']].replace(',', '', regex=True)

Shubham Sharma
– Shubham Sharma

2020-08-22 07:43:37 +00:00
Commented Aug 22, 2020 at 7:43 — Shubham Sharma
– Shubham Sharma, Commented Aug 22, 2020 at 7:43

jeugregg · Accepted Answer · 2020-08-22 08:16:08Z

1

Actually, when you call df[['INR','USD']], his type is pd.DataFrame. This type doesn't have ".replace" method. only pd.Series with this call :

df['INR'].astype(str).str.replace(",", "").astype(int)

Don't forget the .str.

For your case, use applymap method like that :

df[['INR','USD']] = df[['INR','USD']].applymap(lambda x: int(str(x).replace(",","")))

You can have information about applymap here : https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.applymap.html

Shortcut method by infering types (I prefer no shortcut method to understand what is doing...) :

df[['INR','USD']] = df[['INR','USD']].replace(',', '', regex=True)

edited Aug 22, 2020 at 8:16

answered Aug 22, 2020 at 8:10

jeugregg

1346 bronze badges

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

Collectives™ on Stack Overflow

.replace() is not working when applied to multiple columns in pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related