Algoritm coefficient correlations

[Function]

Hello everyone

Consider a 10 x 10 table with 10 by 10 variables and in the cells their correlation coefficient. Suppose that I want to select every combinatory set of variables that have a correlation smaller than 0.1. Is there a way in, let's say, Excel, or SPSS, to do this?

E.g., VAR1 has a correlation smaller than 0.1 with VAR2, VAR3, and VAR4.

VAR2 and VAR3 have a correlation smaller than 0.1, VAR2 and VAR4 too, but VAR3 and VAR4 have a correlation of 0.2

The resultant sets are (1) VAR1, VAR2, and VAR3, and (2) VAR1, VAR2, and VAR4.

You can imagine that this gets really complicated for 10 variables of which some have correlation smaller than 0.1 with at least 6 other variables.

In short: of 10 variables, I need to define every possible set that is made from any combination of variables, of which their inter-variable correlation is less than 0.1.

How can I do this?

Thank you very much!

F.

[Sensei]

How are you keeping data?

Is it Excel table? If so, you can export it to CSV (Comma Separated Values). It's easier to parse from external code.

Then you can write little script in PHP, Perl, Python, or C/C++, which will load row by row, and analyze them, and return result that you want.

 

ps. Maybe give the real example (CSV?) with just 2x2 fields.. so there will be 2x2 data per field. 16 total.

 

[Function]

It's in SPSS, but I need to make a new table, e.g., in Excel, with variable-variable correlation coefficients.

However, I'm afraid I cannot program whatsoever, so I'd appreciate any help ...

You can find the original true values attachted in csv format.

 

Map1.csv

[Sensei]

57 minutes ago, Function said:

However, I'm afraid I cannot program whatsoever, so I'd appreciate any help ...

Why not you cannot program? Everybody can program..

57 minutes ago, Function said:

You can find the original true values attachted in csv format.

It does not look like what you said in the first post, at all..

 

Describing what it all means, will probably take longer time, than time spend on writing script..

 

Edited January 26, 2018 by Sensei

[Function]

1 minute ago, Sensei said:

Why not you cannot program? Everybody can program..

It does not look like what you said in the first post, at all..

 

Not everyone

And how so?

I genuinely believe this is "a 10 x 10 table with 10 by 10 variables and in the cells their correlation coefficient" (correlation coefficient being the correlation coefficient of 2 variables).

So I'd need every possible set of variables, with none of the inter-variable correlation coefficients of the set exceeding, let's say, 0.1.

 

[Sensei]

Oh, I see where was problem.. you exported in CSV, but you have comma as decimal separator, not CSV field separator..

After replacing comma by dot, then replacing semicolon by comma, it loaded correctly in Open Office.

 

[Function]

1 minute ago, Sensei said:

Oh, I see where was problem.. you exported in CSV, but you have comma as decimal separator, not CSV field separator..

After replacing comma by dot, then replacing semicolon by comma, it loaded correctly in Open Office.

 

Ahh yes; sorry, my bad. SPSS has a period as decimal separator, but forgot that Excel is still set in my regional standards (where a decimal separator is a comma)

[Sensei]

In Open Office there is such function which will filter cells depending on cell value.

e.g.

=IF(B2<0.1;B2;"" )

then select row, fill entire row, then select columns, fill entire columns.. and received this:

 

Expected by your coefficients are these? They are lower than threshold 0.1 (even negative)

Edited January 26, 2018 by Sensei