how to find correlation between two columns, selecting rows on the basis of third column

Hi

I have three column data say A, B and C and the number of rows be 1000. Column A is either empty or it has values from 1 to 3 randomly. B and C are two variables. Let's say values of B and C in each row is a case and value of A in the same row assigns the category to that case. So there are (1+3)four categories of cases in b and C. I need to find correlation between B and C with in each category.

Any idea how to do that?

Answer 1

[David Young]

I'm not sure what you mean by column A possibly being empty. Anyway, assuming that it contains the categories, you select the values in a particular category like this:

category = 2;  % for example - could be any valid value
k = A == category;   % find indexes of elements in this category
Bselected = B(k);
Cselected = C(k);

Now you can use your usual correlation function to find the correlation between Bselected and Cselected. You could put the whole lot in a loop to cover each category in turn.

[Muhammad]

I mean to say that column A has cells which are randomly empty, 1,2 and 3.

In other words, it means that among 1000 rows, some of the cells in column A are empty, some have 1, some 2 or 3 as value.

I hope now it is clear.

[David Young]

You can't have an empty cell in a numerical array. Is A a cell array?

Answer 2

[Muhammad]

 %For example this the data i have
 A    B    C
  1    23    4
  2    43    7
  1    64    8
       43    6
  2    65    11
  3    46    9
  2    76    12
  3    76    3
  1    32    5

%I need to get following matrix from above matrix A B C 1 23 4 1 64 8 1 32 5 %Then i can find correlation with in category 1 (i.e when A=1)

%similarly, i need to extract another matrix from the top matrix as;

A    B    C
2    43    7
2    65    11
2    76    12
% and then calculate the correlation between B and C.

% i hope this makes it clear

[Muhammad]

please also note that in original problem i have rows more than 30000.

[David Young]

See above. Is A a cell array?

The code in my answer above gives you exactly what you are asking for, when the category has a number.

If this still doesn't help, perhaps you can show the code that assigns values to the variables A, B and C. (They are variables in MATLAB, aren't they?)

[Muhammad]

Thanks David. Yes its working.