schluk5 schluk5 - 11 days ago 5
R Question

R: Cumulative weighted mean in data.table

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?

Answer

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