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

2 views (last 30 days)
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.

Accepted Answer

Geoff Hayes
Geoff Hayes on 6 May 2014
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
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.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by