Being new to R, I am looking for an efficient way to perform a loop with an analogue of VLOOKUP with two conditions. VLOOKUP allows to look up for a specific value throughout a column and apply it to each row of my data frame.
I have a long data.frame DF of 3 variables:
Car: identification number of the observed items (cars). Unique for each car, but not for each row.Date: date of the observation, format="%Y-%m-%d"Area: logic variable showing if an observation (Car) on thisDatewas in a certain area (TRUE) or not (FALSE)
I need to create a new binary variable AreaChange that shows if the Area changed in the next 30 days for this Car: if yes then 1, if no change then 0. I am also interested in one direction of change: from FALSE to TRUE.
It is possible that Area changes several times in the next 30 days, if at least one of the changes is from FALSE to TRUE, the AreaChange should equal 1.
It is also possible that some Cars were observed for less than 30 days at certain periods, in these cases the AreaChange calculation is also needed.
For me it looks as:
- Extracting 30
FutureAreavalues for each row, matching on two parameters: sameCarandDatebetween (DateandDate+30). I suppose that it can be done in a loop format for the 30 days. - Creating the binary new variable
AreaChangeequaling 0 if all availableFutureAreavalues are the same, or if the currentAreafor this row is TRUE.
I have found suggestions on cases with merging 2 data frames or for matching on just 1 condition or without extracting the Area values on future days, but did not manage to combine them for my case.
For now, I have only managed to get the AreaChange, ignoring the need to match Car and comparing the Area only with the Area in 30 days, not for every day in the next 30 days.
DF$Date30 <- DF$Date+30
library(expss)
DF$Area30 <- vlookup(DF$Date30, DF[,1:3], result_column = 3, lookup_column = 2)
DF$AreaChange30 <- ifelse(DF$Area30!=DF$Area & DF$Area==FALSE, 1, 0)
Many thanks for any suggestions on how to optimize and proceed.
Aucun commentaire:
Enregistrer un commentaire