Creating Timetable from Excel for Driving Cycle.
2 ビュー (過去 30 日間)
古いコメントを表示
I would like to create a timetable for the driving cycle using the above two excel files.
I want to extract the speed data per second from the GPS Cycle excel file,
I want to extract Distance data per second from Lidar Cycle Excel file.
I want to write these two data in a timetable per second and make a graph.
Unfortunately, GPS data is well recorded per second, but Lidar Distance data per second is sometimes missed by 2 seconds.
Ex) 2020.06.25 15:21:23 Distance = 1533 -> 2020.06.25 15:21:25 Distance = 1344 (No 2020.06.25 15:21:24 Data)
I want to process this Missing Data as well.
I want to give the missing Lidar Distance Data a value of 0.
I don't get a sense of which method to use.
Please help.
2 件のコメント
採用された回答
Adam Danz
2020 年 8 月 22 日
編集済み: Adam Danz
2020 年 8 月 22 日
Create the timetable
opts = detectImportOptions('GPS Cycle.xlsx');
opts.VariableTypes = {'double','datetime','double','double','double','double','double','double','double'};
TT = readtimetable('GPS Cycle.xlsx',opts);
View first few rows
head(TT)
8×8 timetable
DATE Column TIME_Second_ Longitude Latitude Altitude_m_ Course Velocity_km_h_ TotalDistance_km_
___________________ ______ ____________ _________ ________ ___________ ______ ______________ _________________
2020-07-30 18:38:05 1 0 127.01 37.542 61.5 285 0 0
2020-07-30 18:38:06 2 1 127.01 37.542 61.5 284.7 0 0
2020-07-30 18:38:07 3 2 127.01 37.542 64.4 284.7 14.2 0.004
2020-07-30 18:38:08 4 3 127.01 37.542 64.4 284.7 0.7 0.004
2020-07-30 18:38:09 5 4 127.01 37.542 64.4 284.7 0 0.004
2020-07-30 18:38:10 6 5 127.01 37.542 64.4 284.7 0 0.004
2020-07-30 18:38:11 7 6 127.01 37.542 64.4 284.7 0 0.004
2020-07-30 18:38:12 8 7 127.01 37.542 67.1 284.7 56.9 0.02
Extract the speed data
TT.Velocity_km_h_
Note, you can clean up the variable names within the file or by using
opts.VariableNames
その他の回答 (1 件)
Cris LaPierre
2020 年 8 月 22 日
Try something like this.
% Get speed data (from Adam)
opts = detectImportOptions('GPS Cycle.xlsx');
opts.VariableTypes = {'double','datetime','double','double','double','double','double','double','double'};
GPS = readtimetable('GPS Cycle.xlsx',opts);
GPS = retime(GPS,"secondly","fillwithconstant","Constant",0);
% Get distance
opts = detectImportOptions('Lidar Cycle.xlsx');
opts.VariableNames = ["Var1", "Var2", "DATE", "Var4", "Day", "Var6", "Time", "Var8", "Var9", "Var10", "Distance", "Unit"];
opts.SelectedVariableNames = ["DATE", "Time", "Distance"];
opts.VariableTypes = ["categorical", "char", "datetime", "char", "categorical", "char", "duration", "char", "categorical", "char", "double", "categorical"];
opts = setvaropts(opts,"Time","DurationFormat","hh:mm:ss.S");
Lidar = readtimetable('Lidar Cycle.xlsx',opts);
Lidar.DATE = Lidar.DATE + Lidar.Time;
Lidar = retime(Lidar,"secondly","fillwithconstant","Constant",0);
Lidar = removevars(Lidar,"Time");
% merge the GPS and Lidar data
comb = synchronize(GPS,Lidar)
% Create plot
yyaxis left
plot(comb.DATE,comb.Distance)
ylabel("Distance (cm)")
yyaxis right
plot(comb.DATE,comb.Velocity_km_h_)
ylabel("Velocity (km/h)")
xlabel("Time (hh:mm:ss)")
xtickformat("hh:mm:ss")
1 件のコメント
Adam Danz
2020 年 8 月 23 日
編集済み: Adam Danz
2020 年 8 月 23 日
+1
@Jingyu Yang this answer more completely addresses your questions. Consider accepting this one instead.
参考
カテゴリ
Help Center および File Exchange で Spreadsheets についてさらに検索
製品
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!