Cannot Convert String To Float In Pandas (valueerror)

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$

Solution 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(',', '')))

Solution 2:

Another solution with list comprehension, if need apply stringfunctions 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 indf], axis=1)

#if need convert column Purchase count to intdf['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