aggregate() runs out of memory

I have a large data.frame Z (2,424,185,944 bytes, 10,256,441 rows, 17 columns).
I want to get the result of
table(aggregate(Z$V1, FUN = length, by = list(id=Z$V2))$x)
alas, aggregate has been running for ~30 minute, RSS is 14G, VIRT is
24.3G, and no end in sight.
both V1 and V2 are characters (not factors).
Is there anything I could do to speed this up?
Thanks.

From here you might

-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf
Of Steve Lianoglou
Sent: Friday, September 14, 2012 12:41 PM
To: sds at gnu.org; r-help at r-project.org
Subject: Re: [R] aggregate() runs out of memory

Hi,

On Fri, Sep 14, 2012 at 3:26 PM, Sam Steingold <sds at gnu.org> wrote:

I have a large data.frame Z (2,424,185,944 bytes, 10,256,441 rows, 17 columns).
I want to get the result of
table(aggregate(Z$V1, FUN = length, by = list(id=Z$V2))$x)
alas, aggregate has been running for ~30 minute, RSS is 14G, VIRT is
24.3G, and no end in sight.
both V1 and V2 are characters (not factors).
Is there anything I could do to speed this up?
Thanks.

You might find you'll get a lot of mileage out of data.table when
working with such large data.frames ...

To get something close to what you're after, you can try:

R> library(data.table)
R> Z <- as.data.table(Z)
R> setkeyv(Z, 'V2')
R> agg <- Z[, list(count=.N), by='V2']

From here you might

R> tab1 <- table(agg$count)

I think that'll get you where you want to be ... I'm ashamed to say
that I haven't really done much w/ aggregate since I mostly have used
plyr and data.table like stuff, so I might be missing your end goal --
providing a reproducible example with a small data.frame from you can
help here (for me at least).

HTH,
-steve

--
Steve Lianoglou
Graduate Student: Computational Systems Biology
 | Memorial Sloan-Kettering Cancer Center
 | Weill Medical College of Cornell University
Contact Info: http://cbio.mskcc.org/~lianos/contact

Hi:

This should give you some idea of what Steve is talking about:

library(data.table)
dt <- data.table(x = sample(100000, 10000000, replace = TRUE),
                  y = rnorm(10000000), key = "x")
dt[, .N, by = x]
system.time(dt[, .N, by = x])

...on my system, dual core 8Gb RAM running Win7 64-bit,

system.time(dt[, .N, by = x])

   user  system elapsed
   0.12    0.02    0.14

.N is an optimized function to find the number of rows of each data subset.
Much faster than aggregate(). It might take a little longer because you
have more columns that suck up space, but you get the idea. It's also about
5-6 times faster if you set a key variable in the data table than if you
don't.

Dennis

On Fri, Sep 14, 2012 at 12:26 PM, Sam Steingold <sds at gnu.org> wrote:

I have a large data.frame Z (2,424,185,944 bytes, 10,256,441 rows, 17
columns).
I want to get the result of
table(aggregate(Z$V1, FUN = length, by = list(id=Z$V2))$x)
alas, aggregate has been running for ~30 minute, RSS is 14G, VIRT is
24.3G, and no end in sight.
both V1 and V2 are characters (not factors).
Is there anything I could do to speed this up?
Thanks.

--
Sam Steingold (http://sds.podval.org/) on Ubuntu 12.04 (precise) X
11.0.11103000
http://www.childpsy.net/ http://www.PetitionOnline.com/tap12009/
http://dhimmi.com http://think-israel.org http://iris.org.il
WinWord 6.0 UNinstall: Not enough disk space to uninstall WinWord

        [[alternative HTML version deleted]]