I am trying to make a table that counts the number of consecutive years grouped by columns "state" and "p" that looks like this:
data_right <- data.table(state = c("NY", "NY", "NY", "NY", "NY","NY", "PA",
"PA", "PA", "PA", "PA", "PA"), p = c("n", "n","n","n", "p", "p", "n", "n", "n",
"p", "p", "p"),Year = c("1973", "1974", "1977", "1978", "1988", "1989" ,"1991",
"1992", "1993", "1920", "1929", "1931"), Consecutive_Yrs =
c(1,2,1,2,1,2,1,2,3,1,1,1))
The code I am using right now is not working properly. I'm trying mutate, and group_by statements in dplyr but am having no luck. I also cannot use the data.table package because my R version is not up to date.
Any help to get this output is greatly appreciated!
library(dplyr)
data_right %>%
group_by(state, p) %>%
mutate(grp = cumsum(c(TRUE, diff(as.integer(Year)) > 1))) %>%
group_by(state, p, grp) %>%
mutate(cy = row_number()) %>%
ungroup() %>%
select(-grp)
# # A tibble: 12 x 5
# state p Year Consecutive_Yrs cy
# <chr> <chr> <chr> <dbl> <int>
# 1 NY n 1973 1 1
# 2 NY n 1974 2 2
# 3 NY n 1977 1 1
# 4 NY n 1978 2 2
# 5 NY p 1988 1 1
# 6 NY p 1989 2 2
# 7 PA n 1991 1 1
# 8 PA n 1992 2 2
# 9 PA n 1993 3 3
# 10 PA p 1920 1 1
# 11 PA p 1929 1 1
# 12 PA p 1931 1 1
Assumes the data is already ordered by Year.
Data:
data_right <- data.table(state = c("NY", "NY", "NY", "NY", "NY","NY", "PA", "PA", "PA", "PA", "PA", "PA"), p = c("n", "n","n","n", "p", "p", "n", "n", "n", "p", "p", "p"),Year = c("1973", "1974", "1977", "1978", "1988", "1989" ,"1991", "1992", "1993", "1920", "1929", "1931"), Consecutive_Yrs = c(1,2,1,2,1,2,1,2,3,1,1,1))