Repository navigation
joinbyv - batch join multiple tables - #694
jangorecki wants to merge 0 commit into
Conversation
|
Sorry for delay - this looks important so want to get it right. Have added to the 1.9.4 milestone. There's a related issue here to tackle as well before release to CRAN (which signals we're happy with function names, syntax etc) : http://r.789695.n4.nabble.com/data-table-is-asking-for-help-tp4692080p4692273.html These might tie in with secondary keys, too. |
|
We may skip this pull request, I'm going to push separate branch to include this function plus some additional. devtools::install_github("data.table", "Rdatatable", build_vignettes=FALSE, ref = NULL, pull = 694)
library(data.table)
# run `populate example data` section from examples:
?joinbyv
# then run code below:
time[,date_code := as.character(date_code)]
n <- 1e5
set.seed(2345)
sales <-
data.table(prod_code = sample(product[,prod_code], n, TRUE),
cust_code = sample(customer[,cust_code], n, TRUE),
state_code = sample(geography[,state_code], n, TRUE),
date_code = sample(time[,date_code], n, TRUE),
quantity = rnorm(n, 500, 200),
value = rnorm(n, 10000,2000))
setkeyv(sales,c("prod_code","cust_code","state_code","date_code"))
# binary search filtering works fine on all four keys
sales[CJ(c(12,13,15), c(15,16,21,24),c("TX","AK","CA"),c("2013-01-18","2013-01-31","2014-08-06")), nomatch=0]
# but if I want to binary search on 3 keys, excluding `state_code` I would like to use some kind of:
sales[CJ(c(12,13,15), c(15,16,21,24),TRUE,c("2013-01-18","2013-01-31","2014-08-06")), nomatch=0]
sales[CJ(c(12,13,15), c(15,16,21,24),NA,c("2013-01-18","2013-01-31","2014-08-06")), nomatch=0]
sales[CJ(c(12,13,15), c(15,16,21,24),character(),c("2013-01-18","2013-01-31","2014-08-06")), nomatch=0]
# none of them works
# binary search workaround, not so nice:
sales[CJ(c(12,13,15), c(15,16,21,24), unique(sales[,state_code]), c("2013-01-18","2013-01-31","2014-08-06")), nomatch=0]
# non binary search workaround:
sales[CJ(c(12,13,15), c(15,16,21,24)), nomatch=0][date_code %in% c("2013-01-18","2013-01-31","2014-08-06")]As 1.9.4 is already long awaited, we may may not hurry with pull request feature for 1.9.4 (as it is tagged now). |
|
the pull request has been removed from master due to branch switch, the one mentioned here is temporary available here: jangorecki@b50bb9d |
2 participants
Hello,
Utility function to batch join multiple tables. Using one function call you can perform many deeply customized joins. I found it very useful in process of denormalization hierarchical data from DataWarehouse/DB organized in star schema or snowflake schema design patterns. Generally it can be useful any time when series of joins is required.
Any comments?
Jan