[Python] Convert to float, pandas string column with mixed thousand and decimal separators

I have a pandas DataFrame with a column containing strings representing numbers. These strings have mixed formats. Some times numbers use comma as a decimal separator and sometimes a dot. When a dot is used as a decimal separator, that number can contain comma as a thousand separator.

For example:

import pandas as pd
data = {
'NumberString': [
'1,234.56',
'789,012.34',
'45,678',
'9,876.54',
'3,210.98',
'1,000,000.01',
'123.45',
'42,000',
'NaN'
]
}
df = pd.DataFrame(data)


I want to convert this column to numeric without losing some of the data due to inconsistent format (commas vs dots). However, using pd.to_numeric with parameter errors='coerce' will drop down some of the number Python

Is there a way to format all the strings to numbers without loosing them due to format?

What I have tried so far:

>>> df['Number'] = pd.to_numeric(df['NumberString'].str.replace(',','.'), errors='coerce')


NumberString Number
1,234.56 NaN
789,012.34 NaN
45,678 45.678
9,876.54 NaN
3,210.98 NaN
1,000,000.01 NaN
123.45 123.450
42,000 42.000
NaN NaN


Desired output:

NumberString Number
1,234.56 1234.56
789,012.34 789012.34
45,678 45.678
9,876.54 9876.54
3,210.98 3210.98
1,000,000.01 1000000.01
123.45 123.450
42,000 42.000
NaN NaN

Continue reading...