labelling rows in one data set based on the date of the measurement compared to two other dates in another dataset

library(data.table)
testset <- data.table(date=as.Date(c("2013-07-02","2013-08-03","2013-09-04",
"2013-10-05","2013-11-06")),
yr = c(2013,2013,2013,2013,2013),
mo = c(07,08,09,10,11),
da = c(02,03,04,05,06),
plant = LETTERS[1:5],
product = as.factor(letters[26:22]),
rating = runif(25))


I have this dataset that for each row I want to create a category or naming on that row depending on the date column. I want to compare this date with dates in another dataset:

library(lubridate)
splitDates <- ymd(c("2013-06-10", "2013-08-15", "2013-10-06"))


Using splitDates I want to evaluate which value in splitDates came last before the measurement was taken. (If you imagine that a new experiment took place from 2013-06-10 and until but not including 2013-08-15, I want to decide what experiment a measurement belongs to).

As I can see the first five rows in this new column should look like this:

NewColumn <- c("2013-06-10", "2013-06-10", "2013-08-15", "2013-08-15", "2013-10-06")

date yr mo da plant product rating NewColumn
1: 2013-07-02 2013 7 2 A z 0.02522850 2013-06-10
2: 2013-08-03 2013 8 3 B y 0.28274066 2013-06-10
3: 2013-09-04 2013 9 4 C x 0.86314441 2013-08-15
4: 2013-10-05 2013 10 5 D w 0.01670862 2013-08-15
5: 2013-11-06 2013 11 6 E v 0.16034175 2013-10-06
...


I can't figure out how to do this.

splitDates

testset[, v := splitDates[findInterval(date, splitDates)]]

3 Answers
3

Here's my take

library(dplyr)
dta <- data.frame(NewColumn=splitDates,newvar=1:3)
testset$newvar <- sapply(testset[,1], function(x) ifelse(x<splitDates[2],1,ifelse(x<splitDates[3],2,3)))
final_data <- semi_join(testset,dta,by="newvar")


Data:

testset <- data.table(date=as.Date(c("2013-07-02","2013-08-03","2013-09-04",
"2013-10-05","2013-11-06")),
yr = c(2013,2013,2013,2013,2013),
mo = c(07,08,09,10,11),
da = c(02,03,04,05,06),
plant = LETTERS[1:5],
product = as.factor(letters[26:22]),
rating = runif(25))

splitDates <- ymd(c("2013-06-10", "2013-08-15", "2013-10-06"))


For me, understanding your question was more difficult than solving it. Please review the answer, and give me a feedback. It has 3 steps:

make a function to return the latest date from the other dataset

findLatest<-function(date)which.min( abs( splitDates-date ))


Then call the function over all the dates in testset:

testset

names<-splitDates[ sapply(testset[,1], findLatest ) ]


Add the result to the dataset

testset$names<-names


So, the first 10 rows are:

date yr mo da plant product rating V8
1 2013-07-02 2013 7 2 A z 0.75801493 2013-06-10
2 2013-08-03 2013 8 3 B y 0.06370597 2013-08-15
3 2013-09-04 2013 9 4 C x 0.25375231 2013-08-15
4 2013-10-05 2013 10 5 D w 0.42900236 2013-10-06
5 2013-11-06 2013 11 6 E v 0.97613291 2013-10-06
6 2013-07-02 2013 7 2 A z 0.78094927 2013-06-10
7 2013-08-03 2013 8 3 B y 0.91312684 2013-08-15
8 2013-09-04 2013 9 4 C x 0.29345599 2013-08-15
9 2013-10-05 2013 10 5 D w 0.80870134 2013-10-06
10 2013-11-06 2013 11 6 E v 0.18735280 2013-10-06


names<-splitDates[ sapply(testset[,1], findLatest ) ]

findLatest

I have to hand the answer to Frank, which commented on my first post.

testset[, v := splitDates[findInterval(date, splitDates)]]


does the trick.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.