How do I execute a rolling join when one table has groups, and the other does not? For example: x.dt <- as.data.table( expand.grid(date = seq(as.Date("2017-01-01"), as.Date("2017-12-31"), "days"), group = c("A", "B") ) ) x.dt[, x := rnorm(.N)] setkey(x.dt, group, date) y.dt <- data.table(date = seq(as.Date("2017-01-01"), as.Date("2017-12-31"), "months")) y.dt[, y := month(date)] setkey(y.dt, date) result <- y.dt[x.dt, roll = TRUE] The last line fails because “group” is part of the key in x.dt, but not y.dt: Error in bmerge(i, x, leftcols, rightcols, io, xo, roll, rollends, nomatch, :
typeof x.date (double) != typeof i.group (integer) I would like the value of y in each row of the result to be the month number (from y.dt), regardless of the value of the group column. Thanks. - Elliot _______________________________________________ datatable-help mailing list [hidden email] https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help |
You mean x.dt[, y := y.dt[.SD, on=.(date), roll=TRUE, x.y]] ? On Wed, Feb 14, 2018 at 3:18 PM, Bernstein, Elliot J <[hidden email]> wrote:
_______________________________________________ datatable-help mailing list [hidden email] https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help |
Frank – Thank you very much for your help. That works, but unfortunately I don’t understand why. I’ve been searching the package help and vignettes, but haven’t found an explanation. Is this syntax documented
somewhere? (Nearly everything about that line is a mystery to me: What does it mean to use .SD in the i argument to DT[]? What is “on = .(date) doing? And how is “x.y” apparently referring to column “y” of y.dt?) Thanks. - Elliot From: Frank Erickson [mailto:[hidden email]]
You mean x.dt[, y := y.dt[.SD, on=.(date), roll=TRUE, x.y]] ? On Wed, Feb 14, 2018 at 3:18 PM, Bernstein, Elliot J <[hidden email]> wrote: How do I execute a rolling join when one table has groups, and the other does not? For example: x.dt <- as.data.table( expand.grid(date = seq(as.Date("2017-01-01"), as.Date("2017-12-31"), "days"), group = c("A", "B") ) ) x.dt[, x := rnorm(.N)] setkey(x.dt, group, date) y.dt <- data.table(date = seq(as.Date("2017-01-01"), as.Date("2017-12-31"), "months")) y.dt[, y := month(date)] setkey(y.dt, date) result <- y.dt[x.dt, roll = TRUE] The last line fails because “group” is part of the key in x.dt, but not y.dt: Error in bmerge(i, x, leftcols, rightcols, io, xo, roll, rollends, nomatch, :
typeof x.date (double) != typeof i.group (integer) I would like the value of y in each row of the result to be the month number (from y.dt), regardless of the value of the group column. Thanks. - Elliot
_______________________________________________ datatable-help mailing list [hidden email] https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help |
Np. Those are documented in ?data.table and ?.SD, but it's hard to put the pieces together. This is an "update join". The developers have a vignette on joins planned, but in the meantime, maybe my notes on it can help: http://franknarf1.github.io/r-tutorial/_book/tables.html#dt-joins On Wed, Feb 14, 2018 at 4:01 PM, Bernstein, Elliot J <[hidden email]> wrote:
_______________________________________________ datatable-help mailing list [hidden email] https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help |
Frank – Thank you very much. Those notes are extremely helpful. - Elliot From: Frank Erickson [mailto:[hidden email]]
Np. Those are documented in ?data.table and ?.SD, but it's hard to put the pieces together. This is an "update join". The developers have a vignette on joins planned, but in the meantime, maybe my notes on it can help: http://franknarf1.github.io/r-tutorial/_book/tables.html#dt-joins On Wed, Feb 14, 2018 at 4:01 PM, Bernstein, Elliot J <[hidden email]> wrote: Frank – Thank you very much for your help. That works, but unfortunately I don’t understand why. I’ve been searching the package help and vignettes,
but haven’t found an explanation. Is this syntax documented somewhere? (Nearly everything about that line is a mystery to me: What does it mean to use .SD in the i argument to DT[]? What is “on = .(date) doing? And how is “x.y” apparently referring to column
“y” of y.dt?) Thanks. - Elliot From: Frank Erickson [mailto:[hidden email]]
You mean x.dt[, y := y.dt[.SD, on=.(date), roll=TRUE, x.y]] ? On Wed, Feb 14, 2018 at 3:18 PM, Bernstein, Elliot J <[hidden email]> wrote: How do I execute a rolling join when one table has groups, and the other does not? For example: x.dt <- as.data.table( expand.grid(date = seq(as.Date("2017-01-01"), as.Date("2017-12-31"), "days"), group = c("A", "B") ) ) x.dt[, x := rnorm(.N)] setkey(x.dt, group, date) y.dt <- data.table(date = seq(as.Date("2017-01-01"), as.Date("2017-12-31"), "months")) y.dt[, y := month(date)] setkey(y.dt, date) result <- y.dt[x.dt, roll = TRUE] The last line fails because “group” is part of the key in x.dt, but not y.dt: Error in bmerge(i, x, leftcols, rightcols, io, xo, roll, rollends, nomatch, :
typeof x.date (double) != typeof i.group (integer) I would like the value of y in each row of the result to be the month number (from y.dt), regardless of the value of the group column. Thanks. - Elliot
_______________________________________________ datatable-help mailing list [hidden email] https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help |
Free forum by Nabble | Edit this page |