Converting currency to numeric value-CodePudding

Since they are in object format, I am trying to create a new variable by converting prices in my df to a numeric value.

I tried to remove the ',' and '$' from the numbers in the column and then convert them to a different type with pd.to_numeric

df_l['price_MXN2'] = df_l['price_MXN'].str.replace(',','')
    
df_l['price_MXN2'] = df_l['price_MXN'].str.replace('$','')
    
df_l['price_MXN2'] = pd.to_numeric(df_l['price_MXN2'])

I get "ValueError: Unable to parse string at position 0"

CodePudding user response：

It looks like you're trying to convert a currency string to a numeric value in a Pandas DataFrame. The error message "ValueError: Unable to parse string at position 0" indicates that the to_numeric() method is unable to parse the string at the first position (index 0) in the price_MXN2 column.

One possible reason for this error is that the price_MXN2 column contains invalid characters or non-numeric values. You can check the values in the price_MXN2 column using the df_l['price_MXN2'].unique() method, which will return an array of all unique values in the column.

If the price_MXN2 column contains invalid characters or non-numeric values, you can use the pd.to_numeric() method with the errors parameter set to 'coerce' to convert the values to numeric while ignoring or replacing any invalid characters. Here's an example:

 # Convert the price_MXN2 column to numeric, ignoring any invalid values
df_l['price_MXN2'] = pd.to_numeric(df_l['price_MXN2'], errors='coerce')

This will convert the price_MXN2 column to numeric, and any invalid values will be replaced with NaN. You can then use the df_l.dropna() method to remove any rows with missing values from the DataFrame.