Python: read_csv into dataframe - can't convert object into float

Question

The csv file I am feeding into read_csv is a couple columns with percentage changes but it has some hidden characters. From repr(data2):

I tried the following:

data2 = pd.read_csv('C:/Users/nnayyar/Documents/MonteCarlo2.csv', "\n", delimiter = ",", dtype = float)

And got the following error:

ValueError: invalid literal for float(): 7.05%

I tried a few things:

float(data2.replace('/n',''))
map(float, data2.strip().split('\r\n'))

But received various errors respectively TypeError: float() argument must be a string or a number AttributeError: 'DataFrame' object has no attribute 'strip'

Any help to get the CSV object type into float type would be helpful! THanks!!

You'll have to either pass a converter func or convert by stripping the % sign as a post-processing job — EdChum
– EdChum, Commented Jan 28, 2016 at 23:37
if i try to use .strip() I get the following error: AttributeError: 'DataFrame' object has no attribute 'strip'; Also tried data2.map(lambda x: x.lstrip('%')) and got a similar error. — user5854307
– user5854307, Commented Jan 29, 2016 at 2:41

EdChum · Accepted Answer · 2016-01-29 08:54:53Z

1

If your entire csv has percentage signs then the following will work:

In [203]:
import pandas as pd
import io
t="""0   1   2  3
1.5%  2.5%   6.5%   0.5%"""
# load some dummy data
df = pd.read_csv(io.StringIO(t), delim_whitespace=True)
df

Out[203]:
      0     1     2     3
0  1.5%  2.5%  6.5%  0.5%

In [205]:
# apply a lambda that replaces the % signs and cast to float    
df.apply(lambda x: x.str.replace('%','')).astype(float)

Out[205]:
     0    1    2    3
0  1.5  2.5  6.5  0.5

So this applys a lambda to each column that calls the vectorised str.replace to remove the % sign, we can then convert the type to float using astype

So in your case the following should work:

data2 = pd.read_csv('C:/Users/nnayyar/Documents/MonteCarlo2.csv', "\n")
data2 = data2.apply(lambda x: x.str.replace('%', '').astype(float))

answered Jan 29, 2016 at 8:54

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

Collectives™ on Stack Overflow

Python: read_csv into dataframe - can't convert object into float

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related