Ответ 1

Присоедините data.table к своему подмножеству по группам, чтобы получить значения из строк, соответствующих неравным критериям.

Резюме:

• Ниже я показываю 5 рабочих решений data.table которые были кандидатами на тестирование производительности в сравнении с фактическим набором данных OP (data.table записей).

• Все 5 решений используют "неравные" объединения (используя неравенство для сравнения столбцов для объединения) в предложении on.

• Каждое решение - это всего лишь небольшое постепенное изменение кода, поэтому следует легко следить за ним, сравнивая различные параметры data.table и синтаксиса.

Подход

Для работы с синтаксисом data.table для этого я разбил его на следующие шаги для проблемы OP:

1. Присоедините dt к своему подмножеству (или к другой таблице данных).
2. Выберите (и переименуйте) нужные столбцы из dt или из подмножества.
3. Определите критерии объединения на основе столбцов из dt по сравнению со столбцами в подмножестве, в том числе с использованием "неравных" (неравных) сравнений.
4. При желании можно указать, будет ли выбрано первое или последнее совпадение, если в подмножестве найдено несколько совпадающих записей.

Решение 1:

# Add row numbers to all records in dt (only because you 
# have criteria based on comparing sequential rows):
dt[, row := .I] 

# Compute result columns (  then standard assignment into dt using <-  )
dt$found_date  <- 
            dt[code=='p'][dt,   # join dt to the data.table matching your criteria, in this case dt[code=='p']
                          .( x.date_up ),   # columns to select, x. prefix means columns from dt[code=='p'] 
                          on = .(id==id, row > row, date_up > date_down),   # join criteria: dt[code=='p'] fields on LHS, main dt fields on RHS
                          mult = "first"]   # get only the first match if multiple matches

Обратите внимание на выражения объединения выше:

• i в этом случае ваш главный дт. Таким образом, вы получите все записи из вашего основного data.table.
• x - это подмножество (или любой другой data.table), из которого вы хотите найти совпадающие значения.

Результат соответствует запрошенному выводу:

dt

    id code  date_down    date_up row found_date
 1:  1    p 2019-01-01 2019-01-02   1       <NA>
 2:  1    f 2019-01-02 2019-01-03   2       <NA>
 3:  2    f 2019-01-02 2019-01-02   3       <NA>
 4:  2    p 2019-01-03       <NA>   4       <NA>
 5:  3    p 2019-01-04       <NA>   5       <NA>
 6:  4 <NA> 2019-01-05 2019-01-05   6       <NA>
 7:  5    f 2019-01-07 2019-01-08   7 2019-01-08
 8:  5    p 2019-01-07 2019-01-08   8 2019-01-09
 9:  5    p 2019-01-09 2019-01-09   9       <NA>
10:  6    f 2019-01-10 2019-01-10  10 2019-01-11
11:  6    p 2019-01-10 2019-01-10  11 2019-01-11
12:  6    p 2019-01-10 2019-01-11  12       <NA>

Примечание. Вы можете удалить столбец row, выполнив команду dt[, row := NULL] если хотите.

Решение 2:

Идентичная логика, как и выше, используется для объединения и поиска столбцов результата, но теперь используется "назначение по ссылке" := для создания found_date в dt:

dt[, row := .I] # add row numbers (as in all the solutions)

# Compute result columns (  then assign by reference into dt using :=  

# dt$found_date  <- 
dt[, found_date :=   # assign by reference to dt$found_date 
            dt[code=='p'][dt, 
                          .( x.date_up ), 
                          on = .(id==id, row > row, date_up > date_down),
                          mult = "first"]]

В решении 2 небольшое изменение для назначения наших результатов "по ссылке" в dt должно быть более эффективным, чем в решении 1. В решении 1 результаты вычисляются точно так же - единственное отличие состоит в том, что в решении 1 используется стандартное присвоение <- для создания dt$found_date (менее эффективны).

Решение 3:

Как и в решении 2, но теперь используется .(.SD) вместо dt для ссылки на исходный dt без непосредственного присвоения ему имени.

dt[, row := .I] # add row numbers (as in all the solutions)
setkey(dt, id, row, date_down)  #set key for dt 

# For all rows of dt, create found_date by reference :=
dt[, found_date := 
            # dt[code=='p'][dt, 
            dt[code=='p'][.(.SD),   # our subset (or another data.table), joined to .SD (referring to original dt)
                          .( x.date_up ), 
                          on = .(id==id, row > row, date_up > date_down),  
                          mult = "first"] ]  

.SD выше ссылается на исходный dt, который мы назначаем обратно. Это соответствует подмножеству data.table, которое содержит строки, выбранные в первом dt[, который является всеми строками, потому что мы не фильтровали его.

Примечание. В решении 3 я использовал setkey() для установки ключа. Я должен был сделать это в Solution 1 и Solution 2 - однако я не хотел менять эти решения после того, как @OllieB успешно их протестировал.

Решение 4:

Как и в решении 3, но с использованием .SD еще раз, чем ранее. Наше основное имя data.table dt теперь появляется только один раз во всем нашем выражении!

# add row column and setkey() as previous solutions

dt[, found_date :=
            # dt[code=='p'][.(.SD), 
            .SD[code=='p'][.SD,   # .SD in place of dt at left!  Also, removed .() at right (not sure on this second change)
                           .(found_date = x.date_up),
                           on = .(id==id, row > row, date_up > date_down),
                           mult = "first"]]

С изменением выше наше имя data.table dt появляется только один раз. Мне это очень нравится, потому что это позволяет легко копировать, адаптировать и использовать в других местах.

Также обратите внимание: где я раньше использовал .(SD) Я теперь удалил.() Вокруг .SD потому что это, кажется, не требует. Однако для этого изменения я не уверен, имеет ли он какое-либо преимущество в производительности или предпочтительный синтаксис data.table. Я был бы признателен, если кто-нибудь может добавить комментарий, чтобы проконсультировать по этому вопросу.

Решение 5:

Подобно предыдущим решениям, но с использованием by для явной группировки подмножеств по операциям при объединении

# add row column and setkey() as previous solutions

dt[, found_date :=
       .SD[code=='p'][.SD,
                      .(found_date = x.date_up),
                      # on = .(id==id, row > row, date_up > date_down),
                      on = .(row > row, date_up > date_down),  # removed the id column from here
                      mult = "first"]
   , by = id]   # added by = id to group the .SD subsets 

В этом последнем решении я изменил его, чтобы использовать предложение by для явной группировки подмножеств .SD по id.

Примечание. Решение 5 не показало хороших результатов по сравнению с фактическими данными OllieB по сравнению с решениями 1-4. Однако, проверяя мои собственные фиктивные данные, я обнаружил, что решение 5 может работать хорошо, когда число уникальных групп в столбце id было низким:
- Имея всего 6 групп по 1,5 млн. Записей, это решение работало так же быстро, как и другие.
- С 40К группами в 1,5М записях я видел такую же низкую производительность, как и ОллиБ.

Результаты

Решения 1 - 4 показали хорошие результаты:

• Для 1,45M записей в фактических данных OllieB каждое из решений с 1 по 4 составляло "истекшее" время 2,42 секунды или менее согласно обратной связи OllieB. Решение 3, похоже, работает быстрее всего для OllieB, имеющего "elapsed = 1.22" секунды.

• Я лично предпочитаю Решение 4 из-за более простого синтаксиса.

Решение 5

• Решение 5 ( с использованием by п) малоэффективное с 577 секунд для тестирования OllieB на его реальных данных.

версия data.table: 1.12.0

Версия 3.5.3 (2019-03-11)

Возможные дальнейшие улучшения:

• Изменение полей даты на целое может помочь объединить более эффективно. Смотрите as.IDate(), чтобы преобразовать даты в целое число в data.tables.
• Шаг SetKey() могут больше не нужны пчелы: Как объяснено здесь @Arun из - за on envoking [часто] более эффективных вторичных indicies и автоматической индексации.

Ссылки на data.table

В рамках вашего вопроса вы задали "любые хорошие ссылки на data.table". Я нашел следующее полезное:

• data.table Начало работы Вики на GitHub - это место для начала.

• В частности, для этой проблемы стоит прочитать:

  • Что означает .SD в data.table в R
  • HTML-виньетка для вторичных индексов и автоматической индексации

Важно отметить, что ответ @Arun, объясняющий "причину реализации аргумента on =", предполагает, что больше нет необходимости устанавливать ключи:

Поэтому важно выяснить, стоит ли время, затрачиваемое на переупорядочение всей таблицы данных, эффективным для кеширования объединения/агрегирования. Обычно, если нет повторяющихся операций группировки/объединения, выполняемых с одним и тем же ключом data.table, заметного различия не должно быть.

Поэтому в большинстве случаев больше не нужно устанавливать ключи. Мы рекомендуем использовать on = везде, где это возможно, если только клавиша настройки не приводит к значительному улучшению производительности, которую вы хотели бы использовать.

• Этот вопрос SO, кажется, является центром информации о различных соединениях data.table: Как объединить (объединить) фреймы данных (внутренний, внешний, левый, правый)?

• И, наконец, шпаргалка data.table - отличная ссылка (по ссылке, найденной на data.table. Начало работы с Wiki на GitHub).

Как всегда, я благодарен, если у кого-то есть предложения, возможно, это можно улучшить в дальнейшем.

Пожалуйста, не стесняйтесь комментировать, исправлять или публиковать другие решения, если вы можете добавить что-нибудь.

Ответ 2

Подход "не таблица данных":

> df <- structure(list(
+   id        = c(1L, 1L, 2L, 2L, 3L, 4L, 5L, 5L, 5L, 6L, 6L, 6L),
+   code      = c("p", "f", "f", "p", "p", "<NA>", "f", "p", "p", "f", "p", "p"),
+   date_down = structure(c(17897, 17898, 17898, 17899, 17900, 17901, 17903, 17903, 17905, 17906, 17906, 17906), class = "Date"),
+   date_up   = structure(c(17898, 17899, 17898, NA, NA, 17901, 17904, 17904, 17905, 17906, 17906, 17907), class = "Date")),
+   class     = c("data.frame"),
+   row.names = c(NA, -12L))
> 
> 
> Lista <- lapply(split(df, df$id), function(x){
+   x$founddate <- 
+     sapply(c(1:nrow(x)), function(y){
+       na.omit(sapply(y:nrow(x), function(i){
+         ifelse(x[i + 1, "code"] == "p" & x[i + 1, "date_up"] > x[y, "date_down"],
+                x[i + 1, "date_up"], NA)
+       }))[1]
+     })
+   x$founddate <- as.Date(x$founddate, origin = "1970-01-01")
+   return(x)
+ })
> 
> 
> df <- do.call(rbind.data.frame, Lista)
> 
> df
     id code  date_down    date_up  founddate
1.1   1    p 2019-01-01 2019-01-02       <NA>
1.2   1    f 2019-01-02 2019-01-03       <NA>
2.3   2    f 2019-01-02 2019-01-02       <NA>
2.4   2    p 2019-01-03       <NA>       <NA>
3     3    p 2019-01-04       <NA>       <NA>
4     4 <NA> 2019-01-05 2019-01-05       <NA>
5.7   5    f 2019-01-07 2019-01-08 2019-01-08
5.8   5    p 2019-01-07 2019-01-08 2019-01-09
5.9   5    p 2019-01-09 2019-01-09       <NA>
6.10  6    f 2019-01-10 2019-01-10 2019-01-11
6.11  6    p 2019-01-10 2019-01-10 2019-01-11
6.12  6    p 2019-01-10 2019-01-11       <NA>
> 

При данных условиях в каждой строке более одного совпадения. Предложенный ответ получает первое совпадение, но это можно изменить.

Надеюсь, поможет.

Ответ 3

Вот быстрый и грязный способ, который не требует больших размышлений с вашей стороны, и фиксирует первый жизнеспособный вариант в подмножестве и оставляет NA если он не существует.

вызов do(f(.)) оценивает предопределенную функцию f в каждом подмножестве dt определенном выражением group_by. Я хотел бы перевести этот простой скрипт в Rcpp для серьезного использования.

library(dplyr)
f <- function(x){
  x <- x %>% mutate(founddate = as.Date(NA))

  for(i in 1:nrow(x)){
    y <- x[i, "date_down"]
    x[i, "founddate"] <-(x[-c(1:i),] %>% filter(code == "p", date_up > y) %>% select(date_up))[1, ]
  }

  return(x)
}

dt %>% group_by(id) %>% do(f(.))

# A tibble: 12 x 5
# Groups:   id [6]
      id code  date_down  date_up    founddate 
   <int> <chr> <date>     <date>     <date>    
 1     1 p     2019-01-01 2019-01-02 NA        
 2     1 f     2019-01-02 2019-01-03 NA        
 3     2 f     2019-01-02 2019-01-02 NA        
 4     2 p     2019-01-03 NA         NA        
 5     3 p     2019-01-04 NA         NA        
 6     4 <NA>  2019-01-05 2019-01-05 NA        
 7     5 f     2019-01-07 2019-01-08 2019-01-08
 8     5 p     2019-01-07 2019-01-08 2019-01-09
 9     5 p     2019-01-09 2019-01-09 NA        
10     6 f     2019-01-10 2019-01-10 2019-01-11
11     6 p     2019-01-10 2019-01-10 2019-01-11
12     6 p     2019-01-10 2019-01-11 NA 

Ваш комментарий о ужасной производительности неудивителен. Если бы я знал, как это сделать, я бы Rcpp::cppFunction личное сообщение, но ниже приведена Rcpp::cppFunction которая делает то же самое.

Rcpp::cppFunction('DataFrame fC(DataFrame x) {
                    int i, j;
                    int n = x.nrows();
                    CharacterVector code = x["code"];
                    DateVector date_up = x["date_up"];
                    DateVector date_down = x["date_down"];
                    DateVector founddate = rep(NA_REAL, n);

                    for(i = 0; i < n; i++){
                      for(j = i + 1; j < n; j++){
                        if(code(j) == "p"){
                          if(date_up(j) > date_down(i)){
                            founddate(i) = date_up(j);
                            break;
                          } else{
                            continue;
                          }
                        } else{
                          continue;
                        }
                      }
                    }
                    x.push_back(founddate, "founddate");
                    return x;
                    }')

dt %>% group_by(id) %>% do(fC(.))