将部分数据表和为另一个数据表的列

Question

我有两种数据。一个随着时间的推移得分，另一个我想填充在特定数量的游戏的分数之和。

> dt = data.table(game=c(1, 2, 3, 1, 2, 3, 1, 2, 3),
    player=c("ace", "ace", "ace", "bob", "bob", "bob", "casey", "casey", "casey"),
    points=c(5, 2, 3, 2, 6, 7, 3, 4, 2))
> dt
   game player points
1:    1    ace      5
2:    2    ace      2
3:    3    ace      3
4:    1    bob      2
5:    2    bob      6
6:    3    bob      7
7:    1  casey      3
8:    2  casey      4
9:    3  casey      2

> out = data.table(start=c(1, 1, 3),
    end=c(2, 2, 3),
    player=c("ace", "bob", "casey"))
> out
   start end player
1:     1   2    ace
2:     1   2    bob
3:     3   3  casey
> ???
> ???
> out 
   start end player points
1:     1   2    ace      7
2:     1   2    bob      8
3:     3   3  casey      2

非R方式是迭代每一行，过滤玩家和游戏号码，使dt中的游戏数大于开始，小于结束，然后将dt中的点数列和到一个新列中。

在R中做这件事的最好方法是什么？

Answer 1

您可以在data.table中使用非等联接，然后在范围内与重叠的points相加。

library(data.table)

dt[out, .(start, end, game, player, points), 
         on=.(player, game>=start, game<=end)][
       , .(points = sum(points)), by = .(start, end, player)]

#   start end player points
#1:     1   2    ace      7
#2:     1   2    bob      8
#3:     3   3  casey      2