schluk5 - 7 months ago 49

R Question

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

`"<="`

`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
```

Source (Stackoverflow)