基于ID和日期合并数据集-R

Question

我正在尝试根据ID和日期将第二个数据集中的信息添加到第一个数据集中。如果ID匹配且“日期”在“开始”和“结束”之间，我想将颜色的值添加到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)

我遇到了以下错误：

Error in between(df1$Date, df2$start, df2$end) : Expecting a single value: [extent=355368].

需要帮助吗？

非常感谢！

更新-

非常感谢大家的答案。

我尝试了所有方法，但最终数据集的行数与第一个数据集不同。我不确定发生了什么。我发布的数据是虚构的，以类似于我正在处理的数据。是否有其他详细信息应该让您知道？我不知道从哪里开始......

- user3047435

---

你能否将生成示例数据框所需的代码转储到您的帖子中，以便我们也可以复制和调试该问题？请参见：https://meta.stackexchange.com/a/191794/346447 或 https://dev59.com/eG025IYBdhLWcg3whGSx - leerssej

Answer 1

看起来你的数据框非常大，你可以尝试使用 data.table 的非等连接来以高效的方式解决这个问题:

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

- Psidom

---

谢谢。我尝试了这个，但没有反应，也没有错误提示。 - user3047435

我和你使用的是同一个版本。对我来说，没有任何异常情况或错误发生。或许你需要将结果分配给变量 dat？dat <- df2[df1, .(ID = i.ID, Date = i.Date, colour), on=.(ID, start <= Date, end >= Date)] - Psidom

那么这意味着df1中的某些行存在多个匹配项，可能是df2中某些ID的start-end存在重叠。如果没有查看真实数据集，很难说清楚。 - Psidom

我没听懂你的意思。你是说 R 最终会指向 df2 中原始颜色列？ - Psidom

例如，我使用表格来查看新数据集中每个类别的计数，但最终得到的是原始数据集中所有颜色的列表，这些颜色不一定在最终数据集中。计数为0，但这些其他类别甚至不应该出现。 - user3047435

Answer 2

我复制了你的例子并给出了一个解决方案。

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包不是必需的，但它很方便输入日期）

下次请提供可重现的示例，这样我们就不必手动重写数据了！

- Quentin Perrier

Answer 3

另一种使用 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
"})

- Santosh M.

Answer 4

dplyr使用非标准评估，因此您可以放弃所有数据框名称和$，您的代码基本上就在正确的方向上开始了。此外，还需要进行一些隐式转换，以便最终获得您指定的数据帧，但是以下内容将使您达到目标。

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>

- leerssej