Reshape dataset with time intervals?

I have a dataset with time(in seconds) in the first column and price for each time stamp in the second column. The increments in seconds are random, so i want to create 10 second time intervals in my data.

I then want to select the price from the data that lies the closest to the upper-limit of the time-interval, as the price value for that given interval. I have tried setting up a loop but i dont know how to tell matlab to choose the first value within each interval, as the position of this first value varies form interval to interval.

i.e.

time, price
4711, 192.30
4713, 192.32
4714, 192.31
4717, 192.34
4718, 192.30

Turned into:

time, price
4711-4715, 192.30
4716-4720, 192.34

[Stephen23]

Are the values strings or numeric?

[pkh]

Both the dates and time are numeric

Answer 1

[Stephen23]

You can use histc (or histcounts) and the inimitably useful accumarray to do this:

N = 5;
T = [  4711,  4713,  4715,  4717,  4718];%,4800];
P = [192.30,192.32,192.31,192.34,192.30];%,3];
R = 1+N*floor(T/N);
S = min(R)-N:N:max(R)+N;
[cnt,idx] = histc(T,S)
C = accumarray(idx(:),P,[],@(n){n});
idy = cnt>0;
out = cellfun(@(v)v(1),C(idy));
lhb = S(idy);
rhb = S([false,idy])-1;

and the outputs:

>> out
out =
   192.30
   192.34
>> lhb
lhb = 
   4711   4716
>> rhb
rhb =
   4715   4720

[pkh]

Thank you, this is exactly what i want. Can the acumarray be modified to take yet another column of dates into account as subs? i.e.

date, time, price
20100601, 4711, 192.30
20100601, 4713, 192.32
20100601, 4714, 192.31
20100601, 4717, 192.34
20100601, 4718, 192.30
20100603, 4711, 192.30
20100603, 4713, 192.32
20100603, 4714, 192.31
20100603, 4717, 192.34
20100603, 4718, 192.30

into

date, time, price
20100601, 4711-4715, 192.30
20100601, 4716-4720, 192.34
20100603, 4711-4715, 192.30
20100603, 4716-4720, 192.34

[Stephen23]

You just need to provide indices to accumarray. In the example above I used histc to generate these indices, but you can use any method that provides an index for each unique member of the set of values, such as using unique with its rows option. Here I assumed that those values given are numeric:

N = 5;
D = [20100601,20100601,20100601,20100601,20100601,20100603,20100603,20100603,20100603,20100603];
T = [    4711,    4713,    4715,    4717,    4718,    4711,    4713,    4714,    4717,    4718];
P = [  192.30,  192.32,  192.31,  192.34,  192.30,  192.30,  192.32,  192.31,  192.34,  192.30];
X(:,2) = N*ceil(T/N);
X(:,1) = D;
[mat,~,idx] = unique(X,'rows');
C = accumarray(idx(:),P,[],@(n){n});
out = cellfun(@(v)v(1),C);

which generates these output variables:

>> out
out =
   192.30
   192.34
   192.30
   192.34
>> mat
mat =
   20100601       4715
   20100601       4720
   20100603       4715
   20100603       4720

[pkh]

Both the dates and time are numeric, thank you for the help