How to calculate weighted average using slide_index_dbl()?

April 16, 2023

In R I’m looking to create a row calculation that averages prices based on Product type and a date range based on Order_Date. Additionally, I need to weight the average using quantity. In the example from Excel, I’m using a SUMPRODUCT function that has three criteria: 1) Product type, 2) beginning date, and 3) ending date. For example Product type is --(A:A=A4). These three criteria then calculate a sum product of Price and Quantity. Lastly, I’m dividing the sum product by the sum of Quantity based on the previously mentioned criteria.

Here’s my dataset:

structure(list(Product = c("A", "B", "B", "F", "D", "F", "A", 
"F", "D", "A", "A", "D", "C", "C", "A", "B", "C", "A", "B", "A", 
"A", "E", "D", "F", "B", "B", "E", "F", "F", "E", "F", "A", "A", 
"D", "F", "C", "C", "C", "A", "D", "D", "E", "D", "B", "C", "B", 
"D", "F", "C", "A", "A", "F", "D", "E", "B", "B", "A", "E", "A", 
"D", "E", "C", "C", "C", "E", "D", "F", "E", "B", "E", "D", "B", 
"A", "B", "D", "F", "C", "C", "E", "A", "A", "F", "D", "D", "A", 
"F", "C", "A", "A", "F", "A", "B", "A", "D", "C", "C", "A", "B", 
"E", "B", "D", "B", "F", "F", "B", "C", "B", "B", "C", "F", "A", 
"B", "A", "E", "C", "E", "E", "E", "D", "B", "C", "D", "B", "C", 
"F", "F", "C", "F", "D", "F", "A", "D", "B", "B", "C", "E", "B"
), Price = c(6.502, 4.526, 2.272, 9.097, 0.594, 5.41, 9.85, 1.197, 
5.09, 7.343, 3.339, 1.107, 7.993, 7.922, 4.558, 4.75, 1.278, 
9.55, 8.223, 6.195, 0.668, 9.741, 9.679, 3.488, 4.159, 3.756, 
6.233, 7.658, 8.896, 9.724, 6.582, 7.422, 1.172, 2.734, 4.917, 
0.784, 9.284, 0.7, 6.869, 3.054, 9.945, 4.173, 5.217, 6.016, 
5.559, 5.247, 7.024, 8.845, 8.436, 7.482, 8.609, 3.675, 2.76, 
5.357, 4.125, 3.199, 7.736, 5.255, 5.581, 5.282, 2.753, 1.568, 
2.083, 8.938, 7.562, 7.513, 9.493, 8.404, 5.266, 9.992, 3.813, 
5.522, 6.295, 5.385, 1.91, 3.597, 4.105, 9.484, 6.697, 4.818, 
1.644, 2.699, 0.608, 9.6, 0.447, 4.123, 2.997, 5.085, 0.903, 
5.455, 1.869, 4.053, 8.843, 1.171, 8.491, 9.236, 5.642, 6.565, 
3.168, 4.367, 6.008, 6.267, 8.363, 0.318, 5.226, 6.597, 2.932, 
3.149, 8.578, 1.814, 9.288, 9.96, 8.44, 3.514, 2.832, 5.881, 
4.57, 7.646, 2.19, 5.446, 5.318, 3.674, 6.235, 9.414, 7.201, 
9.846, 0.824, 0.757, 4.928, 0.499, 9.165, 1.564, 6.944, 3.474, 
8.537, 5.412, 7.142), Quantity = c(25, 33, 25, 12, 39, 28, 24, 
2, 8, 22, 18, 14, 38, 42, 41, 7, 20, 5, 48, 40, 8, 11, 48, 34, 
45, 14, 27, 2, 36, 33, 36, 37, 5, 30, 44, 41, 4, 42, 29, 41, 
32, 47, 22, 48, 30, 39, 44, 15, 34, 33, 41, 43, 3, 34, 8, 40, 
28, 10, 16, 40, 33, 37, 1, 17, 21, 25, 26, 50, 37, 43, 8, 22, 
45, 13, 19, 27, 39, 42, 10, 39, 45, 43, 23, 22, 38, 38, 31, 49, 
46, 13, 27, 33, 23, 5, 20, 21, 27, 27, 12, 31, 38, 12, 28, 39, 
23, 50, 6, 11, 6, 19, 9, 3, 2, 39, 37, 45, 34, 46, 33, 34, 46, 
46, 34, 45, 50, 17, 12, 22, 50, 37, 43, 6, 21, 27, 29, 12, 40
), Order_Date = structure(c(19208, 19210, 19337, 19288, 19339, 
19318, 19352, 19290, 19245, 19317, 19352, 19177, 19306, 19300, 
19196, 19314, 19270, 19212, 19232, 19199, 19238, 19274, 19265, 
19311, 19257, 19344, 19294, 19331, 19181, 19348, 19317, 19332, 
19334, 19338, 19184, 19274, 19240, 19350, 19195, 19306, 19198, 
19283, 19260, 19263, 19177, 19197, 19186, 19205, 19264, 19328, 
19268, 19190, 19210, 19300, 19273, 19188, 19280, 19264, 19211, 
19241, 19296, 19350, 19260, 19201, 19254, 19262, 19176, 19175, 
19335, 19179, 19196, 19190, 19253, 19178, 19190, 19234, 19253, 
19310, 19323, 19325, 19258, 19292, 19213, 19269, 19225, 19299, 
19350, 19224, 19193, 19250, 19242, 19260, 19340, 19276, 19211, 
19222, 19305, 19305, 19273, 19268, 19306, 19277, 19225, 19188, 
19190, 19181, 19268, 19291, 19272, 19238, 19184, 19228, 19199, 
19237, 19205, 19251, 19357, 19238, 19288, 19301, 19331, 19260, 
19320, 19175, 19253, 19312, 19354, 19357, 19176, 19187, 19225, 
19281, 19352, 19315, 19246, 19308, 19356), class = "Date"), Weighted_Mean = c(4.86222058823529, 
4.91654166666667, 4.63969072164948, 4.04514736842105, 1.5244347826087, 
5.89505982905983, 7.69063076923077, 4.04514736842105, 4.61877586206897, 
6.47659493670886, 7.69063076923077, 4.78144881889764, 8.48185245901639, 
8.48185245901639, 4.94103365384615, 5.4376511627907, 3.90273529411765, 
5.61707339449541, 8.32517647058824, 5.04922535211268, 4.75870476190476, 
4.9231, 6.86458333333333, 5.4318954248366, 4.67877914110429, 
5.37029197080292, 4.59054248366013, 6.11739393939394, 4.32138666666667, 
7.10853731343284, 5.89505982905983, 6.79381761006289, 6.70562773722628, 
1.5244347826087, 4.32138666666667, 3.90273529411765, 6.17783333333333, 
1.55910655737705, 4.887703125, 4.47491139240506, 5.55313178294574, 
4.61875384615385, 6.94804093567251, 4.75871584699454, 7.362, 
4.60912738853503, 5.72447904191617, 5.01206896551724, 3.95868085106383, 
6.79381761006289, 5.8291320754717, 4.6041125, 5.62133333333333, 
4.77567924528302, 4.95427536231884, 4.70277372262774, 8.25473913043478, 
6.27438383838384, 5.44942084942085, 5.25, 4.59054248366013, 1.55910655737705, 
4.73968823529412, 5.76418918918919, 6.26311842105263, 6.86458333333333, 
4.32138666666667, 9.13823655913978, 4.69287272727273, 9.13823655913978, 
6.6261320754717, 4.70277372262774, 4.61464457831325, 4.48619387755102, 
5.72447904191617, 4.94255405405405, 6.85711214953271, 8.48185245901639, 
5.99609090909091, 6.79381761006289, 5.4215572519084, 4.04514736842105, 
5.87075862068965, 6.68180459770115, 5.0480251572327, 4.59084246575342, 
1.55910655737705, 5.0480251572327, 4.887703125, 5.67598780487805, 
4.238525, 4.65287134502924, 6.78867597765363, 6.84263309352518, 
6.53164210526316, 8.87258536585366, 6.40571428571429, 5.25782857142857, 
4.9231, 4.75871584699454, 4.47491139240506, 5.03948275862069, 
4.94255405405405, 4.32138666666667, 4.70277372262774, 7.362, 
4.75871584699454, 5.62216666666667, 3.90273529411765, 5.81543065693431, 
4.64604191616766, 8.32517647058824, 5.04922535211268, 5.79543846153846, 
5.76418918918919, 5.99229192546584, 7.10853731343284, 5.79543846153846, 
1.98884090909091, 4.94439622641509, 5.318, 6.94804093567251, 
5.38738636363636, 7.362, 5.67598780487805, 5.4318954248366, 1.55910655737705, 
0.757, 4.78144881889764, 4.32138666666667, 5.0480251572327, 4.52589393939394, 
5.40888, 5.4376511627907, 6.1217397260274, 4.94131034482759, 
6.45450666666667)), row.names = c(NA, -137L), class = "data.frame")

Here’s my Excel function:

Here’s what I’ve tried in R:

df <- df %>%
  arrange(Order_Date) %>%
  mutate(
    WAVG = slide_index_dbl(
      Price,
      Order_Date, 
      ~weighted.mean(
        Price,
        Quantity
      ), 
      .before = days(15), .after = days(15)
    ),
    .by = Product
  )

While WAVG does calculate, it doesn’t tie-out to my Weighted_Mean column from my Excel spreadsheet (column E). Here is the output from the code:

c(6.3222426035503, 5.6334269005848, 4.89748936170213, 4.90580536912752, 
4.90580536912752, 5.6334269005848, 5.22891776798825, 6.3222426035503, 
4.89748936170213, 5.6334269005848, 4.89748936170213, 5.55218692810458, 
4.90580536912752, 4.89748936170213, 5.22891776798825, 4.89748936170213, 
4.89748936170213, 5.22891776798825, 4.90580536912752, 5.22891776798825, 
5.55218692810458, 5.55218692810458, 5.55218692810458, 4.90580536912752, 
5.22891776798825, 4.90580536912752, 5.55218692810458, 5.55218692810458, 
5.6334269005848, 4.89748936170213, 5.6334269005848, 5.55218692810458, 
5.22891776798825, 4.90580536912752, 5.55218692810458, 5.6334269005848, 
5.55218692810458, 4.90580536912752, 5.6334269005848, 5.55218692810458, 
5.55218692810458, 4.89748936170213, 5.55218692810458, 5.22891776798825, 
5.22891776798825, 4.89748936170213, 6.3222426035503, 5.55218692810458, 
4.89748936170213, 6.3222426035503, 5.6334269005848, 4.90580536912752, 
5.55218692810458, 4.90580536912752, 5.6334269005848, 4.89748936170213, 
6.3222426035503, 5.55218692810458, 5.6334269005848, 4.89748936170213, 
6.3222426035503, 5.22891776798825, 5.55218692810458, 4.90580536912752, 
5.6334269005848, 5.22891776798825, 4.90580536912752, 4.90580536912752, 
5.22891776798825, 5.6334269005848, 6.3222426035503, 4.90580536912752, 
5.55218692810458, 5.22891776798825, 5.22891776798825, 4.90580536912752, 
5.6334269005848, 5.6334269005848, 5.22891776798825, 6.3222426035503, 
6.3222426035503, 5.6334269005848, 4.90580536912752, 5.22891776798825, 
5.55218692810458, 4.90580536912752, 6.3222426035503, 4.89748936170213, 
4.90580536912752, 4.89748936170213, 5.22891776798825, 4.89748936170213, 
6.3222426035503, 6.3222426035503, 4.89748936170213, 5.6334269005848, 
6.3222426035503, 5.22891776798825, 5.55218692810458, 5.22891776798825, 
5.6334269005848, 4.90580536912752, 4.90580536912752, 6.3222426035503, 
5.6334269005848, 4.89748936170213, 4.89748936170213, 5.22891776798825, 
5.22891776798825, 5.55218692810458, 4.89748936170213, 4.89748936170213, 
5.22891776798825, 6.3222426035503, 5.55218692810458, 5.55218692810458, 
4.89748936170213, 5.6334269005848, 5.55218692810458, 5.55218692810458, 
5.22891776798825, 5.22891776798825, 4.90580536912752, 4.90580536912752, 
5.55218692810458, 5.22891776798825, 6.3222426035503, 5.6334269005848, 
5.6334269005848, 5.6334269005848, 5.55218692810458, 5.55218692810458, 
5.22891776798825, 5.6334269005848, 5.22891776798825, 6.3222426035503, 
4.89748936170213)

Here is the View(df):

>Solution :

df %>%
  arrange(Order_Date) %>%
  mutate(
    WAVG = slide_index2_dbl(
      Price,
      Quantity,
      Order_Date, 
      ~sum(.x * .y) / sum(.y),
      .before = days(15), .after = days(15)
    ),
    .by = Product
  )