3
$\begingroup$

I have a range of data (server performance statistics) is formatted as follows, for each server:

Time            | Average |  Min  |  Max  | StdDev  | SampleCount |
-------------------------------------------------------------------
Monday 1st      |    125  |   15  |  220  | 12.56   |     5       |
Tuesday 2nd     |    118  |   11  |  221  | 13.21   |     4       |
Wednesday 3rd   |    118  |   11  |  221  | 13.21   |     3       |
....            |    ...  |   ..  |  ...  | .....   |     .       |
and so on...

These data points are calculated from data that has a finer resolution (e.g. hourly data).

I need to aggregate this data into a single summary point so the end result is a list of servers and an aggregate average, min, max, standard deviation.

For average, I take the average of all the averages. For min, we take the minimum min. For max, we take the maximum max.

However, I'm not sure what method I should be using to aggregate standard deviation? I've seen various answers including square roots and variance but I really need a concrete answer on this - can anyone help?

$\endgroup$
4
$\begingroup$

General Solution

To compute mean, variance, and standard deviation you only need to keep track of three sums $s_0, s_1, s_2$ defined as follows for a set of values $X$:

$$(s_0, s_1, s_2) = \sum_{x \in X} (1, x, x^2)$$

In English, $s_0$ is the number of values, $s_1$ is the sum of the values, and $s_2$ is the sum of the square of each value. Given these sums, we can now derive mean (average) $\mu$, variance (population) $\sigma^2$, and standard deviation (population) $\sigma$:

$$\mu = \frac{s_1}{s_0} \qquad \sigma^2 = \frac{s_2}{s_0} - \left(\frac{s_1}{s_0}\right)^2 \qquad \sigma = \sqrt{\frac{s_2}{s_0} - \left(\frac{s_1}{s_0}\right)^2}$$

In English, the variance is the average of the square of each value minus the square of the average value.

Your particular case

You have $s_0, \mu, \sigma$, so you need to compute $s_1$ and $s_2$ by solving the above for those variables:

$$s_1 = s_0\mu \qquad s_2 = s_0\left(\mu^2 + \sigma^2\right)$$

Once you have $s_0, s_1, s_2$ for each data set, aggregation is just a matter of adding the corresponding sums together and deriving the desired aggregate values from those sums.

Variance Equation Derivation

We start with the standard equation for variance (population) and go from there:

$$\sigma^2 = \frac{1}{n}\sum_{x \in X} \left(x - \mu\right)^2 = \frac{1}{s_0}\sum_{x \in X} \left(x - \frac{s_1}{s_0}\right)^2$$

$$= \frac{1}{s_0}\sum_{x \in X} \left(x^2 - 2x\frac{s_1}{s_0} + \left(\frac{s_1}{s_0}\right)^2\right) = \frac{1}{s_0}\sum_{x \in X} x^2 - 2\frac{s_1}{s_0^2}\sum_{x \in X} x + \frac{s_1^2}{s_0^3}\sum_{x \in X} 1 $$

$$= \frac{1}{s_0}(s_2) - 2\frac{s_1}{s_0^2}(s_1) + \frac{s_1^2}{s_0^3}(s_0) = \frac{s_2}{s_0} - 2\frac{s_1^2}{s_0^2} + \frac{s_1^2}{s_0^2} = \frac{s_2}{s_0} - \left(\frac{s_1}{s_0}\right)^2 $$

$\endgroup$
  • $\begingroup$ This is a good answer, should be accepted. $\endgroup$ – Nick Mar 18 at 17:07
1
$\begingroup$

You say you take the average of all the averages, but I notice that you have a sample count column. Are these averages over different sample sizes? If so, then you would probably want a weighted average for your aggregate average: $$\text{Aggregate Average} =\frac{\sum_i (\text{sample size})_i(\text{average})_i}{\sum_i (\text{sample size})_i}$$

But without knowing more about the data, I cannot say for sure.

Now standard deviation is just the square root of the average variance. Over entire populations, it is defined by $$\sigma = \sqrt\frac{\sum_{i=1}^N(x_i - \bar x)^2}{N}$$ where $\bar x = \left(\sum_{i=1}^N x_i\right) /N$ is the average. Note that the variance $\sigma^2$ is just an average itself. So you can combine them just like you do other averages. Assuming that I am right about needing to include sample sizes, you want: $$ \text{Aggregate } \sigma^2 = \frac{\sum_i (\text{sample size})_i\sigma^2_i}{\sum_i (\text{sample size})_i}$$ Then you just take the square root to get the aggregate standard deviation. (If I am wrong about needing to include sample size, then you use the same equation with each sample size $= 1$.)

$\endgroup$
  • $\begingroup$ Thanks for the heads up on the weighted average, I had realised this when testing and corrected. Unfortunately I tried calculating the aggregate SD using the formula you described but it doesn't match up to the value I'm expecting (SD calculate from the raw data). To expand, I'm trying to calculate the aggregate SD of some raw data given some already aggregate data. For example, calculate annual aggregate given the monthly aggregated data. Is this possible, given the data shown in my question? $\endgroup$ – Dave Clarke Nov 26 '15 at 15:24
  • $\begingroup$ It may be a matter of which formula for standard deviation you are using. The formula I gave above is for summing the entire population. If you are instead using a sample from the population to estimate the whole, then the formula divides by $N-1$ instead of the sample size $N$. If each datum is itself calculated from a sample, then you would need: $$\text{Aggregate } \sigma^2 = \frac{\sum_i ((\text{sample size})_i-1)sigma^2_i}{\left(\sum_i (\text{sample size})_i\right)-1}$$ $\endgroup$ – Paul Sinclair Nov 26 '15 at 15:44
  • $\begingroup$ That was a great thought, thanks - but it still doesn't seem to calculate quite right. Given a 'raw' data set and its SD, is it possible to calculate the same SD given only an aggregated data set? $\endgroup$ – Dave Clarke Nov 26 '15 at 16:27
  • $\begingroup$ Do you know the formulas that are used to calculate the standard deviations, or are you using stock functions whose workings are unknown to you? $\endgroup$ – Paul Sinclair Nov 26 '15 at 16:37
  • $\begingroup$ The data is coming from a Server Performance reporting tool, but under the hood this uses the SQL STDEV function (on the raw data set) $\endgroup$ – Dave Clarke Nov 26 '15 at 17:35

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.