Decompose a matrix into smaller matrices by the date
1 view (last 30 days)
Show older comments
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!
0 Comments
Accepted Answer
Cedric
on 17 Jan 2013
Edited: Cedric
on 17 Jan 2013
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
2 Comments
Jan
on 25 Aug 2013
~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
on 26 Aug 2013
I gave this answer before our discussion a few weeks ago about this matter, but thank you!
More Answers (2)
Jan
on 17 Jan 2013
Edited: Jan
on 17 Jan 2013
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]
0 Comments
Andrei Bobrov
on 19 Aug 2013
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);
0 Comments
See Also
Categories
Find more on Logical in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!