3
$\begingroup$

I have a pandas data frame where I want to replace all the values that are not numeric in one column for ""

The error I'm getting in my code is the following:

ValueError: could not convert string to float: '690,276.00'

Since I'm trying to convert all values into float so I can do internal operations with them.

Part of my cleaning data frame code looks like this:

    # Cleaning:              
df_clean = df_read[~(df_read['Ratio of Similarity (Gray)'] <= .2)]
print(df_clean, 'clean 1: Eliminate Ratio of similarity less than 0.2')
df_clean_2 = df_clean.dropna(subset=['buybox_price'])  
print(df_clean_2, 'clean 2: Eliminate Nan Buybox Prices')
df_clean_2 = df_clean_2.replace(",", "").replace('', '').astype({'product_ranking':'float64'})
df_clean_3 = df_clean_2[~(df_clean_2['product_ranking'] >= 5000000)]
print(df_clean_3, 'clean 3: Eliminate Product Ranking + than 5.000.000')
df_clean_4 = df_clean_3[~(df_clean_3['buybox_price'] <= 6)]
print(df_clean_4, 'clean 4: Eliminate Buybox Price less than 6$')
# Save Cleaned File
path_file = os.path.join(BASE_DIR, 'csv/amazon_product_comparator.csv')
df_hc = df_clean_4.to_csv(path_file) 

The error can be found in the line:

df_clean_3 = df_clean_2[~(df_clean_2['product_ranking'] >= 5000000)]
    print(df_clean_3, 'clean 3: Eliminate Product Ranking + than 5.000.000')
$\endgroup$

1 Answer 1

1
$\begingroup$

EDITED to include a working example.

I have made an example with string type numeric data. I have also include a poorly formatted string number 1,002,*8320.

The code below will convert the numbers in the format of digits and digits with commas to floats, and convert all other strings to NaN. I have used a regular expression to replace commas with blanks.

The NaN must be used over "" since the comparison operator will not work on strings

import pandas as pd
import numpy as np
import re

# example data frame
df2 = pd.DataFrame([['1', '2000000000'],
                    ['2', '1,002,*8320'],
                    ['3', '1,000,000']],
                   columns = ['idx','product_ranking'])
# Remove Commas
df2['product_ranking'] = df2['product_ranking'].map(lambda x: re.sub('[,]*' , '', x))

# Remove Convert strings that are numeric into floats.
df2['product_ranking'] = df2['product_ranking'].map(lambda x: float(x) if x.isnumeric() else np.nan)

#Comparison is working
df2['product_ranking'] > 100000000
$\endgroup$
4
  • $\begingroup$ Sure! there you have some code, there is the line that gives the error $\endgroup$
    – The Dan
    Commented Apr 14, 2020 at 18:06
  • $\begingroup$ Some of them are in the correct format, the errors I have found come form commas and dots $\endgroup$
    – The Dan
    Commented Apr 14, 2020 at 18:13
  • $\begingroup$ There shouldn't be other types of variables. Actually those values are supposed to be only integers $\endgroup$
    – The Dan
    Commented Apr 14, 2020 at 18:25
  • $\begingroup$ See if this might do the trick. import re; df_clean2['product_ranking'].map(lambda x: float(re.sub('[,\s]*' , '', x))) $\endgroup$
    – nwaldo
    Commented Apr 14, 2020 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.