MATLAB: Merging data from different matrices with different row numbers by matching date

Question

I have 1600 matrices (1600 companies info) each containing data similar to the following (first column is company ID, second is a date, and third is return)

    % code


10001       73500     0.05
10001       73440     0.06
10001       73320     0.04
10001       73280     0.03
.....       .....     ....

The issue is each of these 1600 matrices have different row number depending on the availability of the data from the database (i.e return is available for different companies on different dates and different number of periods).

Now the task at hand is to merge all these matrices into one.

So I created a vector of all possible dates like in the following:

 % code
73500
73480     
73440 
73380    
73320
73300   
73280
.....

I am looking to extract the data so that each company return is listed in a column at the right date (row), the rest of the rows should show Nan. Something like the following:

 % code
73500       0.04      nan       nan        ........ for the rest of companies until column 1601
73480       nan       nan       0.02
73440       0.05      0.04      nan
73380       0.03      0.05      0.02 
73320       nan       0.02      nan
73300       0.05       nan      nan
73280       nan        nan      0.05
.....       ....       ....     ....

Appreciate any help. Thanks

Answer 1

result = nan(nDates, nCompanies + 1);
result(:, 1) = Dates;
for iComp = 1:nCompanies
  aMatrix  = <how to get your iComp.th matrix>
  [iR, iM] = ismember(Dates, aMatrix(:, 2));
  result(iR, iComp + 1) = aMatrix(iM, 3);
end