CODEWITHSUNDEEP
pythonpandas
Alex T
Alex T

Reputation: 3754

Converting commas to dots from excel file in pandas

I have this kind of column in excel file:

Numbers
   13.264.999,99
  1.028,10
756,4
  1.100,1

So when I load it with pd.read_excel some numbers like 756,4 get converted to 756.4 and become floats while other 3 from the example above remain the same and are strings.

Now I want to have the column in this form (type float):

Numbers
13264999.99
1028.10
756.4
1100.1

However when converting the loaded column from excel using this code:

df["Numbers"]=df["Numbers"].str.replace('.','')
df["Numbers"]=df["Numbers"].str.replace(',','.')
df["Numbers"]=df["Numbers"].astype(float)

I get:

Numbers
13264999.99
1028.10
nan
1100.1

What to do?

Upvotes: 3

Views: 1649

Answers (3)

Alex T
Alex T

Reputation: 3754

Okay so I managed to solve this issue:

So first I convert every value to string and then replace every comma to dot. Then I leave last dot so that the numbers can be converted to float easily:

df["Numbers"]=df["Numbers"].astype(str).str.replace(",", ".")
df["Numbers"]=df["Numbers"].str.replace(r'\.(?=.*?\.)', '')  
df["Numbers"]=df["Numbers"].astype(float)

Upvotes: 3

Rohit Jain
Rohit Jain

Reputation: 1

You can try reading excel with default type as string

df=pd.read_excel('file.xlsx',dtype=str)

Upvotes: 0

Jopie
Jopie

Reputation: 1

As shown in the comment by Anton vBR, using the parameter thousands='.', you will get the data read in correctly.

Upvotes: 0

Related Questions