Объедините два набора данных на основе значений POSIXct

Я изо всех сил пытаюсь объединить два набора данных друг с другом.

Dataset1 содержит «до времени», «после времени» и «канал».

Dataset2 содержит только один столбец «время» и «канал».

Я хочу добавить двоичный столбец (Да / Нет) в набор данных 1, используя следующую логику: если в наборе данных 2 есть строка, где канал == канал, а время находится в пределах времени «до» и «после», я хочу иметь "ДА". Иначе «НЕТ».

Данные1

ID   Channel   before_time   after_time 
1       A1  2019-09-02 20:13:00 2019-09-02 20:33:00
2       B1  2019-09-02 20:03:00 2019-09-02 20:23:00
3       C1  2019-09-02 20:23:00 2019-09-02 20:43:00
4       D1  2019-09-02 20:23:00 2019-09-02 20:43:00

Данные2

ID_B     Channel_B    Time_B
Hallo       A1        2019-09-02 20:23:00
Hi          B2        2019-09-02 20:05:00
Hoi         C1        2019-09-02 22:23:00

Желаемый результат

ID   Channel   before_time   after_time                     Available
1       A1  2019-09-02 20:13:00 2019-09-02 20:33:00         Yes  # Channel == Channel, Time between before & after
2       B1  2019-09-02 20:03:00 2019-09-02 20:23:00          No  # Channel != Channel
3       C1  2019-09-02 20:23:00 2019-09-02 20:43:00          No  # Time is not between before & after
4       D1  2019-09-02 20:23:00 2019-09-02 20:43:00          No  # There is no matching data where channel is D1

Желаемый результат 2 (комментарии к решениям)

Добавление дополнительных столбцов из второго набора данных (Data2).

ID   Channel   before_time   after_time                     Available   ID_B     
1       A1  2019-09-02 20:13:00 2019-09-02 20:33:00          Yes        Hallo       
2       B1  2019-09-02 20:03:00 2019-09-02 20:23:00          No         x 
3       C1  2019-09-02 20:23:00 2019-09-02 20:43:00          No         x
4       D1  2019-09-02 20:23:00 2019-09-02 20:43:00          No         x

Воспроизводимый пример (данные):

ID <- c("1", "2", "3", "4")
channel <- c("A1", "B1", "C1", "D1)
#startdate <- as.POSIXct(c("2019-09-02 20:23:00", "2019-09-02 20:13:00", "2019-09-02 20:33:00", "2019-09-02 20:33:00"))
before_time <- as.POSIXct(c("2019-09-02 20:13:00", "2019-09-02 20:03:00", "2019-09-02 20:23:00", "2019-09-02 20:23:00"))
after_time  <- as.POSIXct(c("2019-09-02 20:33:00", "2019-09-02 20:23:00", "2019-09-02 20:43:00","2019-09-02 20:43:00"))
data1 <- data.frame(ID, channel,   before_time, after_time)
View(data1)


ID_B <- c("Hallo", "Hi", "Hoi")
channel_B <- c("A1", "B2", "C1")
Time_B <- as.POSIXct(c("2019-09-02 20:23:00", "2019-09-02 20:05:00", "2019-09-02 22:23:00"))
data2 <- data.frame(ID_B, channel_B, Time_B)
View(data2)

r data.table join dplyr posixct
person R overflow    schedule 13.02.2020    source источник
comment
Ваши реальные данные равны количеству строк?   -  person Armando González Díaz    schedule 13.02.2020
comment
Изучите неэквивалентные соединения, обновите соединения, это можно сделать с помощью data.table. Присоединение к dplyr (пока) не настолько продвинуто, но если вы выполните полное присоединение, а затем сравните даты, тоже должно работать.   -  person arg0naut91    schedule 13.02.2020
comment
@ ArmandoGonzálezDíaz реальные данные не имеют равного количества строк. Я изменил свой пример.   -  person R overflow    schedule 14.02.2020
comment
@ arg0naut91 ценю совет. Добавил тег data.table и рассмотрит полные соединения.   -  person R overflow    schedule 14.02.2020

Ответы (4)


arrow_upward
2
arrow_downward

Как упоминалось arg0naut91, здесь неэквивалентное соединение в data.table:

library(data.table)
setDT(data1)
setDT(data2)
data1[, c("Available", "ID_B") :=
        data2[.SD, on=.(channel_B=channel, Time_B>=before_time, Time_B<=after_time), 
            by=.EACHI, .(.N > 0, ID_B)][, (1L:3L) := NULL]
    ]

выход:

   ID channel         before_time          after_time Available  ID_B
1:  1      A1 2019-09-02 20:13:00 2019-09-02 20:33:00      TRUE Hallo
2:  2      B1 2019-09-02 20:03:00 2019-09-02 20:23:00     FALSE  <NA>
3:  3      C1 2019-09-02 20:23:00 2019-09-02 20:43:00     FALSE  <NA>
4:  4      D1 2019-09-02 20:23:00 2019-09-02 20:43:00     FALSE  <NA>
person chinsoon12    schedule 14.02.2020
comment
Отлично, @ chinsoon12. Однако, если я запустил ваш пример, я получаю сообщение об ошибке (или это я?): Ошибка в наборе (i, j = lc, value = newfactor): .SD заблокирован. Обновление .SD по ссылке с использованием: = или set зарезервировано для использования в будущем. Используйте: = в j напрямую. Или используйте копию (.SD) как (медленное) последнее средство, пока не будет экспортирован shallow (). - person R overflow; 14.02.2020
comment
вы можете использовать обновленную версию data.table? и если вы не можете обновить свою версию, вам нужно будет заменить .SD на copy(.SD) или data1 - person chinsoon12; 14.02.2020
comment
Ваше решение работает. Быстрый вопрос, поскольку у меня проблемы с синтаксисом DT: можно ли добавить столбцы из data2, если есть ИСТИНА (совпадение)? Например, добавление «ID_B» в выходной файл? - person R overflow; 14.02.2020
comment
Вы можете показать несколько примеров данных? Не перед компьютером сейчас - person chinsoon12; 14.02.2020
comment
Конечно, я обновил вопрос (примеры данных все еще есть). Это имеет для вас смысл? Спасибо! - person R overflow; 14.02.2020
comment
@Roverflow, я обновил код, чтобы добавить новый столбец - person chinsoon12; 15.02.2020

arrow_upward
1
arrow_downward

Вот базовое решение R с использованием merge + ifelse, т.е.

dfout <- subset(within(merge(data1,data2[-1],by.x = "channel",by.y = "channel_B",all.x = T), 
                       Available <- ifelse(!is.na(Time_B)& Time_B>=before_time & Time_B<=after_time,"Yes","No")),
                select = -Time_B)

такой, что

> dfout
  channel ID         before_time          after_time Available
1      A1  1 2019-09-02 20:13:00 2019-09-02 20:33:00       Yes
2      B1  2 2019-09-02 20:03:00 2019-09-02 20:23:00        No
3      C1  3 2019-09-02 20:23:00 2019-09-02 20:43:00        No
4      D1  4 2019-09-02 20:23:00 2019-09-02 20:43:00        No
person ThomasIsCoding    schedule 14.02.2020

arrow_upward
1
arrow_downward

Это решение должно работать:

df<-(cbind(data1,data2)

df<-df%>%mutate(ID=as.integer(ID),
        channel=as.character(channel),
        ID_B=as.character(ID_B),
        channel_B=as.character(channel_B))

df%>%
  mutate(available= ifelse(channel==channel_B & Time_B >= before_time & Time_B <= after_time, "yes","no"))%>%
  select(-ID_B,Time_B,-channel_B)


 # A tibble: 3 x 5
 ID channel before_time         after_time          available
 <int> <chr>   <dttm>              <dttm>              <chr>    
 1     1 A1      2019-09-02 20:13:00 2019-09-02 20:33:00 yes      
 2     2 B1      2019-09-02 20:03:00 2019-09-02 20:23:00 no       
 3     3 C1      2019-09-02 20:23:00 2019-09-02 20:43:00 no
person TobKel    schedule 13.02.2020
comment
Я пробовал это решение. Однако это приводит к: Ошибка: ожидается одно значение: [степень = 3]. - person R overflow; 14.02.2020
comment
Не могли бы вы загрузить решение, включая примеры данных, опубликованные выше? Я все еще получаю ошибки (хотя я изменил имена data1 на df и т. Д.). Спасибо заранее! - person R overflow; 14.02.2020

arrow_upward
1
arrow_downward

Также имеет смысл изучить sqldf, например:

library(sqldf)

sqldf('SELECT t1.ID, t1.channel, 
      t1.before_time, t1.after_time, 
      CASE WHEN t2.ID_B IS NULL THEN "No" ELSE "Yes" END Available 
      FROM data1 t1 LEFT JOIN data2 t2 ON t1.channel = t2.channel_B
      AND t2.Time_B BETWEEN t1.before_time AND t1.after_time')

Выход:

  ID channel         before_time          after_time Available
1  1      A1 2019-09-02 20:13:00 2019-09-02 20:33:00       Yes
2  2      B1 2019-09-02 20:03:00 2019-09-02 20:23:00        No
3  3      C1 2019-09-02 20:23:00 2019-09-02 20:43:00        No
4  4      D1 2019-09-02 20:23:00 2019-09-02 20:43:00        No

Вот еще один способ сделать это в dplyr:

library(dplyr)

data1 %>%
  left_join(data2,
            by = c('channel' = 'channel_B')
            ) %>%
  mutate(
    Available = ifelse(
      !is.na(Time_B) & Time_B >= before_time & Time_B <= after_time, 'Yes', 'No')
    ) %>%
  select(-ends_with('_B'))

Добавление дополнительной колонки:

# sqldf

sqldf('SELECT t1.ID, t1.channel, 
      t1.before_time, t1.after_time, 
      CASE WHEN t2.ID_B IS NULL THEN "No" ELSE "Yes" END Available,
      CASE WHEN t2.ID_B IS NULL THEN "x" ELSE t2.ID_B END ID_B
      FROM data1 t1 LEFT JOIN data2 t2 ON t1.channel = t2.channel_B
      AND t2.Time_B BETWEEN t1.before_time AND t1.after_time')

# dplyr

data1 %>%
  left_join(data2,
            by = c('channel' = 'channel_B')
  ) %>%
  mutate(
    Available = ifelse(
      !is.na(Time_B) & Time_B >= before_time & Time_B <= after_time, 'Yes', 'No'),
    ID_B = ifelse(
      Available == 'Yes', as.character(ID_B), 'x')
  ) %>%
  select(-Time_B)
person arg0naut91    schedule 14.02.2020