CODEWITHSUNDEEP
pythonpandasnumber-formatting
manoelpqueiroz
manoelpqueiroz

Reputation: 637

Parsing numbers stored as text with comma as decimal and dot as thousands

I have a Excel file to import with pandas whose columns are stored as text. The caveat is that this text is a number under French/Latin convention for decimals (radix) and thousands, such that by letting pandas infer its type, it brings a text column just as it is presented in the original file:

           NUMBER
0   23.639.826,11
1       92.275,00
2    1.917.000,00
8        2.409,02
9       13.501,00
Name: NUMBER, dtype: object

How can I make pandas convert this text to the correct float format without having to do the conversion on the Excel file itself or applying string methods to replace the commas and dots?

           NUMBER
0     23639826.11
1        92275.00
2      1917000.00
8         2409.02
9        13501.00

I have tried using the thousands='.' parameter when reading the file with pd.read_excel as suggested by the docs to no avail and using pd.to_numeric outputs a ValueError as it is unable to parse the string.

Upvotes: 1

Views: 936

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

Try df=pd.read_excel(filename, decimal=',', thousands='.')

Upvotes: 2

Related Questions