How to convert numeric strings with period separators to float?

Question

I am trying to convert the values in a Pandas DataFrame from string to float but am running into an error.

The column looks like this:

PIB (IBGE/2005)
---------------
 71.638.000
114.250.000
 44.373.000
462.258.000
186.812.000

Where the . are the digit group separators, so 71.638.000 should become the float 71638000.

But I am getting the error:

ValueError: could not convert string to float: '71.638.000'

Here is an image of my full DataFrame:

Dataframe screenshot How can I convert this column from string to float?

It looks like that the character . in your dataset is a thousand separator, which confuses your parsing library (probably set by default to understand . as the English decimal point). I'd recommend cleaning your data to remove those . — Pac0
– Pac0, Commented May 16, 2020 at 22:21
@Pac0 maybe the problem is really the thousand separator, I'll check that out. thank you — Geraldo Britto
– Geraldo Britto, Commented May 16, 2020 at 22:30
use pd.read_csv(yourfile, thousands='.') or pd.to_numeric(df[column]) — Umar.H
– Umar.H, Commented May 16, 2020 at 22:38
Does this answer your question? Pandas: convert dtype 'object' to int — Umar.H
– Umar.H, Commented May 16, 2020 at 22:39

Chris Greening · Accepted Answer · 2020-05-16 23:00:56Z

1

The values in column 'PIB (IBGE/2005)' appear to have a period as thousands separators. You'll have to remove those before converting the values to floats. You could try something along these lines to prepare the column for conversion to float:

df1 = df1['PIB (IBGE/2005)'].apply(lambda x: x.replace('.', ''))

EDIT: As suggested in the comments below as a more proper solution to avoid apply

df1 = df1['PIB (IBGE/2005)'].str.replace('.', '')

edited May 16, 2020 at 23:00

answered May 16, 2020 at 22:29

Chris Greening

5505 silver badges14 bronze badges

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

3 Comments

Arne Over a year ago

Or use one of pandas' string methods for that: df1 = df1['PIB (IBGE/2005)'].str.replace('.', '')

Umar.H Over a year ago

don't use apply when you have methods can you use in its place.

Geraldo Britto Over a year ago

It worked, the problem was really the period as a thousands separator ... thank you very much to everyone who contributed

Hirusha Fernando · Accepted Answer · 2020-05-17 03:33:43Z

0

Replace '.' with '' and convert string into float

S = "123.567.000"
D = float(S.replace('.',''))
print(D)

answered May 17, 2020 at 3:33

Hirusha Fernando

1,3852 gold badges14 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to convert numeric strings with period separators to float?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related