I have data that looks like this (I am using R):

       5    6    7
10    550  168  333
20    390  133  299
30    280  135  255
40    145  100  34
50    130  54   12


The values are the counts of how many observations took on the values represented in the column and row names (values). For example, there were 550 observations that took on 5 of the first variable and 10 of the second variable.

I do not observe the following data, but the above data is constructed from something like the following:

Obs   FirstVar   SecondVar
1       5          10
2       7          20
3       5          20
4       6          10
5       7          50
...


My question is how to think about and construct (preferably in R) correlation using only the first data. (If we had access to the second data, it would be one line of code - just correlation between FirstVar and SecondVar.)

• bill999 are both those variables numerical values? ordered categorical? just arbitrary labels? Dec 10 '14 at 6:39
• The title of this question suggests that it's about understanding correlation, while in reality it's about data reshaping. Dec 10 '14 at 9:04
• Is it just about reshaping? I think Aksakal's answer gets to heart of how I interpreted the question - if you only have the counts, how does it relate to correlation? Dec 10 '14 at 9:14
• Oh. Now that you mention it. Right. Dec 10 '14 at 9:22

There may be a more efficient way, but you could use for loops

newDat = matrix(nrow = 0,ncol=2)
for(i in 1:nrow(oldDat)){
for(j in 1:ncol(oldDat)){
obs = matrix(nrow = oldDat[i,j],ncol=2)
obs[,1] = names(oldDat)[j]
obs[,2] = row.names(oldDat)[i]
newDat = rbind(newDat,obs)
}
}


Then you've recreated your original observations (just not in order)

• I'm not running this code in R, but does it create 3018 rows as it should? Dec 10 '14 at 4:14
• It should. The first line after the for statements creates a new matrix that has the number of rows which corresponds to the number of observations with that pairing. Dec 10 '14 at 8:11
• I think that a closed parentheses is missing in line 2 and in line 3. Dec 10 '14 at 14:29
• Ah, it did. My bad, I'm use to the auto-completion of parentheses and such. Dec 10 '14 at 17:13

Correlation is $\rho=\frac{Cov[x,y]}{\sqrt{Var[x]Var[y]}}$

I would compute everything based off the table you gave like this in MATLAB:

x=[5    6    7]
y=[10:10:50]'

a= [   550  168  333    ;...
390  133  299;...
280  135  255;...
145  100  34;...
130  54   12]

n =sum(sum(a))
mu_x = sum(a)*x'/n
mu_y = sum(a,2)'*y/n
mu_xy = sum(sum(y*x.*a))/n
cov_xy = mu_xy - mu_x*mu_y

mu_x2 = sum(a)*x.^2'/n
var_x = mu_x2 - mu_x^2
mu_y2 = sum(a,2)'*y.^2/n
var_y = mu_y2 - mu_y^2

corr = cov_xy/sqrt(var_x*var_y)


OUTPUT:

x =

5     6     7

y =

10
20
30
40
50

a =

550   168   333
390   133   299
280   135   255
145   100    34
130    54    12

n =

3018

mu_x =

5.813783962889331

mu_y =

22.534791252485089

mu_xy =

1.302120609675282e+02

cov_xy =

-0.800347023228426

mu_x2 =

34.569913850231941

var_x =

0.769829883082771

mu_y2 =

6.538436050364480e+02

var_y =

1.460267882433695e+02

corr =

-0.075485699630891


If you use additional packages you can get the kick of doing the reformatting in one line.

library(plyr)
library(reshape2)