Aggregate data every 10 seconds

Question

roberto 2023-6-29

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1989588-aggregate-data-every-10-seconds

编辑： Matt J 2023-6-30

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

2 个评论
显示无隐藏无

Mathieu NOE 2023-6-29

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 2023-6-29

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

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Matt J 2023-6-29

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1989588-aggregate-data-every-10-seconds#answer_1264628

This syntax of retime looks like the applicable one,

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

9 个评论
显示 7更早的评论隐藏 7更早的评论

Matt J 2023-6-29

编辑：Matt J 2023-6-29

在 MATLAB Online 中打开

TICKTYPE2.mat

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    
00:10.000    1905.1    1905.4      NaN         NaN          6    
00:20.000    1905.1    1905.3      NaN         NaN          6    
00:30.000      1905    1905.3      NaN         NaN          6    
00:40.000      1905    1905.3      NaN         NaN          6    
00:50.000      1905    1905.2      NaN         NaN          6    
01:00.000    1904.9    1905.2      NaN         NaN          6    
01:10.000    1904.9    1905.1      NaN         NaN          6    
01:20.000    1904.8      1905      NaN         NaN          6    
01:30.000    1904.7    1904.9      NaN         NaN          6    
01:40.000    1904.6    1904.9      NaN         NaN          6    
01:50.000    1904.6    1904.8      NaN         NaN          6    
02:00.000    1904.5    1904.8      NaN         NaN          6    
02:10.000    1904.5    1904.7      NaN         NaN          6    
02:20.000    1904.6    1904.8      NaN         NaN          6    
02:30.000    1904.7    1904.9      NaN         NaN          6    

Matt J 2023-6-30

编辑：Matt J 2023-6-30

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 2023-6-30

在 MATLAB Online 中打开

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