Skip to content

Instantly share code, notes, and snippets.

@Keiku
Last active January 8, 2023 20:45
Show Gist options
  • Save Keiku/b3e0038a2d1145ea82eda8444b98c02a to your computer and use it in GitHub Desktop.
Save Keiku/b3e0038a2d1145ea82eda8444b98c02a to your computer and use it in GitHub Desktop.
Convert number strings with commas in pandas DataFrame to float.
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
@gjain75
Copy link

gjain75 commented Jun 5, 2020

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

@alexander-konrad-umusic

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

@Keiku
Copy link
Author

Keiku commented Feb 1, 2022

Thank you for comments. I have updated.

@jmd10
Copy link

jmd10 commented Oct 6, 2022

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

@marvyn
Copy link

marvyn commented Oct 28, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment