Checking If Date Is Between Two Dates in R

Check if date is between two dates in another data frame, and manipulate date if it is

With data.table this is relatively simple with a non-equi update join:

library(data.table)
setDT(df1)
setDT(df2)

df1[df2
    , on = .(ID
             , unique_posix > Date.Time.Start
                , unique_posix < Date.Time.End)
    , `:=`(Dive.Shape = Shape, Dive.Depth = Depth)]

df1

> df1
      ID        unique_posix Dive.Shape Dive.Depth
 1: SW12 2010-05-03 16:47:00       <NA>         NA
 2: SW12 2010-05-03 16:53:00       <NA>         NA
 3: SW12 2010-05-03 17:00:00          V          3
 4: SW12 2010-05-03 18:00:00     Square          4
 5: SW12 2010-05-03 18:12:00       <NA>         NA
 6: SW13 2010-08-15 17:13:00          U          6
 7: SW13 2010-08-15 17:18:00          U          6
 8: SW13 2010-08-15 17:37:00          U          7
 9: SW13 2010-08-15 18:00:00     Square          8
10: SW13 2010-08-15 18:52:00       <NA>         NA

See also: How to do a data.table rolling join?

If date between two dates in second table return value

You can try the following:

library(tidyverse)
library(lubridate)

dates <- dates %>% 
  mutate(match_date = format(date, "%Y-%m-%d"), 
         match_hour = hour(date - minutes(1)))

lookup <- lookup %>% 
  mutate(match_date = format(begin, "%Y-%m-%d"), 
         match_hour = hour(begin))


left_join(dates, lookup, by = c("match_date", "match_hour")) %>% 
  filter(date >= begin & date <= end) %>% 
  select(- match_date, - match_hour) %>% 
  head()

# A tibble: 6 x 5
#                  date               begin                 end value1 value2
#                <dttm>              <dttm>              <dttm>  <int>  <int>
# 1 2017-07-01 00:15:00 2017-07-01 00:01:00 2017-07-01 01:00:00      1      2
# 2 2017-07-01 00:30:00 2017-07-01 00:01:00 2017-07-01 01:00:00      1      2
# 3 2017-07-01 00:45:00 2017-07-01 00:01:00 2017-07-01 01:00:00      1      2
# 4 2017-07-01 01:00:00 2017-07-01 00:01:00 2017-07-01 01:00:00      1      2
# 5 2017-07-01 01:15:00 2017-07-01 01:01:00 2017-07-01 02:00:00      3      4
# 6 2017-07-01 01:30:00 2017-07-01 01:01:00 2017-07-01 02:00:00      3      4

First I extract the dates and hour of the day to match on. I substract one minute from the date in the dates-table as your end times in your lookup-table contain the times sharp (I mean e.g. 01:00:00). As I want to join on the begin-dates to get the right matching hour (e.g. 0 in this case), I substract the minute.

Then I do a left_join of dates and lookup and filter on your desired criteria.

Check if a date is within an interval in R

Everybody has their favourite tool for this, mine happens to be data.table because of what it refers to as its dt[i, j, by] logic.

library(data.table)

dt <- data.table(date = as.IDate(pt))

dt[, YR := 0.0 ]                        # I am using a numeric for year here...

dt[ date >= as.IDate("2002-09-01") & date <= as.IDate("2003-08-31"), YR := 1 ]
dt[ date >= as.IDate("2003-09-01") & date <= as.IDate("2004-08-31"), YR := 2 ]
dt[ date >= as.IDate("2004-09-01") & date <= as.IDate("2005-08-31"), YR := 3 ]

I create a data.table object, converting your times to date for later comparison. I then set up a new column, defaulting to one.

We then execute three conditional statements: for each of the three intervals (which I just create by hand using the endpoints), we set the YR value to 1, 2 or 3.

This does have the desired effect as we can see from

R> print(dt, topn=5, nrows=10)
           date YR
  1: 2003-06-11  1
  2: 2004-08-11  2
  3: 2004-06-03  2
  4: 2004-01-20  2
  5: 2005-02-25  3
 ---              
 96: 2002-08-07  0
 97: 2004-02-04  2
 98: 2006-04-10  0
 99: 2005-03-21  3
100: 2003-12-01  2
R> table(dt[, YR])

 0  1  2  3 
26 31 31 12 
R> 

One could have done this also simply by computing date differences and truncating down, but it is also nice to be a little explicit at times.

Edit: A more generic form just uses arithmetic on the dates:

R> dt[, YR2 := trunc(as.numeric(difftime(as.Date(date), 
+                                        as.Date("2001-09-01"),
+                                        unit="days"))/365.25)]
R> table(dt[, YR2])

 0  1  2  3  4  5  6  7  9 
 7 31 31 12  9  5  1  2  1 
R> 

This does the job in one line.

count row if date falls within date range for all dates in series in R

Here's a base R method:

date = seq(min(df$start_date), max(df$end_date))
count = sapply(date, \(x) sum(x >= df$start_date & x <= df$end_date))
data.frame(date, count)
#    date count
# 1     1     2
# 2     2     4
# 3     3     5
# 4     4     6
# 5     5     7
# 6     6     6
# 7     7     5
# 8     8     4
# 9     9     3
# 10   10     2
# 11   11     1

---

Related Topics