Convert number strings with commas in pandas DataFrame to float.

convert_number_strings_to_numbers.py

import pandas as pd
import locale
from locale import atof

df = pd.DataFrame([['1,200', '4,200'], ['7,000', '-0.03'], ['5', '0']],
                  columns=['col1', 'col2'])
#     col1   col2
# 0  1,200  4,200
# 1  7,000  -0.03
# 2      5      0

# Check dtypes
df.dtypes
# col1    object
# col2    object
# dtype: object

# Convert the entire DataFrame
locale.setlocale(locale.LC_NUMERIC, '')
df.applymap(atof)
#      col1     col2
# 0  1200.0  4200.00
# 1  7000.0    -0.03
# 2     5.0     0.00

# Check dtypes
df.dtypes
# col1    float64
# col2    float64
# dtype: object

# Convert a specific column
df['col2'] = df['col2'].map(atof)
df.dtypes
# col1     object
# col2    float64
# dtype: object

Used this to answer a Stackoverflow question, with credit. Thanks!

this function works for dataframe as a whole, trying to use same solution for a column of dataframe, returns an error stating that "series object does not have such functionality". Any solution for individual columns - some columns are text and throw error

@gjain75 you can just use .map(atof) instead of .applymap. This works for a specific column.

Thank you for comments. I have updated.

So much struggle with astype() and to_numeric(). This totally rescued me from angst and massive frustration. Thank you!!

Very helpful, thank you!
A little tip:
If using pd.read_csv from a datafile, we can set the thousands parameter to ','.