Create an index variable in R based on a row index
I think it's time to ask for help. Suppose I have a data.frame or data.table
State Date Event
CA Oct27 1
CA Oct28 0
CA Oct29 0
CA Oct30 0
CA Oct31 1
TX Oct27 0
TX Oct28 1
TX Oct29 1
TX Oct30 0
TX Oct31 0
TX Nov1 0
I want to create a new binary variable "active" that indicates if there is an active event on a specific date and state (assuming all events have lasted three days). The value "1" in the "Event" column indicates when the event started. So my data will look like this:
State Date Event Active
CA Oct27 1 1
CA Oct28 0 1
CA Oct29 0 1
CA Oct30 0 0
CA Oct31 1 1
TX Oct27 0 0
TX Oct28 1 1
TX Oct29 1 1
TX Oct30 0 1
TX Oct31 0 1
TX Nov1 0 0
Any suggestions would be appreciated.
source to share
I like the solution data.table
. Here I think this is a cleaner R solution.
s <- split(df, df$State)
newlist <- lapply(s, function(x) {
days <- c(which(x$Event==1)+1, which(x$Event==1)+2)
x$Event[seq_along(x$Event) %in% days] <- 1
x
}
)
do.call(rbind, newlist)
First, split the data frame into state. For each condition, specify two days after the start of the event. If these days are on the list, assign them 1
. Finally, put the states together.
It outputs:
State Date Event
CA.1 CA Oct27 1
CA.2 CA Oct28 1
CA.3 CA Oct29 1
CA.4 CA Oct30 0
CA.5 CA Oct31 1
TX.6 TX Oct27 0
TX.7 TX Oct28 1
TX.8 TX Oct29 1
TX.9 TX Oct30 1
TX.10 TX Oct31 1
TX.11 TX Nov1 0
source to share
Given that your table is sorted and you don't like unrelated days, you can try:
library(data.table)
setDT(df)[, Active:=Event|c(0, head(Event,-1))|c(0,0,head(Event,-2)), State][
, Active:=Active+0]
# State Date Event Active
# 1: CA Oct27 1 1
# 2: CA Oct28 0 1
# 3: CA Oct29 0 1
# 4: CA Oct30 0 0
# 5: CA Oct31 1 1
# 6: TX Oct27 0 0
# 7: TX Oct28 1 1
# 8: TX Oct29 1 1
# 9: TX Oct30 0 1
#10: TX Oct31 0 1
#11: TX Nov1 0 0
source to share
Dude, this was a serious problem. I think I used it with help by()
to group State
and Reduce()
reapply the vectorized boolean OR |
to the vector Active
to account for any past day in the specified range (3) that had an event started.
df <- data.frame(State=c('CA','CA','CA','CA','CA','TX','TX','TX','TX','TX','TX'), Date=c('Oct27','Oct28','Oct29','Oct30','Oct31','Oct27','Oct28','Oct29','Oct30','Oct31','Nov1'), Event=c(1,0,0,0,1,0,1,1,0,0,0) );
E <- 3;
do.call(rbind,by(df,df$State,function(x) { s <- x$Event==1; x$Active <- Reduce(function(a,b) a|c(rep(F,b),s[-seq(length(s)-b+1,len=b)]),c(list(s),1:(E-1))); x; }));
## State Date Event Active
## CA.1 CA Oct27 1 TRUE
## CA.2 CA Oct28 0 TRUE
## CA.3 CA Oct29 0 TRUE
## CA.4 CA Oct30 0 FALSE
## CA.5 CA Oct31 1 TRUE
## TX.6 TX Oct27 0 FALSE
## TX.7 TX Oct28 1 TRUE
## TX.8 TX Oct29 1 TRUE
## TX.9 TX Oct30 0 TRUE
## TX.10 TX Oct31 0 TRUE
## TX.11 TX Nov1 0 FALSE
The advantage of this solution is that it parameterizes the duration of the event, which means you can easily change it in the future:
E <- 2;
do.call(rbind,by(df,df$State,function(x) { s <- x$Event==1; x$Active <- Reduce(function(a,b) a|c(rep(F,b),s[-seq(length(s)-b+1,len=b)]),c(list(s),1:(E-1))); x; }));
## State Date Event Active
## CA.1 CA Oct27 1 TRUE
## CA.2 CA Oct28 0 TRUE
## CA.3 CA Oct29 0 FALSE
## CA.4 CA Oct30 0 FALSE
## CA.5 CA Oct31 1 TRUE
## TX.6 TX Oct27 0 FALSE
## TX.7 TX Oct28 1 TRUE
## TX.8 TX Oct29 1 TRUE
## TX.9 TX Oct30 0 TRUE
## TX.10 TX Oct31 0 FALSE
## TX.11 TX Nov1 0 FALSE
The correctness of this solution depends on two assumptions regardless of each unique one State
: (1) there Date
are no spaces in the sequence , and (2) the order of the data is ordered by Date
.
Here's another solution, using by()
again but now with seq()
to create all the dates covered by the event and merge()
to concatenate those dates back into a subset of the data.frame for a specific State
to set Active
to true. This solution weakens both of the above assumptions; now the input data.frame should no longer be gapless or ordered. However, you now have to force the column Date
to a class Date
(as in my demo below), although I would say that something should always be done when you work with dates.
df2 <- transform(df,Date=as.Date(Date,'%b%d'));
E <- 3;
transform(do.call(rbind,by(df2,df2$State,function(x) merge(x,data.frame(Date=unique(do.call(c,lapply(x$Date[x$Event==1],seq,by=1,len=E))),Active=T),all.x=T))),Active=replace(Active,is.na(Active),F));
## Date State Event Active
## CA.1 2015-10-27 CA 1 TRUE
## CA.2 2015-10-28 CA 0 TRUE
## CA.3 2015-10-29 CA 0 TRUE
## CA.4 2015-10-30 CA 0 FALSE
## CA.5 2015-10-31 CA 1 TRUE
## TX.1 2015-10-27 TX 0 FALSE
## TX.2 2015-10-28 TX 1 TRUE
## TX.3 2015-10-29 TX 1 TRUE
## TX.4 2015-10-30 TX 0 TRUE
## TX.5 2015-10-31 TX 0 TRUE
## TX.6 2015-11-01 TX 0 FALSE
E <- 2;
transform(do.call(rbind,by(df2,df2$State,function(x) merge(x,data.frame(Date=unique(do.call(c,lapply(x$Date[x$Event==1],seq,by=1,len=E))),Active=T),all.x=T))),Active=replace(Active,is.na(Active),F));
## Date State Event Active
## CA.1 2015-10-27 CA 1 TRUE
## CA.2 2015-10-28 CA 0 TRUE
## CA.3 2015-10-29 CA 0 FALSE
## CA.4 2015-10-30 CA 0 FALSE
## CA.5 2015-10-31 CA 1 TRUE
## TX.1 2015-10-27 TX 0 FALSE
## TX.2 2015-10-28 TX 1 TRUE
## TX.3 2015-10-29 TX 1 TRUE
## TX.4 2015-10-30 TX 0 TRUE
## TX.5 2015-10-31 TX 0 FALSE
## TX.6 2015-11-01 TX 0 FALSE
source to share