Cannot convert string to float in pandas (ValueError)

Question

I have a dataframe created form a JSON output that looks like this:

        Total Revenue    Average Revenue    Purchase count    Rate
Date    
Monday  1,304.40 CA$     20.07 CA$          2,345             1.54 %

The value stored are received as string from the JSON. I am trying to:

1) Remove all characters in the entry (ex: CA$ or %) 2) convert rate and revenue columns to float 3) Convert count columns as int

I tried to do the following:

df[column] = (df[column].str.split()).apply(lambda x: float(x[0]))

It works fine except when I have a value with a coma (ex: 1,465 won't work whereas 143 would).

I tried to use several function to replace the "," by "", etc. Nothing worked so far. I always receive the following error:

ValueError: could not convert string to float: '1,304.40'

Answer 1

These strings have commas as thousands separators so you will have to remove them before the call to float:

df[column] = (df[column].str.split()).apply(lambda x: float(x[0].replace(',', '')))

This can be simplified a bit by moving split inside the lambda:

df[column] = df[column].apply(lambda x: float(x.split()[0].replace(',', '')))

Answer 2

Another solution with list comprehension, if need apply string functions working only with Series (columns of DataFrame) like str.split and str.replace:

df = pd.concat([df[col].str.split()
                       .str[0]
                       .str.replace(',','').astype(float) for col in df], axis=1)

#if need convert column Purchase count to int
df['Purchase count'] = df['Purchase count'].astype(int)
print (df)
         Total Revenue  Average Revenue  Purchase count  Rate
Date                                                        
Monday         1304.4            20.07            2345  1.54