Aggregate data every 10 seconds

hello everyone, I have a table file "TICKTYPE" with a data column "TICKTYPE.mean" class double and a column "TICKTYPE.x_TIME_" class duration in the format HH:mm:ss.SSS.

I would like to aggregate the data of TICKTYPE.mean every 10 seconds. thank you who will help me.

this is the first row for example:

{'2023.06.29'} 08:55:10.753 15145

[Mathieu NOE]

Resample and Aggregate Data in Timetable - MATLAB & Simulink - MathWorks France

Reformatting a table with (yyyy-mm-dd HH:mm:ss) in one column to a new table with dates in the rows and time in the columns. - MATLAB Answers - MATLAB Central (mathworks.com)

[roberto]

it seems simple to say but i can't write the correct code. Thanks to those who will help me

Answer 1

[Matt J]

This syntax of retime looks like the applicable one,

TT2 = retime(TT1,'regular',method,'TimeStep',dt)

[roberto]

TT= retime(TICKTYPE,'regular','mean','TimeStep',seconds(10));

Incorrect number or types of inputs or outputs for function 'retime'.

[roberto]

I also tried converting "TICKTYPE" to timetable first :

tt= retime(TICKTYPE,'regular','mean','TimeStep',seconds(10));

Error using timetable/retime

All variables in input timetables must be numeric, datetime, or duration when synchronizing using 'mean'.

[Matt J]

Well, attach the timetable in a .mat file so we can see what it really is.

[roberto]

ok file attached, tks.

[Matt J]

You'll have to discard the DATE column, since it is non-numeric.

load('TICKTYPE2.mat')

T=table2timetable(TICKTYPE);
T(:,1)=[];


tt= retime(T,'regular','mean','TimeStep',seconds(10))

tt = 2874×5 timetable
      x_TIME_       x_BID_    x_ASK_    x_LAST_    x_VOLUME_    x_FLAGS_
    ____________    ______    ______    _______    _________    ________

    08:00:00.000    1905.1    1905.4      NaN         NaN          6    
    08:00:10.000    1905.1    1905.4      NaN         NaN          6    
    08:00:20.000    1905.1    1905.3      NaN         NaN          6    
    08:00:30.000      1905    1905.3      NaN         NaN          6    
    08:00:40.000      1905    1905.3      NaN         NaN          6    
    08:00:50.000      1905    1905.2      NaN         NaN          6    
    08:01:00.000    1904.9    1905.2      NaN         NaN          6    
    08:01:10.000    1904.9    1905.1      NaN         NaN          6    
    08:01:20.000    1904.8      1905      NaN         NaN          6    
    08:01:30.000    1904.7    1904.9      NaN         NaN          6    
    08:01:40.000    1904.6    1904.9      NaN         NaN          6    
    08:01:50.000    1904.6    1904.8      NaN         NaN          6    
    08:02:00.000    1904.5    1904.8      NaN         NaN          6    
    08:02:10.000    1904.5    1904.7      NaN         NaN          6    
    08:02:20.000    1904.6    1904.8      NaN         NaN          6    
    08:02:30.000    1904.7    1904.9      NaN         NaN          6    

[roberto]

thanks very very much Matt J!

[roberto]

unfortunately there is a problem.

tt= retime(T,'regular','mean','TimeStep',seconds(10)) does not calculate the exact average of the values ​​included in every 10 seconds. can you help?

[Matt J]

I would have to see a demonstration of the problem. Offhand, I don't know why it wouldn't be computing the exact mean.

[Mathieu NOE]

well , I don't want to say something stupid here ,especially as I don't think I am expert with timetable and retime , but my attempt to compare retime results with my own code (certainly not the best) gives some minor deltas . Not sure why .

only thing I noticed is that the original time data has not constant spacing but I suppose that's something retime does handle (to be confirmed?)

so here is it , whatever it proves or not and sorry if I just brought more confusion !

does retime fill missing bins with interpolated data ?

the x axis on the plots are simply the samples count after 10 s averaging

load('TICKTYPE2.mat')
xBID = TICKTYPE.x_BID_;
xASK = TICKTYPE.x_ASK_;

% 10 seconds average results with retime
T=table2timetable(TICKTYPE);
T(:,1)=[];
tt= retime(T,'regular','mean','TimeStep',seconds(10));

% my own 10 s average computation
tx = (TICKTYPE.x_TIME_);
s = seconds(tx);
% NB that s spacing is not constant 
dt = 10; % seconds
start = floor(s(1)); 
stop = dt*ceil(s(end)/dt);
n = (stop-start)/dt;

for ck = 1:n
    ind1 = start + (ck-1)*dt;
    ind2 = ind1 + dt -1;
    id = (s>= ind1 & s<= ind2); % NB that s spacing is not constant
    xBID_10s_mean(ck,1) =  mean(xBID(id));
    xASK_10s_mean(ck,1) =  mean(xASK(id));
end

% plot the results 
figure(1)
plot(tt.x_BID_);
hold on
plot(xBID_10s_mean);
title('x BID');
legend('retime','own code');

figure(2)
plot(tt.x_ASK_);
hold on
plot(xASK_10s_mean);
title('x ASK');
legend('retime','own code');