Python Pandas Changing Column String Values to Float values in a new column

Question

I have a dataframe which contains a column of strings of float values (negative and positive float values) in the format .000 (3dp). This number is represented as a string in Column1 and I would like to add a column2 to the DataFrame as a float and convert the string representation of the float value to a float value preserving the 3 dp. I have had problems trying to do this and have error message of "ValueError: could not convert string to float:" Grateful for any help

Code

dataframe4['column2'] = ''
dataframe4['column2']=dataframe4['column1'].astype('float64')

#Round column1, column2 float columns to 3 decimal places

dataframe4.round({'column1': 3, 'column2': 3})

if column 1 contains string it would give error when round it. — tawab_shakeel
– tawab_shakeel, Commented Jul 14, 2019 at 18:17
Thanks Tawab. I was wondering how to do type conversion of string to float in pandas — daveb
– daveb, Commented Jul 14, 2019 at 18:34

GatienC · Accepted Answer · 2019-07-14 18:44:25Z

1

I don't know if I totally understood your question but you can try

dataframe4['column2'] = dataframe4['column1'].apply(lambda x : float(x))

Edit : If there are some numbers with commas, you can try:

dataframe4['column2'] = dataframe4['column1'].apply(lambda x : float(x.replace(",","")))

edited Jul 14, 2019 at 18:44

answered Jul 14, 2019 at 18:19

GatienC

135 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

daveb Over a year ago

Hi GatienC. Unfortunately I tried the above and got an error message when trying to convert string to float. ValueError: could not convert string to float: '9,826.000'

patrick Over a year ago

@daveb can you confirm that you have a comma in these numbers? That would break your conversion

daveb Over a year ago

Hi Patrick, Many thanks, unfortunately there is commas in the string representation of the float number. Would it be possible to add them back in if I remove them before the conversion?

patrick Over a year ago

yes that should def work. Let me look at it real quick

GatienC Over a year ago

edited my answer with a .replace , it should work (didn't see ur precedent comment, sorry)

patrick · Accepted Answer · 2019-07-14 21:26:48Z

0

The problem appears to be that you have commas in your floats, e.g. '9,826.000'

You can fix like below

import re
re.sub(r",", "", "1,1000.20")
# returns '11000.20' and the below works
float(re.sub(r",", "", "1,1000.20"))
# you can e.g. use apply to apply to all your numbers in the DataFrame
df["new_col"] = df["old_col"].apply(lambda x: float(re.sub(r",", "", x)))

To still show the resulting float with commas afterwards in pandas, you can change the display setting for float as described here

IDK how you want to output these, but e.g. in the to_excel function, you can specify a float format, cf here or re-format the column before output, similar to the above. See this answer for some ideas.

edited Jul 14, 2019 at 21:26

answered Jul 14, 2019 at 18:49

patrick

4,9726 gold badges52 silver badges66 bronze badges

Collectives™ on Stack Overflow

Python Pandas Changing Column String Values to Float values in a new column

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related