I am trying to synchronize two tables, how do I do this to keep the date and time format?

4 views (last 30 days)
Chester Rosie
Chester Rosie on 14 Feb 2022
Edited: Cris LaPierre on 14 Feb 2022
app.bow_time_axis = datetime(app.bow{:,1:2},'InputFormat',"dd/MM/uuuu HH:mm:ss");
app.bow_timetable = timetable(app.bow_time_axis,app.bow{:,3:4});
app.stern_time_axis = datetime(app.bow{:,1:2},'InputFormat',"dd/MM/uuuu HH:mm:ss");
app.stern_timetable = timetable(app.stern_time_axis,app.bow{:,3:4});
app.comb = synchronize(app.bow_timetable, app.stern_timetable, 'first', 'pchip');
This the error message due to the datetime difficulty:
Unable to concatenate the specified table variables.
Caused by:
Error using datetime/horzcat (line 1341)
All inputs must be datetimes or date/time character vectors or date/time strings.
This is an example of the table format being used (note it is dd/mm/yyyy):
10/10/2020,20:37:00.00,426988.025,5830157.551
  2 Comments
Chester Rosie
Chester Rosie on 14 Feb 2022
classdef Static < matlab.apps.AppBase
% Properties that correspond to app components
properties (Access = public)
UIFigure matlab.ui.Figure
LoadBowFileButton matlab.ui.control.Button
UIAxes matlab.ui.control.UIAxes
UITable matlab.ui.control.Table
LoadSternFileButton matlab.ui.control.Button
LoadHydrinsFileButton matlab.ui.control.Button
ComputeButton matlab.ui.control.Button
SaveHeadingComparisionButton matlab.ui.control.Button
end
properties (Access = public)
file;
path;
columnName;
x;
y;
bow;
stern;
comb;
final;
headingtable;
bow_easting
stern_easting
bow_northing
stern_northing;
heading;
hydrins;
compare;
loadhydrins;
bow_time_axis;
stern_time_axis;
hydrins_time_axis;
bow_timetable;
stern_timetable;
hydrins_timetable;
end
% Callbacks that handle component events
methods (Access = private)
% Button pushed function: LoadBowFileButton
function LoadBowFileButtonPushed(app, event)
[app.file,app.path] = uigetfile('*.txt', 'Select file');
app.bow = readtable(fullfile(app.path, app.file));
app.UITable.Data = app.bow;
app.bow.Properties.VariableNames{1} = 'Date';
app.bow.Properties.VariableNames{2} = 'Time';
app.bow.Properties.VariableNames{3} = 'Easting';
app.bow.Properties.VariableNames{4} = 'Northing';
app.UITable.ColumnName = app.bow.Properties.VariableNames;
app.x = app.UITable.Data{:,3};
app.y = app.UITable.Data{:,4};
plot(app.UIAxes, app.x, app.y);
title(app.UIAxes,'Bow GNSS');
ylabel(app.UIAxes,'Northing');
xlabel(app.UIAxes,'Easting');
app.bow_time_axis = datetime(app.bow{:,1:2},'InputFormat',"dd/MM/uuuu HH:mm:ss");
app.bow_timetable = timetable(app.bow_time_axis,app.bow{:,3:4});
end
% Button pushed function: LoadSternFileButton
function LoadSternFileButtonPushed(app, event)
[app.file,app.path] = uigetfile('*.txt', 'Select file');
app.stern = readtable(fullfile(app.path, app.file));
app.stern.Properties.VariableNames{1} = 'Stern Date';
app.stern.Properties.VariableNames{2} = 'Stern Time';
app.stern.Properties.VariableNames{3} = 'Stern Easting';
app.stern.Properties.VariableNames{4} = 'Stern Northing';
app.stern_time_axis = datetime(app.stern{:,1:2},'InputFormat',"dd/MM/uuuu HH:mm:ss");
app.stern_timetable = timetable(app.stern_time_axis,app.bow{:,3:4});
app.comb = synchronize(app.bow_timetable, app.stern_timetable, 'first', 'pchip');
app.headingtable = table('Size',[height(app.comb) 1],'VariableTypes',{'double'});
for i = 1:1:height(app.comb)
app.bow_easting = (app.comb{i,3});
app.stern_easting = (app.comb{i,5});
app.bow_northing = (app.comb{i,4});
app.stern_northing = (app.comb{i,6});
app.heading = atan2d(((app.bow_northing)-(app.stern_northing)),((app.bow_easting)-(app.stern_easting)));
if app.heading > 0
app.headingtable{i,1} = 360 - (app.heading - 90);
elseif app.heading < 0
app.headingtable{i,1} = 90 - app.heading;
elseif app.heading == 0
app.headingtable{i,1} = 90;
end
end
app.final = horzcat(app.comb, app.headingtable);
app.UITable.Data = app.final;
app.final.Properties.VariableNames{1} = 'Date';
app.final.Properties.VariableNames{2} = 'Time';
app.final.Properties.VariableNames{3} = 'BowEasting';
app.final.Properties.VariableNames{4} = 'BowNorthing';
app.final.Properties.VariableNames{5} = 'SternEasting';
app.final.Properties.VariableNames{6} = 'SternNorthing';
app.final.Properties.VariableNames{7} = 'Heading';
app.UITable.ColumnName = app.final.Properties.VariableNames;
app.x = app.UITable.Data{:,2};
app.y = app.UITable.Data{:,7};
plot(app.UIAxes, app.x, app.y);
title(app.UIAxes,'Calculated Heading over Time');
ylabel(app.UIAxes,'Calculated Heading');
xlabel(app.UIAxes,'Time');
end

Sign in to comment.

Sign in to answer this question.

Answers (2)

Seth Furman
Seth Furman on 14 Feb 2022
Ran in:
Create example data
bowData = [
"10/10/2020,20:37:00.00,426988.025,5830157.551"
"10/10/2020,20:37:01.00,426988.027,5830157.549"
"10/10/2020,20:37:02.00,426988.029,5830157.552"
"10/10/2020,20:37:03.00,426988.036,5830157.555"
"10/10/2020,20:37:04.00,426988.042,5830157.553"
"10/10/2020,20:37:05.00,426988.042,5830157.553"
"10/10/2020,20:37:06.00,426988.041,5830157.551"
"10/10/2020,20:37:07.00,426988.031,5830157.546"
];
writematrix(bowData, "BOW.txt", "QuoteStrings", false);
sternData = [
"10/10/2020,20:39:59.00,426991.426,5830128.000"
"10/10/2020,20:40:00.00,426991.430,5830127.999"
"10/10/2020,20:40:01.00,426991.431,5830128.005"
"10/10/2020,20:40:02.00,426991.436,5830128.006"
"10/10/2020,20:40:03.00,426991.430,5830128.006"
"10/10/2020,20:40:04.00,426991.425,5830128.007"
"10/10/2020,20:40:05.00,426991.419,5830128.009"
"10/10/2020,20:40:06.00,426991.420,5830128.007"
];
writematrix(sternData, "STERN.txt", "QuoteStrings", false);
Read data
opts = detectImportOptions("BOW.txt");
opts = setvaropts(opts,1, "InputFormat", "MM/dd/uuuu");
ttBow = readtimetable("BOW.txt",opts)
ttBow = 8×3 timetable
Var1 Var2 Var3 Var4 __________ ________ __________ __________ 10/10/2020 20:37:00 4.2699e+05 5.8302e+06 10/10/2020 20:37:01 4.2699e+05 5.8302e+06 10/10/2020 20:37:02 4.2699e+05 5.8302e+06 10/10/2020 20:37:03 4.2699e+05 5.8302e+06 10/10/2020 20:37:04 4.2699e+05 5.8302e+06 10/10/2020 20:37:05 4.2699e+05 5.8302e+06 10/10/2020 20:37:06 4.2699e+05 5.8302e+06 10/10/2020 20:37:07 4.2699e+05 5.8302e+06
ttBow.Var1 = ttBow.Var1 + ttBow.Var2;
ttBow.Var1.Format = "MM/dd/uuuu HH:mm:ss.SS";
ttBow = removevars(ttBow, "Var2")
ttBow = 8×2 timetable
Var1 Var3 Var4 ______________________ __________ __________ 10/10/2020 20:37:00.00 4.2699e+05 5.8302e+06 10/10/2020 20:37:01.00 4.2699e+05 5.8302e+06 10/10/2020 20:37:02.00 4.2699e+05 5.8302e+06 10/10/2020 20:37:03.00 4.2699e+05 5.8302e+06 10/10/2020 20:37:04.00 4.2699e+05 5.8302e+06 10/10/2020 20:37:05.00 4.2699e+05 5.8302e+06 10/10/2020 20:37:06.00 4.2699e+05 5.8302e+06 10/10/2020 20:37:07.00 4.2699e+05 5.8302e+06
ttStern = readtimetable("STERN.txt",opts)
ttStern = 8×3 timetable
Var1 Var2 Var3 Var4 __________ ________ __________ __________ 10/10/2020 20:39:59 4.2699e+05 5.8301e+06 10/10/2020 20:40:00 4.2699e+05 5.8301e+06 10/10/2020 20:40:01 4.2699e+05 5.8301e+06 10/10/2020 20:40:02 4.2699e+05 5.8301e+06 10/10/2020 20:40:03 4.2699e+05 5.8301e+06 10/10/2020 20:40:04 4.2699e+05 5.8301e+06 10/10/2020 20:40:05 4.2699e+05 5.8301e+06 10/10/2020 20:40:06 4.2699e+05 5.8301e+06
ttStern.Var1 = ttStern.Var1 + ttStern.Var2;
ttStern.Var1.Format = "MM/dd/uuuu HH:mm:ss.SS";
ttStern = removevars(ttStern, "Var2")
ttStern = 8×2 timetable
Var1 Var3 Var4 ______________________ __________ __________ 10/10/2020 20:39:59.00 4.2699e+05 5.8301e+06 10/10/2020 20:40:00.00 4.2699e+05 5.8301e+06 10/10/2020 20:40:01.00 4.2699e+05 5.8301e+06 10/10/2020 20:40:02.00 4.2699e+05 5.8301e+06 10/10/2020 20:40:03.00 4.2699e+05 5.8301e+06 10/10/2020 20:40:04.00 4.2699e+05 5.8301e+06 10/10/2020 20:40:05.00 4.2699e+05 5.8301e+06 10/10/2020 20:40:06.00 4.2699e+05 5.8301e+06
Synchronize
synchronize(ttBow, ttStern, "first", "pchip")
ans = 8×4 timetable
Var1 Var3_ttBow Var4_ttBow Var3_ttStern Var4_ttStern ______________________ __________ __________ ____________ ____________ 10/10/2020 20:37:00.00 4.2699e+05 5.8302e+06 4.3213e+05 5.836e+06 10/10/2020 20:37:01.00 4.2699e+05 5.8302e+06 4.3205e+05 5.8359e+06 10/10/2020 20:37:02.00 4.2699e+05 5.8302e+06 4.3196e+05 5.8358e+06 10/10/2020 20:37:03.00 4.2699e+05 5.8302e+06 4.3188e+05 5.8357e+06 10/10/2020 20:37:04.00 4.2699e+05 5.8302e+06 4.318e+05 5.8356e+06 10/10/2020 20:37:05.00 4.2699e+05 5.8302e+06 4.3171e+05 5.8355e+06 10/10/2020 20:37:06.00 4.2699e+05 5.8302e+06 4.3163e+05 5.8354e+06 10/10/2020 20:37:07.00 4.2699e+05 5.8302e+06 4.3155e+05 5.8353e+06
Cris LaPierre
Cris LaPierre on 14 Feb 2022
Edited: Cris LaPierre on 14 Feb 2022
I think what may be of most help is this page on accessing data in tables. In the end, that is the biggest changes I made. Here are some suggestions:
  1. You can set the variable names in a single line of code using a cell array
  2. You can access data in a table using tableNm.varNm
  3. There is ambiguity in your date format. Specify this using detetectImportOptions and setvaropts.
  4. You can just add a datetime and a duration to combine date and time.
  5. Use table2timetable to create your timetable. Use the 'RowTimes' name-value pair to specify the time data. This way, you columns remain separate variables.
  6. UITable data cannot be a timetable. Convert back to a table using timetable2table.
I believe your error came from using the curly brace syntax to extract array data from your table: app.bow{:,1:2}. Here, column 1 is a datetime, but column 2 is a duration. These are different datatypes, so they cannot be placed into the same array, hence the error.
There may be some other small changes, so I'll share the updated functions below.
% Button pushed function: LoadBowFileButton
function LoadBowFileButtonPushed(app, event)
[app.file,app.path] = uigetfile('*.txt', 'Select file');
opts = detectImportOptions(fullfile(app.path, app.file));
opts = setvaropts(opts,1,"InputFormat","MM/dd/yyyy");
app.bow = readtable(fullfile(app.path, app.file),opts);
app.bow.Properties.VariableNames = {'Date','Time','Easting','Northing'};
app.UITable.ColumnName = app.bow.Properties.VariableNames;
app.UITable.Data = app.bow;
app.x = app.UITable.Data.Easting;
app.y = app.UITable.Data.Northing;
plot(app.UIAxes, app.x, app.y);
title(app.UIAxes,'Bow GNSS');
ylabel(app.UIAxes,'Northing');
xlabel(app.UIAxes,'Easting');
app.bow_time_axis = app.bow.Date + app.bow.Time;
app.bow_time_axis.Format = "MM/dd/yyyy HH:mm:ss";
app.bow_timetable = table2timetable(app.bow(:,3:4),"RowTimes",app.bow_time_axis);
end
% Button pushed function: LoadSternFileButton
function LoadSternFileButtonPushed(app, event)
[app.file,app.path] = uigetfile('*.txt', 'Select file');
app.stern = readtable(fullfile(app.path, app.file));
app.stern.Properties.VariableNames = {'Stern Date','Stern Time','Stern Easting','Stern Northing'};
app.stern_time_axis = app.stern.('Stern Date') + app.stern.('Stern Time');
app.stern_time_axis.Format = "MM/dd/yyyy HH:mm:ss";
app.stern_timetable = table2timetable(app.stern(:,3:4),"RowTimes",app.stern_time_axis);
app.comb = synchronize(app.bow_timetable, app.stern_timetable, 'first', 'pchip');
app.headingtable = table('Size',[height(app.comb) 1],'VariableTypes',{'double'});
for i = 1:1:height(app.comb)
app.bow_easting = app.comb.Easting(i);
app.stern_easting = app.comb.('Stern Easting')(i);
app.bow_northing = app.comb.Northing(i);
app.stern_northing = app.comb.('Stern Northing')(i);
app.heading = atan2d(((app.bow_northing)-(app.stern_northing)),((app.bow_easting)-(app.stern_easting)));
if app.heading > 0
app.headingtable{i,1} = 360 - (app.heading - 90);
elseif app.heading < 0
app.headingtable{i,1} = 90 - app.heading;
elseif app.heading == 0
app.headingtable{i,1} = 90;
end
end
app.final = timetable2table(horzcat(app.comb, app.headingtable));
app.final.Properties.VariableNames = {'Time','BowEasting','BowNorthing','SternEasting','SternNorthing','Heading'};
app.UITable.Data = app.final;
app.UITable.ColumnName = app.final.Properties.VariableNames;
app.x = app.UITable.Data.Time;
app.y = app.UITable.Data.Heading;
plot(app.UIAxes, app.x, app.y);
title(app.UIAxes,'Calculated Heading over Time');
ylabel(app.UIAxes,'Calculated Heading');
xlabel(app.UIAxes,'Time');
end
  2 Comments
Chester Rosie
Chester Rosie on 14 Feb 2022
Thanks this is great, but I need only rows where both tables being synchronised have data to be added into the new table, with all others being removed.
Should I be using a loop for that or adjusting this line?
app.comb = synchronize(app.bow_timetable, app.stern_timetable, 'first', 'pchip');
Cris LaPierre
Cris LaPierre on 14 Feb 2022
Edited: Cris LaPierre on 14 Feb 2022
Ran in:
That is not what synchronize does. Instead, it uses the method you specify to fill in the missing data: "Synchronize timetables to common time vector, and resample or aggregate data from input timetables"
You probably want to look into joining tables if you only want to keep that data that corresponds to timestamps present in both tables. I think innerjoin accomplishes what you want.
% Load BOW data
opts = detectImportOptions('BOW.txt');
opts.VariableNames = {'Date','Time','Easting','Northing'};
opts = setvaropts(opts,'Date',"InputFormat","MM/dd/yyyy");
bow = readtable('BOW.txt',opts);
bow.Timestamp = bow.Date + bow.Time;
bow.Timestamp.Format = "MM/dd/uuuu HH:mm:ss"
bow = 1299×5 table
Date Time Easting Northing Timestamp __________ ________ __________ __________ ___________________ 10/10/2020 20:37:00 4.2699e+05 5.8302e+06 10/10/2020 20:37:00 10/10/2020 20:37:01 4.2699e+05 5.8302e+06 10/10/2020 20:37:01 10/10/2020 20:37:02 4.2699e+05 5.8302e+06 10/10/2020 20:37:02 10/10/2020 20:37:03 4.2699e+05 5.8302e+06 10/10/2020 20:37:03 10/10/2020 20:37:04 4.2699e+05 5.8302e+06 10/10/2020 20:37:04 10/10/2020 20:37:05 4.2699e+05 5.8302e+06 10/10/2020 20:37:05 10/10/2020 20:37:06 4.2699e+05 5.8302e+06 10/10/2020 20:37:06 10/10/2020 20:37:07 4.2699e+05 5.8302e+06 10/10/2020 20:37:07 10/10/2020 20:37:08 4.2699e+05 5.8302e+06 10/10/2020 20:37:08 10/10/2020 20:37:09 4.2699e+05 5.8302e+06 10/10/2020 20:37:09 10/10/2020 20:37:10 4.2699e+05 5.8302e+06 10/10/2020 20:37:10 10/10/2020 20:37:11 4.2699e+05 5.8302e+06 10/10/2020 20:37:11 10/10/2020 20:37:12 4.2699e+05 5.8302e+06 10/10/2020 20:37:12 10/10/2020 20:37:13 4.2699e+05 5.8302e+06 10/10/2020 20:37:13 10/10/2020 20:37:14 4.2699e+05 5.8302e+06 10/10/2020 20:37:14 10/10/2020 20:37:15 4.2699e+05 5.8302e+06 10/10/2020 20:37:15
% Load STERN data
opts = detectImportOptions('STERN.txt');
opts.VariableNames = {'Date','Time','Easting','Northing'};
opts = setvaropts(opts,'Date',"InputFormat","MM/dd/yyyy");
stern = readtable('STERN.txt',opts);
stern.Timestamp = stern.Date + stern.Time;
stern.Timestamp.Format = "MM/dd/uuuu HH:mm:ss"
stern = 987×5 table
Date Time Easting Northing Timestamp __________ ________ __________ __________ ___________________ 10/10/2020 20:39:59 4.2699e+05 5.8301e+06 10/10/2020 20:39:59 10/10/2020 20:40:00 4.2699e+05 5.8301e+06 10/10/2020 20:40:00 10/10/2020 20:40:01 4.2699e+05 5.8301e+06 10/10/2020 20:40:01 10/10/2020 20:40:02 4.2699e+05 5.8301e+06 10/10/2020 20:40:02 10/10/2020 20:40:03 4.2699e+05 5.8301e+06 10/10/2020 20:40:03 10/10/2020 20:40:04 4.2699e+05 5.8301e+06 10/10/2020 20:40:04 10/10/2020 20:40:05 4.2699e+05 5.8301e+06 10/10/2020 20:40:05 10/10/2020 20:40:06 4.2699e+05 5.8301e+06 10/10/2020 20:40:06 10/10/2020 20:40:07 4.2699e+05 5.8301e+06 10/10/2020 20:40:07 10/10/2020 20:40:08 4.2699e+05 5.8301e+06 10/10/2020 20:40:08 10/10/2020 20:40:09 4.2699e+05 5.8301e+06 10/10/2020 20:40:09 10/10/2020 20:40:10 4.2699e+05 5.8301e+06 10/10/2020 20:40:10 10/10/2020 20:40:11 4.2699e+05 5.8301e+06 10/10/2020 20:40:11 10/10/2020 20:40:12 4.2699e+05 5.8301e+06 10/10/2020 20:40:12 10/10/2020 20:40:13 4.2699e+05 5.8301e+06 10/10/2020 20:40:13 10/10/2020 20:40:14 4.2699e+05 5.8301e+06 10/10/2020 20:40:14
% Join tables only keeping timestamps common to both
joinedData = innerjoin(bow,stern,"Keys","Timestamp","LeftVariables",...
["Timestamp","Easting","Northing"],"RightVariables",["Easting","Northing"])
joinedData = 987×5 table
Timestamp Easting_bow Northing_bow Easting_stern Northing_stern ___________________ ___________ ____________ _____________ ______________ 10/10/2020 20:39:59 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:00 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:01 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:02 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:03 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:04 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:05 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:06 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:07 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:08 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:09 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:10 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:11 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:12 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:13 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06 10/10/2020 20:40:14 4.2699e+05 5.8302e+06 4.2699e+05 5.8301e+06

Sign in to comment.

Categories

Find more on Timetables in Help Center and File Exchange

Tags

Products


Release

R2020a

Community Treasure Hunt

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

Start Hunting!