以下是我的dataframe/data.table的样子。rank列是我所需的计算字段。
library(data.table)
df <- fread('
Name Score Date Rank
John 42 1/1/2018 3
Rob 85 12/31/2017 2
Rob 89 12/26/2017 1
Rob 57 12/24/2017 1
Rob 53 08/31/2017 1
Rob 72 05/31/2017 2
Kate 87 12/25/2017 1
Kate 73 05/15/2017 1
')
df[,Date:= as.Date(Date, format="%m/%d/%Y")]
我正在尝试计算每个学生在 30 天窗口内数据中每个给定时间点的排名。为此，我需要获取给定时间点所有学生的最新分数，然后传递排名函数。
在第 1 行中，截至1/1/2018年，John在过去 30 天窗口中还有两个竞争对手：Rob 在12/31/2017中的最新得分为85，凯特在12/25/2017中的最新得分为87，这两个日期都在1/1/2018 - 30日窗口内。约翰获得3分最低的42分。如果只有一个学生在date(at a given row) - 30 day window范围内，则排名为1。
在第 3 行中，日期是12/26/2017。所以罗布截至12/26/2017的得分是89。只有一个学生的案例落在12/26/2017 - 30天的时间窗口内，那就是凯特在12/25/2017上的最新分数(87)。所以在(12/26/2017) - 30的时间窗口内，罗伯的89分高于凯特的87分，因此罗伯的排名1。
我正在考虑从这里使用框架 在过去的 365 天窗口中执行运行总计的有效方法，但在使用排名之前，很难想出一种方法来获取给定时间点所有学生的所有最近分数。
这似乎有效：
ranks = df[.(d_dn = Date - 30L, d_up = Date), on=.(Date >= d_dn, Date <= d_up), allow.cart=TRUE][,
.(LatestScore = last(Score)), by=.(Date = Date.1, Name)]
setorder(ranks, Date, -LatestScore)
ranks[, r := rowid(Date)]
df[ranks, on=.(Name, Date), r := i.r]
Name Score Date Rank r
1: John 42 2018-01-01 3 3
2: Rob 85 2017-12-31 2 2
3: Rob 89 2017-12-26 1 1
4: Rob 57 2017-12-24 1 1
5: Rob 53 2017-08-31 1 1
6: Rob 72 2017-05-31 2 2
7: Kate 87 2017-12-25 1 1
8: Kate 73 2017-05-15 1 1
。使用last，因为笛卡尔连接似乎可以排序，我们想要最新的测量值。
更新联接的工作原理
i.前缀表示它是x[i, ...]联接中i的列，并且分配:=始终处于x中。因此，它会查找x中的每一行i以及找到匹配项的位置，将值从i复制到x。
另一种有时有用的方法是在i中查找x行，类似于df[, r := ranks[df, on=.(Name,Date), x.r]]在这种情况下，x.r仍然来自ranks表(现在相对于连接处于x位置)。
还有...
ranks = df[CJ(Name = Name, Date = Date, unique=TRUE), on=.(Name, Date), roll=30, nomatch=0]
setnames(ranks, "Score", "LatestScore")
# and then use the same last three lines above
我不确定一个与另一个的效率，但我想这取决于名称的数量、测量频率以及测量日重合的频率。
使用data.table但不确定它是否是最有效的用法的解决方案：
df[.(iName=Name, iScore=Score, iDate=Date, StartDate=Date-30, EndDate=Date),
.(Rank=frank(-c(iScore[1L], .SD[Name != iName, max(Score), by=.(Name)]$V1),
ties.method="first")[1L]),
by=.EACHI,
on=.(Date >= StartDate, Date <= EndDate)]
解释：
1) 外部方括号在日期范围内(即 30 天前和每行的最新日期)进行非等值连接。尝试根据输入数据研究以下输出：
df[.(iName=Name, iScore=Score, iDate=Date, StartDate=Date-30, EndDate=Date),
c(.(RowGroup=.GRP),
.SD[, .(Name, Score, Date, OrigDate, iName, iScore, iDate, StartDate, EndDate)]),
by=.EACHI,
on=.(Date >= StartDate, Date <= EndDate)]
2).EACHI是对每一行i进行j计算。
3)在j，iScore[1L]是当前行的分数，.SD[Name != iName]表示取与当前行中学生不对应的分数。然后，我们在 30 天内为这些学生的每个学生使用max(Score)。
4)连接所有这些分数并计算当前行分数的排名，同时通过采取第一个平局来处理平局。
注意：
请参阅?data.table以了解i、j、by、on和.EACHI所指的内容。
OP评论后编辑：
我会添加一个 OrigDate 列并找到与最新日期匹配的列。
df[, OrigDate := Date]
df[.(iName=Name, iScore=Score, iDate=Date, StartDate=Date-30, EndDate=Date),
.(Name=iName, Score=iScore, Date=iDate,
Rank=frank(-c(iScore[1L],
.SD[Name != iName, Score[OrigDate==max(OrigDate)], by=.(Name)]$V1),
ties.method="first")[1L]),
by=.EACHI,
on=.(Date >= StartDate, Date <= EndDate)]
我想出了以下部分解决方案，但是遇到了问题 - 是否有可能有两个人在同一日期发生？
如果没有，请查看以下代码段：
library(tidyverse) # easy manipulation
library(lubridate) # time handling
# This function can be added to
get_top <- function(df, date_sel) {
temp <- df %>%
filter(Date > date_sel - months(1)) %>% # look one month in the past from given date
group_by(Name) %>% # and for each occuring name
summarise(max_score = max(Score)) %>% # find the maximal score
arrange(desc(max_score)) %>% # sort them
mutate(Rank = 1:n()) # and rank them
temp
}
现在，您必须在表中找到给定日期的名称并返回其排名。
library(data.table)
library(magrittr)
setorder(df, -Date)
fun <- function(i){
df[i:nrow(df), head(.SD, 1), by = Name] %$%
rank(-Score[Date > df$Date[i] - 30])[1]
}
df[, rank := sapply(1:.N, fun)]
这可以通过联df那些比它晚 30 天或同一日期且分数更高或相等的df行来完成。 然后，对于每个原始行和联接行名称，获取最新的联接行名称。 每个原始df行的剩余联接行数是排名。
library(sqldf)
sqldf("with X as
(select a.rowid r, a.*, max(b.Date) Date
from df a join df b
on b.Date between a.Date - 30 and a.Date and b.Score >= a.Score
group by a.rowid, b.Name)
select Name, Date, Score, count(*) Rank
from X
group by r
order by r")
给：
Name Date Score Rank
1 John 2018-01-01 42 3
2 Rob 2017-12-31 85 2
3 Rob 2017-12-26 89 1
4 Rob 2017-12-24 57 1
5 Rob 2017-08-31 53 1
6 Rob 2017-05-31 72 2
7 Kate 2017-12-25 87 1
8 Kate 2017-05-15 73 1
tidyverse解决方案(dplyr+tidyr)：
df %>%
complete(Name,Date) %>%
group_by(Name) %>%
mutate(last_score_date = `is.na<-`(Date,is.na(Score))) %>%
fill(Score,last_score_date) %>%
filter(!is.na(Score) & Date-last_score_date <30) %>%
group_by(Date) %>%
mutate(Rank = rank(-Score)) %>%
right_join(df)
# # A tibble: 8 x 5
# # Groups: Date [?]
# Name Date Score last_score_date Rank
# <chr> <date> <int> <date> <dbl>
# 1 John 2018-01-01 42 2018-01-01 3
# 2 Rob 2017-12-31 85 2017-12-31 2
# 3 Rob 2017-12-26 89 2017-12-26 1
# 4 Rob 2017-12-24 57 2017-12-24 1
# 5 Rob 2017-08-31 53 2017-08-31 1
# 6 Rob 2017-05-31 72 2017-05-31 2
# 7 Kate 2017-12-25 87 2017-12-25 1
# 8 Kate 2017-05-15 73 2017-05-15 1
- 我们添加了所有缺少的
Date和Name组合 - 然后我们为
last_score_date创建一个列，当分数不是 NA 时等于Date。 - 通过填充 NA 下来 分数已成为最新分数
- 我们过滤掉 NA，只保留年龄<30 天的分数
- 这是我们按日期划分的有效分数表
- 从那里很容易添加等级
- 原始表上的最终right_join为我们提供了预期的输出
数据
library(data.table)
df <- fread('
Name Score Date
John 42 01/01/2018
Rob 85 12/31/2017
Rob 89 12/26/2017
Rob 57 12/24/2017
Rob 53 08/31/2017
Rob 72 05/31/2017
Kate 87 12/25/2017
Kate 73 05/15/2017
')
df[,Date:= as.Date(Date, format="%m/%d/%Y")]