vio vio - 3 months ago 10
R Question

Check for event in last x years return dummy

I want to check whether an event (grouped by airline) occurred in the last 3 years (including current year). If it did, I want a 1 returned. If it didn't, I want a 0 returned. How do I do this? My data looks like this:

Airline Year Fatal Non_Fatal
French_Airline 1989 0 1
French_Airline 1990 1 0
French_Airline 1991 0 0
French_Airline 1992 0 1
French_Airline 1993 0 0
UK_Airline 1989 1 1
UK_Airline 1990 0 0
UK_Airline 1991 1 0
UK_Airline 1992 0 0
UK_Airline 1993 0 0


My output would ideally look something like this

Airline Year Fatal Non_Fatal Last_3_Fatal Last_3_NonFatal
French_Airline 1989 0 1 0 1
French_Airline 1990 1 0 1 1
French_Airline 1991 0 0 1 1
French_Airline 1992 0 1 1 1
French_Airline 1993 0 0 0 1
UK_Airline 1989 1 1 1 1
UK_Airline 1990 0 0 1 1
UK_Airline 1991 1 0 1 1
UK_Airline 1992 0 0 1 0
UK_Airline 1993 0 0 1 0


This is about airline crashes.

Answer

My original comment on your question:

Isn't this just a rolling operation? You want rolling maximum. Use package zoo, and try rollapply.


Here is an example demonstration.

#install.packages("zoo")
library(zoo)

We look at the basic case first, where data are not grouped. Consider a toy vector:

set.seed(0);x <- sample(0:1,10,TRUE)
# [1] 1 0 0 1 1 0 1 1 1 1
rollapply(x, max, width = 3, partial = TRUE, align = "right")
# [1] 1 1 1 1 1 1 1 1 1 1

Note that, you want partial = TRUE and align = "right" here. Read details section in ?rollapply for more. (Also note that it is for the need of partial = TRUE, that we can not use rollmax.) For convenience, we define a wrapper function:

last3 <- function (u) rollapply(u, max, width = 3, partial = TRUE, align = "right")

For grouped data, there is nothing special but apply rollapply by group, for which we use tapply function.

## a data frame: two groups `a` and `b`, each with 10 data
set.seed(0)
dat <- data.frame(group = gl(2, 10, labels = letters[1:2]),
                  x = sample(0:1, 20, TRUE))

## apply `last3` to `x` by `group`, and append result to `dat`
dat$last3 <- unlist(with(dat, tapply(x, group, FUN = last3)), use.names = FALSE)
#   group x last3
#1      a 1     1
#2      a 0     1
#3      a 0     1
#4      a 1     1
#5      a 1     1
#6      a 0     1
#7      a 1     1
#8      a 1     1
#9      a 1     1
#10     a 1     1
#11     b 0     0
#12     b 0     0
#13     b 0     0
#14     b 1     1
#15     b 0     1
#16     b 1     1
#17     b 0     1
#18     b 1     1
#19     b 1     1
#20     b 0     1

In case you do not understand the description of align in ?rollapply, compare the following:

x <- 1:10
rollapply(x, max, width = 3, partial = TRUE, align = "left")
# [1]  3  4  5  6  7  8  9 10 10 10
rollapply(x, max, width = 3, partial = TRUE, align = "center")
# [1]  2  3  4  5  6  7  8  9 10 10
rollapply(x, max, width = 3, partial = TRUE, align = "right")
# [1]  1  2  3  4  5  6  7  8  9 10

Obviously align = "right" is the behaviour you hope for. (Just another comment, you can use rollapplyr for right alignment.)

Comments