Can I merge two matrices of different length with respect to a date column contained in both?

I have two matrices each containing date numbers in the first and observations in the second column. I would like to merge them so that my new matrix only contains the dates present in both matrices and the matching observations - it should be a three column matrix [dates obsMatrix1 obsMatrix2]

Answer 1

[Jos (10584)]

Take a look at INTERSECT. Something along these lines could work:

[~,i,j] = intersect(A(:,1),B(:,1))
C = [A(i,:) B(j,2)]

[Benedict Keim]

Looks like that works. Many thanks for the quick reply!

Answer 2

[Guillaume]

It's trivial to do using intersect:

tstart = datenum(now);
t1 = [tstart + (0:19); 1:20] .'           %demo matrix
t2 = [tstart + (1:2:21); 101:2:121] .'    %demo matrix
[commontime, t1rows, t2rows] = intersect(t1(:, 1), t2(:, 1));
tcommon = [commontime, t1(t1rows, 2), t2(t2rows, 2)]

Or using the synchronize method of new timetable class (which gives a lot more flexibility):

tt1 = timetable(datetime(t1(:, 1), 'ConvertFrom', 'datenum'), t1(:, 2));
tt2 = timetable(datetime(t2(:, 1), 'ConvertFrom', 'datenum'), t2(:, 2));
tcommon = synchronize(tt1, tt2, 'intersection')

I recommend you move to using timetables.

[Peter Perkins]

Also, if you're moving to timetables, meaning you have R2016b, you should also move to using datetimes instead of datenums.

Answer 3

[Jan]

[Lia, Locb] = ismember(A, B);
C = cat(2, A(Lia, :), B(LocB, 2));

Answer 4

[Peter Perkins]

Benedict, you might find that using tables and their various join methods makes what you're doing very straight-forward. The following assumes you have at least R2014b, with datetime, but that's not crucial:

>> Date = datetime(2016,12,[1 2 3 5]');
>> Value = randn(4,1);
>> T1 = table(Date,Value)
T1 =
       Date         Value 
    ___________    _______
    01-Dec-2016    0.53767
    02-Dec-2016     1.8339
    03-Dec-2016    -2.2588
    05-Dec-2016    0.86217
>> Date = datetime(2016,12,[1 3 4 5]');
>> Value = randn(4,1);
>> T2 = table(Date,Value)
T2 =
       Date         Value  
    ___________    ________
    01-Dec-2016     0.31877
    03-Dec-2016     -1.3077
    04-Dec-2016    -0.43359
    05-Dec-2016     0.34262
>> T12 = innerjoin(T1,T2,'Key','Date')
T12 =
       Date        Value_T1    Value_T2
    ___________    ________    ________
    01-Dec-2016    0.53767     0.31877 
    03-Dec-2016    -2.2588     -1.3077 
    05-Dec-2016    0.86217     0.34262 
>> T12 = outerjoin(T1,T2,'Key','Date')
T12 =
      Date_T1      Value_T1      Date_T2      Value_T2
    ___________    ________    ___________    ________
    01-Dec-2016    0.53767     01-Dec-2016     0.31877
    02-Dec-2016     1.8339     NaT                 NaN
    03-Dec-2016    -2.2588     03-Dec-2016     -1.3077
    NaT                NaN     04-Dec-2016    -0.43359
    05-Dec-2016    0.86217     05-Dec-2016     0.34262

You can do all that by hand using intersect and so on, but it's all already there in inner/outerjoin. And as Guillaume says, if you have R2016b, you should look at timetables.