Geek Answers Handbook

Efficient calculation of row row summation in R

I need to calculate the moving amount of a column row in a data frame in which there is a condition. The data that I have have several observations for sku. I want to calculate the sum of 5 consecutive lines for each value of "sku". In the event that I get to the stage when I do not have 5 consecutive observations for "sku", we would summarize the remaining observations of the string for this value.

As an illustrative example, consider the following data frame:

data <- structure(list(sku = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
                           2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
                           3L, 3L, 3L, 3L), tf = c(50.79, 46.39, 47.85, 45.79, 44.46, 49.99, 
                                                   46.12, 44.4, 41.21, 53.7, 53.9, 44.91, 59.64, 41.96, 52.26, 43.48, 
                                                   46.93, 51.2, 54.31, 42.5, 47.2, 57.54, 63.23, 48.98, 52.38, 59.9, 
                                                   53.01, 50.35, 41.86, 46.42)), .Names = c("sku", "tf"), row.names = c(NA, 
                                                  -30L), class = "data.frame")

In this data frame, we want to summarize the "tf" variable for the 5 rolling values ​​of each "sku" value.

We were able to accomplish this using the following code:

data[,c("day_5")]<-unlist(mapply(function(y){
end1<-(which(data$sku==unique(data$sku)[y]))[length(which(data$sku==unique(data$sku)[y]))]  
start<-(which(data$sku==unique(data$sku)[y]))[1]
d<-data$tf[start:end1]
r<-mapply(function(x){if (x+4 <= length(d)) {sum(d[x:(x+4)])} else {sum(d[x:length(d)])}},1:length(d))
},1:length(unique(data$sku))))

"day_5" , , , "sku".

- , ?

+4
3

data.table. , .

library(data.table)
DT <- data.table(data)

library(zoo)
fun <- function(x, i) {
  x <- c(x, rep(0, i-1))
  rollsumr(x, k=i)
}

DT[, day_5a:=fun(tf,5), by=sku]
print(DT)

#     sku    tf  day_5 day_5a
# 1:    1 50.79 235.28 235.28
# 2:    1 46.39 234.48 234.48
# 3:    1 47.85 234.21 234.21
# 4:    1 45.79 230.76 230.76
# 5:    1 44.46 226.18 226.18
# 6:    1 49.99 181.72 181.72
# 7:    1 46.12 131.73 131.73
# 8:    1 44.40  85.61  85.61
# 9:    1 41.21  41.21  41.21
# 10:   2 53.70 254.11 254.11
# 11:   2 53.90 252.67 252.67
#<snip>
+4

, :

fun <- function(x, i) {
  x <- c(x, rep(0, i-1))
  rollsumr(x, k=i)
}
data$day_5_a <- ave(data$tf, data$sku, FUN= function(x) fun(x, 5))
+1

base (, , data.table):

data_ls <- split(data, data$sku)

res <- lapply(data_ls, 
           function(z) sapply(1:length(z$tf), 
               function(vec, x) { sum(vec[x:(x+4)], na.rm = T) }, 
                 vec = z$tf))

data$day_5 <- unlist(res)

#> data
#   sku    tf  day_5
#1    1 50.79 235.28
#2    1 46.39 234.48
#3    1 47.85 234.21
#4    1 45.79 230.76
#5    1 44.46 226.18
#6    1 49.99 181.72
#7    1 46.12 131.73
#8    1 44.40  85.61
#9    1 41.21  41.21
#10   2 53.70 254.11
#11   2 53.90 252.67
#12   2 44.91 242.25
+1

More articles:


All Articles