# Set difference of dates from different data sets R

## Set difference of dates from different data sets R

Contents

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)

1   2022-09-01  2022-09-03
2   2022-09-25  2022-09-26")

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.