I have hourly sales data & want to aggregate it to a day level. Out of 24 hours, we are classifying 6 hrs as peak hours and 18 hrs as non-peak.

Assume peak hr sales for a day as: $X_1,X_2,\ldots,X_6$

Non-peak hr sales for a day as: $Y_1,Y_2,\ldots,Y_{18}$

How can we take a weighted average of peak hr and non-peak hrs to get the data to a day level?

Ideal case would be to use the units sold in each hour as weights. But i don't have that information. What i have instead is a supply-demand information. Out of which, the trend of hourly demand follows the trend of the hourly price.

From what I understand there are 2 ways to go about this:

  1. Use demand as weights and calculate weighted average (but demand is not equal to actual units sold)

  2. Use $$ \frac{\frac{1}{4}\sum_{i=1}^{6}X_{i} + \frac{3}{4}\sum_{i=1}^{18}Y_{i}}{15} $$ where $$ 15 = \frac{1}{4}*6 + \frac{3}{4}*18 $$

Which would be more accurate? Or is there a better way?


Just weight the respective means by $\frac{1}{4}$ and $\frac{3}{4}$, respectively.

$$ \overline{\text{Sales}} = \frac{1}{4} \sum_{i=1}^6 X_i + \frac{3}{4} \sum_{i=1}^{18} Y_i $$

  • 1
    $\begingroup$ Thanks. This might work if we are dividing by 15. I have added more information to the question. Which method would be better in your opinion? $\endgroup$ – christinehiroki May 21 at 10:05
  • $\begingroup$ I would not divide it by 15. You want to have a daily average, obtained from two components with their respective time share. I don't see why you would divide this by 15. Plus, I don't get "(but demand is not equal to actual units sold)" means. $\endgroup$ – E. Sommer May 22 at 7:25
  • $\begingroup$ Without dividing by 15, this solution is not a weighted mean (because the weights do not sum to 1). Can you explain why you think this is appropriate? $\endgroup$ – knrumsey - Reinstate Monica May 23 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.