# Merging two matrices by first column values

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

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
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.

### Community Treasure Hunt

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

Start Hunting!

Translated by