Accumulate observations based on date stamp in MATLAB

Question

If I have the following data-set

A =

20061201         100
20061201          10
20061201           9
20061202          15
20061202          50
20061204          40
20061204          16
20061204          12

column 1 has dates while columns 2 has corresponding observations where . corresponds to no observation.

I expect the output to be something like

row 1: 20061201  100 10 9 
row 2: 20061202   15 50 .
row 3: 20061204   40 16 12

how can I do that, any tips?

it may require equal size vectors when for one date there will be lets say 10 observations and for others more or less. — Jessi, May 21 '15 at 19:28
How is it specified how many observations correspond to each date? Your question is unclear — Luis Mendo, May 21 '15 at 20:47
@Jessi - That still doesn't explain why you can't use `reshape`. I don't understand your problem statement. — rayryeng, May 21 '15 at 21:02
I have added an answer.. i have used cell arrays as it is appropriate for your problem.. if you need changes, let me know :) — Santhan Salai, May 22 '15 at 01:57

score 0 · Answer 1 · edited May 23 '17 at 10:26

Using unique and accumarray

[uVals,~,subs] = unique(A(:,1));
out = [num2cell(uVals), accumarray(subs,A(:,2),[],@(x) {x.'})]

Your Sample Input:

>> A

A =

20061201         100
20061201          10
20061201           9
20061202          15
20061202          50
20061204          40
20061204          16
20061204          12

Results:

>> out

out =  

[20061201]    [1x3 double]
[20061202]    [1x2 double]
[20061204]    [1x3 double]

I have used cell-Array for the output.. you don't have to pad . to the deficient rows in this case. If you want to display a particular cell, you could use {} instead of () like this..

>> out{1,2}

ans =

100    10     9

>> out{2,2}

ans =

15    50

Here is a method (based on this answer) you might use if you insist to obtain a matrix instead of cell-array. I also suggest, you use NaN(Not-a-Number) padding instead of .

[uVals,~,subs] = unique(A(:,1));
valC = accumarray(subs,A(:,2),[],@(x) {x.'});
maxSize = max(cellfun(@numel,valC));
valMat = cellfun(@(x) [x nan(1,maxSize-numel(x))],valC,'uni',0);
valMat = vertcat(valMat{:});
out = [uVals, valMat];

Results for the sample Input:

>> out

out =

20061201         100          10           9
20061202          15          50         NaN
20061204          40          16          12

Accumulate observations based on date stamp in MATLAB

1 Answers1