weighted average price for n contracts sold

Question

I'm wondering if someone can help me out with the following: I am trying to calculate the weighted average price for n contracts sold in my dataset.

Here is the formula:

The dataset is from a limit order book that goes 10 layers deep, with each layer having its own price and volume. Furthermore, it includes a column that holds the last traded volume, which I am trying to calculate the weighted average price for. What is troubling me is how to sum up the bid prices to the point where the sum of ni = n

Here is a sample from my dataset:

Midcap_sample <- structure(list(BidPrice1 = c(274180, 274200, 272790, 273020, 
273060), BidVolume1 = c(7, 3, 1, 1, 1), BidPrice2 = c(274170, 
274190, 272780, 273010, 273050), BidVolume2 = c(7, 5, 5, 9, 3
), BidPrice3 = c(274160, 274180, 272770, 273000, 273040), BidVolume3 = c(7, 
7, 6, 7, 4), BidPrice4 = c(274150, 274170, 272760, 272990, 273030
), BidVolume4 = c(18, 8, 6, 10, 7), BidPrice5 = c(274140, 274160, 
272750, 272980, 273020), BidVolume5 = c(19, 7, 6, 6, 6), BidPrice6 = c(274130, 
274150, 272740, 272970, 273010), BidVolume6 = c(6, 6, 7, 27, 
7), BidPrice7 = c(274120, 274140, 272730, 272960, 273000), BidVolume7 = c(10, 
7, 6, 14, 7), BidPrice8 = c(274110, 274130, 272720, 272950, 272990
), BidVolume8 = c(7, 7, 5, 8, 7), BidPrice9 = c(274100, 274120, 
272710, 272940, 272980), BidVolume9 = c(5, 7, 6, 8, 6), BidPrice10 = c(274090, 
274110, 272700, 272930, 272970), BidVolume10 = c(5, 7, 6, 16, 
8), last_traded_volume = c(52, 17, 33, 55, 27)), row.names = c(NA, 
-5L), class = c("tbl_df", "tbl", "data.frame"))

Edit- I adjusted the the last_traded_volume variables as some went beyond the volume of the first 10 layers in the sample

Answer 1

Midcap_sample %>%
  pivot_longer(-last_traded_volume, names_to = c('.value', 'Bid'),
               names_pattern = 'Bid([A-z]+)(\\d+)') %>%
  group_by(last_traded_volume) %>%
  filter(lag(cumsum(Volume)<=last_traded_volume, default = TRUE)) %>%
  mutate(more = cumsum(Volume) - last_traded_volume,
        Volume = ifelse(more > 0, Volume - more, Volume)) %>%
  summarise(avg = weighted.mean(Price, Volume)) %>%
  data.frame()

  last_traded_volume avg_price
1                 52  274155.6
2                 55  272986.0
3                 68  273006.1
4                188  272740.6
5                272  274151.4