Matrix summary - 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

Answer 1

[Andrei Bobrov]

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));

[Xavier]

Working very well thanks for your answer. One more question, I need to take the previous value when there is no data (as per example 09:01:00), is there a way to do that?

Thanks

Xavier

[Xavier]

Actually one more thing I do not understand. In my case n has 26 elements but R has only 25 elements. How is that possible? The more n increases and the more the difference of elements between n and R increases as well. Does it make sense? Happy to send matrices, codes if neeeded.

Thanks again

[Xavier]

All solved.