Merging two matrices by first column values

8 views (last 30 days)
Scott
Scott on 15 Apr 2014
Edited: Patrik Ek on 15 Apr 2014
I have two matrices (A and B), the first column in each is a date, the second column is a reading (double). The dates in A and B are not contiguous. The result should be a matrix containing both A and B on the dates for which either OR both are relevent.
e.g.
A =
01/01/2014 1.2345
03/01/2014 1.2345
04/01/2014 1.2345
...
B=
02/01/2014 9.8765
03/01/2014 9.8765
04/01/2014 9.8765
...
Merged=
[date] [A] [B]
01/01/2014 1.2345 NaN;
02/01/2014 NaN 9.8765
03/01/2014 1.2345 9.8765
04/01/2014 1.2345 9.8765
I'm not even sure of the proper name of what I'm trying to achieve? Is there one command or a series of commands to do this, as at the moment I am using a script which just loops through each input but this approach will become unsuitable when the input lengths become large.
  9 Comments
Scott
Scott on 15 Apr 2014
At the moment, the date data is being read from an Excel spreadsheet. In my script I am working with the numeric MatLab serial value e.g. 01-Jan-2014 = 735600. In my script, after I have finished looping through both arrays I am converting the MatLab date serial to text for display.
I don't really care which way I have to go through the process; I can either convert dates to text or cells before processing or after, just as long as the resultant contains both of the inputs in the right (combined) order. I thought it would be faster working with numerics rather than cells containing text?

Sign in to comment.

Accepted Answer

Patrik Ek
Patrik Ek on 15 Apr 2014
Edited: Patrik Ek on 15 Apr 2014
Since the type of the date is only vaguely specified I will select one.
A = {'01/01/2014', 1.2345;'03/01/2014', 1.2345;'04/01/2014', 1.2345};
B = {'02/01/2014', 6.7890;'03/01/2014', 6.7890;'04/01/2014', 6.7890};
q = unique([A(:,1);B(:,1)]); % Unique sorted catenate.
aInd = ismember(cell2mat(q),cell2mat(A(:,1)),'rows'); % All dates in A
bInd = ismember(cell2mat(q),cell2mat(B(:,1)),'rows'); % All dates in b
fullCell = cell(length(q),3);
fullCell(:,1) = q;
fullCell(aInd,2) = A(:,2);
fullCell(not(aInd),2) = {nan};
fullCell(bInd,3) = B(:,2);
fullCell(not(bInd),3) = {nan};
For the case where dates are serial date number you just define matrices instead and aInd and bInd are instead
aInd = ismember(q,A(:,1)); % All dates in A
bInd = ismember(q,B(:,1)); % All dates in b
  3 Comments
Patrik Ek
Patrik Ek on 15 Apr 2014
Glad I could help. And yes the mex files are always faster. However for the datenum part would be good to solve with a cellfun in the future (if you not does that already, since you most likely uses the RAW format in the excel file).
cellfun(@(x),datenum(x),dateCell);
or if uses the TXT format, then datenum evaluates the date row-wise for a matrix input.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by