Skip to content

Reading in csv with footer

8 messages · Noah Silverman, Steve Lianoglou, Henrique Dallazuanna +3 more

Original
Noah Silverman
# 
Hi,

I have a CSV file that is formatted well, except that the last line is a "summary" not is CSV format.

Toy example:

label_1, label_2, label_3
1,2,3
3,2,4
2,3,4
Total Rows: 3


When I try to import this into R with:  d <- read.table("foo.csv", header=T, sep=",")
It fails to import properly because of the last line.

Currently, I have a shell script that strips the last line from the file, then it imports to R cleanly.  I don't like this extra layer of processing.

Is there a way to import something like this cleanly in R.

Thanks!

--
Noah
Steve Lianoglou
# 
Hi,
On Sun, Feb 12, 2012 at 7:05 PM, Noah Silverman <noahsilverman at ucla.edu> wrote:
This is arguably the file's problem, so I'm not sure how many "clean"
solutions you will find, but one thing you can do is perhaps count the
number of lines in the file, then set the `nrows` argument in your
call to read.table to be 1 less than that.

How to count the lines, though? Assuming you're on *nix (or have
cygwin), you can do something like:

N <- system("wc -l /path/to/file.csv")

(you'll have to do some parsing on N)

You could also first call `readLines` and find the length of the
result, but this would require you to read the file twice, so ... pick
your poison.

Too bad the person authoring the file doesn't prefix those lines with
some comment character ...

-steve
William Dunlap
# 
That prints nicely, but the first column in the
result got turned into a factor:
  > d <- head(read.csv(textConnection(Lines)), -1)
  > str(d)
  'data.frame':   3 obs. of  3 variables:
   $ label_1: Factor w/ 4 levels "1","2","3","Total Rows: 3": 1 3 2
   $ label_2: int  2 2 3
   $ label_3: int  3 4 4
(Remove the call to head and you will see why.)

You could use head(,-1) on the output of readLines so
read.csv never sees the last value:
  > d2 <- read.csv(textConnection(head(readLines(textConnection(Lines)), -1)))
  > str(d2)
  'data.frame':   3 obs. of  3 variables:
   $ label_1: int  1 3 2
   $ label_2: int  2 2 3
   $ label_3: int  3 4 4
or you could use a pipe connection that called the shell script.

Bill Dunlap
Spotfire, TIBCO Software
wdunlap tibco.com
Rolf Turner
#
On 13/02/12 13:05, Noah Silverman wrote:
How clean is clean?

You need to count the number of lines in the file, and then set the nrows
argument of read.csv() to be two less.  (*Two* r.t. one, because of the 
header.)

Counting the lines --- three possibilities that I can see:

     (1) nlines() from the "parser" package
     (2) countLines() from the "R.utils" package
     (3) brute force:
         x <- readLines(<filename>)
         n <- length(x)

Having determined n, do:

         y <- read.csv(<filename>,nrows=n-2)

     cheers,

         Rolf Turner
chuck.01
# 
I believe this should work

d <- read.table("foo.csv", header=T, sep=",", comment="T")

although its spitting back a warning... this used to work for me.  




Noah Silverman wrote
--
View this message in context: http://r.789695.n4.nabble.com/Reading-in-csv-with-footer-tp4382441p4382980.html
Sent from the R help mailing list archive at Nabble.com.