read.table performance

system.time(dat<-read.table("test2.txt"))

system.time(dat <- read.table('test2.txt', nrows=-1, sep='\t', 

dim(dat)

head(names(dat))

tail(names(dat))

peter dalgaard <pdalgd at gmail.com> 
Odeslal: r-help-bounces at r-project.org

07.12.2011 23:11

Komu

"R. Michael Weylandt" <michael.weylandt at gmail.com>

Kopie

r-help at r-project.org, Gene Leynes <gleynes at gmail.com>

P?edm?t

Re: [R] read.table performance


On Dec 7, 2011, at 22:37 , R. Michael Weylandt wrote:

R 2.13.2 on Mac OS X 10.5.8 takes about 1.8s to read the file
verbatim: system.time(read.table("test2.txt"))

About 2.3s with 2.14 on a 1.86 GHz MacBook Air 10.6.8. 

Gene, are you by any chance storing the file in a heavily virus-scanned 
system directory?

-pd

Michael

2011/12/7 Gene Leynes <gleynes at gmail.com>:

Peter,

You're quite right; it's nearly impossible to make progress without a
working example.

I created an ** extremely simplified ** example for distribution. The 

data has numeric, character, and boolean classes.

The file still takes 25.08 seconds to read, despite it's small size.

I neglected to mention that I'm using R 2.13.0 and I"m on a windows 7
machine (not that it should particularly matter with this type of 

functions).

## The code:
options(stringsAsFactors=FALSE)
system.time(dat <- read.table('test2.txt', nrows=-1, sep='\t', 

str(dat, 0)


Thanks again!



On Wed, Dec 7, 2011 at 1:21 AM, peter dalgaard <pdalgd at gmail.com> 

On Dec 6, 2011, at 22:33 , Gene Leynes wrote:

Mark,

Thanks for your suggestions.

That's a good idea about the NULL columns; I didn't think of that.
Surprisingly, it didn't have any effect on the time.

Hmm, I think you want "character" and "NULL" there (i.e., quoted). 

fix both?

read.table(whatever, as.is=TRUE, colClasses = c(rep(character,4),
rep(NULL,3696)).

As a general matter, if you want people to dig into this, they need 

paraphrase of the file to play with. Would it be possible to set up 

R program that generates a data file which displays the issue? 

try seems to take about a second to read in.

-pd

This problem was just a curiosity, I already did the import using 

and

VBA.  I was just going to illustrate the power and simplicity of R, 

it

ironically it's been much slower and harder in R...
The VBA was painful and messy, and took me over an hour to write; 

least it worked quickly and reliably.
The R code was clean and only took me about 5 minutes to write, but 

run

time was prohibitively slow!

I profiled the code, but that offers little insight to me.

Profile results with 10 line file:

summaryRprof("C:/Users/gene.leynes/Desktop/test.out")

$by.self
            self.time self.pct total.time total.pct
scan             12.24    53.50      12.24     53.50
read.table       10.58    46.24      22.88    100.00
type.convert      0.04     0.17       0.04      0.17
make.names        0.02     0.09       0.02      0.09

$by.total
            total.time total.pct self.time self.pct
read.table        22.88    100.00     10.58    46.24
scan              12.24     53.50     12.24    53.50
type.convert       0.04      0.17      0.04     0.17
make.names         0.02      0.09      0.02     0.09

$sample.interval
[1] 0.02

$sampling.time
[1] 22.88


Profile results with 250 line file:

summaryRprof("C:/Users/gene.leynes/Desktop/test.out")

$by.self
            self.time self.pct total.time total.pct
scan             23.88    68.15      23.88     68.15
read.table       10.78    30.76      35.04    100.00
type.convert      0.30     0.86       0.32      0.91
character         0.02     0.06       0.02      0.06
file              0.02     0.06       0.02      0.06
lapply            0.02     0.06       0.02      0.06
unlist            0.02     0.06       0.02      0.06

$by.total
              total.time total.pct self.time self.pct
read.table          35.04    100.00     10.78    30.76
scan                23.88     68.15     23.88    68.15
type.convert         0.32      0.91      0.30     0.86
sapply               0.04      0.11      0.00     0.00
character            0.02      0.06      0.02     0.06
file                 0.02      0.06      0.02     0.06
lapply               0.02      0.06      0.02     0.06
unlist               0.02      0.06      0.02     0.06
simplify2array       0.02      0.06      0.00     0.00

$sample.interval
[1] 0.02

$sampling.time
[1] 35.04




On Tue, Dec 6, 2011 at 2:34 PM, Mark Leeds <markleeds2 at gmail.com> 

hi gene: maybe someone else will reply with some  subtleties that 

not

aware of. one other thing
that might help: if you know which columns you want , you can set 

others to NULL through
colClasses and this should speed things up also. For example, say 

knew

you only wanted the
first four columns and they were character. then you could do,

read.table(whatever, as.is=TRUE, colClasses = c(rep(character,4),
rep(NULL,3696)).

hopefully someone else will say something that does the trick. it 

odd to me as far as the
difference in timings ? good luck.





On Tue, Dec 6, 2011 at 1:55 PM, Gene Leynes <gleynes at gmail.com> 

Mark,

Thank you for the reply

I neglected to mention that I had already set
options(stringsAsFactors=FALSE)

I agree, skipping the factor determination can help performance.

The main reason that I wanted to use read.table is because it 

correctly determine the column classes for me.  I don't really 

specify 3700 column classes!  (I'm not sure what they are 

On Tue, Dec 6, 2011 at 12:40 PM, Mark Leeds 

wrote:

Hi Gene: Sometimes using colClasses in read.table can speed 

If you know what your variables are ahead of time and what you 

them to

be, this allows you to be specific  by specifying, character or

numeric,

etc  and often it makes things faster. others will have more to 

also, if most of your variables are characters, R will try to 

convert them into factors by default. If you use as.is = TRUE it

won't

do this and that might speed things up also.


Rejoinder:  above tidbits are  just from experience. I don't 

it's in stone or a hard and fast rule.








On Tue, Dec 6, 2011 at 1:15 PM, Gene Leynes <gleynes at gmail.com>

wrote:

** Disclaimer: I'm looking for general suggestions **
I'm sorry, but can't send out the file I'm using, so there is 

reproducible example.

I'm using read.table and it's taking over 30 seconds to read a 

file.
The strange thing is that it takes roughly the same amount of 

the
file is 100 times larger.

After re-reviewing the data Import / Export manual I think the 

approach would be to use Python, or perhaps the readLines 

but

I
was hoping to understand why the simple read.table approach 

working
as expected.

Some relevant facts:

 1. There are about 3700 columns.  Maybe this is the problem? 

the

 file size is not very large.
 2. The file encoding is ANSI, but I'm not specifying that in 

 function.  Setting fileEncoding="ANSI" produces an 

conversion"
 error
 3. readLines imports the lines quickly
 4. scan imports the file quickly also


Obviously, scan and readLines would require more coding to 

columns, etc.

my code:
system.time(dat <- read.table('C:/test.txt', nrows=-1, 

header=TRUE))

It's taking 33.4 seconds and the file size is only 315 kb!

Thanks

Gene

      [[alternative HTML version deleted]]

      [[alternative HTML version deleted]]

-- 
Peter Dalgaard, Professor,
Center for Statistics, Copenhagen Business School
Solbjerg Plads 3, 2000 Frederiksberg, Denmark
Phone: (+45)38153501
Email: pd.mes at cbs.dk  Priv: PDalgd at gmail.com