After convert "object" to "float" all values are NaN - how to solve this?

user_12692510 0
Viewed 603

I have the following column in my dataset. The Dtype is "object". I want to convert it into a float but could need some help.

Value
$166.400,00
$135.200,00
$124.800,00
$124.800,00
$118.809,60

First I had separated the $.

def split(x):
    return x.split("$")[1]
df["Value_conv"] = df["Value"].apply(split)


Value_conv
166.400,00
135.200,00
124.800,00
124.800,00
118.809,60

After this I tried pd.to_numeric

df['Value_conv'] = pd.to_numeric((df['Value_conv']), errors='coerce')

The result is that Value_conv now is a float64. But: All values are NAN.

Value_conv
NaN
NaN
NaN
NaN
NaN

What do I have to do to convert this column into a float but with the values not NaN? After doing some Google I believe it has something to do with the separators "." and "," but I just can not figure out a way to solve it. Thanks for your help!

pandas nan python
1 Answers

It doesn't work because there is a comma. If you want the values before the comma and after the dollar sign, try this:

df["Value_conv"] = df["Value"].str.split('$').str[1].str.split(',').str[0].astype(float)

The reason it gives None is because errors='coerce' gives None if it's not a integer/float like.

As mentioned in the documentation:

It says:

If ‘coerce’, then invalid parsing will be set as NaN.

user_87083640
Related