I am conducting an event study with the market model. There are several events per ISIN. There is an event, if "Rating_Change" is "non-zero". What I need is an "ID" for each event per ISIN and also for the pre-window and post window of the event data. It would be ideal to have per event one ID to identify also the pre and post event windows which should be -2:+2 (here in the example, in "real" it should be -5:+5).
At the moment I tried this....:
testing1 <- testing %>%
group_by(ISIN) %>%
mutate(event=cumsum(Rating_Change!=0 & !is.na(Rating_Change)))
... and received the following output:
Date ISIN R STOXX_Return Rating_Change Rating event
<date> <fct> <dbl> <dbl> <dbl> <fct> <int>
3 2016-10-01 CH00 0.0175 -0.000375 0 A 0
4 2016-11-01 CH00 -0.0734 -0.0221 0 A 0
5 2016-12-01 CH00 -0.0107 0.0183 0 A 0
6 2017-01-01 CH00 0.0457 0.0642 1.9 A 1
7 2017-02-01 CH00 0.0208 0.000647 0 A 1
8 2017-03-01 CH00 0.0352 0.0364 0 A 1
...
15 2017-11-01 CH00 -0.0780496 0.0179349 0 A 1
16 2017-12-01 CH00 0.0688210 -0.0312227 0 A 1
17 2018-01-01 CH00 -0.0064685 0.0144049 -0.90 A 2
18 2018-02-01 CH00 -0.0997418 0.0119439 0 A 2
19 2018-03-01 CH00 -0.0203781 -0.0463974 0 A 2
...
45 2017-02-01 GB00 0.0056672 0.0006471 0 B+ 1
46 2017-03-01 GB00 0.0028146 0.0364348 0 B+ 1
47 2017-04-01 GB00 0.0366418 0.0144673 3.66 B+ 2
48 2017-05-01 GB00 0.0745412 0.0242931 0 B+ 2
49 2017-06-01 GB00 0.1555046 0.0222243 0 B+ 2
...
I also tried it with the following way:
filter_lmco_<- within(testing, {
event <- if_else(Rating_Change!=0,1,0)
event <- ave(event, lag(ISIN), FUN=cumsum)
event <- ifelse(Rating_Change != 0, event-1, event)
})
This had a similar output, however, it only takes the pre-windows and not the window "around" the event - at least I don't know how to formulate the syntax to have the windows around (-2:+2) the event.
Here I show you what I actually need:
Date ISIN R STOXX_Return Rating_Change Rating event
<date> <fct> <dbl> <dbl> <dbl> <fct> <int>
3 2016-10-01 CH00 0.0175 -0.000375 0 A 0
4 2016-11-01 CH00 -0.0734 -0.0221 0 A 1
5 2016-12-01 CH00 -0.0107 0.0183 0 A 1
6 2017-01-01 CH00 0.0457 0.0642 1.9 A 1
7 2017-02-01 CH00 0.0208 0.000647 0 A 1
8 2017-03-01 CH00 0.0352 0.0364 0 A 1
...
15 2017-11-01 CH00 -0.0780496 0.0179349 0 A 2
16 2017-12-01 CH00 0.0688210 -0.0312227 0 A 2
17 2018-01-01 CH00 -0.0064685 0.0144049 -0.90 A 2
18 2018-02-01 CH00 -0.0997418 0.0119439 0 A 2
19 2018-03-01 CH00 -0.0203781 -0.0463974 0 A 2
...
45 2017-02-01 GB00 0.0056672 0.0006471 0 B+ 1
46 2017-03-01 GB00 0.0028146 0.0364348 0 B+ 1
47 2017-04-01 GB00 0.0366418 0.0144673 3.66 B+ 1
48 2017-05-01 GB00 0.0745412 0.0242931 0 B+ 1
49 2017-06-01 GB00 0.1555046 0.0222243 0 B+ 1
...
Has anyone an idea or approach how to solve this? All in all I have about 1600 events (plus pre and post window data). Any help and support is much appreciated!! Thank you so much in advance. Please let me know if I shall clarify anything or give more details.
i think this should do the trick
testing1 <- testing %>%
group_by(ISIN) %>%
mutate(event=cumsum(lead(Rating_Change, 2)!=0 & !is.na(lead(Rating_Change, 2))))
it works in this case for some dummy data
tibble::tibble(
Rating_Change = c(0,0,0,1,0,0,0,2,0,0,0,4)) %>%
dplyr::mutate(event=cumsum(dplyr::lead(Rating_Change, 2)!=0 & !is.na(dplyr::lead(Rating_Change, 2))))