CODEWITHSUNDEEP
rsumconditional-statementsrolling-computation
Janjua
Janjua

Reputation: 237

Years based rolling window and count of column value in R

Trying to create a rolling window sums based on years, value etc slice of my data table

 dt <- structure(list(row = c("2481", "1153", "1898", "2090", "2423", 
"568", "1275", "839", "421", "478", "1270", "2449", "1059", "2449", 
"1686", "1620", "633", "684", "1686", "684", "1816", "2129", 
"2262", "2129", "1412", "2149", "204", "792", "935", "1151", 
"1855", "1816", "605", "1842", "2588", "76", "337", "515", "1764", 
"1815", "1710", "203", "1360", "1456", "1239", "962", "1296", 
"1851", "1978", "1978"), year = c(1984, 1985, 1986, 1986, 1986, 
1987, 1987, 1988, 1989, 1989, 1994, 1994, 1994, 1994, 1994, 1994, 
1994, 1994, 1994, 1994, 1995, 1995, 1995, 1995, 1995, 1995, 1995, 
1995, 1995, 1995, 1995, 1995, 1996, 1996, 1996, 1996, 1996, 1996, 
1996, 1996, 1996, 1996, 1996, 1996, 1996, 1996, 1996, 1996, 1996, 
1996), variable = c("X1", "X1", "X1", "X1", "X1", "X1", "X1", 
"X1", "X1", "X1", "X1", "X1", "X1", "X2", "X1", "X1", "X1", "X1", 
"X2", "X2", "X1", "X1", "X1", "X2", "X1", "X1", "X1", "X1", "X1", 
"X1", "X1", "X2", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", 
"X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X2"), 
    value = c("Sh", "Sh", "Sh", "Sh", "Sh", "Sh", "SM", "Sh", 
    "Sh", "SM", "Ap", "De", "El", "El", "Ga", "Ic", "SA", "St", 
    "St", "Th", "Au", "Bi", "El", "El", "Pe", "SA", "St", "St", 
    "St", "St", "St", "St", "Ap", "Ap", "Ap", "Au", "Ca", "Ca", 
    "Ca", "Ca", "Co", "El", "El", "El", "ES", "Ic", "Ic", "Ic", 
    "MC", "Mi"), valueCount = c(8L, 8L, 8L, 8L, 8L, 8L, 2L, 8L, 
    8L, 2L, 4L, 1L, 7L, 7L, 1L, 4L, 2L, 8L, 8L, 1L, 2L, 1L, 7L, 
    7L, 1L, 2L, 8L, 8L, 8L, 8L, 8L, 8L, 4L, 4L, 4L, 2L, 4L, 4L, 
    4L, 4L, 1L, 7L, 7L, 7L, 1L, 4L, 4L, 4L, 1L, 1L), yearlycount = c(1L, 
    1L, 3L, 3L, 3L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 1L, 1L, 
    1L, 2L, 2L, 1L, 1L, 1L, 2L, 2L, 1L, 1L, 6L, 6L, 6L, 6L, 6L, 
    6L, 3L, 3L, 3L, 1L, 4L, 4L, 4L, 4L, 1L, 3L, 3L, 3L, 1L, 3L, 
    3L, 3L, 1L, 1L), yeartotal = c(1L, 1L, 3L, 3L, 3L, 2L, 2L, 
    1L, 2L, 2L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
    10L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 
    12L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 
    18L, 18L, 18L, 18L, 18L, 18L, 18L)), row.names = c(NA, -50L
), .Names = c("row", "year", "variable", "value", "valueCount", 
"yearlycount", "yeartotal"), class = "data.frame")

I want to calculate sum for each 'value' in the given year based on the column 'yearlycount' over the 3 preceding years as shown in the 'sum-ycount' Furthermore, I also want to calculate 'yeartotal' each 'year' based on the sum of the 3 preceding years as shown 'sum-ytotal'

year    value   sum-ycount  sum-ytotal
1987    Sh  5   5
1987    SM  0   0
1988    Sh  5   6
1989    Sh  5   6
1989    SM  1   2
1994    Ap  0   0

Upvotes: 0

Views: 93

Answers (1)

Daniel O
Daniel O

Reputation: 4358

This creates what I believe you are looking for using only Base-R.

t$sumycount <- NA
dt$sumytotal <- NA
span <- 3

for(i in 1:nrow(dt)){
  val <- dt$value[i]
  yr <- dt$year[i]

  slice <- do.call(rbind,lapply(1:span, function(x) head(dt[with(dt,year==yr-x) & with(dt,value==val),],n=1)))
  dt$sumycount[i] <- sum(slice$yearlycount)
  dt$sumytotal[i] <- sum(slice$yeartotal)
}

output

> dt
    row year variable value valueCount yearlycount yeartotal sumycount sumytotal
1  2481 1984       X1    Sh          8           1         1         0         0
2  1153 1985       X1    Sh          8           1         1         1         1
3  1898 1986       X1    Sh          8           3         3         2         2
4  2090 1986       X1    Sh          8           3         3         2         2
5  2423 1986       X1    Sh          8           3         3         2         2
6   568 1987       X1    Sh          8           1         2         5         5
7  1275 1987       X1    SM          2           1         2         0         0
8   839 1988       X1    Sh          8           1         1         5         6
9   421 1989       X1    Sh          8           1         2         5         6
10  478 1989       X1    SM          2           1         2         1         2
11 1270 1994       X1    Ap          4           1        10         0         0
12 2449 1994       X1    De          1           1        10         0         0
13 1059 1994       X1    El          7           2        10         0         0
14 2449 1994       X2    El          7           2        10         0         0
15 1686 1994       X1    Ga          1           1        10         0         0
16 1620 1994       X1    Ic          4           1        10         0         0
17  633 1994       X1    SA          2           1        10         0         0
18  684 1994       X1    St          8           2        10         0         0
19 1686 1994       X2    St          8           2        10         0         0
20  684 1994       X2    Th          1           1        10         0         0
21 1816 1995       X1    Au          2           1        12         0         0
22 2129 1995       X1    Bi          1           1        12         0         0
23 2262 1995       X1    El          7           2        12         2        10
24 2129 1995       X2    El          7           2        12         2        10
25 1412 1995       X1    Pe          1           1        12         0         0
26 2149 1995       X1    SA          2           1        12         1        10
27  204 1995       X1    St          8           6        12         2        10
28  792 1995       X1    St          8           6        12         2        10
29  935 1995       X1    St          8           6        12         2        10
30 1151 1995       X1    St          8           6        12         2        10
31 1855 1995       X1    St          8           6        12         2        10
32 1816 1995       X2    St          8           6        12         2        10
33  605 1996       X1    Ap          4           3        18         1        10
34 1842 1996       X1    Ap          4           3        18         1        10
35 2588 1996       X1    Ap          4           3        18         1        10
36   76 1996       X1    Au          2           1        18         1        12
37  337 1996       X1    Ca          4           4        18         0         0
38  515 1996       X1    Ca          4           4        18         0         0
39 1764 1996       X1    Ca          4           4        18         0         0
40 1815 1996       X1    Ca          4           4        18         0         0
41 1710 1996       X1    Co          1           1        18         0         0
42  203 1996       X1    El          7           3        18         4        22
43 1360 1996       X1    El          7           3        18         4        22
44 1456 1996       X1    El          7           3        18         4        22
45 1239 1996       X1    ES          1           1        18         0         0
46  962 1996       X1    Ic          4           3        18         1        10
47 1296 1996       X1    Ic          4           3        18         1        10
48 1851 1996       X1    Ic          4           3        18         1        10
49 1978 1996       X1    MC          1           1        18         0         0
50 1978 1996       X2    Mi          1           1        18         0         0

Upvotes: 1

Related Questions