library(tidyverse)
gvkey <- c(1, 1, 1, 1, 2,2,2, 4, 4 )
fyear <- c(2005,2006,2007,2008, 2007,2008,2009 , 2011,2012)
nibi <- c(100, 110, 120, 130, 500, 550, 600, 50, 60)
lagAT <- c(1000,1500,1300,1200, 300,500, 800, 70, 40)
(Thesis <- data.frame(gvkey, fyear, nibi, lagAT) %>% arrange(gvkey,fyear))
(Thesis2 <- arrange(Thesis,
gvkey,
fyear) %>% group_by(gvkey) %>% mutate(nibi_change = (nibi-lag(nibi))/lagAT))
# calculate median of positive valaues of nibi_change per gvkey
(median_nibis <- Thesis2 %>% filter(nibi_change>0) %>% summarise(med_nibi_change = median(nibi_change)))
(Thesis3 <- left_join(Thesis2 , median_nibis , by = "gvkey") %>% mutate(
dBath = ifelse(nibi_change < med_nibi_change,1,0),
Bath = ifelse(nibi_change < med_nibi_change,nibi_change,0
)))
# A tibble: 9 x 8
# Groups: gvkey [3]
gvkey fyear nibi lagAT nibi_change med_nibi_change dBath Bath
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 1 2005 100 1000 NA 0.00769 NA NA
2 1 2006 110 1500 0.00667 0.00769 1 0.00667
3 1 2007 120 1300 0.00769 0.00769 0 0
4 1 2008 130 1200 0.00833 0.00769 0 0
5 2 2007 500 300 NA 0.0812 NA NA
6 2 2008 550 500 0.1 0.0812 0 0
7 2 2009 600 800 0.0625 0.0812 1 0.0625
8 4 2011 50 70 NA 0.25 NA NA
9 4 2012 60 40 0.25 0.25 0 0