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

조회 수: 29 (최근 30일)
AgonW
AgonW 2020년 2월 6일
댓글: AgonW 2020년 2월 14일
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
Stephen23
Stephen23 2020년 2월 7일
Note to future readers: see dpb's answer for simple, robust, effiicent code.
AgonW
AgonW 2020년 2월 14일
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

댓글을 달려면 로그인하십시오.

답변 (2개)

dpb
dpb 2020년 2월 6일
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
AgonW
AgonW 2020년 2월 14일
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 2020년 2월 14일
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

댓글을 달려면 로그인하십시오.


AgonW
AgonW 2020년 2월 6일
%% 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
dpb
dpb 2020년 2월 6일
편집: dpb 2020년 2월 6일
"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 2020년 2월 7일
편집: Adam Danz 2020년 2월 7일
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.

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 Calendar에 대해 자세히 알아보기

제품


릴리스

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by