Variance based on given frequencies using SPSS

Question

I'd be too happy, if someone could post a code snippet, which explains how to compute mean and variance of a set of records, which contain frequencies?

Suppose we have records like (FORMAT F)

- GroupA, 6 x Grade 1, 5 x Grade 2, 10 x Grade 3
- GroupB, 2 x Grade 1, 7 x Grade 2, 18 x Grade 3
- GroupA, 23 x Grade 1, 5 x Grade 2, 1 x Grade 3
- GroupA, 10 x Grade 1, 15 x Grade 2, 12 x Grade 3

Goal: Compute for each Group's mean and variance of grades using SPSS syntax.

Clarification:

Using SPS, processing cases (FORMAT C) would be simple (as colleagues told me):

# Record number 1 (line 1 of the above data)
GroupA, Grade 1
   ... 6 times
GroupA, Grade 1
GroupA, Grade 2
   ... 5 times
GroupA, Grade 2
GroupA, Grade 3
   ... 10 times
GroupA, Grade 3
# Record number 2 (line 2 of the above data) 
GroupB, Grade 1
GroupB, Grade 1
GroupB, Grade 2
   ... 78 times
GroupB, Grade 7
   ... and so on

Unfortunately, we don't have cases of FORMAT C, but already aggregated data as in FORMAT F.

I probably should add this:

I implemented a solution, which uses (user entered) FORMAT F data using PHP/SQL: The PHP program constructs an SQL query, which computes the mean, the variance and the standard deviation of each Group.

I even tested the solution using well-known sample data. The results are identical. Thus I'm sure the solution is correct.

Since the computed statistical properties are sensible, I'd like to get a verification using a standard statistical package using the real FORMAT F data (not the test case data).

My colleague is going to use SPSS.

Answer 1

I will to demonstrate how to do this without reshaping the data, as all it entails is simple arithmetic. If I am reading your question correctly, the data look something like this;

Data List Free / Group (A1) Grade1 Grade2 Grade3.
Begin Data
A 1 2 3
A 6 5 10
B 2 7 18
C 23 5 1
D 7 7 13
End Data.

To calculate any type of variance, you need to assign some type of numeric values to your grade variables. Here I will assume that a value for Grade1 is the equivalent of a 95, Grade 2 is equal to an 85, and Grade3 is equal to a 75. All that one needs to do after this is to essentially plug in the values for the calculation of the variance. The Wikipedia page for standard deviation has an explicit example taking you through the steps. The only difference in the code below is that I need to multiply the squared differences by the number of observations within that specific grade variable.

compute row_sum = (Grade1*95)+(Grade2*85)+(Grade3*75).
compute row_n = Grade1+Grade2+Grade3.
compute row_mean = row_sum/row_n.
execute.

compute square_diff1 = (95 - row_mean)*(95 - row_mean).
compute square_diff2 = (85 - row_mean)*(85 - row_mean).
compute square_diff3 = (75 - row_mean)*(75 - row_mean).
execute.

compute row_variance = ( (Grade1*square_diff1) + (Grade2*square_diff2) + (Grade3*square_diff3) ) / (row_n).
execute.

The resulting variable, row_variance , is a calculation of the variance for the observations in the row. In your example given it suggested you would have groupings of variables, and hence would need to calculate not only the row variance, but the group variance. You can simply use the AGGREGATE command to sum the Grade variables within each group, and then follow the same steps above. An example would be;

DATASET DECLARE Grouped.
AGGREGATE
  /OUTFILE='Grouped'
  /BREAK=Group
  /Grade1 =SUM(Grade1) 
  /Grade2 =SUM(Grade2)
  /Grade3 =SUM(Grade3).

This will produce a new dataset named Grouped, in which you can calculate the row variance using the exact same code above.

As a note, you asked for the variance in your question. I suspect the sample standard deviation is another statistic you might be interested in. One can not simply take the square root of the variance I gave above though, as one divides by the number of observations minus one for the sample standard deviation. So if you were interested in the sample standard deviation in the row you could use the code below;

compute row_sd =SQRT( ( (Grade1*square_diff1) + (Grade2*square_diff2) + (Grade3*square_diff3) ) / (row_n - 1) ).
execute.

I doubt this is the simplest solution in terms of length of code. But I hope it is straightforward mathematically.