Matrix summary - weighted average
3 views (last 30 days)
Show older comments
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
0 Comments
Accepted Answer
Andrei Bobrov
on 9 Sep 2011
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));
3 Comments
More Answers (0)
See Also
Categories
Find more on Multidimensional Arrays 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!