Error when using interpolation method

7 次查看（过去 30 天）
Emad
Emad 2023-10-28
评论： Star Strider 2023-10-29
Ran in:
Dears,
I have a dataset, includes variables serial, year, month, day, second, and price. I want to calculate the 5 minute frequency, using last tick interpolation. When I reaech to the last step, the serial numbers are changed. The codes here are provided by Star Strider.
T1 = readtable('Data.txt');
% (1) is the serial, (2) year, (3) month, (4) days, (5) time in seconds, and (6) price
T1.Properties.VariableNames = {'serial','year','month','day','seconds','price'}
T1 = 9123×6 table
serial year month day seconds price ______ ____ _____ ___ _______ ______ 1972 2013 7 31 1 168.98 1972 2013 7 31 2 169 1972 2013 7 31 3 169 1972 2013 7 31 4 168.99 1972 2013 7 31 5 168.98 1972 2013 7 31 6 168.98 1972 2013 7 31 7 168.98 1972 2013 7 31 8 168.98 1972 2013 7 31 9 168.99 1972 2013 7 31 10 168.98 1972 2013 7 31 11 168.97 1972 2013 7 31 12 168.97 1972 2013 7 31 13 168.98 1972 2013 7 31 14 168.97 1972 2013 7 31 16 168.97 1972 2013 7 31 17 168.96
% T1(end-4:end,:)
DT = datetime(T1{:,[2 3 4]}) + seconds(T1{:,5});
T2 = table(DT,T1{:,1},T1{:,6}, 'VariableNames',{'time','serial','price'})
T2 = 9123×3 table
time serial price ____________________ ______ ______ 31-Jul-2013 00:00:01 1972 168.98 31-Jul-2013 00:00:02 1972 169 31-Jul-2013 00:00:03 1972 169 31-Jul-2013 00:00:04 1972 168.99 31-Jul-2013 00:00:05 1972 168.98 31-Jul-2013 00:00:06 1972 168.98 31-Jul-2013 00:00:07 1972 168.98 31-Jul-2013 00:00:08 1972 168.98 31-Jul-2013 00:00:09 1972 168.99 31-Jul-2013 00:00:10 1972 168.98 31-Jul-2013 00:00:11 1972 168.97 31-Jul-2013 00:00:12 1972 168.97 31-Jul-2013 00:00:13 1972 168.98 31-Jul-2013 00:00:14 1972 168.97 31-Jul-2013 00:00:16 1972 168.97 31-Jul-2013 00:00:17 1972 168.96
TT2 = table2timetable(T2);
TT2 = retime(TT2,'regular','linear','Timestep',minutes(5))
TT2 = 79×2 timetable
time serial price ____________________ ______ ______ 31-Jul-2013 00:00:00 1972 168.96 31-Jul-2013 00:05:00 1972 169.19 31-Jul-2013 00:10:00 1972 169.11 31-Jul-2013 00:15:00 1972 169.04 31-Jul-2013 00:20:00 1972 169.19 31-Jul-2013 00:25:00 1972 169.05 31-Jul-2013 00:30:00 1972 169.14 31-Jul-2013 00:35:00 1972 169.09 31-Jul-2013 00:40:00 1972 169.04 31-Jul-2013 00:45:00 1972 169.06 31-Jul-2013 00:50:00 1972 169.13 31-Jul-2013 00:55:00 1972 169.11 31-Jul-2013 01:00:00 1972 169.45 31-Jul-2013 01:05:00 1972 169.41 31-Jul-2013 01:10:00 1972 169.57 31-Jul-2013 01:15:00 1972 169.56
A simple and results that I got is attached. Could you guide me to get the code accurately.
  6 个评论
显示 4更早的评论
Star Strider
Star Strider 2023-10-28
What calculation is necessary to produce the result you want?
I suspect that whatever calculation is is being appliied to the price is being applied to the serial numbers as well, and that is the reason they are changing. The serial numbers probably only need to be interpolated to match the five-minute intervals, and not have any calculations applied to them.
Emad
Emad 2023-10-28
The calculation thaI want to get after the interpolation is construct 5 minutes log-returns. That is the goal from the prepration data.

请先登录，再进行评论。

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

采纳的回答

Star Strider
Star Strider 2023-10-28
编辑：Star Strider 2023-10-28
Ran in:
The serial numbers change position because timetable arrays require that the time vector be the first column, regardless of how the original table was constructed.
The serial numbers themselves do not change. (If they were included in the computations, one approach would simply be to remove them, calculate the timetable, and then re-insert them afterwards, interpolated separately to create an appropriate lrow size. However here they are simply interpolated, since that is all this code does.)
Example —
T1 = readtable('Data.txt');
% (1) is the serial, (2) year, (3) month, (4) days, (5) time in seconds, and (6) price
T1.Properties.VariableNames = {'serial','year','month','day','seconds','price'}
T1 = 9123×6 table
serial year month day seconds price ______ ____ _____ ___ _______ ______ 1972 2013 7 31 1 168.98 1972 2013 7 31 2 169 1972 2013 7 31 3 169 1972 2013 7 31 4 168.99 1972 2013 7 31 5 168.98 1972 2013 7 31 6 168.98 1972 2013 7 31 7 168.98 1972 2013 7 31 8 168.98 1972 2013 7 31 9 168.99 1972 2013 7 31 10 168.98 1972 2013 7 31 11 168.97 1972 2013 7 31 12 168.97 1972 2013 7 31 13 168.98 1972 2013 7 31 14 168.97 1972 2013 7 31 16 168.97 1972 2013 7 31 17 168.96
% T1(end-4:end,:)
DT = datetime(T1{:,[2 3 4]}) + seconds(T1{:,5});
% T2 = table(DT,T1{:,1},T1{:,6}, 'VariableNames',{'time','serial','price'})
T2 = table(T1{:,1},DT,T1{:,6}, 'VariableNames',{'serial','time','price'})
T2 = 9123×3 table
serial time price ______ ____________________ ______ 1972 31-Jul-2013 00:00:01 168.98 1972 31-Jul-2013 00:00:02 169 1972 31-Jul-2013 00:00:03 169 1972 31-Jul-2013 00:00:04 168.99 1972 31-Jul-2013 00:00:05 168.98 1972 31-Jul-2013 00:00:06 168.98 1972 31-Jul-2013 00:00:07 168.98 1972 31-Jul-2013 00:00:08 168.98 1972 31-Jul-2013 00:00:09 168.99 1972 31-Jul-2013 00:00:10 168.98 1972 31-Jul-2013 00:00:11 168.97 1972 31-Jul-2013 00:00:12 168.97 1972 31-Jul-2013 00:00:13 168.98 1972 31-Jul-2013 00:00:14 168.97 1972 31-Jul-2013 00:00:16 168.97 1972 31-Jul-2013 00:00:17 168.96
T2(end-4:end,:)
ans = 5×3 table
serial time price ______ ____________________ ______ 1972 31-Jul-2013 06:29:56 168.65 1972 31-Jul-2013 06:29:57 168.61 1972 31-Jul-2013 06:29:58 168.63 1972 31-Jul-2013 06:29:59 168.62 1972 31-Jul-2013 06:30:00 168.65
TT2 = table2timetable(T2)
TT2 = 9123×2 timetable
time serial price ____________________ ______ ______ 31-Jul-2013 00:00:01 1972 168.98 31-Jul-2013 00:00:02 1972 169 31-Jul-2013 00:00:03 1972 169 31-Jul-2013 00:00:04 1972 168.99 31-Jul-2013 00:00:05 1972 168.98 31-Jul-2013 00:00:06 1972 168.98 31-Jul-2013 00:00:07 1972 168.98 31-Jul-2013 00:00:08 1972 168.98 31-Jul-2013 00:00:09 1972 168.99 31-Jul-2013 00:00:10 1972 168.98 31-Jul-2013 00:00:11 1972 168.97 31-Jul-2013 00:00:12 1972 168.97 31-Jul-2013 00:00:13 1972 168.98 31-Jul-2013 00:00:14 1972 168.97 31-Jul-2013 00:00:16 1972 168.97 31-Jul-2013 00:00:17 1972 168.96
TT2 = retime(TT2,'regular','linear','Timestep',minutes(5))
TT2 = 79×2 timetable
time serial price ____________________ ______ ______ 31-Jul-2013 00:00:00 1972 168.96 31-Jul-2013 00:05:00 1972 169.19 31-Jul-2013 00:10:00 1972 169.11 31-Jul-2013 00:15:00 1972 169.04 31-Jul-2013 00:20:00 1972 169.19 31-Jul-2013 00:25:00 1972 169.05 31-Jul-2013 00:30:00 1972 169.14 31-Jul-2013 00:35:00 1972 169.09 31-Jul-2013 00:40:00 1972 169.04 31-Jul-2013 00:45:00 1972 169.06 31-Jul-2013 00:50:00 1972 169.13 31-Jul-2013 00:55:00 1972 169.11 31-Jul-2013 01:00:00 1972 169.45 31-Jul-2013 01:05:00 1972 169.41 31-Jul-2013 01:10:00 1972 169.57 31-Jul-2013 01:15:00 1972 169.56
TT2(end-4:end,:)
ans = 5×2 timetable
time serial price ____________________ ______ ______ 31-Jul-2013 06:10:00 1972 169.12 31-Jul-2013 06:15:00 1972 168.85 31-Jul-2013 06:20:00 1972 168.7 31-Jul-2013 06:25:00 1972 168.51 31-Jul-2013 06:30:00 1972 168.65
T3 = readtable('Result.txt') % Second File
T3 = 865×4 table
Var1 Var2 Var3 Var4 ________________ _____________ ____ ______ {''03-Jan-2005'} {'00:00:00''} 1 121.56 {''03-Jan-2005'} {'00:05:00''} 1 121.64 {''03-Jan-2005'} {'00:10:00''} 1 121.69 {''03-Jan-2005'} {'00:15:00''} 1 121.65 {''03-Jan-2005'} {'00:20:00''} 1 121.65 {''03-Jan-2005'} {'00:25:00''} 1 121.51 {''03-Jan-2005'} {'00:30:00''} 1 121.51 {''03-Jan-2005'} {'00:35:00''} 1 121.29 {''03-Jan-2005'} {'00:40:00''} 1 121.18 {''03-Jan-2005'} {'00:45:00''} 1 121.15 {''03-Jan-2005'} {'00:50:00''} 1 120.99 {''03-Jan-2005'} {'00:55:00''} 1 120.98 {''03-Jan-2005'} {'01:00:00''} 1 120.98 {''03-Jan-2005'} {'01:05:00''} 1 120.92 {''03-Jan-2005'} {'01:10:00''} 1 120.82 {''03-Jan-2005'} {'01:15:00''} 1 120.8
T3(end-4:end,:)
ans = 5×4 table
Var1 Var2 Var3 Var4 ________________ _____________ ______ ______ {''05-Jan-2005'} {'23:40:00''} 3.9809 118.44 {''05-Jan-2005'} {'23:45:00''} 3.9857 118.44 {''05-Jan-2005'} {'23:50:00''} 3.9904 118.44 {''05-Jan-2005'} {'23:55:00''} 3.9952 118.44 {''06-Jan-2005'} {'00:00:00''} 3.9999 118.44
Time = datetime(T3.Var1, 'InputFormat','''''dd-MMM-yyyy') + timeofday(datetime(T3.Var2,'InputFormat','HH:mm:ss'''''));
T3 = removevars(T3,[1 2]);
T3 = addvars(T3, Time,'Before','Var3')
T3 = 865×3 table
Time Var3 Var4 ____________________ ____ ______ 03-Jan-2005 00:00:00 1 121.56 03-Jan-2005 00:05:00 1 121.64 03-Jan-2005 00:10:00 1 121.69 03-Jan-2005 00:15:00 1 121.65 03-Jan-2005 00:20:00 1 121.65 03-Jan-2005 00:25:00 1 121.51 03-Jan-2005 00:30:00 1 121.51 03-Jan-2005 00:35:00 1 121.29 03-Jan-2005 00:40:00 1 121.18 03-Jan-2005 00:45:00 1 121.15 03-Jan-2005 00:50:00 1 120.99 03-Jan-2005 00:55:00 1 120.98 03-Jan-2005 01:00:00 1 120.98 03-Jan-2005 01:05:00 1 120.92 03-Jan-2005 01:10:00 1 120.82 03-Jan-2005 01:15:00 1 120.8
T3(end-4:end,:)
ans = 5×3 table
Time Var3 Var4 ____________________ ______ ______ 05-Jan-2005 23:40:00 3.9809 118.44 05-Jan-2005 23:45:00 3.9857 118.44 05-Jan-2005 23:50:00 3.9904 118.44 05-Jan-2005 23:55:00 3.9952 118.44 06-Jan-2005 00:00:00 3.9999 118.44
The second file (‘Result.txt’) appears to be entirely different form the first. The variables are not defined, so I have no idea what they are.
.
  6 个评论
显示 4更早的评论
Emad
Emad 2023-10-28
I really benefit from your codes, you nearly right in most of it. What I want to check, is the interpolation method, as I want to get the last tick. I explain it in the previous replay.
Star Strider
Star Strider 2023-10-29
Thank you!
The last tick would be:
Last = T2(end,:)
or:
Last = TT2(end,:)
depending on what you want.
The same approach works for any of the other table or timetable arrays.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Multirate Signal Processing 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by