count number of consecutive cells under some conditions

sc5994a 0
Viewed 22

I'd like to create a new column "mzpceyrs" that records the number of years the variable "code" remains at peace. The "mzinit" variable codes whether or not "ccode" initiates a conflict in a given year.

ccode year mzinit mzpceyrs
2 1816 1 NA
2 1817 1 0
2 1818 1 0
2 1819 0 1
2 1820 0 ??
2 1821 0 ??

I suppose there would be far more efficient ways to do this, but the following codes are the ones that I've been coming up with. I basically consider four different scenarios:

for(i in 1:nrow(test)){
  previousindex<-i-1
  if(identical(previousindex,integer(0))){
    test$mzpceyrs[i]<-NA}
  else if(
    test$mzinit[previousindex]==1 & test$mzinit[i]==1){
      test$mzpceyrs[i]<-0
    }
    else if(test$mzinit[previousindex]==1 & test$mzinit[i]==0){
      test$mzpceyrs[i]<-1
    }
    else if(test$mzinit[previousindex]==0 & test$mzinit[i]==1){
      test$mzpceyrs[i]<-0
    }
    else if(test$mzinit[previousindex]==0 & test$mzinit[i]==0){
      test$mzpceyrs[i]<-??
    }
  }

i) If "ccode" initiates a conflict in the previous year AND in the current year, I assign a value of 0 (no peace year).

ii) If "ccode" initiates a conflict in the previous year and DOES NOT in the current year, I assign a value of 1 (one peace year).

iii) If "ccode" does NOT initiate a conflict in the previous year and DOES initiate a conflict in the current year, I assign a value of 0 (no peace year).

iv) If "ccode" DOES NOT initiate a conflict in the previous year and DOES in the current year, I assign the number of peace years "ccode" has remaining.

I'm struggling with how to code the last scenario. Would you be able to share your insights in terms of how to calculate consecutive "0" values after it has 1 in the "mzinit" column? For example, my desired outcome is to have 2 and 3 in the "mzpceyrs" variable in rows 5 and 6. Any advice would be much appreciated.

dplyr r
2 Answers

Using data.table

library(data.table)
setDT(test)[, mzcpeyrs := rowid(mzinit)* (mzinit == 0)]

-output

> test
   ccode  year mzinit mzcpeyrs
   <int> <int>  <int>    <int>
1:     2  1816      1        0
2:     2  1817      1        0
3:     2  1818      1        0
4:     2  1819      0        1
5:     2  1820      0        2
6:     2  1821      0        3

data

test <- structure(list(ccode = c(2L, 2L, 2L, 2L, 2L, 2L), year = 1816:1821, 
    mzinit = c(1L, 1L, 1L, 0L, 0L, 0L)), class = "data.frame", row.names = c(NA, 
-6L))
akrun0

You can use cumsum() combined with rleid() from the data.table package:

library(data.table)
setDT(df)[, mzcpeyrs:=cumsum(mzinit==0),rleid(mzinit)]

Output:

   ccode year mzinit mzcpeyrs
1:     2 1816      1        0
2:     2 1817      1        0
3:     2 1818      1        0
4:     2 1819      0        1
5:     2 1820      0        2
6:     2 1821      0        3

Input:

structure(list(ccode = c(2L, 2L, 2L, 2L, 2L, 2L), year = 1816:1821, 
    mzinit = c(1L, 1L, 1L, 0L, 0L, 0L)), row.names = c(NA, -6L
), class = "data.frame")

Alternatively, you can use this data.table::rleid() function within a dplyr pipeline of course, but it is slower and more verbose:

df %>% 
  group_by(rl = data.table::rleid(mzinit)) %>% 
  mutate(mzcpeyrs = cumsum(mzinit==0)) %>%
  ungroup() %>% 
  select(-rl)
langtang0
Related