Объединение наборов данных на основе идентификатора и даты-R

Я пытаюсь добавить информацию из второго набора данных в свой первый на основе идентификатора и дат. Если идентификатор совпадает и «Дата» находится между «началом» и «концом», я хочу добавить значение для цвета в df1.

    df1
    ID Date 
    1  3/31/2017
    2  2/11/2016
    2  4/10/2016 
    3  5/15/2015

   df2
   ID  start      end        colour
    1   1/1/2000 3/31/2011    blue
    1   4/1/2011  6/4/2012    purple
    1   6/5/2012  3/31/2017   blue
    2   5/1/2014  3/31/2017   red
    3   1/12/2012  2/12/2014  purple

Чтобы получить такой результат:

    dat
    ID Date        colour
    1  3/31/2017   blue
    2  2/11/2016   red
    2  4/10/2016   red
    3  5/15/2015   NA

Что можно создать с помощью кода здесь:

library(lubridate)
df1 <- tibble(ID = c(1,2,2,3), Date = mdy(c("3/31/2017","2/11/2016","4/10/2016","5/15/2015")))
df2 <- tibble(ID = c(1,1,1,2,3), start = mdy(c("1/1/2000","4/1/2011","6/5/2012","5/1/2014","1/12/2012")), end = mdy(c("3/31/2011","6/4/2012","3/31/2017","3/31/2017","2/12/2014")), colour = c("blue", "purple", "blue", "red", "purple"))

Я использовал ответ на аналогичный вопрос: Проверка наличия даты между две даты в R и использовали приведенный ниже код:

    library(dplyr)
    dat <- inner_join(df1, df2, by = "ID")
    dat %>% rowwise() %>%
    mutate(match = ifelse(between(df1$Date, df2$start, df2$end), 1 , 0))%>%
    select(-c(df2$start, df2$end))%>%
    arrange(df1$Date, desc(match))%>%
    distinct(df1$Date)

и я получаю следующую ошибку:

Ошибка между (df1 $ Date, df2 $ start, df2 $ end): ожидается одно значение: [extension = 355368].

помощь?

Огромное спасибо!

Обновлять-

Большое спасибо всем за ваши ответы.

Я пробовал их все, но все окончательные наборы данных имеют другое количество строк, чем первый набор данных. Я не понимаю, что происходит. Данные, которые я опубликовал, составлены так, чтобы напоминать данные, с которыми я работаю. Есть ли дополнительные подробности, о которых я должен вам сообщить? Не знаю, с чего начать ...


r date inner-join dplyr mutate
person user3047435    schedule 26.10.2017    source источник
comment
Можете ли вы добавить в свой пост код, необходимый для создания образца фрейма данных, чтобы мы могли воспроизвести и исправить проблему? см. meta.stackexchange.com/a/191794/346447 или stackoverflow.com/q/5963269/5088194   -  person leerssej    schedule 26.10.2017

Ответы (4)


arrow_upward
1
arrow_downward

dplyr использует нестандартную оценку, поэтому вы можете сбросить все имена фреймов данных, $s и ваши код начинается в основном в правильном направлении. Кроме того, существует ряд неявных преобразований, необходимых для получения указанного вами фрейма данных, но приведенные ниже приведут вас туда.

dat <- 
    df1 %>% 
    inner_join(df2) %>%
    rowwise %>% 
    mutate(match = ifelse(between(Date, start, end), 1 , NA)) %>%
    arrange(ID, Date, desc(match)) %>%
    ungroup %>% 
    group_by(ID, Date) %>% 
    mutate(best = row_number(ID), 
           colour = if_else(is.na(match), NA_character_, colour)) %>%
    filter(best == 1) %>% 
    select(ID, Date, colour) 

> dat
    # A tibble: 4 x 3
    # Groups:   ID, Date [4]
         ID       Date colour
      <dbl>     <date>  <chr>
    1     1 2017-03-31   blue
    2     2 2016-02-11    red
    3     2 2016-04-10    red
    4     3 2015-05-15   <NA>
person leerssej    schedule 26.10.2017

arrow_upward
2
arrow_downward

Кажется, ваш фрейм данных большой, вы можете попробовать data.table non-equi join, чтобы сделать это эффективным способом:

library(lubridate)
library(data.table)

setDT(df1); setDT(df2)
df1[, Date := mdy(Date)]
df2[, c("start", "end") := .(mdy(start), mdy(end))]

df2[df1, .(ID = i.ID, Date = i.Date, colour), on=.(ID, start <= Date, end >= Date)]

#   ID       Date colour
#1:  1 2017-03-31   blue
#2:  2 2016-02-11    red
#3:  2 2016-04-10    red
#4:  3 2015-05-15     NA
person Psidom    schedule 26.10.2017
comment
Спасибо. Я пробовал это, но ничего не произошло, и я не получаю ошибки. - person user3047435; 28.10.2017
comment
Возможно, это проблема с версией data.table. Какая у вас data.table версия? Попробуйте обновить его. - person Psidom; 28.10.2017
comment
С какой версией вы работаете? У меня установлена ​​версия 1.10.4-2. Похоже, есть более поздняя версия (cran.r-project. org / web / packages / data.table / index.html), но он не обновляется, когда я пытаюсь выполнить обновление. - person user3047435; 28.10.2017
comment
У меня такая же версия, как и у вас. Ничего не произошло, и никакая ошибка не кажется мне странной. А может вам нужно присвоить результат переменной dat? dat <- df2[df1, .(ID = i.ID, Date = i.Date, colour), on=.(ID, start <= Date, end >= Date)] - person Psidom; 28.10.2017
comment
о да конечно. Теперь это работает, но у меня больше строк, чем в исходном наборе данных. - person user3047435; 28.10.2017
comment
Тогда это означает, что есть несколько совпадений для некоторых строк в df1, возможно, есть совпадения start-end в df2 для некоторых ID. Трудно сказать, не глядя на реальный набор данных. - person Psidom; 28.10.2017
comment
Большое спасибо, между start-end в df2 было перекрытие. Я успешно объединил данные, но когда я обращаюсь к цвету в моем новом наборе данных, R обращается к столбцу исходного цвета в df2. Как я могу это исправить? - person user3047435; 30.10.2017
comment
Я тебя не понял. Что вы имеете в виду, R в конечном итоге ссылается на столбец исходного цвета в df2? - person Psidom; 30.10.2017
comment
Например, я использую таблицу, чтобы увидеть количество каждой категории в новом наборе данных, но в итоге получаю список всех цветов, которые были в исходном наборе данных, но не обязательно в окончательном наборе данных. Счетчик равен 0, но эти другие категории даже не должны отображаться. - person user3047435; 30.10.2017
comment
Скорее всего, ваш столбец цвета является фактором, который сохраняет все свои уровни во время объединения, попробуйте table(as.character(new_data$colour)) - person Psidom; 30.10.2017

arrow_upward
1
arrow_downward

Я воспроизвел ваш пример и дал ему одно решение.

library(tidyverse)
library(lubridate)

df1 <- data.frame(ID=c(1, 2, 2, 3), 
                  actual.date=mdy('3/31/2017', '2/11/2016','4/10/2016','5/15/2015')) 

df2 <- data.frame(ID = c(1, 1, 1, 2, 3),
              start = mdy('1/1/2000', '4/1/2011', '6/5/2012', '5/1/2014', '1/12/2012'),
              end = mdy('3/31/2011', '6/4/2012', '3/31/2017', '3/31/2017', '2/12/2014'),
              colour = c("blue", "purple", "blue", "red", "purple"))


df <- full_join(df1, df2, by = "ID") %>% 
  mutate(test = ifelse(actual.date <= end & actual.date > start, 
                       TRUE, 
                       FALSE)) %>% 
  filter(test) %>% 
  left_join(df1, ., by = c("ID", "actual.date")) %>% 
  select(ID, actual.date, colour)

(Пакет lubridate не нужен, но он удобен для ввода дат)

И, пожалуйста, в следующий раз приведите воспроизводимый пример, чтобы нам не пришлось вручную переписывать данные!

person Quentin Perrier    schedule 26.10.2017

arrow_upward
1
arrow_downward

Другая альтернатива с использованием sqldf

library(sqldf)
df1$Date <- as.Date(df1$Date, "%m/%d/%Y")
df2$start <- as.Date(df2$start, "%m/%d/%Y")
df2$end <- as.Date(df2$end, "%m/%d/%Y")
sqldf({"
  SELECT df1.*, df2.colour FROM df1 
  INNER JOIN df2
  ON df1.ID = df2.ID AND df1.Date <= df2.end AND df1.Date >= df2.start
"})
person Santosh M.    schedule 26.10.2017