Extract data from cell array based on date and back into dataset

Hi everyone,

I have a cell array with date (converted using datenum) and data values in other columns. I would like to use this cell arrya as a reference point from which I can extract data (say from col 5) based on matching times.

For instance, the larger dataset can be:

724642  605  250  10.8024000000000  240  
724643  605  250  11.8312000000000  230  
724644  605  240  11.3168000000000  230  
724645  605  240  10.8024000000000  230

and the lookup dataset can be:

724642  605  13    
724644  605  22

And the result I would like would be the values from the lookup dataset and in the last column, the extracted data from the corresponding date in the 5 col of the larger dataset:

724642  605  13  240  
724644  605  22  230

I have used ismember and find but the indices returned to not work as the two cell arrays will have different sizes and thus after finding the matching indices (fairly easy) I cannot just plug that in to extract the data as dates may not be found in one dataset.

Any help would be appreciated! Many thanks.

Answer 1

[Geoff Hayes]

Hi Masao - A combination of the ismember and removing of zeros may work. If A is your first matrix (larger data set) and B is your second matrix (lookup dataset), then

[isInA,whereInA]=ismember(B(:,1),A(:,1))

will return the isInA indicating which elements of B are in A (1) and which elements aren't (0). The whereInA will indicate what those indices are in A. If all elements of B are in A then there is no problem and we could use the following to append the final column of A onto B:

B(isInA,4) = A(whereInA,5);

But if there is an element of B not in A then there is a zero in the same row of each of the isInA and whereInA matrices. Using that fact, and that zero indices on the left-hand side of the assignment will be ignored, we can do:

% remove the zeros from whereInA
whereInA(whereInA==0) = [];

% append the column of A onto B for those elements of B in A wrt time
B(isInA,4) = A(whereInA,5);

I added some dummy rows to your example of B (so that the first and third rows of this updated matrix would not match wrt time in A) and the result was as expected - the two rows that did match in A had their fourth column updated with the fifth column of A.

Geoff

[mashtine]

You have saved the day and my sanity! Many thanks Geoff

[Geoff Hayes]

Anytime!