I have two data.tables, `main` and `metrics`, both keyed by `cid` I want to add to table `main` the average of each of several values located in metrics. However, I would like to filter by `code`, only averaging those rows in `metrics` with a given `code`. > metrics cid code DZ value1 value2 1: 1001 A 101 8 21 2: 1001 B 102 11 26 3: 1001 A 103 17 25 4: 1002 A 104 25 39 5: 1002 B 105 6 30 6: 1002 A 106 23 40 7: 1003 A 107 27 32 8: 1003 B 108 16 37 9: 1003 A 109 14 42 # DESIRED OUTPUT > main cid A.avg.val1 A.avg.val2 B.avg.val1 B.avg.val2 1: 1001 12.5 23.0 11 26 2: 1002 24.0 39.5 6 30 3: 1003 20.5 37.0 16 37 # SAMPLE DATA library(data.table) set.seed(1) main <- data.table(cid=1e3+1:3, OtherCol=c(77,32,29), key="cid") metrics <- data.table(cid=rep(1e3+1:3, each=3), code=rep(c("A", "B", "A"), 3), DZ=101:109, value1=sample(30, 9), value2=sample(20:50, 9), key="cid") code.filters <- c("A", "B") These lines get the desired output, but I am having difficulty assigning the new col back into main. (also, doing it programatically would be preferred). main[metrics[code==code.filters[[1]]]][, list(mean(c(value1))), by=cid] main[metrics[code==code.filters[[1]]]][, list(mean(c(value2))), by=cid] main[metrics[code==code.filters[[2]]]][, list(mean(c(value1))), by=cid] main[metrics[code==code.filters[[1]]]][, list(mean(c(value2))), by=cid] #`````````````````````````````` ANSWER: dummyVar <- sapply(code.filters, function(cf) main[metrics[code==cf, list(avgv1 = mean(value1), avgv2 = mean(value2)), by=cid], paste0(cf, c(".avg.val1", ".avg.val2")) :=list(avgv1, avgv2)] ) > main cid A.avg.val1 A.avg.val2 B.avg.val1 B.avg.val2 1: 1001 12.5 23.0 11 26 2: 1002 24.0 39.5 6 30 3: 1003 20.5 37.0 16 37 #`````````````````````````````` PROGRAMATICALLY # ------------- SAMPLE DATA --------------------# main <- copy(main.bak) main[, Also := 1:3] metrics <- copy(metrics.bak) mets <-list(str = c("value1", "value2")) #derived programmattically newnamesA <- paste0("A.avgVal", 1:2) newnamesB <- paste0("B.avgVal", 1:2) tmpnames <- c("tmpName1", "tmpName2") # ------------- SAMPLE DATA --------------------# #--------------------------------------------------------------------------------------# # GOAL # #--------------------------------------------------------------------------------------# main[ metrics[code=="A", list(tmpName1=mean(value1), tmpName2=mean(value2)), by=cid] , c(newnamesA) := list(tmpName1, tmpName2)] #--------------------------------------------------------------------------------------# #--------------------------------------------------------------------------------------# #--------------------------------------------------------------------------------------# # ASSIGNING NAMES TO THE LIST in lapply WORKS # #--------------------------------------------------------------------------------------# names(mets$str) <- tmpnames main[metrics[code=="A", lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2)))) , by=cid]] #--------------------------------------------------------------------------------------# #--------------------------------------------------------------------------------------# #--------------------------------------------------------------------------------------# # THIS IS USING `COPY` AND WORKS WELL FOR NOW # #--------------------------------------------------------------------------------------# # Assigning for code==A main <- copy( main[metrics[code=="A", {names(mets$str) <- newnamesA lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2))))} , by=cid]][, c(newnamesA) := lapply(newnamesA, function(x) get(x, envir=parent.frame(2)))] ); main # Assigning for code==B main <- copy( main[metrics[code=="B", {names(mets$str) <- newnamesB lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2))))} , by=cid]][, c(newnamesB) := lapply(newnamesB, function(x) get(x, envir=parent.frame(2)))] ); main #--------------------------------------------------------------------------------------# #--------------------------------------------------------------------------------------# # CREATE LIST OF METRICS TO PULL mets <- list() mets$str <- c("value1", "value2") # CREATE COLUMN NAMES FOR NEW COLUMNS # eg: for each prefix, we get: mets$bfr <- paste0("bfr.avg.", mets$str) prefixes <- c("bfr.avg.", "afr.avg.", "inc.perc.", "loginc.perc.") for (p in prefixes) mets[[substr(p, 1, str_locate(p, "\\.")[1] )]] <- paste0(p, mets$str) # CREATE BEFORE COLUMNS tmpnames <- paste0("tmp.", mets$str) tmp.v <- copy(v3[vt.join[diff >= bfr.st & diff <= bfr.end, {x <- lapply(eval(mets$str), function(m) mean(get(m, envir=parent.frame(2)), na.rm=TRUE) ); names(x) <- tmpnames; x}, # lapply(eval(mets$str), function(m) mean(get(m, envir=parent.frame(2)), na.rm=TRUE) ), by=concertID] ]) #, tmp.new <- copy(tmp.v[, c(mets$bfr) := lapply(tmpnames, function(x) get(x, envir=parent.frame(2)))]) # Assigning for "BEFORE" v3 <- copy( v3[vt.join[code=="B", [diff >= bfr.st & diff <= bfr.end, {names(mets$str) <- mets$bfr lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2)), na.rm=TRUE ))} , by=cid]][, c(mets$bfr) := lapply(mets$bfr, function(x) get(x, envir=parent.frame(2))) ] ); v3 # Assigning for "AFTER" v3 <- copy( v3[vt.join[code=="A", [diff >= afr.st & diff <= afr.end, {names(mets$str) <- mets$afr lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2)), na.rm=TRUE ))} , by=cid]][, c(mets$afr) := lapply(mets$afr, function(x) get(x, envir=parent.frame(2))) ] ); v3 #--------------------------------------------------------------------------------------# # WORKS BUT CANNNOT ASSIGN # % main[metrics[code=="A", lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2)))) , by=cid]] %! main[metrics[code=="A", c(newnamesA) = lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2)))) , by=cid]] #--------------------------------------------------------------------------------------# main[metrics[code=="A", c(newnamesA) := lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2)))) , by=cid]] main[metrics, list(code, DZ, avgVal1, avgVal1), by=cid] main[metrics[code=="A", mapply(function(m, nam) {x <- mean(get(m, envir=parent.frame(2))); names(x) <- nam; x}, mets$str, newnamesA) , by=cid]] main[metrics[code=="A", {x <- lapply(mets$str, function(m) mean(get(m, envir=parent.frame(2)))); names(x) <- tmpnames; x} , by=cid]] test <- copy(v3[vt.join[diff >= bfr.st & diff <= bfr.end, c(tmpnames) := lapply(eval(mets$str), function(m) mean(get(m, envir=parent.frame(2)), na.rm=TRUE) ), by=concertID] ]) #,