i am implementing a rolling sum calculation through dplyr, but in my database i have a number of variables that have only one or only a few observations, causing an (k is smaller than n) error. i have tried to resolve this in thisj example with filter and merge, but wondering if there is a way to do this more elegantly and automatically within dplyr. please see the example below
#create data
dg = expand.grid(site = c("Boston","New York"),
year = 2000:2004)
dg$animal="dog"
dg$animal[10]="cat";dg$animal=as.factor(dg$animal)
dg$count = rpois(dim(dg)[1], 5)
If i would run the code below, because i only have one row with "cat", one gets the (Error: k <= n is not true) error
#running average
dg2 = dg %>%
arrange(site,year,animal) %>%
group_by(site,animal) %>%
# filter(animal=="dog") %>%
mutate(roll_sum = rollsum(x = count, 2, align = "right", fill = NA))
i have tried to solve this by using the following code, which filters out the "cat" value and does a subsequent merge, but I was wondering whether one can do this directly inside dplyr, especially as in this solution one would have to specify / know the number of unique rows for each variable in advance and adjust manually if one would change the range of the rolling sum, etc.
dg2 = dg %>%
arrange(site,year,animal) %>%
group_by(site,animal) %>%
filter(animal=="dog") %>%
mutate(roll_sum = rollsum(x = count, 2, align = "right", fill = NA))
merge(dg,dg2,c("site", "year","animal","count"),all.x=TRUE)
site year animal count roll_sum
1 Boston 2000 dog 5 NA
2 Boston 2001 dog 6 11
3 Boston 2002 dog 6 12
4 Boston 2003 dog 5 11
5 Boston 2004 dog 3 8
6 New York 2000 dog 8 NA
7 New York 2001 dog 3 11
8 New York 2002 dog 12 15
9 New York 2003 dog 3 15
10 New York 2004 cat 3 NA
Many thanks - W
You can instead use RcppRoll::roll_sum
which returns NA if the sample size(n
) is less than the window size(k
).
set.seed(1)
dg$count = rpois(dim(dg)[1], 5)
library(RcppRoll)
library(dplyr)
dg %>%
arrange(site,year,animal) %>%
group_by(site, animal) %>%
mutate(roll_sum = roll_sum(count, 2, align = "right", fill = NA))
# site year animal count roll_sum
#1 Boston 2000 dog 4 NA
#2 Boston 2001 dog 5 9
#3 Boston 2002 dog 3 8
#4 Boston 2003 dog 9 12
#5 Boston 2004 dog 6 15
#6 New York 2000 dog 4 NA
#7 New York 2001 dog 8 12
#8 New York 2002 dog 8 16
#9 New York 2003 dog 6 14
#10 New York 2004 cat 2 NA
roll_Sum from RcppRoll will return an NA in place of an error wherever the number of data points are the less than the window size.
However, in case you want to return the sum of the number of data points present - even if lesser than the window the size, you can use the rollapplyr function from zoo.
library(zoo)
library(dplyr)
dg %>%
arrange(site,year,animal) %>%
group_by(site, animal) %>%
mutate(roll_sum = roll_sum(count, 2, align = "right", fill = NA)) %>%
mutate(rollapply_sum =rollapplyr(count, 2, sum, partial = TRUE) )
Rollapply_sum will return the original value or the sum of data points present, even if its less than the window size instead of returning NA.
site year animal count roll_sum rollapply_sum
(fctr) (int) (fctr) (int) (dbl) (int)
1 Boston 2000 dog 4 NA 4
2 Boston 2001 dog 5 9 9
3 Boston 2002 dog 3 8 8
4 Boston 2003 dog 9 12 12
5 Boston 2004 dog 6 15 15
6 New York 2000 dog 4 NA 4
7 New York 2001 dog 8 12 12
8 New York 2002 dog 8 16 16
9 New York 2003 dog 6 14 14
10 New York 2004 cat 2 NA 2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With