Decompose a matrix into smaller matrices by the date

Dear all,

I have a matrix with the dimension 946836x3 and it is a cell. The first column is the date (dd.mm.yyyy 'HH:MM:SS), followed by two exchange rates. The rows look like this: '01.01.2004 00:00:00.000', 1.2587, 1.2698. The span of time goes from '01.01.2004 00:00:00.000' to '31.12.2012 23:55:00.000'. I have for every day intra-day data points(every 5 minutes, but they are not equal for every month, some have 288 and others 287 or 286). Now I want for every day a matrix filtered by the date. As an example in one matrix should be all datapoints of january 2004 and in the next for february and so. As a result I should have 12*9 matrices. How can I do that? Thank you in advance!

Answer 1

[Cedric]

A few hints to help you start:

 >> C = {'01.01.2004 18:23:54', 3, 4; ...
         '02.01.2004 18:23:54', 2, 6; ...
         '01.02.2004 18:23:54', 8, 7} ;

 >> regexp(C(:,1), '01.2004')
 ans = 
    [4]
    [4]
    []

 >> cellfun(@(x)~isempty(x), regexp(C(:,1), '01.2004'))
 ans =
     1
     1
     0

 >> C(cellfun(@(x)~isempty(x), regexp(C(:,1), '01.2004')), 2:3)
 ans = 
    [3]    [4]
    [2]    [6]

 >> m = 1 ; y = 2004 ;
 >> sprintf('%.2d.%d', m ,y)
 ans =
    01.2004

 >> C(cellfun(@(x)~isempty(x), regexp(C(:,1), sprintf('%.2d.%d', m ,y))), 2:3)
 ans = 
    [3]    [4]
    [2]    [6]

 >> cell2mat(ans)
 ans =
     3     4
     2     6

It is certainly not the most efficient way to do it, but it makes you "play a bit" with regexp, cellfun, etc. Note that as the month and the year are stored in numerical variables, you can easily loop over both of them. EDIT(2): e.g.

 D = cell(9, 12) ;  Cex = cell2mat(C(:,2:3)) ;
 for yId = 1 : 9
    for m = 1 : 12
       D{yId,m} = Cex(cellfun(@(x)~isempty(x), regexp(C(:,1), ...
                     sprintf('%.2d.%d', m ,2003+yId))), :) ;
    end
 end

Hope it helps,

Cedric

[Jan]

~cellfun(@isempty, c) is faster than cellfun(@(x) ~isempty(x), c). But this is even faster, because it avoids the call from the MEX level back to Matlab: ~cellfun('isempty', c).

[Cedric]

I gave this answer before our discussion a few weeks ago about this matter, but thank you!

Answer 2

[Jan]

DateV  = datevec(C(:, 1), 'dd.mm.yyyy HH:MM:SS');
Year   = DateV(:, 1);
Month  = DateV(:, 2);
Result = cell(12, 9);
for iYear = 1:9
  yearMatch = (Year == iYear + 2003);
  for iMonth = 1:12
    Result{iMonth, iYear} = C(Month == ii & yearMatch, :);
  end
end

Now you have a {12 x9} cell, which contains cell with the monthly data. If the elements should be double matrices:

      D = C(Month == ii & yearMatch, :);
      Result{iMonth, iYear} = reshape(cat(1, D{:}), [], 2);

[This is not tested or debugged, but written in the web interface only]

Answer 3

[Andrei Bobrov]

DD = datevec(C(:,1),'dd.mm.yyyy HH:MM:SS')
[a1,i1,i1] = unique(DD(:,1));
[a2,i2,i2] = unique(DD(:,2));
T = accumarray([i1,i2],(1:size(DD,1))',[12 9],@(x){x});
D2 = cell2mat(C(:,2:3));
out = cellfun(@(x)D2(x,:),T,'un',0);