How to combine datetime and duration columns to form 1 new datetime column in table

28 views (last 30 days)
As there are some times and dates missing it is not possible to create a new datetime array and insert the column then. Simple concatenation does not seem to work. How could i achieve the following;
Input table
Column 1 Column 2 Column 3
Date Time data
01-01-2001 00:00:00 789.2
01-01-2001 01:00:00 892.2
+ + +
01-01-2016 00:00:00 287.3
Desired Output
Column 1 Column 2
Date Time data
01-01-2001 00:00:00 789.2
01-01-2001 01:00:00 892.2
+ +
01-01-2016 23:00:00 287.3
  6 Comments
AgonW
AgonW on 14 Feb 2020
t1 = datetime(2014,12,01,00,00,00);
t2 = datetime(2020,01,01,00,00,00);
t = t1:minutes(10):t2;
t = t.';
Tsum = datetime(t, 'InputFormat', 'dd-MM-yyyy hh:mm:ss');
Tsum = array2table(Tsum);
Tsum = table2timetable(Tsum);
for a = 1:9
disp(['Sync start']);
name = ['FileRef', int2str(a), '.csv'];
Data1 = readtable(name);
Data1 = convertvars(Data1, 'Date', 'string');
Data1 = convertvars(Data1, 'Time', 'string');
Data1.DateTime = Data1.Date + ' ' + Data1.Time;
Data1.DateTime = datetime(Data1.DateTime, 'InputFormat', 'dd-MM-yyyy HH:mm:ss');
Data1 = Data1(:,[4 3]);
name = ['P_T', int2str(a)];
Data1.Properties.VariableNames = {'DateTime' name};
TT = table2timetable(Data1);
Tsum = synchronize(Tsum, TT, 'first');
disp([int2str(a), ' passed']);
end

Sign in to comment.

Answers (2)

dpb
dpb on 6 Feb 2020
Add the duration to the date...
Starting with:
t =
3×3 table
Date Time Data
___________ ________ _____
01-Jan-2001 00:00:00 789.2
01-Jan-2001 01:00:00 892.2
01-Jan-2016 00:00:00 287.3
>>
use
t.DateTime=t.Date+t.Time;
to result in
t =
3×4 table
Date Time Data DateTime
___________ ________ _____ ____________________
01-Jan-2001 00:00:00 789.2 01-Jan-2001 00:00:00
01-Jan-2001 01:00:00 892.2 01-Jan-2001 01:00:00
01-Jan-2016 00:00:00 287.3 01-Jan-2016 00:00:00
>>
you can always overwrite the .Date variable and clear .Time to end up with the order of the table as you wish. Or you might choose a timetable instead of ordinary table--
>> tt=timetable(t.Date+t.Time,t.Data)
tt =
3×1 timetable
Time Var1
____________________ _____
01-Jan-2001 00:00:00 789.2
01-Jan-2001 01:00:00 892.2
01-Jan-2016 00:00:00 287.3
>>
  16 Comments
AgonW
AgonW on 14 Feb 2020
t1 = datetime(2014,12,01,00,00,00);
t2 = datetime(2020,01,01,00,00,00);
t = t1:minutes(10):t2;
t = t.';
Tsum = datetime(t, 'InputFormat', 'dd-MM-yyyy hh:mm:ss');
Tsum = array2table(Tsum);
Tsum = table2timetable(Tsum);
for a = 1:9
disp(['Sync start']);
name = ['FileRef', int2str(a), '.csv'];
Data1 = readtable(name);
Data1 = convertvars(Data1, 'Date', 'string');
Data1 = convertvars(Data1, 'Time', 'string');
Data1.DateTime = Data1.Date + ' ' + Data1.Time;
Data1.DateTime = datetime(Data1.DateTime, 'InputFormat', 'dd-MM-yyyy HH:mm:ss');
Data1 = Data1(:,[4 3]);
name = ['P_T', int2str(a)];
Data1.Properties.VariableNames = {'DateTime' name};
TT = table2timetable(Data1);
Tsum = synchronize(Tsum, TT, 'first');
disp([int2str(a), ' passed']);
end
AgonW
AgonW on 14 Feb 2020
I have gotten back around to this, the above solution is now much more robust combining all of the above suggestions. Thanks guys for all the help

Sign in to comment.


AgonW
AgonW on 6 Feb 2020
%% Combining Into 1 table
t1 = datetime(2014,12,01,00,00,00);
t2 = datetime(2020,01,01,00,00,00);
t = t1:minutes(10):t2;
t = t.';
Tsum = datetime(t, 'InputFormat', 'dd-MM-yyyy hh:mm:ss');
Tsum = array2table(Tsum);
Tsum = table2timetable(Tsum);
for a = 1:9
disp(['Sync start ', int2str(a)]);
name = ['FileRef', int2str(a), '.csv'];
Data1 = readtable(name);
arr = [];
for b = 1:length(Data1.Date)
arr{b} = [char(Data1.Date(b)), ' ', char(Data1.Time(b))];
end
arr = arr.';
arr = datetime(arr, 'InputFormat', 'dd-MM-yyyy HH:mm:ss');
Data1.DateTime = arr;
Data1 = Data1(:,[4 3]);
name = ['Output', int2str(a)];
Data1.Properties.VariableNames = {'DateTime' name};
TT = table2timetable(Data1);
Tsum = synchronize(Tsum, TT, 'first');
disp([int2str(a), ' passed']);
end
  5 Comments
dpb
dpb on 6 Feb 2020
Edited: dpb on 6 Feb 2020
"data are" :)
You could certainly share as much as you've already posted...
That's all it would take is to see the actual file format; four or five lines are as good as a thousand; just has to have the same structure as the real file(s).
Adam Danz
Adam Danz on 7 Feb 2020
Edited: Adam Danz on 7 Feb 2020
It's likely that the solution(s) in dpb's answer is much more efficient and you should reconsider that. There's also quite a bit of hard-coding in your answer (for a = 1:9; Data1(:,[4 3]); etc) which should be avoided.
Regarding the request for sample data, you don't have to share the actual data! The best way for you to get (free) help is to supply the volunteers with a sample of your data so we know what it looks like. For example, 01:30:00 could be in character format, string format, datetime format, duration format, we don't know. The onus is on you to create some sample data that look like your real data in order to put less work on the volunteers trying to help you.

Sign in to comment.

Products


Release

R2019a

Community Treasure Hunt

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

Start Hunting!