How can I do this reshape faster and so that it takes up less memory? My aim is to reshape a dataframe that is 500,000 rows by 500 columns with 4 Gb RAM.
Here's a function that will make some reproducible data:
make_example <- function(ndoc, ntop){
# doc numbers
V1 = seq(1:ndoc)
# filenames
V2 <- list("vector", size = ndoc)
for (i in 1:ndoc){
V2[i] <- paste(sample(c(rep(0:9,each=5),LETTERS,letters),5,replace=TRUE),collapse='')
}
# topic proportions
tvals <- data.frame(matrix(runif(1:(ndoc*ntop)), ncol = ntop))
# topic number
tnumvals <- data.frame(matrix(sample(1:ntop, size = ndoc*ntop, replace = TRUE), ncol = ntop))
# now make topic props and topic numbers alternating columns (rather slow!)
alternating <- data.frame(c(matrix(c(tnumvals, tvals), 2, byrow = T)) )
# make colnames for topic number and topic props
ntopx <- sapply(1:ntop, function(j) paste0("ntop_",j))
ptopx <- sapply(1:ntop, function(j) paste0("ptop_",j))
tops <- c(rbind(ntopx,ptopx))
# make data frame
dat <- data.frame(V1 = V1,
V2 = unlist(V2),
alternating)
names(dat) <- c("docnum", "filename", tops)
# give df as result
return(dat)
}
Make some reproducible data:
set.seed(007)
dat <- make_example(500000, 500)
Here's my current method (thanks to https://stackoverflow.com/a/8058714/1036500):
library(reshape2)
NTOPICS = (ncol(dat) - 2 )/2
nam <- c('num', 'text', paste(c('topic', 'proportion'), rep(1:NTOPICS, each = 2), sep = ""))
system.time( dat_l2 <- reshape(setNames(dat, nam), varying = 3:length(nam), direction = 'long', sep = ""))
system.time( dat.final2 <- dcast(dat_l2, dat_l2[,2] ~ dat_l2[,3], value.var = "proportion" ) )
Some timings, just for the reshape
since that's the slowest step:
make_example(5000,100)
= 82 sec
make_example(50000,200)
= 2855 sec (crashed on attempting the second step)
make_example(500000,500)
= not yet possible...
What other methods are there that are faster and less memory intensive for this reshape (data.table
, this)?