reading csv file to pandas dataframe as float

Question

I have a .csv file with strings in the top row and first column, with the rest of the data as floating point numbers. I want to read it into a dataframe with the first row and column as column names and index respectively, and all the floating values as float64.

If I use df = pd.read_csv(filename,index_col=0) all the numeric values are left as strings.

If I use df = pd.read_csv(filename, index_col=0, dtype=np.float64) I get an exception: ValueError: could not convert string to float as it attempts to parse the first column as float.

There are a large number of columns, and i do not have the column names, so I don't want to identify each column for parsing as float; I want to parse every column except the first one.

What is the format of your numbers? I'm pretty sure pandas will infer the dtypes without any arguments. Have you tried that? What was the result? — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Jul 11, 2017 at 7:00
df.convert_objects(convert_numeric=True) You can convert the values after you have the dataFrame, — omri_saadon
– omri_saadon, Commented Jul 11, 2017 at 7:02
I have found a single row in the .csv which has non-numeric data in it, so the whole column is parsed as a string. Thank you juanpa.arrivillaga — doctorer
– doctorer, Commented Jul 11, 2017 at 7:09

Ragsparkz · Accepted Answer · 2017-07-11 08:50:11Z

4

Get the list of all column names, remove the first one. cast other columns.

cols = df.columns
cols.remove('fistcolumn')
for col in cols:
    df[col] = df[col].astype(float)

answered Jul 11, 2017 at 8:50

Ragsparkz

414 bronze badges

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

Comments

doctorer · Accepted Answer · 2017-07-12 05:54:20Z

4

The original code was correct

df = pd.read_csv(filename,index_col=0)

but the .csv file had been constructed incorrectly.

As @juanpa.arrivillaga pointed out, pandas will infer the dtypes without any arguments, provided all the data in a column is of the same dtype. The columns were being interpreted as strings because although most of the data was numeric, one row contained non-numeric data (actually dates). Removing this row from the .csv solved the problem.

answered Jul 12, 2017 at 5:54

doctorer

1,7828 gold badges28 silver badges52 bronze badges

Collectives™ on Stack Overflow

reading csv file to pandas dataframe as float

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related