[R] Speeding reading of large file

David L Carlson dcarlson at tamu.edu
Wed Nov 28 21:14:54 CET 2012


Once you've read the data with readLines()

head(raw) gets you the first 6 lines or
head(nchar(raw)) the lengths of those lines

Once you know which row has the headers (here it is 2), just use

names(dta) <- read.table(text=raw[2], stringsAsFactors=FALSE)

to add column names to the data frame.

-------
David

> -----Original Message-----
> From: Fisher Dennis [mailto:fisher at plessthan.com]
> Sent: Wednesday, November 28, 2012 1:42 PM
> To: dcarlson at tamu.edu
> Cc: r-help at r-project.org
> Subject: Re: [R] Speeding reading of large file
> 
> An interesting approach -- I lose the column names (which I need) but I
> could get them with something cute such as:
> 	1.  read the first few lines only with readLines(FILENAME, n=10)
> 	2.  use your approach to read.table -- this will grab the column
> names
> 	3.  replace the headers in the full version with the correct
> column names
> 
> Dennis Fisher MD
> P < (The "P Less Than" Company)
> Phone: 1-866-PLessThan (1-866-753-7784)
> Fax: 1-866-PLessThan (1-866-753-7784)
> www.PLessThan.com
> 
> On Nov 28, 2012, at 11:32 AM, David L Carlson wrote:
> 
> > Using your first approach, this should be faster
> >
> > raw <- readLines(con=filename)
> > dta <- read.table(text=raw[!grepl("[A:DF:Z]" ,raw)], header=FALSE)
> >
> > ----------------------------------------------
> > David L Carlson
> > Associate Professor of Anthropology
> > Texas A&M University
> > College Station, TX 77843-4352
> >
> >> -----Original Message-----
> >> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-
> >> project.org] On Behalf Of Fisher Dennis
> >> Sent: Wednesday, November 28, 2012 11:43 AM
> >> To: r-help at r-project.org
> >> Subject: [R] Speeding reading of large file
> >>
> >> R 2.15.1
> >> OS X and Windows
> >>
> >> Colleagues,
> >>
> >> I have a file that looks that this:
> >> TABLE NO.  1
> >> PTID        TIME        AMT         FORM        PERIOD      IPRED
> >> CWRES       EVID        CP          PRED        RES         WRES
> >>  2.0010E+03  3.9375E-01  5.0000E+03  2.0000E+00  0.0000E+00
> >> 0.0000E+00  0.0000E+00  1.0000E+00  0.0000E+00  0.0000E+00
> 0.0000E+00
> >> 0.0000E+00
> >>  2.0010E+03  8.9583E-01  5.0000E+03  2.0000E+00  0.0000E+00
> >> 3.3389E+00  0.0000E+00  1.0000E+00  0.0000E+00  3.5321E+00
> 0.0000E+00
> >> 0.0000E+00
> >>  2.0010E+03  1.4583E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 5.8164E+00  0.0000E+00  1.0000E+00  0.0000E+00  5.9300E+00
> 0.0000E+00
> >> 0.0000E+00
> >>  2.0010E+03  1.9167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 8.3633E+00  0.0000E+00  1.0000E+00  0.0000E+00  8.7011E+00
> 0.0000E+00
> >> 0.0000E+00
> >>  2.0010E+03  2.4167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.0092E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.0324E+01
> 0.0000E+00
> >> 0.0000E+00
> >>  2.0010E+03  2.9375E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.1490E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.1688E+01
> 0.0000E+00
> >> 0.0000E+00
> >>  2.0010E+03  3.4167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.2940E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.3236E+01
> 0.0000E+00
> >> 0.0000E+00
> >>  2.0010E+03  4.4583E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.1267E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.1324E+01
> 0.0000E+00
> >> 0.0000E+00
> >>
> >> The file is reasonably large (> 10^6 lines) and the two line header
> is
> >> repeated periodically in the file.
> >> I need to read this file in as a data frame.  Note that the number
> of
> >> columns, the column headers, and the number of replicates of the
> >> headers are not known in advance.
> >>
> >> I have tried two approaches to this:
> >> 	First Approach:
> >> 		1.  readLines(FILENAME) to read in the file
> >> 		2.  use grep to find the repeat headers; strip out the
> >> repeat headers
> >> 		3.  write() the object to tempfile, read in that temporary
> >> file using read.table(tempfile, header=TRUE, skip=1) [an alternative
> is
> >> to use textConnection but that does not appear to speed things]
> >>
> >> 	Second Approach:
> >> 		1.  TEMP	<- read.table(FILENAME, header=TRUE, skip=1,
> >> fill=TRUE, as.is=TRUE)
> >> 		2.  get rid of the errant entries with:
> >> 			TEMP[!is.na(as.numeric(TEMP[,1])),]
> >> 		3.  reading of the character entries forced all columns to
> >> character mode.  Therefore, I convert each column to numeric:
> >> 			for (COL in 1:ncol(TEMP)) TEMP[,COL] <-
> >> as.numeric(TEMP[,COL])
> >> The second approach is ~ 20% faster than the first.  With the second
> >> approach, the conversion to numeric occupies 50% of the elapsed
> time.
> >>
> >> Is there some approach that would be much faster?  For example,
> would a
> >> vectorized approach to conversion to numeric improve throughput?
> Or,
> >> is there some means to ensure that all data are read as numeric (I
> >> tried to use colClasses but that triggered an error when the text
> >> string was encountered).
> >>
> >> ############################
> >> A dput version of the data is:
> >> c("TABLE NO.  1", " PTID        TIME        AMT         FORM
> >> PERIOD      IPRED       CWRES       EVID        CP          PRED
> >> RES         WRES",
> >> "  2.0010E+03  3.9375E-01  5.0000E+03  2.0000E+00  0.0000E+00
> >> 0.0000E+00  0.0000E+00  1.0000E+00  0.0000E+00  0.0000E+00
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  8.9583E-01  5.0000E+03  2.0000E+00  0.0000E+00
> >> 3.3389E+00  0.0000E+00  1.0000E+00  0.0000E+00  3.5321E+00
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  1.4583E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 5.8164E+00  0.0000E+00  1.0000E+00  0.0000E+00  5.9300E+00
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  1.9167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 8.3633E+00  0.0000E+00  1.0000E+00  0.0000E+00  8.7011E+00
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  2.4167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.0092E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.0324E+01
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  2.9375E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.1490E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.1688E+01
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  3.4167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.2940E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.3236E+01
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  4.4583E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.1267E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.1324E+01
> 0.0000E+00
> >> 0.0000E+00"
> >> )
> >>
> >> This can be assembled into a large dataset and written to a file
> named
> >> FILENAME with the following code:
> >> cat(c("TABLE NO.  1", " PTID        TIME        AMT         FORM
> >> PERIOD      IPRED       CWRES       EVID        CP          PRED
> >> RES         WRES",
> >> "  2.0010E+03  3.9375E-01  5.0000E+03  2.0000E+00  0.0000E+00
> >> 0.0000E+00  0.0000E+00  1.0000E+00  0.0000E+00  0.0000E+00
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  8.9583E-01  5.0000E+03  2.0000E+00  0.0000E+00
> >> 3.3389E+00  0.0000E+00  1.0000E+00  0.0000E+00  3.5321E+00
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  1.4583E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 5.8164E+00  0.0000E+00  1.0000E+00  0.0000E+00  5.9300E+00
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  1.9167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 8.3633E+00  0.0000E+00  1.0000E+00  0.0000E+00  8.7011E+00
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  2.4167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.0092E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.0324E+01
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  2.9375E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.1490E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.1688E+01
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  3.4167E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.2940E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.3236E+01
> 0.0000E+00
> >> 0.0000E+00",
> >> "  2.0010E+03  4.4583E+00  5.0000E+03  2.0000E+00  0.0000E+00
> >> 1.1267E+01  0.0000E+00  1.0000E+00  0.0000E+00  1.1324E+01
> 0.0000E+00
> >> 0.0000E+00"
> >> )[rep(1:10, 1000)], file="FILENAME", sep="\n")
> >>
> >>
> >> Dennis
> >>
> >>
> >> Dennis Fisher MD
> >> P < (The "P Less Than" Company)
> >> Phone: 1-866-PLessThan (1-866-753-7784)
> >> Fax: 1-866-PLessThan (1-866-753-7784)
> >> www.PLessThan.com
> >>
> >> ______________________________________________
> >> R-help at r-project.org mailing list
> >> https://stat.ethz.ch/mailman/listinfo/r-help
> >> PLEASE do read the posting guide http://www.R-project.org/posting-
> >> guide.html
> >> and provide commented, minimal, self-contained, reproducible code.
> >




More information about the R-help mailing list