Replace comma and dot in Pandas

Question

I have a Column with data like 3.4500,00 EUR. Now I want to compare this with another column having float numbers like 4000.00. How do I take this string, remove the EUR and replace comma with decimal and then convert into float to compare?

Answer 1

Here is my solution:

mock data:

         amount     amount2
0   3.4500,00EUR    4000
1   3.600,00EUR     500

use apply() then convert the data type to float

data['amount'] = data['amount'].apply(lambda x: x.replace('EUR', '')).apply(lambda x: x.replace('.', '')).apply(lambda x: x.replace(',', '.')).astype('float')

result:

    amount    amount2
0   34500.0     4000
1   3600.0      500

Answer 2

You can use regular expressions to make your conditions general that would work in all cases:

# Make example dataframe for showing answer
df = pd.DataFrame({'Value':['3.4500,00 EUR', '88.782,21 DOLLAR']})

              Value
0     3.4500,00 EUR
1  88.782,21 DOLLAR

Use str.replace with regular expression:

df['Value'].str.replace('[A-Za-z]', '').str.replace(',', '.').astype(float)

0    34500.00
1    88782.21
Name: Value, dtype: float64

Explanation:

• str.replace('[A-Za-z\.]', '') removes all alphabetic characters and dots.
• str.replace(',', '.') replaces the comma for a dot
• astype(float) converts it from object (string) type to float