Merge data sets based on id and date-R

Question

I am trying add information from a second data set to my first based on ID and dates. If the ID matches and 'Date' is between 'start' and 'end', I want to add the value for colour to 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

To get a result like this:

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

Which can be created with the code here:

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"))

I used a response from a similar question, Checking if Date is Between two Dates in R and used the code below:

    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)

and I get the following error:

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

help?

Thanks so much!

Update-

Thanks so much everyone for your answers.

I tried them all but all the final datasets have different number of rows than the first dataset. I am not sure what is happening. The data I have posted is made-up to resemble the data I am working with. Are there additional details that I should let you know? I don't know where to start...

Can you dump the code needed to generate your sample dataframe into your post so that we may replicate and tinker with the issue, too? see:https://meta.stackexchange.com/a/191794/346447 or https://stackoverflow.com/q/5963269/5088194 — leerssej, Oct 26 '17 at 19:26

score 2 · Answer 1 · answered Oct 26 '17 at 19:29

2

It seems your data frame is large, you can try data.table non-equi join to do this in an efficient way:

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

answered Oct 26 '17 at 19:29

Psidom

209,562
33
339
356

Thanks. I tried this but nothing happened, and I get no error. – user3047435 Oct 27 '17 at 21:51
It maybe a `data.table` version issue. What is your `data.table` version? Try upgrade it. – Psidom Oct 27 '17 at 21:55
What version are you working with? I have version 1.10.4-2 installed. There seems to be a later version (https://cran.r-project.org/web/packages/data.table/index.html) but it's not updating when I try to update. – user3047435 Oct 27 '17 at 22:24
I have the same version as yours. Nothing happened and no error seems strange to me. Or maybe you need to assign the result to a variable `dat`? `dat <- df2[df1, .(ID = i.ID, Date = i.Date, colour), on=.(ID, start <= Date, end >= Date)]` – Psidom Oct 27 '17 at 22:27
oh yes, of course. Now it works, but I have more rows than my original dataset. – user3047435 Oct 27 '17 at 22:32
Then it means there are multiple matches for some rows in `df1`, may be there are overlaps of `start-end` in `df2` for some `ID`s. Hard to say without taking a look at the real data set. – Psidom Oct 27 '17 at 22:35
Thanks so much, there was overlap between start-end in df2. I have now successfully merged the data but when I refer to colour in my new dataset, R ends up referring to the original colour column in df2. How can I fix this? – user3047435 Oct 30 '17 at 18:49
I didn't get you. What do you mean *R ends up referring to the original colour column in df2*? – Psidom Oct 30 '17 at 18:59
For example, I use table to see the count of each category in the new dataset but I end up getting a list of all the colours that were in the original dataset that are not necessarily in the final dataset. The count is 0 but those other categories shouldn't even show up. – user3047435 Oct 30 '17 at 19:03
Most likely your color column is a factor, which keeps all its levels during the joining, try `table(as.character(new_data$colour))` – Psidom Oct 30 '17 at 19:06

score 1 · Answer 2 · answered Oct 26 '17 at 19:49

I reproduced your example and and give it one solution.

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)

(The lubridate package is not necessary, but it is handy to enter dates)

And please, next time, provide a reproducible example, so that we don't have to rewrite the data manually!

score 1 · Answer 3 · answered Oct 26 '17 at 19:57

Another alternative using 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
"})

leerssej · Accepted Answer · 2017-10-27T17:42:55.203

dplyr uses non standard evaluation and so you can dump all the dataframe names and $s and your code begins basically in the right direction. There are additionally a number of implicit transformations necessary for you to end up with the data frame you specified, but this below will get you there.

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>

Merge data sets based on id and date-R

4 Answers4