zoo::rollapply window with over column values rather than rows

Question

dat = structure(list(index = c(10505L, 10506L, 10511L, 10539L, 10542L, 
10579L, 10642L, 11008L, 11012L, 13011L, 13110L, 13116L, 13118L, 
13156L, 13259L, 13273L, 13313L, 13365L, 13380L, 13382L, 13445L, 
13453L, 13482L, 13483L, 13494L, 13543L, 13550L, 14462L, 14464L, 
14564L, 14599L, 14604L, 14674L, 14719L, 14728L, 14775L, 14860L, 
14874L, 14930L, 14933L, 14975L, 15031L, 15089L, 15117L, 15179L, 
15211L, 15241L, 15245L, 15255L, 15260L, 15418L, 15585L, 15627L, 
15644L, 15774L, 15776L, 15777L, 15790L, 15791L, 15833L, 15849L, 
15850L, 15886L, 16042L, 16127L, 16140L, 16141L, 16142L, 16365L, 
16485L, 16489L, 16515L, 16542L, 16738L, 16834L, 16949L, 17272L, 
17462L, 17569L, 17571L, 17641L, 17654L, 17694L, 17695L, 17709L, 
17748L, 17836L, 17922L, 18643L, 20113L, 20131L, 28914L, 29318L, 
30524L, 30741L, 30912L, 30923L, 30998L, 46650L, 46698L), V2 = c(3L, 
3L, 3L, 2L, 2L, 2L, 2L, 1L, 0L, 3L, 2L, 2L, 2L, 0L, 1L, 1L, 0L, 
0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 
0L, 0L, 1L, 2L, 2L, 2L, 2L, 1L, 0L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 
2L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 
0L, 0L, 0L, 2L, 3L, 5L, 3L, 0L, 0L, 3L, 1L, 0L, 3L, 0L, 0L, 2L, 
1L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 2L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 
1L, 1L, 1L)), row.names = c(NA, -100L), class = "data.frame")

Let's say I want to calculate a function across dat in a rolling window.

n_sites = function(x) {
    return(sum(x > 1))
}

zoo::rollapply(dat$V2, FUN=n_sites, width=100)

However, rather than using the number of rows as the window size, I'd like to use the actual numeric values in the index column. So I would like to have each window so that it includes roughly 100 units in the index column. Given there is roughly 100 units of index between the 1st and 7th rows, the first window would include these rows. Is this possible?

Happy for a solution using zoo or data.table or the like.

score 2 · Answer 1 · answered May 14 '21 at 09:58

You may also use package runner where argument idx is exactly what you're looking for

dat$n_sites <- runner::runner(x = dat$V2,
                              idx = dat$index,
                              k = 100,
                              f = n_sites)

head(dat, 10)
   index V2 n_sites
1  10505  3       1
2  10506  3       2
3  10511  3       3
4  10539  2       4
5  10542  2       5
6  10579  2       6
7  10642  2       2
8  11008  1       0
9  11012  0       0
10 13011  3       1

Waldi · Answer 2 · 2021-05-11T09:14:53.400

1

You could use slider::slide_index instead of zoo::rollapply:

library(slider)
dat$n_sites <- slider::slide_index(.x = dat$V2,
                                   .i = dat$index,
                                   .f = n_sites,
                                   .before = 100)

head(dat,10)
   index V2 n_sites
1  10505  3       1
2  10506  3       2
3  10511  3       3
4  10539  2       4
5  10542  2       5
6  10579  2       6
7  10642  2       3
8  11008  1       0
9  11012  0       0
10 13011  3       1

edited May 11 '21 at 09:14

answered May 11 '21 at 09:03

Waldi

39,242
6
30
78

Why row7 output is 3? when window size is 100 and not 101? – AnilGoyal May 14 '21 at 09:59
1

@Anilgoyal, `.before` is the number of values before current index, so 10642-100 goes down to 10542. Depending on what OP expects, argument value could be 99 – Waldi May 14 '21 at 10:08

G. Grothendieck · Accepted Answer · 2021-05-11T22:30:46.223

The width in rollapply can be a vector such that the i-th element is the width to use for the i-th row. There are a number of interpretations of the question. We could use the largest width covering no more than 100 index units, the smallest width at least 100 index units or the closest width to 100 index units. The question seems to ask for the third but the example width of 7 is not consistent with that and suggests that maybe the second interpretation is wanted. We give all three widths at the end. Pick whichever you want. Also the question says that the first window is 7 so that indicates that left alignment is wanted.

library(zoo)

w <- w2 # see calcs of w1, w2 and w3 at end.  Use whichever you want.
transform(dat, roll = rollapplyr(V2, w, n_sites, fill = NA, align = "left"))

If n_sites is just a stand-in for the actual function then we can use the above but if it is the actual function we can eliminate it and write it like this:

transform(dat, roll = rollapplyr(V2 > 1, w, sum, fill = NA, align = "left"))

Width

Many variations of this are possible and we compute the three mentioned here.

The code below use base R's findInterval. Recall that findInterval(x, vec), where x and vec are vectors and vec is non-decreasing, returns a vector the same length as x such that the ith component of the result is sum(x[i] >= vec) but does it more efficiently. That is, if x[i] is found in vec then it finds the last position in vec that equals x[i] and if x[i] is not in vec then it finds the last position in vec that is less than x[i]. Note that it returns positions, i.e. indexes, not values of vec. For example, findInterval(c(20, 30), c(10, 30, 30, 30, 40)) returns c(1, 4) since 1 is the position of the largest value in vec less than 20 and 4 is the position of the last value in vec equal to 30.

n <- nrow(dat)
index <- dat$index

# i1 is row number of last index no more than current index + 100
i1 <- findInterval(index + 100, index)
w1 <- i1 - 1:n + 1

# i2 is row number of first index at least equal to index + 100
i2 <- pmin(findInterval(index + 100 - 1, index) + 1, n)
w2 <- i2 - 1:n + 1
w2[1]
## [1] 7

# i is row number of index closest to current index + 100
i <- ifelse(index + 100 - index[i1] <= index[i2] - (index + 100), i1, i2)
w3 <- i - 1:n + 1

zoo::rollapply window with over column values rather than rows

3 Answers3

Width