我的方法在data.table重复行的高效？ [英] Is my way of duplicating rows in data.table efficient?

问题描述

我有每月的数据在一个 data.table 和年度数据在另一个 data.table ，现在我想以匹配年度数据与每月数据中的相应观察值。

I have monthly data in one data.table and annual data in another data.table and now I want to match the annual data to the respective observation in the monthly data.

我的方法如下：每月复制年度数据，然后加入每月和年度数据。现在我有一个关于行的重复的问题。我知道如何做，但我不知道这是否是最好的方式，所以一些意见会是巨大的。

My approach is as follows: Duplicating the annual data for every month and then join the monthly and annual data. And now I have a question regarding the duplication of rows. I know how to do it, but I'm not sure if it is the best way to do it, so some opinions would be great.

这里是一个示例 data.table DT 为我的年度数据和我目前如何复制：

Here is an exemplatory data.table DT for my annual data and how I currently duplicate:

library(data.table)
DT <- data.table(ID = paste(rep(c("a", "b"), each=3), c(1:3, 1:3), sep="_"),
                    values = 10:15,
                    startMonth = seq(from=1, by=2, length=6),
                    endMonth = seq(from=3, by=3, length=6))
DT
      ID values startMonth endMonth
[1,] a_1     10          1        3
[2,] a_2     11          3        6
[3,] a_3     12          5        9
[4,] b_1     13          7       12
[5,] b_2     14          9       15
[6,] b_3     15         11       18
#1. Alternative
DT1 <- DT[, list(MONTH=startMonth:endMonth), by="ID"]
setkey(DT,  ID)
setkey(DT1, ID)
DT1[DT]
ID MONTH values startMonth endMonth
a_1     1     10          1        3
a_1     2     10          1        3
a_1     3     10          1        3
a_2     3     11          3        6
[...]

最后一个连接正是我想要的。然而， DT [，list（MONTH = startMonth：endMonth），by =ID] 已经做了我想要的一切，除了添加其他列到 DT ，所以我想知道我是否可以删除我的代码中的最后三行，即 setkey 和 join 操作。原来，你可以，只要做以下：

The last join is exactly what I want. However, DT[, list(MONTH=startMonth:endMonth), by="ID"] already does everything I want except adding the other columns to DT, so I was wondering if I could get rid of the last three rows in my code, i.e. the setkey and join operations. It turns out, you can, just do the following:

#2. Alternative: More intuitiv and just one line of code
DT[, list(MONTH=startMonth:endMonth, values, startMonth, endMonth), by="ID"]
 ID MONTH values startMonth endMonth
a_1    1     10          1        3
a_1    2     10          1        3
a_1    3     10          1        3
a_2    3     11          3        6
...

但是，这只有工作，因为我将列名硬编码到 list 表达式。在我的真实数据中，我不提前知道所有列的名称，所以我想知道是否可以告诉 data.table 返回列 MONTH 我如上所示计算和 DT 的所有其他列。 .SD 似乎能够做到这一点，但是：

This, however, only works because I hardcoded the column names into the list expression. In my real data, I do not know the names of all columns in advance, so I was wondering if I could just tell data.table to return the column MONTH that I compute as shown above and all the other columns of DT. .SD seemed to be able to do the trick, but:

DT[, list(MONTH=startMonth:endMonth, .SD), by="ID"]
Error in `[.data.table`(DT, , list(YEAR = startMonth:endMonth, .SD), by = "ID") : 
  maxn (4) is not exact multiple of this j column's length (3)

b $ b

总之，我知道它是如何做的，但我只是想知道这是否是最好的方法，因为我仍然在努力一点点语法 data.table ，并经常阅读的帖子和维基上有好和坏的做事方式。另外，我不太明白为什么当使用 .SD 时会出现错误。我认为这只是任何简单的方法来告诉 data.table ，你想要所有的列。

So to summarize, I know how it's been done, but I was just wondering if this is the best way to do it because I'm still struggling a little bit with the syntax of data.table and often read in posts and on the wiki that there are good and bads ways of doing things. Also, I don't quite get why I get an error when using .SD. I thought it is just any easy way to tell data.table that you want all columns. What do I miss?

推荐答案

很好的问题。你试过的是非常合理的。假设你使用的是v1.7.1，现在可以更容易地创建 list 列。在这种情况下，它试图在 .SD （3个项目）旁边的MONTH列中创建一个列表第二组（4项）。

Great question. What you tried was very reasonable. Assuming you're using v1.7.1 it's now easier to make list columns. In this case it's trying to make one list column out of .SD (3 items) alongside the MONTH column of the 2nd group (4 items). I'll raise it as a bug , thanks.

在此期间，请尝试：

DT[, cbind(MONTH=startMonth:endMonth, .SD), by="ID"]
 ID MONTH values startMonth endMonth
a_1     1     10          1        3
a_1     2     10          1        3
a_1     3     10          1        3
a_2     3     11          3        6
...

此外，只是为了检查 roll = TRUE 通常你只有一个startMonth列（不规则，有间隙），然后只需 roll 加入它。但您的示例数据具有重叠的月份范围，因此会使其复杂化。

Also, just to check you've seen roll=TRUE? Typically you'd have just one startMonth column (irregular with gaps) and then just roll join to it. Your example data has overlapping month ranges though, so that complicates it.

这篇关于我的方法在data.table重复行的高效？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！