3

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
  • 267
  • 3
  • 15
  • bill999 are both those variables numerical values? ordered categorical? just arbitrary labels? – Glen_b Dec 10 '14 at 06:39
  • The title of this question suggests that it's about understanding correlation, while in reality it's about data reshaping. – miura Dec 10 '14 at 09:04
  • 1
    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? – D L Dahly Dec 10 '14 at 09:14
  • Oh. Now that you mention it. Right. – miura Dec 10 '14 at 09:22

3 Answers3

4

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
Aksakal
  • 55,939
  • 5
  • 90
  • 176
3

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

library(plyr)
library(reshape2)

# read in data
x = matrix(c(550, 168, 333,
             390, 133, 299,
             280, 135, 255,
             145, 100, 34,
             130, 54, 12), ncol = 3, byrow = T, dimnames = list(1:5*10, 5:7))

# reformat
x = adply(melt(x), 1, function(df) df[rep(1, df$value), ])

# calculate correlation
with(x, cor.test(Var1, Var2))
miura
  • 3,364
  • 3
  • 21
  • 27
2

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)

BrewStats
  • 180
  • 7