Re: [Rd] read.csv reads more rows than indicated by wc -l

From: Ben Bolker <bbolker_at_gmail.com>
Date: Wed, 19 Dec 2012 16:43:22 +0000

G See <gsee000 <at> gmail.com> writes:

>
> When I have a csv file that is more than 6 lines long, not including
> the header, and one of the fields is blank for the last few lines, and
> there is an extra comma on of the lines with the blank field,
> read.csv() makes creates an extra line.
>
> I attached an example file; I'll also paste the contents here:
>
> A,apple
> A,orange
> A,orange
> A,orange
> A,orange
> A,,,
> A,,
>
> -----
> wc -l reports that this file has 7 lines
>
> R> system("wc -l test.csv")
> 7 test.csv
>
> But, read.csv reads 8.
>
> R> read.csv("test.csv", header=FALSE, stringsAsFactors=FALSE)
> V1 V2
> 1 A apple
> 2 A orange
> 3 A orange
> 4 A orange
> 5 A orange
> 6 A
> 7
> 8 A
>
> If I increase the number of commas at the end of the line, it
> increases the number of rows.
>
> This R command to read a 7 line csv:
>
> read.csv(header=FALSE, text="A,apple
> A,orange
> A,orange
> A,orange
> A,orange
> A,,,,,
> A,,")
>
> will produce this:
>
> V1 V2
> 1 A apple
> 2 A orange
> 3 A orange
> 4 A orange
> 5 A orange
> 6 A
> 7
> 8
> 9 A
>
> But if the file has fewer than 7 lines, it doesn't increase the number of rows.
>
> This R command to read a 6 line csv:
> read.csv(header=FALSE, text="A,apple
> A,orange
> A,orange
> A,orange
> A,,,,,
> A,,")
>
> will produce this:
>
> V1 V2 V3 V4 V5 V6
> 1 A apple NA NA NA NA
> 2 A orange NA NA NA NA
> 3 A orange NA NA NA NA
> 4 A orange NA NA NA NA
> 5 A NA NA NA NA
> 6 A NA NA NA NA
>
> Is this intended behavior?
>
> Thanks,
> Garrett See
 

 [snip]

I don't know if it's exactly *intended* or not, but I think it's more or less as [IMPLICITLY] documented. From ?read.table,

     The number of data columns is determined by looking at the first
     five lines of input (or the whole file if it has less than five
     lines), or from the length of ‘col.names’ if it is specified and
     is longer.  This could conceivably be wrong if ‘fill’ or
     ‘blank.lines.skip’ are true, so specify ‘col.names’ if necessary
     (as in the ‘Examples’).

txt <- "A,apple

 A,orange
 A,orange
 A,orange
 A,orange
 A,,,,,
 A,,"

read.csv(header=FALSE, text=txt )

What is happening here is that
(1) read.table is determining from the first five lines that there are two columns;
(2) when it gets to line six, it reads each set of two fields as a separate row

If you try

read.csv(header=FALSE, text=txt, fill=FALSE,blank.lines.skip=FALSE)

you at least get an error.

But it gets worse:     

txt2 <- "A,apple

 A,orange
 A,orange
 A,orange
 A,orange
 A,b,c,d,e,f
 A,g"

read.csv(header=FALSE, text=txt2, fill=FALSE,blank.lines.skip=FALSE)

produces bad results even though fill=FALSE and blank.lines.skip=FALSE ...

Even specifying col.names explicitly doesn't help:

read.csv(header=FALSE, text=txt2, col.names=paste0("V",1:2))

At least count.fields() does detect a problem ...

count.fields(textConnection(txt2),sep=",")

Somewhere on my wish/TO DO list is for someone to rewrite read.table for better robustness *and* efficiency ...



R-devel_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-devel Received on Wed 19 Dec 2012 - 16:46:31 GMT

This quarter's messages: by month, or sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

All messages

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Wed 19 Dec 2012 - 18:12:52 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-devel. Please read the posting guide before posting to the list.

list of date sections of archive