Merging two matrices by first column values

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.

[Rose]

are the dates in ascending order?

[Scott]

Yes, the dates are in A and B are sorted in ascending order, with no repetitions.

[pietro]

Are A and B cell arrays?

[Scott]

No. Both A and B are of double-types. The dates are represented by MatLab serial date numbers, e.g. datenum('01-Jan-2014') = 735600 etc.

[Patrik Ek]

Ok now I cannot follow no longer. Do you mean that the date is a string with dates, a string of serial numbers, or a double with serial numbers? You need to give a better explanation.

[Scott]

A=[
datenum('1-Jan-2014'),1.2345;
datenum('3-Jan-2014'),1.2345;
datenum('4-Jan-2014'),1.2345
]

A =

1.0e+005 *

    7.3560    0.0000
    7.3560    0.0000
    7.3560    0.0000

[David Sanchez]

The first column of your A and B matrices is in date format or is it the serial date number:

A =
01/01/2014 1.2345
03/01/2014 1.2345
04/01/2014 1.2345

OR:

A =
735600 1.2345
735659 1.2345
735690 1.2345

???

This is key to give a proper answer

[Patrik Ek]

Ok but what format do you want? The serial date number or the date string? Both works, except that the date requires a cell

[Scott]

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?

Answer 1

[Patrik Ek]

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

[Scott]

Excellent... thankyou everyone. Here's what I ended up using:

clc;
clear all;

A=[
    datenum('1-Jan-2014'),1.2345;
    datenum('3-Jan-2014'),1.2345;
    datenum('4-Jan-2014'),1.2345
    ];

B=[
    datenum('2-Jan-2014'),9.8765;
    datenum('3-Jan-2014'),9.8765;
    datenum('4-Jan-2014'),9.8765
    ];

q = unique([A(:,1);B(:,1)]); % Unique sorted catenate.
aInd = ismember(q,A(:,1)); % All dates in A
bInd = ismember(q,B(:,1)); % All dates in b
x = zeros(length(q),3);
x(:,1) = q;
x(aInd,2) = A(:,2);
x(not(aInd),2) = NaN;
x(bInd,3) = B(:,2);
x(not(bInd),3) = NaN;

clear A B aInd bInd q

[Scott]

Oh, and it's MUCH faster than looping through!

[Patrik Ek]

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.