Set difference of dates from different data sets R

Set difference of dates from different data sets R

Problem Description:

I have following two dataframes,

IDfirst_datelast_date
12022-09-012022-09-03
22022-09-252022-09-26
IDdates
12022-09-01
12022-09-02
22022-09-25
22022-09-26

If an ID of a first dataframe doesn’t have date values within a range from first_date to last_date in second dataframe, I have to add 1 to the column named missing or 0 otherwise as below.

IDfirst_datelast_datemissing
12022-09-012022-09-031
22022-09-252022-09-260

How can I do this in R? Can anyone help please!

Solution – 1

A dplyr/tidyr way: First calculate the amount of days between the two days in df1 and compare with the amount of days present in df2.

library(dplyr)
library(tidyr)

df1 |> 
  group_by(ID) |>
    mutate(days = length(seq(from = first_date, to = last_date, by = 1))) |>
  ungroup() |>
  left_join(count(df2, ID)) |>
  mutate(missing = +!(days == n)) |>
  select(-days, -n) # You might be interested in keeping these!

Output:

# A tibble: 2 × 4
     ID first_date last_date  missing
  <dbl> <date>     <date>       <int>
1     1 2022-09-01 2022-09-03       1
2     2 2022-09-25 2022-09-26       0

Data:

library(readr)

df1 <- read_table("ID   first_date  last_date
1   2022-09-01  2022-09-03
2   2022-09-25  2022-09-26")

df2 <- read_table("ID   dates
1   2022-09-01
1   2022-09-02
2   2022-09-25
2   2022-09-26")

Solution – 2

Here I am using outer and & to check if any date from df2 is contained in any of the ranges specified in df1.

mutate(df1, missing = (outer(first_date, df2$dates, `<`) &
                       outer(last_date, df2$dates,  `>`)) |>
                apply(1, any) |>
                as.integer())

##>   ID first_date  last_date missing
##> 1  1 2022-09-01 2022-09-03       1
##> 2  2 2022-09-25 2022-09-26       0

For larger problems, it would be useful to know if the ranges are disjointed or have overlaps.

You may also want to look into ?iranges::findOverlap

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.
Accept
Reject