CODEWITHSUNDEEP
rloops
SlyGrogger
SlyGrogger

Reputation: 317

Iterate and sum a calculation across rows by varying row position

I have a simple dataframe as follows:

thedata <-  data.frame(values = c(30,20,10,40,20)
                     ,week = seq(from = 1, to = 5, by = 1))
thedata$lengths <-length(thedata$values):1-1

I am looking to run the following calculation across each row: 

values*0.2^lengths

...where I would like to iterate through and sum each cumulative length. For instance, the first row calculation would be:

sum(30*.20^1, 30*.20^2, 30*.20^3, 30*.20^4)

The third would be:

sum(10*.20^1, 10*.20^2)

...and so forth (the last row would be 0, as it is the last value in the time series). The approach I've had most success with so far is a loop/sapply combo:

for (i in thedata$lengths){
  print(unlist(sapply(thedata[1], function(x) {x*0.2^i})))
}

But it becomes a bit messy manipulating the data to the right format and I'll need to do something different to get the iteration working properly.

I've played around with rollapply and stats::filter/reduce combo with little success.

Note: have a similar but broader question here: Calculate running sum/decay value in time series

Part two:

For completeness, I am also interested in the same problem above, but with the added condition that each iteration uses the corresponding value from the values column. So the first row calculation would be:

sum(20*.20^1, 10*.20^2, 40*.20^3, 20*.20^4)

I think this is mostly solved with this code:

thisfunc <- function(x) { w = 1:length(x); sum(x*.2^w)}
thedata$filtervalues2 <- rollapply(thedata$values, width=5,FUN=thisfunc, align="left", partial=TRUE)
thedata
shift <- function(x, n){
  c(x[-(seq(n))], rep(NA, n))
}
thedata$filtervalues2 <- shift(thedata$filtervalues2, 1)
thedata[is.na(thedata)] <- 0
thedata

  values week filtervalues2
1     30    1         4.752
2     20    2         3.760
3     10    3         8.800
4     40    4         4.000
5     20    5         0.000

Although a bit clunky. I think I prefer this sqldf approach:

thedata$values2 <-  thedata$values
trythis <- sqldf("select a.week, 
                 sum(case when b.week > a.week 
                 then b.values2*power(0.2,b.week-a.week) 
                 else 0 end) as calc1 
                 from thedata a, 
                 thedata b  
                 group by a.week")

Upvotes: 2

Views: 273

Answers (3)

Oriol Mirosa
Oriol Mirosa

Reputation: 2826

Having seen @snoram's answer, I see that combining our two approaches you get the result in the fewest lines:

library(dplyr)

thedata %>%
  rowwise() %>%
  mutate(new = sum(values * 0.2^seq_len(lengths)))

##   values  week lengths    new
##    <dbl> <dbl>   <dbl>  <dbl>
## 1     30     1       4  7.488
## 2     20     2       3  4.960
## 3     10     3       2  2.400
## 4     40     4       1  8.000
## 5     20     5       0  0.000

Original answer

This is how I would do it:

func <- function(values, lengths) {
  calc = 0
  for(i in 1:lengths) {
    calc = calc + values * 0.2^i 
  }
  return(calc)  
}

library(dplyr)

thedata %>%
  rowwise() %>%
  mutate(new = func(values, lengths))

##   values  week lengths    new
##    <dbl> <dbl>   <dbl>  <dbl>
## 1     30     1       4  7.488
## 2     20     2       3  4.960
## 3     10     3       2  2.400
## 4     40     4       1  8.000
## 5     20     5       0 24.000

Upvotes: 2

d.b
d.b

Reputation: 32548

thedata$values * sapply(NROW(thedata):1, function(i) ifelse(i == 1, 0, sum(0.2^((i-1):1))))
#[1] 7.488 4.960 2.400 8.000 0.000

Upvotes: 0

s_baldur
s_baldur

Reputation: 33488

A rough base-R solution.

n <- nrow(thedata)
thedata$result <- numeric(n)

for (row in seq.int(to = n)) {
  len <- thedata[row, "lengths"]
  if (len > 0) {
    thedata[row, "result"] <- 
      sum(thedata[row, "values"] * 0.2 ^ seq.int(to = len))   
  }
}



thedata
  values week lengths result
1     30    1       4  7.488
2     20    2       3  4.960
3     10    3       2  2.400
4     40    4       1  8.000
5     20    5       0  0.000

Upvotes: 2

Related Questions