How to convert multiple columns with european numbers (comma as decimal separator) to float?

Question

I have multiple columns that contain numbers in european format, e.g.

1.630,78

They have different chars in front or at the end (€, %) so I can't use pandas converter function.

pd.read_csv("file.csv", decimal=',', separator={"col1": float, "col": float}

won't work because I have to remove the signs first which I can only do after reading in the whole file.

Search and replace dots and commas in pandas dataframe

did not work, I get a

ValueError: could not convert string to float: ''

but every row has an entry

How can I change those strings in specific columns then to floats?

Answer 1

Read the colums as strings and then use translate:

tt = str.maketrans(',', '.', '.€%')
df.col1 = df.col1.str.translate(tt).astype(float)

PS: you may need to adopt the third argument with the characters to remove as needed.