Creating Timetable from Excel for Driving Cycle.

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.

[Cris LaPierre]

What have you tired so far?

[Jingyu Yang]

I tried

TT= readtimetable('GPS Cycle.xlsx');

summary(TT)

But It doesn't work.

I'm new to matlab's timetable feature.

And I'm not goot at english. So I can't fully understand MATLAB's information about "readtimetable" function.

Answer 1

[Adam Danz]

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

[Jingyu Yang]

Thanks. But I had another problems.. T.T

Answer 2

[Cris LaPierre]

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")

[Adam Danz]

+1

@Jingyu Yang this answer more completely addresses your questions. Consider accepting this one instead.