1
$\begingroup$

I have a table with a couple hundred columns and a few hundred thousand rows and want to determine which combination of 5-10 columns best correlate with a target column.

I have SQL Server and Excel but no budget beyond that.

My current plan is:

  1. Pull the table into Excel
  2. Use the correl() function on each column
  3. Combine subsets of the best correlated columns to see if their combined correlation coefficient improves. (i.e. new ab merge column =A1*1000+B1)

Is there a better way to do this?

Improve this question
$\endgroup$
2
  • $\begingroup$ R is a free and open-source statistical environment that can interface with Excel and various DBs. Here's some documentation: [R Data Import/Export][2] (PDF) [Connecting to SQL Server from R using RJDBC][3] [2]: cran.r-project.org/doc/manuals/R-data.pdf [3]: cerebralmastication.com/2010/09/… $\endgroup$
    – nico
    Commented Aug 12, 2011 at 8:29
  • 1
    $\begingroup$ Let's see now: there are 22,451,004,309,013,280 subsets of 10 columns you can pick from 200 columns. I wonder how long it would take Excel to process all those to find the subset with the best correlation to the target? :-) $\endgroup$
    – whuber ♦
    Commented Aug 12, 2011 at 15:08

1 Answer 1

Reset to default
1
$\begingroup$

You probably want to use R and perform linear regression -- it will give you the linear combination of columns that correlates best with the target column (dependent variable).

Improve this answer
$\endgroup$
3
  • 1
    $\begingroup$ How does linear regression accomplish that, exactly? $\endgroup$
    – whuber ♦
    Commented Aug 12, 2011 at 14:38
  • 1
    $\begingroup$ Finding the best set of 5-10 columns would require some kind of algorithm (to select those columns among the 200). Many options are possible. But to evaluate the quality of the fit for any 5-10 columns, the R^2 from the linear regression would be an excellent metric to look at (being the squared correlation between the dependent variable and the predicted value); and the coefficients from this regression would maximize this fit. $\endgroup$
    – crayola
    Commented Aug 13, 2011 at 8:59
  • 1
    $\begingroup$ See, for example, section 3.3 of www-stat.stanford.edu/~tibs/ElemStatLearn about subset selection. $\endgroup$
    – crayola
    Commented Aug 13, 2011 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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