CODEWITHSUNDEEP
rdateloops
gabrown86
gabrown86

Reputation: 1661

How to find which date range a date is in?

I have a dataframe that holds the id and launch dates, and for each one I want to know which date range (Time_Seq) each launch date is in.

The vector of dates looks like:

data:
    ID     LAUNCH_DATE
1 50314486  2004-01-01
2 55842774  2004-01-27

and I have a time sequence dataframe that holds the different data ranges:

head(Time_Table):
  Time_Seq   DateFrom     DateTo
1      726 2003-12-29 2004-01-04
2      727 2004-01-05 2004-01-11
3      728 2004-01-12 2004-01-18
4      729 2004-01-19 2004-01-25
5      730 2004-01-26 2004-02-01
6      731 2004-02-02 2004-02-08

Ideally I would like the output to look like:

    ID     LAUNCH_DATE   Time_Seq
1 50314486  2004-01-01      726
2 55842774  2004-01-27      731

Currently I can work it out for one date in a very longhand what by doing:

Time_Table$Time_Seq[which( (date$LAUNCH_DATE[1]>=Time_Table$DATEFROM)&(date$LAUNCH_DATE[1]<=Time_Table$DATETO) )]

Can anyone suggest a simpler way without having to loop in this way?

Upvotes: 2

Views: 99

Answers (2)

Henrik
Henrik

Reputation: 67778

I just wanted to play around with interval and %within% in package lubridate:

library(lubridate)

# create intervals
interval <- with(Time_Table, new_interval(ymd(DateFrom), ymd(DateTo)))

# for each LAUNCH_DATE, test whether it falls within an interval, and pick corresponding Time_Seq
data$Time_Seq <- sapply(data$LAUNCH_DATE, function(x) Time_Table$Time_Seq[ymd(x) %within% interval])

Upvotes: 2

Richie Cotton
Richie Cotton

Reputation: 121077

The cut function for cutting continuous variables up into categories works for dates.

data$Time_Seq <- cut(
  data$LAUNCH_DATE, 
  c(Time_Table$DateFrom, Time_Table$DateTo[nrow(Time_Table)]), 
  labels = Time_Table$Time_Seq,
  right = FALSE
)

Upvotes: 1

Related Questions