read.csv row.names

Question

I'm trying to read a column oriented csv file into R as a data frame.

the first line of the file is like so:

sDATE, sTIME,iGPS_ALT, ...

and then each additional line is a measurement:

4/10/2011,2:15,78, ...

when I try to read this into R, via

d = read.csv('filename')

I get a duplicate row.names error since R thinks that the first column of the data is the row names, and since all of the measurements were taken on the same day, the values in the first column do not change.

If I put in row.names = NULL into the read.csv call, I get an extraneous column d$row.names which corresponds to the sDATE column, and everything is "shifted" one column down, so d$sDATE would have 2:15 in it, not 4/10/2011 as needed.

If I open my csv in excel, do nothing and then save it, everything's cool. I have to process hundreds of these, so manually saving in excel is not something I want. If there's something programmatically I can do to preprocess these csv's in python or otherwise, that would be great.

Solution 1

read.csv only assumes there are any row names if there are less values in the header than in the other rows. So somehow you are either missing a column name or have an extra column you don't want.

Solution 2

You probably DO have an extra column.
But it probably arises from a stray formatted cell (or column of cells) that is actually empty, to the right of your data in your original spreadsheet.
Here is the key: Excel will save empty fields in the CSV file for any empty cells that are formatted in your sheet. Here is why you probably have this problem: Because when you open the CSV file with Excel and re-save it the problem with R goes away.
What is happening: when you pull a CSV file back into Excel, it will subsequently ignore empty cells to the right or below your data (since CSV files have no formatting).

Conclusion: be careful saving formatted spreadsheets as CSV files for use with statistical packages. Stray formatting means stray fields in the CSV.