2

Basis is the following data table:

library(data.table)
dt <- data.table(Position = 1:3, Price = c(50, 45, 40), Volume = c(10, 10, 10))

dt
   Position Price Volume
1:        1    50     10
2:        2    45     10
3:        3    40     10

Now I would like to calculate the weighted mean for each position, taking into account all positions "<=" the current position. The result should be:

dt[, Vwa := c(50, 47.5, 45)]

dt
   Position Price Volume  Vwa
1:        1    50     10 50.0
2:        2    45     10 47.5
3:        3    40     10 45.0

Any idea how to do achieve this efficiently?

Martin Schmelzer
  • 23,283
  • 6
  • 73
  • 98
schluk5
  • 177
  • 2
  • 13

1 Answers1

5

Assuming your Position column contains unique values and has been sorted before hand, you can calculate based on the definition of weighted average. If Volume is the weight factor:

dt[, Vwa := cumsum(Price * Volume)/cumsum(Volume)]
dt
#   Position Price Volume  Vwa
#1:        1    50     10 50.0
#2:        2    45     10 47.5
#3:        3    40     10 45.0
Psidom
  • 209,562
  • 33
  • 339
  • 356
  • 1
    Great! Exactly, what I was looking for. Yes, `Position` contains unique values and is sorted. I actually used `cumsum` before, but just on pure sums. Makes sense to just apply it on the product of the two vectors. Thanks for this! – schluk5 Nov 28 '16 at 14:44