R (data.table) computing mean after two merges with massive datasets

user_11968604 0
Viewed 50

I have three datasets, two of which two are massive. Dataset 1 informs multiple values of y for a given x. Dataset 2 informs multiple values of w for a given y. Finally, dataset 3 informs a single z for each w.

I would like to compute the average value of z for each x. The simplest way would be to, first, merge DT_xy with DT_yw (using y as key variable) to then merge it with DT_wz (using w as key variable) to, finally, compute the mean of z for every x. I show the code below for this.

I am looking for a more efficient code, given that DT_xy is already massive. Merging it with DT_yw increases its rows by 1000 which is unfeasible. I don't need to know the individual values of w for every x-y. I just need to know average z for each x.

DT_xy = data.table(x=c(rep(1,10),rep(2,13)), y=(c(1:10, 1:13)))
DT_yw = data.table(y=rep(1:13,200), w=1:13*200)
DT_wz = data.table(w=1:13*200, z=1000:2600)

data_merge1 <- DT_yw[DT_xy, on="y", allow.cartesian = TRUE]
data_merge2 <- DT_wz[data_merge1, on="w", allow.cartesian = TRUE]

output <- data_merge2[, mean(z), by="x"]
bigdata data.table r merge
1 Answers

You can do this more efficiently, by collapsing DT_wz into the mean and row count of z by w, and collapsing DT_yw into the rowcount by w and y, and generate row_count weighted means.. This avoids any cartesian joins

wz = DT_wz[, .(.N, mean(z)),w]
yw = DT_yw[, .N, .(y,w)]
f <- function(df) df[, sum(N*V2*i.N)/sum(N*i.N)]
yw[DT_xy, on="y"][wz, on="w"][, f(.SD),x]

Output:

   x       V1
1: 1 1798.802
2: 2 1800.000
user_44475400
Related