Change Count on every change but reset on encountering 0 in R

Question

I have a dataset DF

   structure(list(Company= c("ABC", "ABC", 
"ABC", "ABC", "ABC", 
"ABC", "ABC", "XYZ", 
"XYZ", "XYZ"), year = 1951:1960, 
    dummyconflict = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L), .Label = c("0", "1"), class = "factor")), row.names = 2:11, class = "data.frame")

I want to add another column such that it increases counts upwards. That is should a Company move from level “1” to “0” over a year, the count starts with one and if it has level “1” for the year after the count continues; 2,3,4,5,6 etc. Should it however move back to “0” again, the count starts over again with zero..

Please help in adding another column based on above condition

EXPECTED RESULTS in image

enter image description here

What is the `dummyconflict` column? Is this the 'level'? They are all 0 in your example - can you post your expected result? — Chris, Aug 08 '18 at 16:46
Possible duplicate of [Cumulative sum that resets when 0 is encountered](https://stackoverflow.com/questions/32501902/cumulative-sum-that-resets-when-0-is-encountered) — A. Suliman, Aug 08 '18 at 16:49
There are 0 & 1 in dummyconflict, sorry the sample included only 1 @Chris Yes it is a factor variable with only 0 & 1 — Vaibhav Singh, Aug 08 '18 at 16:55
So, when a Company starts from 1 the new variable should be 0, right? — AntoniosK, Aug 08 '18 at 17:14

AntoniosK · Accepted Answer · 2018-08-08T17:26:13.833

df = structure(list(Company= c("ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "XYZ", "XYZ", "XYZ"), 
                    year = 1951:1960, 
                    dummyconflict = structure(c(1L, 1L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 2L), .Label = c("0", "1"), class = "factor")), 
                  row.names = 2:11, class = "data.frame")

library(dplyr)
library(data.table)

df %>%
  mutate(dummyconflict = as.numeric(as.character(dummyconflict))) %>% # update column to numeric
  group_by(Company) %>%                                               # for each company
  mutate(dummy2 = ifelse(row_number() == 1, 0, dummyconflict)) %>%    # create dummy2 variable to ignore 1s in first row
  group_by(Company, flag = rleid(dummy2)) %>%                         # create another group based on 1s and 0s positions and group by that and company
  mutate(NewVar = cumsum(dummy2)) %>%                                 # get cumulative sum of dummy2 column
  ungroup() %>%                                                       # forget the grouping
  select(Company, year, dummyconflict, NewVar)                        # keep relevant columns

# # A tibble: 10 x 4
#   Company  year dummyconflict NewVar
#   <chr>   <int>         <dbl>  <dbl>
# 1 ABC      1951             0      0
# 2 ABC      1952             0      0
# 3 ABC      1953             1      1
# 4 ABC      1954             1      2
# 5 ABC      1955             1      3
# 6 ABC      1956             0      0
# 7 ABC      1957             1      1
# 8 XYZ      1958             1      0
# 9 XYZ      1959             1      1
#10 XYZ      1960             1      2

It would be good to run this process step by step to make sure you get how it works so you can easily spot any bugs when you apply it to your big dataset.

This is just amazing...HATS off to you Sir :D – Vaibhav Singh Aug 08 '18 at 18:40 — Vaibhav Singh, Aug 08 '18 at 18:40
You can tick the answer if it was useful :) – AntoniosK Aug 09 '18 at 12:03 — AntoniosK, Aug 09 '18 at 12:03

Change Count on every change but reset on encountering 0 in R

1 Answers1