How to combine matrixes of different lengths in order of date?

Hey everyone,

I am wondering whether there exists a simple way in matlab to do the following:

I got three matrixes. Each matrix consists of two colums of which the first column contain dates. These date vectors have different lengths in each matrix and sometimes there are gaps between some time steps. For example in one matrix the date 12-27-2010 might be missing, while the date exists in the other two matrixes. The second column in each matrix contains my data. Now I want to combine the three matrixes into one matrix. The first column of this new matrix is supposed to contain a date vector without any gaps between time steps. As the date vectors in my three matrixes are not exactly the same (because of gabs and differen lengths) I cannot simply do copy-paste with my data. I need to order them by date somehow. Please see my example for a better explanation of what I want.

Example:

Matrix 1

 A=[2010-12-27  1
    2010-12-28  3
    2010-12-29  2
    2010-12-30  4]

Matrix 2:

 B=[2010-12-26  1
    2010-12-28  5
    2010-12-29  3
    2010-12-30  4]

Matrix 3:

 C=[2010-12-27  1
    2010-12-28  1
    2010-12-30  5]

I want to get the following matrix:

 D=[2010-12-26  0  1  0
    2010-12-27  1  0  1
    2010-12-28  3  5  1
    2010-12-29  2  3  0
    2010-12-30  4  4  5]

Later I want to add all three data colums to get:

 E=[2010-12-26  1
    2010-12-27  2
    2010-12-28  9
    2010-12-29  5
    2010-12-30  13]

So if there is a simple way to just to this last step and skip the creation of matrix D I'd be grateful for your ideas, too!!

Thanks so much!!

Answer 1

[Guillaume]

accumarry can perform the last step. The 3rd return value of unique applied to the concatenation of the dates (as datenum) will give you the subs for accumarray:

A=[datenum('2010-12-27')  1
   datenum('2010-12-28')  3
   datenum('2010-12-29')  2
   datenum('2010-12-30')  4];
B=[datenum('2010-12-26')  1
   datenum('2010-12-28')  5
   datenum('2010-12-29')  3
   datenum('2010-12-30')  4];
C=[datenum('2010-12-27')  1
   datenum('2010-12-28')  1
   datenum('2010-12-30')  5];

[dates, ~, subs] = unique([A(:, 1); B(:,1); C(:,1)]);
E = [dates, accumarray(subs, [A(:, 2); B(:, 2); C(:, 2)])]

[MC3105]

thank you for your help!

unfortunately if i skip the creation of matrix D, I have got another problem that I don't now how to solve, before being able to try out your advice....

The matrixes I want to combine are saved to my computer and they have all been saved with the same variable name - so all matrixes that I want to combine are called the same. Now my idea was to create a loop that loads one matrix at a time, extracts the data and puts it into matrix D before doing the same all over again for the second matrix in the next loop iteration...

Now if I don't create a matrix D before adding all of my values up... I don't know how I can get all three matrixes (that all have the same name) into my matlab workspace at the same time...

Do you have an idea of how I could do this? Or in which manner I could load up my three matrixes to combine them?

Thank you so much in advance!

[Guillaume]

To create D, use ismember:

A=[datenum('2010-12-27')  1
   datenum('2010-12-28')  3
   datenum('2010-12-29')  2
   datenum('2010-12-30')  4];
B=[datenum('2010-12-26')  1
   datenum('2010-12-28')  5
   datenum('2010-12-29')  3
   datenum('2010-12-30')  4];
C=[datenum('2010-12-27')  1
   datenum('2010-12-28')  1
   datenum('2010-12-30')  5];
dates = unique([A(:, 1); B(:,1); C(:,1)]);
D = [dates zeros(numel(dates), 3)];
D(ismember(dates, A(:, 1)), 2) = A(:, 2);
D(ismember(dates, B(:, 1)), 3) = B(:, 2);
D(ismember(dates, C(:, 1)), 4) = C(:, 2);

[Guillaume]

Well, either

1. load first file, copy matrix into new variable, load 2nd file, copy matrix into another variable, load 3rd file, or
2. load each file into different structures or a structure array:

s = struct('a', {}); %assuming common name to variables is 'a'
for f = {'file1.mat', 'file2.mat', 'file3.mat'}
  s(end+1) = load(f{1})
end

[Guillaume]

The above for loop will work just as well for 300 files. Just put all the filenames in the cell array however you want.

And it actually makes the rest of the code simpler. Once you've got all the matrices in s:

allm = vertcat(s(:).a);
[dates, ~, subs] = unique(allm(:, 1));
E = accumarray(subs, allm(:, 2));

[MC3105]

thank you so much!!! i feel like sending you flowers!! :)

[MC3105]

hmmm.. it doesn't really work quiet right yet... I'm working on it... for some reason the dates get messed up... in my data i have dates from 2010 to 2011... but now all of a sudden I get years around 6030...

the addition doesn't work right either... matlab does add values... but they are definitely not the right ones...

I think my main problem is now, that I need to have a complete date vector in the first column of matrix E. Right now there are still some dates missing, as these missing dates are missing in all three of my original matrices...

[Guillaume]

Can you attach an example file?

[MC3105]

it's ok i figured out a way now using a loop :) probably less efficient than your way but whatever :) thanks again!!