MATLAB: Matrix summary – weighted average

weighted average

Hi,
I have been looking for hours how to do the following but I guess my competence is not that good. I would really appreciate your help.
I have two matrices:
1. Matrice A = a large matrix around 3,000 lines with 3 columns. First column is time, second column is price and third column is volume
2. Matrice B = pre defined time frame = 1 column with time
Let's have the following example:
Matrice A :
09:00:00  11 100
09:00:30  22 100
09:02:30  22 50
Matrice B
09:00:00
09:01:00
09:02:00
Results:
Matrice C
09:00:00 16.5
09:01:00 16.5
09:02:00 22
The first line in Matrice C = 16.5 (=(11*100+22*100)/(100*100)=weighted average of Matrice A columns 2 and 3))
The second line in Matrice C = Matrice C line 1 because there is no data between 09:01:00 and 09:02:00)
(The third line in Matrice C = 22 (=22*50/50=same logic as Matrice C line 1)
Basically this is calculating a weighted average for data that respect the condition of time.
Once again I would really appreciate your help.
Thanks
Xavier

Best Answer

variant
inital data (A, B)
A = [cellstr(datestr(sort(rand(20,1)),'HH:MM:SS')),...
                            mat2cell(randi(45,20,2),ones(20,1),2)];
[i1 i2] = meshgrid(0:23,[0 30]);
HM = [i1(:) i2(:)];
B = datestr(datenum([zeros(size(HM,1),3) HM zeros(size(HM,1),1)]),'HH:MM:SS');
solution
ta = rem(datenum(A(:,1),'HH:MM:SS'),1);
tb = rem(datenum(B,'HH:MM:SS'),1);
[n,bin] = histc(ta,tb);
R = accumarray([repmat(bin,2,1),reshape(repmat([1 2],size(bin)),[],1)],...
                    reshape(cell2mat(A(:,2)),[],1),[size(tb,1) 2],@(x){x})  ;
M = arrayfun(@(i1)R{i1,1}'*R{i1,2}/sum(R{i1,2}),(1:size(R,1))','un',0);
C = [cellstr(B) M];
ADD (12.09.2011 16:45 MSK) reply at " I need to take the previous value when there is no data "
t = ~cellfun(@isempty,M);
M2 = M(t);
M = M2(cumsum(t));
Related Question