2

I am trying to convert a df to all numeric values but getting the following error.

ValueError: Unable to parse string "15,181.80" at position 0

Here is my current code:

data = pd.read_csv('pub?gid=1704010735&single=true&output=csv',
                 usecols=[0,1,2], 
                 header=0,
                 encoding="utf-8-sig",
                 index_col='Date') 

data.apply(pd.to_numeric)

print("we have a total of:", len(data), " samples")

data.head()

And df before I am trying to convert:

Clicks  Impressions
Date        
01/03/2020  15,181.80   1.22%
02/03/2020  12,270.76   0.56%
03/03/2020  39,420.79   0.80%
04/03/2020  22,223.97   0.79%
05/03/2020  17,084.45   0.88%

I think the issue is that it handle the special characters E.G. "," - is this correct? What is the best recommendation to help convert the DF into all numeric values?

Thanks!

JackNesbitt
  • 125
  • 1
  • 7

1 Answers1

7

Deleting all , in the numbers of your dataframe will fixe your problem.

This is the code I used:

import pandas as pd

df = pd.DataFrame({'value':['10,000.23','20,000.30','10,000.10']})
   
df['value'] = df['value'].str.replace(',', '').astype(float)

df.apply(pd.to_numeric)

OUTPUT:

      value
0  10000.23
1  20000.30
2  10000.10

EDIT:

You can use also:

df= df.value.str.replace(',', '').astype(float)

The value is the column that you want to convert

Youness Saadna
  • 792
  • 2
  • 8
  • 25
  • 1
    Thanks for your help so far. I have amended my code to: data = data.str.replace(',', '').astype(float) data.apply(pd.to_numeric) And getting a AttributeError: 'DataFrame' object has no attribute 'str' error – JackNesbitt Aug 15 '20 at 11:10