Problems with converting column from object to float

Question

After scraping the website, I have a column Price.

5        € 9.500,00
7        € 2.950,00
8        € 5.750,00
11      € 64.718,00
14       € 4.800,00
           ...     
3050     € 8.099,00
3051    € 12.500,00
3052    € 16.900,00
3054       € 699,00
3059     € 6.500,00
dtype: object

I have tried to drop Euro sign and convert it to float with a standard str.replace.astype(float) function, but it yielded mistakes.

I have found another possibility here with:

locale.setlocale(locale.LC_ALL,'')
df3['Price']=df3.Price.map(lambda x: locale.atof(x.strip('€')))

However, I have now a problem that dots now and prices are now incorrectly reflected for my further analysis:

5           9.500
7           2.950
8           5.750
11         64.718
14          4.800
          ...    
3050        8.099
3051       12.500
3052       16.900
3054    69900.000
3059        6.500
dtype: float64

orlp · Accepted Answer · 2020-05-27 17:03:52Z

1

Check if the way you load the data supports a thousands and decimal option, for example Pandas read_csv does. You could also set the appropriate locale, like de_DE for example, but I personally don't like messing with locales as they're global state.

Personally I would solve this with a simple string replace:

df3['Price'] = df3.Price.map(lambda x: float(x.strip('€')
                                              .replace('.', '')
                                              .replace(',', '.')))

answered May 27, 2020 at 17:03

orlp

119k39 gold badges226 silver badges324 bronze badges

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

1 Comment

Iva Over a year ago

Thank you very much.

NYC Coder · Accepted Answer · 2020-05-27 17:13:39Z

1

You can try this:

df['amount'] = df['amount'].str.replace(r'€|\.', '').str.replace(',', '.')
df['amount'] = df['amount'].astype(float)
print(df)

    amount
0   9500.0
1   2950.0
2   5750.0
3  64718.0
4   4800.0
5   8099.0
6  12500.0
7  16900.0
8    699.0
9   6500.0

answered May 27, 2020 at 17:13

NYC Coder

7,6443 gold badges14 silver badges25 bronze badges

Comments

Hunter · Accepted Answer · 2020-05-27 17:01:28Z

-1

My naive answer is that you could multiply every value in your column by 1000 and solve your number issue.

So,

def fun(x):
   return x * 1000
df3['Price']=df3.Price.map(fun)

answered May 27, 2020 at 17:01

Hunter

1011 silver badge8 bronze badges

2 Comments

Hunter Over a year ago

I must have misunderstood what your problem was.

orlp Over a year ago

I downvoted your answer because it simply does not work in a disastrous manner. Check what will happen to € 699,00.

Collectives™ on Stack Overflow

Problems with converting column from object to float

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related