Ранжируйте самые последние результаты студентов за заданную дату - 30-дневное окно

Я придумал следующее частичное решение, но столкнулся с проблемой - возможно ли, что два человека будут встречаться с одной и той же датой?

если нет, взгляните на следующий фрагмент кода:

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
}

Теперь вам нужно найти имя в таблице на заданную дату и вернуть его рейтинг.

Решение, использующее 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 должен выполнять вычисления j для каждой строки i.

3) Внутри jiScore[1L] - это оценка для текущей строки, .SD[Name != iName] означает получение оценок, не соответствующих ученику в текущей строке. Затем мы используем max(Score) для каждого студента из этих студентов в течение 30 дней.

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(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 те ряды df, которые отстают в пределах 30 дней от него или на ту же дату и имеют более высокие или равные оценки. Затем для каждой исходной строки и имени объединенной строки получите имя объединенной строки, которое является самым последним. Количество оставшихся соединенных строк для каждой из исходных строк 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

Кажется, это работает:

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. означает, что это столбец из i в соединении x[i, ...], а назначение := всегда находится в x. Таким образом, он ищет каждую строку i в x и там, где обнаруживаются совпадения, копируя значения из i в x.

Другой способ, который иногда бывает полезен, - это поиск строк x в i, что-то вроде 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    

Я не уверен в эффективности одного по сравнению с другим, но думаю, это зависит от количества Имен, частоты измерений и того, как часто совпадают дни измерений.

Решение 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, равный Date, когда оценка не является NA.
• путем заполнения NAs Down Score превратился в рейтинг последний
• мы отфильтровываем НП и сохраняем только оценки возрастом менее 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")]