16

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.

bartektartanus
  • 15,284
  • 6
  • 74
  • 102
nimish
  • 4,755
  • 3
  • 24
  • 34
  • What about setting `row.names = 1:n`, where `n` is the number of rows in the file. – nullglob Jul 06 '11 at 11:41
  • Results in a `Error in read.table(file = file, header = header, sep = sep, quote = quote, : invalid 'row.names' specification` – nimish Jul 06 '11 at 11:59
  • 2
    Paste the first few lines (and columns) of the csv file, please. – Roman Luštrik Jul 06 '11 at 12:21
  • 8
    `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. – wkmor1 Jul 06 '11 at 12:51
  • @wkmor1: you should post this as an answer, then people can see that the question has been addressed. – IRTFM Jul 06 '11 at 13:38
  • Cool, that solved it. The first three lines had length 246, while the rest had length 247. Don't know why excel shaved those off. – nimish Jul 06 '11 at 13:49

4 Answers4

30

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.

wkmor1
  • 7,226
  • 3
  • 31
  • 23
5

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.

pleiby
  • 63
  • 1
  • 3
0

One possible reason can be an extra comma at the end of lines after the header line. Excel silently ignores them and removes while saving.

At least it was the case for me

svural
  • 961
  • 1
  • 9
  • 17
0

I faced the same issue. It got resolved by adding header=TRUE like below

tempdata <- read.csv("C:\\File.csv",header=TRUE)

The first column which was the date column got aligned properly.

Ram
  • 3,092
  • 10
  • 40
  • 56
Anant Gupta
  • 1,090
  • 11
  • 11