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?

$\endgroup$
  • $\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 Aug 12 '11 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 Aug 12 '11 at 15:08
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).

$\endgroup$
  • 1
    $\begingroup$ How does linear regression accomplish that, exactly? $\endgroup$ – whuber Aug 12 '11 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 Aug 13 '11 at 8:59
  • 1
    $\begingroup$ See, for example, section 3.3 of www-stat.stanford.edu/~tibs/ElemStatLearn about subset selection. $\endgroup$ – crayola Aug 13 '11 at 9:02

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.