изменить новый столбец на основе последовательности отметок времени в dbplyr

У меня есть набор данных из базы данных PostgreSQL, в которой есть два столбца:

id = идентификатор участника

time_stamp = отметка времени записанного измерения

Мне нужно работать с dbplyr, чтобы изменить новый столбец на основе последовательности time_stamp. Другими словами, если time_stamp происходит последовательно (то есть с интервалом одна минута), это распознается как одно событие.

Например, это мой набор данных:

library(dplyr)
library(dbplyr)
library(lubridate)

mf <- memdb_frame(
  id = "id001", 
  time_stamp = c(
    seq(from = as_datetime("2021-01-01 08:00:00"), to = as_datetime("2021-01-01 08:03:00"), by = "1 min"),
    seq(from = as_datetime("2021-01-01 08:05:00"), to = as_datetime("2021-01-01 08:08:00"), by = "1 min"),
    seq(from = as_datetime("2021-01-01 08:12:00"), to = as_datetime("2021-01-01 08:18:00"), by = "1 min")
  )
)

mf %>% 
  collect() %>% 
  mutate(time_stamp = as_datetime(time_stamp))

#> # A tibble: 15 x 2
#>    id    time_stamp         
#>    <chr> <dttm>             
#>  1 id001 2021-01-01 08:00:00
#>  2 id001 2021-01-01 08:01:00
#>  3 id001 2021-01-01 08:02:00
#>  4 id001 2021-01-01 08:03:00
#>  5 id001 2021-01-01 08:05:00
#>  6 id001 2021-01-01 08:06:00
#>  7 id001 2021-01-01 08:07:00
#>  8 id001 2021-01-01 08:08:00
#>  9 id001 2021-01-01 08:12:00
#> 10 id001 2021-01-01 08:13:00
#> 11 id001 2021-01-01 08:14:00
#> 12 id001 2021-01-01 08:15:00
#> 13 id001 2021-01-01 08:16:00
#> 14 id001 2021-01-01 08:17:00
#> 15 id001 2021-01-01 08:18:00

Теперь мне нужно распознать события. Это означает, что нужно найти time_stamps, которые произошли в последовательности (последовательность = интервал в 1 минуту). Например, вот мой ожидаемый результат:

#> # A tibble: 15 x 3
#>    id    time_stamp          events 
#>    <chr> <dttm>              <chr>  
#>  1 id001 2021-01-01 08:00:00 event_1
#>  2 id001 2021-01-01 08:01:00 event_1
#>  3 id001 2021-01-01 08:02:00 event_1
#>  4 id001 2021-01-01 08:03:00 event_1
#>  5 id001 2021-01-01 08:05:00 event_2
#>  6 id001 2021-01-01 08:06:00 event_2
#>  7 id001 2021-01-01 08:07:00 event_2
#>  8 id001 2021-01-01 08:08:00 event_2
#>  9 id001 2021-01-01 08:12:00 event_3
#> 10 id001 2021-01-01 08:13:00 event_3
#> 11 id001 2021-01-01 08:14:00 event_3
#> 12 id001 2021-01-01 08:15:00 event_3
#> 13 id001 2021-01-01 08:16:00 event_3
#> 14 id001 2021-01-01 08:17:00 event_3
#> 15 id001 2021-01-01 08:18:00 event_3

Обратите внимание, что от строки 4 до 5 был интервал 2 минуты, что привело к запуску следующего события. То же самое от строки 8 до 9: был интервал 4 минуты, а затем началось следующее событие.

PS: мне нужно, чтобы он полноценно работал в dbplyr, то есть: без использования collect()

Любые идеи будут очень признательны!

Благодарю вас!


mysql r postgresql dplyr dbplyr
person FMM    schedule 21.01.2021    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Работа без collect означает, что мы ограничены в основном функциями dplyr, поскольку это функции, для которых определены преобразования SQL.

Очень похоже на @Sinh_Nguyen, я бы предложил следующее:

output = mf %>%
  group_by(id) %>%
  arrange(time_stamp) %>%
  mutate(prev_time_stamp = lag(time_stamp, 1)) %>%
  mutate(hours_diff = DATEPART('hour', time_stamp - prev_time_stamp),
         min_part_diff = DATEPART('minute', time_stamp - prev_time_stamp)) %>%
  mutate(gap = hours_diff * 60 + min_part_diff) %>%
  mutate(is_gap = ifelse(is.na(prev_time_stamp) | gap == 1, 0, 1)) %>%
  mutate(event_index = cumsum(is_gap))

Примечания:

  • group_by и arrange встречаются один раз в начале, но неявно используются функциями lag и cumsum.
  • Если в dbplyr не определен перевод, команда передается как есть. Написание DATEPART заглавными буквами гарантирует, что оно не будет переведено, поэтому мы получаем функцию PostgreSQL DATEPART. Я обычно использую SQL-сервер, поэтому я следую этим примерам для расчета разница в PostgreSQL.
  • Идея последних двух строк заключается в создании бинарного индикатора, когда запись не является продолжением предыдущего события. Суммирование этих индикаторов увеличивает счетчик событий для каждого нового события.
  • Если есть ошибки, вы можете использовать show_query(output) для просмотра перевода SQL. Просмотр/предоставление доступа к переводу SQL часто помогает при устранении неполадок.
person Simon.S.A.    schedule 22.01.2021
comment
фантастический ответ!!! Мне нужно было изменить только часть DATEPART, вместо которой я использовал функции hour() и minute() из lubridate. - person FMM; 22.01.2021

arrow_upward
1
arrow_downward

Вот код, использующий логику 1 mins разницы, означающей 1 событие.

mf %>% 
  collect() %>% 
  mutate(time_stamp = as_datetime(time_stamp)) %>%
  mutate(diff_mins = difftime(time_stamp, lag(time_stamp, 1), units = "mins")) %>%
  mutate(event_count = if_else(diff_mins == 1 | is.na(diff_mins), 0, 1)) %>%
  mutate(event_index = paste0("event_", cumsum(event_count) + 1))

Выход:

# A tibble: 15 x 5
   id    time_stamp          diff_mins event_count event_index
   <chr> <dttm>              <drtn>          <dbl> <chr>      
 1 id001 2021-01-01 08:00:00 NA mins             0 event_1    
 2 id001 2021-01-01 08:01:00  1 mins             0 event_1    
 3 id001 2021-01-01 08:02:00  1 mins             0 event_1    
 4 id001 2021-01-01 08:03:00  1 mins             0 event_1    
 5 id001 2021-01-01 08:05:00  2 mins             1 event_2    
 6 id001 2021-01-01 08:06:00  1 mins             0 event_2    
 7 id001 2021-01-01 08:07:00  1 mins             0 event_2    
 8 id001 2021-01-01 08:08:00  1 mins             0 event_2    
 9 id001 2021-01-01 08:12:00  4 mins             1 event_3    
10 id001 2021-01-01 08:13:00  1 mins             0 event_3    
11 id001 2021-01-01 08:14:00  1 mins             0 event_3    
12 id001 2021-01-01 08:15:00  1 mins             0 event_3    
13 id001 2021-01-01 08:16:00  1 mins             0 event_3    
14 id001 2021-01-01 08:17:00  1 mins             0 event_3    
15 id001 2021-01-01 08:18:00  1 mins             0 event_3
person Sinh Nguyen    schedule 21.01.2021
comment
спасибо за хороший ответ - однако мне нужно, чтобы он работал с dbplyr, то есть без использования collect() - person FMM; 22.01.2021
comment
Что вы подразумеваете под работой с dbplyr - вы имеете в виду не использовать dplyr или что? - person Sinh Nguyen; 22.01.2021