I have a data set of equity returns for some thousand companies for the past 30 years. Some of these companies are "DEAD" (usually delisted or bankrupt), and therefore they have returns = 0. I want to assign NAs to the returns of these companies, but only after the time they actually "died". In order to do so, I have tried to use the following code:
if(Returns$r == 0 & stri_detect_fixed(Returns$Company, "DEAD"), na.rm = TRUE){
Returns$r[Returns$r == 0 & stri_detect_fixed(Returns$Company, "DEAD")] <- NA
}
This works pretty well, but unfortunately there are on occasion return values equal to 0 for DEAD/delisted companies even before they "died", and these values I want to remain at 0.
Therefore, what I need is a command/if condition telling R that I only want to return NAs if the return in the next row is equal to 0 as well. Do you guys have any suggestions? I hope I made my problem clear, though I do know my explanation may be a bit confusing.
Reproducible example
Returns <- structure(list(Date = c("04.09.17", "05.09.17", "06.09.17", "01.09.17",
"02.09.17", "03.09.17", "04.09.17", "05.09.17", "06.09.17", "04.09.17",
"05.09.17", "06.09.17"), Company = c("ORKLA", "ORKLA", "ORKLA",
"VISMA DEAD 04.09.17", "VISMA DEAD 04.09.17", "VISMA DEAD 04.09.17",
"VISMA DEAD 04.09.17", "VISMA DEAD 04.09.17", "VISMA DEAD 04.09.17",
"XNEWCO", "XNEWCO", "XNEWCO"), r = c(0.04, 0, -0.02, 0.01, 0, -0.03,
0, 0, 0, 0.01, 0, 0)), .Names = c("Date", "Company", "r"), row.names = c(NA,
-12L), class = "data.frame")
(Edited to cover the case where a "live" company has zero returns at the end of the time series)
My data frame Returns looks something like this:
Date Company r
04.09.17 ORKLA 0.04
05.09.17 ORKLA 0.00
06.09.17 ORKLA -0.02
01.09.17 VISMA DEAD 04.09.17 0.01
02.09.17 VISMA DEAD 04.09.17 0.00
03.09.17 VISMA DEAD 04.09.17 -0.03
04.09.17 VISMA DEAD 04.09.17 0.00
05.09.17 VISMA DEAD 04.09.17 0.00
06.09.17 VISMA DEAD 04.09.17 0.00
04.09.17 XNEWCO 0.01
05.09.17 XNEWCO 0.00
06.09.17 XNEWCO 0.00
I would want it to be like this:
Date Company r
04.09.17 ORKLA 0.04
05.09.17 ORKLA 0.00
06.09.17 ORKLA -0.02
01.09.17 VISMA DEAD 04.09.17 0.01
02.09.17 VISMA DEAD 04.09.17 0.00
03.09.17 VISMA DEAD 04.09.17 -0.03
04.09.17 VISMA DEAD 04.09.17 NA
05.09.17 VISMA DEAD 04.09.17 NA
06.09.17 VISMA DEAD 04.09.17 NA
04.09.17 XNEWCO 0.01
05.09.17 XNEWCO 0.00
06.09.17 XNEWCO 0.00
My current code (as you'll see above) wouldn't work, as it would replace the return of 0.00 for VISMA 02.09.17 with NA. I need it to remain 0.00, as this is before VISMA "died"