Replacing multiple cells with median if value of first column is the same

I have a 302451x3 matrix with stock quotes (bid / offers). Timestamp (in seconds) is in the first column.

If the timestamp is the same for several quotes (the rows) i want to replace all the rows with this timestamp with 1 single row that has that particular timestamp and with the median of the bid / offers in the other columns.

For example the first 60 rows share the same timestamp (34287) and the bids range from 87.57 to 87.67. The median is 87.60.

How do i replace these 60 rows with 1 row where the timestamp 34287 is the value in the first column and this 87.60 median is the value in the second column?

Hope you understand

[Geoff Hayes]

Rasmus - are all of your rows in chronological order?

[Rasmus Larsen]

The timestamps are (column 1)!

Like as an example i have data the attached image.

So for this part of the data i would like to turn it into 3 rows of data with 34287, 34288 and 34289 in column 1 and the respective medians in the other two columns.

I have 302,451 rows of data like this and i tried using the unique() function on the timestamps and i see there are 20,359 unique values. So i'd like to turn the 302,451 rows into 20,359 as in the style above.

Thanks!

Answer 1

[the cyclist]

This is the type of task that accumarray is designed to carry out:

M = [32767 2 3;
     32767 3 4;
     32767 4 5;
     32768 3 4;
     32768 4 5;
     32769 4 5;
     32769 5 6;
     32770 5 6];

[uniqueDatenum,~,idx] = unique(M(:,1));
medianBid = accumarray(idx,M(:,2),[],@median);
medianOffer = accumarray(idx,M(:,3),[],@median);

medianM = [uniqueDatenum,medianBid,medianOffer]

I suggest a careful reading and understanding of the accumarray documentation I linked to.

[Rasmus Larsen]

Wow that's a lot easier than i thought!

Thanks a lot :)