Proper/fastest way to reshape a data.table
Rdata.tableR Problem Overview
I have a [data table][1] in R:
library(data.table)
set.seed(1234)
DT <- data.table(x=rep(c(1,2,3),each=4), y=c("A","B"), v=sample(1:100,12))
DT
x y v
[1,] 1 A 12
[2,] 1 B 62
[3,] 1 A 60
[4,] 1 B 61
[5,] 2 A 83
[6,] 2 B 97
[7,] 2 A 1
[8,] 2 B 22
[9,] 3 A 99
[10,] 3 B 47
[11,] 3 A 63
[12,] 3 B 49
I can easily sum the variable v by the groups in the data.table:
out <- DT[,list(SUM=sum(v)),by=list(x,y)]
out
x y SUM
[1,] 1 A 72
[2,] 1 B 123
[3,] 2 A 84
[4,] 2 B 119
[5,] 3 A 162
[6,] 3 B 96
However, I would like to have the groups (y) as columns, rather than rows. I can accomplish this using reshape
:
out <- reshape(out,direction='wide',idvar='x', timevar='y')
out
x SUM.A SUM.B
[1,] 1 72 123
[2,] 2 84 119
[3,] 3 162 96
Is there a more efficient way to reshape the data after aggregating it? Is there any way to combine these operations into one step, using the data.table operations? [1]: http://cran.r-project.org/web/packages/data.table/index.html
R Solutions
Solution 1 - R
The data.table
package implements faster melt/dcast
functions (in C). It also has additional features by allowing to melt and cast multiple columns. Please see the new Efficient reshaping using data.tables on Github.
melt/dcast functions for data.table have been available since v1.9.0 and the features include:
-
There is no need to load
reshape2
package prior to casting. But if you want it loaded for other operations, please load it before loadingdata.table
. -
dcast
is also a S3 generic. No moredcast.data.table()
. Just usedcast()
. -
melt
:-
is capable of melting on columns of type 'list'.
-
gains
variable.factor
andvalue.factor
which by default areTRUE
andFALSE
respectively for compatibility withreshape2
. This allows for directly controlling the output type ofvariable
andvalue
columns (as factors or not). -
melt.data.table
'sna.rm = TRUE
parameter is internally optimised to remove NAs directly during melting and is therefore much more efficient. -
NEW:
melt
can accept a list formeasure.vars
and columns specified in each element of the list will be combined together. This is faciliated further through the use ofpatterns()
. See vignette or?melt
.
-
-
dcast
:-
accepts multiple
fun.aggregate
and multiplevalue.var
. See vignette or?dcast
. -
use
rowid()
function directly in formula to generate an id-column, which is sometimes required to identify the rows uniquely. See ?dcast.
-
-
Old benchmarks:
melt
: 10 million rows and 5 columns, 61.3 seconds reduced to 1.2 seconds.dcast
: 1 million rows and 4 columns, 192 seconds reduced to 3.6 seconds.
Reminder of Cologne (Dec 2013) presentation slide 32 : Why not submit a dcast
pull request to reshape2
?
Solution 2 - R
This feature is now implemented into data.table (from version 1.8.11 on), as can be seen in Zach's answer above.
I just saw this great chunk of code from Arun here on SO. So I guess there is a data.table
solution. Applied to this problem:
library(data.table)
set.seed(1234)
DT <- data.table(x=rep(c(1,2,3),each=1e6),
y=c("A","B"),
v=sample(1:100,12))
out <- DT[,list(SUM=sum(v)),by=list(x,y)]
# edit (mnel) to avoid setNames which creates a copy
# when calling `names<-` inside the function
out[, as.list(setattr(SUM, 'names', y)), by=list(x)]
})
x A B
1: 1 26499966 28166677
2: 2 26499978 28166673
3: 3 26500056 28166650
This gives the same results as DWin's approach:
tapply(DT$v,list(DT$x, DT$y), FUN=sum)
A B
1 26499966 28166677
2 26499978 28166673
3 26500056 28166650
Also, it is fast:
system.time({
out <- DT[,list(SUM=sum(v)),by=list(x,y)]
out[, as.list(setattr(SUM, 'names', y)), by=list(x)]})
## user system elapsed
## 0.64 0.05 0.70
system.time(tapply(DT$v,list(DT$x, DT$y), FUN=sum))
## user system elapsed
## 7.23 0.16 7.39
UPDATE
So that this solution also works for non-balanced data sets (i.e. some combinations do not exist), you have to enter those in the data table first:
library(data.table)
set.seed(1234)
DT <- data.table(x=c(rep(c(1,2,3),each=4),3,4), y=c("A","B"), v=sample(1:100,14))
out <- DT[,list(SUM=sum(v)),by=list(x,y)]
setkey(out, x, y)
intDT <- expand.grid(unique(out[,x]), unique(out[,y]))
setnames(intDT, c("x", "y"))
out <- out[intDT]
out[, as.list(setattr(SUM, 'names', y)), by=list(x)]
Summary
Combining the comments with the above, here's the 1-line solution:
DT[, sum(v), keyby = list(x,y)][CJ(unique(x), unique(y)), allow.cartesian = T][, setNames(as.list(V1), paste(y)), by = x]
It's also easy to modify this to have more than just the sum, e.g.:
DT[, list(sum(v), mean(v)), keyby = list(x,y)][CJ(unique(x), unique(y)), allow.cartesian = T][, setNames(as.list(c(V1, V2)), c(paste0(y,".sum"), paste0(y,".mean"))), by = x]
# x A.sum B.sum A.mean B.mean
#1: 1 72 123 36.00000 61.5
#2: 2 84 119 42.00000 59.5
#3: 3 187 96 62.33333 48.0
#4: 4 NA 81 NA 81.0
Solution 3 - R
Data.table objects inherit from 'data.frame' so you can just use tapply:
> tapply(DT$v,list(DT$x, DT$y), FUN=sum)
AA BB
a 72 123
b 84 119
c 162 96
Solution 4 - R
You can use dcast
from reshape2
library. Here is the code
# DUMMY DATA
library(data.table)
mydf = data.table(
x = rep(1:3, each = 4),
y = rep(c('A', 'B'), times = 2),
v = rpois(12, 30)
)
# USE RESHAPE2
library(reshape2)
dcast(mydf, x ~ y, fun = sum, value_var = "v")
NOTE: The tapply
solution would be much faster.