Calculating years between occurence of 1 in a column by group

Question

I have applied both code suggestions to my original data set to compare peace1 and peace2.

peace1:

d_muslim <- d_muslim %>% 
   mutate(last_conflict = lag(if_else(conflict == 1, year,NA),default = min(year) - 1),
   .by = country) %>%     
   tidyr::fill(last_conflict, .direction = "down") %>% 
   mutate(peace1 = year - last_conflict - 1)

peace2:

d_muslim <- d_muslim %>%
  mutate(helper = cumsum(lag(conflict, default = 1) == 1),.by = country) %>% 
  mutate(peace2 = year - first(year), .by = c(country, helper)) %>%
  select(-helper)

result:

country	year	conflict	peace1	peace2
Iran	1946	0	-73	0
Iran	1947	0	-72	1
Iran	1948	0	-71	2
Iran	1949	0	-70	3
Iran	1950	0	-69	4
Iran	1951	0	-68	5
Iran	1952	0	-67	6
Iran	1953	0	-66	7
Iran	1954	0	-65	8
Iran	1955	0	-64	9
Iran	1956	0	-63	10
Iran	1957	0	-62	11
Iran	1958	0	-61	12
Iran	1959	0	-60	13
Iran	1960	0	-59	14
Iran	1961	0	-58	15
Iran	1962	0	-57	16
Iran	1963	0	-56	17
Iran	1964	0	-55	18
Iran	1965	0	-54	19
Iran	1966	1	-1	0
Iran	1967	0	0	1
Iran	1968	0	1	2
Iran	1969	0	2	3
Iran	1970	0	3	4
Iran	1971	0	4	5
Iran	1972	0	5	6
Iran	1973	0	6	7
Iran	1974	0	7	8
Iran	1975	0	8	9
Iran	1976	0	9	10
Iran	1977	0	10	11
Iran	1978	0	11	12
Iran	1979	1	-1	0
Iran	1980	0	0	1
Iran	1981	0	1	2
Iran	1982	0	2	3
Iran	1983	0	3	4
Iran	1984	0	4	5
Iran	1985	0	5	6
Iran	1986	1	-1	0
Iran	1987	0	0	1
Iran	1988	0	1	2
Iran	1989	0	2	3
Iran	1990	1	-1	0
Iran	1991	1	-1	0
Iran	1992	0	0	1
Iran	1993	1	-1	0
Iran	1994	0	0	1
Iran	1995	0	1	2
Iran	1996	1	-1	0
Iran	1997	1	-1	0
Iran	1998	0	0	1
Iran	1999	1	-1	0
Iran	2000	0	0	1
Iran	2001	0	1	2
Iran	2002	0	2	3
Iran	2003	0	3	4
Iran	2004	0	4	5
Iran	2005	1	-1	0
Iran	2006	0	0	1
Iran	2007	0	1	2
Iran	2008	0	2	3
Iran	2009	0	3	4
Iran	2010	0	4	5
Iran	2011	0	5	6
Iran	2012	0	6	7
Iran	2013	0	7	8
Iran	2014	0	8	9
Iran	2015	0	9	10
Iran	2016	1	-1	0
Iran	2017	1	-1	0
Iran	2018	1	-1	0

Both codes do not fulfill my conditions.

1. the cumulation of the years of peace must extend into the year of conflict. And not be set to 0.
2. the year after a year of conflict must start with peace == 0. As the function of peace1 does correctly.

Answer 1

I think your 15 should be 18, but otherwise ...

Using your "expected" as a starting point (for side-by-side comparison):

quux |>
  mutate(
    last_conflict = lag(if_else(conflict == 1, year, NA), default = min(year) - 1),
    .by = country) |>
  tidyr::fill(last_conflict, .direction = "down") |>
  mutate(peace2 = year - last_conflict - 1)
#     country year conflict peace last_conflict peace2
# 1  country1 1990        1     0          1989      0
# 2  country1 1991        0     0          1990      0
# 3  country1 1992        0     1          1990      1
# 4  country1 1993        0     2          1990      2
# 5  country1 1994        1     3          1990      3
# 6  country1 1995        1     0          1994      0
# 7  country1 1996        0     0          1995      0
# 8  country1 1997        0     1          1995      1
# 9  country1 1998        0     2          1995      2
# 10 country1 1999        0     3          1995      3
# 11 country1 2014        0    15          1995     18
# 12 country2 1990        0     0          1989      0
# 13 country2 1991        1     1          1989      1
# 14 country2 1992        0     0          1991      0
# 15 country2 1995        1     3          1991      3
# 16 country2 1996        0     0          1995      0
# 17 country2 2000        1     4          1995      4

---

Data

quux <- structure(list(country = c("country1", "country1", "country1", "country1", "country1", "country1", "country1", "country1", "country1", "country1", "country1", "country2", "country2", "country2", "country2", "country2", "country2"), year = c(1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2014L, 1990L, 1991L, 1992L, 1995L, 1996L, 2000L), conflict = c(1L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 1L), peace = c(0L, 0L, 1L, 2L, 3L, 0L, 0L, 1L, 2L, 3L, 15L, 0L,  1L, 0L, 3L, 0L, 4L)), class = "data.frame", row.names = c(NA, -17L))

Answer 2

We can use cumsum along with lag to create a helper column for grouping and then simply find the difference between year and the frist(year) within each group;

library(dplyr)

df1 %>% 
  mutate(helper = cumsum(lag(conflict, default = 1) == 1), .by = country) %>% 
  mutate(peace = year - first(year), .by = c(country, helper)) %>% 
  select(-helper)

#>     country year conflict peace
#> 1  country1 1990        1     0
#> 2  country1 1991        0     0
#> 3  country1 1992        0     1
#> 4  country1 1993        0     2
#> 5  country1 1994        1     3
#> 6  country1 1995        1     0
#> 7  country1 1996        0     0
#> 8  country1 1997        0     1
#> 9  country1 1998        0     2
#> 10 country1 1999        0     3
#> 11 country1 2014        0    18
#> 12 country2 1990        0     0
#> 13 country2 1991        1     1
#> 14 country2 1992        0     0
#> 15 country2 1995        1     3
#> 16 country2 1996        0     0
#> 17 country2 2000        1     4

^{Created on 2024-02-22 with reprex v2.0.2}