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

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

[Guillaume]

Why is the datetime on the last row 01-01-2001 23:00:00 and not 01-01-2016 00:00:00 ?

What are the classes of Date and Time, if Date is a datetime vector, what actual time information is stored in there? (You can see it by changing the Format property to display the time.

[AgonW]

sorry typo, it has been corrected

date is read as category type and time is of duration type

[Guillaume]

Do you mean categorical ? And are you sure? That would be a very weird use of the categorical type?

[Adam Danz]

Note, this is a continuation of this thread

https://www.mathworks.com/matlabcentral/answers/503906-how-do-i-combine-data-from-seperate-tables-into-one-table-with-seperate-columns

[Stephen23]

Note to future readers: see dpb's answer for simple, robust, effiicent code.

[AgonW]

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

Answer 1

[dpb]

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

[Guillaume]

This works, as long as the time embedded in Date (assuming it is of class datetime) is 0:0:0. Otherwise, you're just adding to the existing time which is not currently displayed.

[dpb]

The data read in that would generate two fields as such can be presumed to have been just a date and a time field separately; hence the time in the Date field will be identically zero.

If the file were malformed somehow and had both a date-time field incorporating a non-zero time and a separate time, that would be an error indeed.

[Steven Lord]

It would be unusual, but removing the time part of the datetime is possible using dateshift to shift it to the 'start' of the 'day'.

dt = datetime('now') % About 3:20 PM on February 6, 2020 as I type this
dt2 = dateshift(dt, 'start', 'day') % Midnight on February 6, 2020

[dpb]

That's so also, Steven, but I purposely didn't bring it up as I figure if that were the case then it might well be the two fields are something entirely different...like the first being the date/time of the acquisition or event but the second a measured duration of some observed quantity not related to the calendar time at all.

That the code the OP posted later is reading a .csv file makes it highly likely imo the file does indeed contain the two fields of a date and a time such that they are read as separate variables; the assumption I made above.

Of course, that could be dealt with pretty efficiently on reading the file in all likelihood as well; if we had the file structure to be sure what it is we have to start from... :)

[Adam Danz]

I also had Steven Lord's idea in mind. It removes the need to assume the date data will always be rounded to midnight.

% Produce a table of dates, durations, and data
T = table(datetime(2001,1,1:5)', duration(1:5,0,0)', rand(5,1), 'VariableNames', {'Date','Duration','Data'}); 
% Add a date+time column
T.DateTime = dateshift(T.Date, 'start', 'day') + T.Duration; 

[Guillaume]

Yes, it's simple and completely removes the uncertainty. Considering that merging a data and a time is a fairly common question on answers, perhaps it could be a function that comes with matlab?

[Adam Danz]

That may come in handy although the manual addition can also be done in 1 line of code. I'd have two concerns with the function.

1) it would have to be very clear that the function is rounding-down the datetime values before adding the duration. It's also common to add a duration value to a datetime value that already contains hours/minutes/seconds and users of the function shouldn't confuse the two approaches.

2) Since many datetime formats only show the year/month/day, a less experienced user may assume that the datetime value doesn't contain hour/minute/second information and the round-down approach may result in the unintended loss of information.

It's fairly clear when using the dateshift() function that the datetimes are shifted to midnight and that adds a layer of intentionality and awareness of what the user is doing with the data.

I think it would be useful to have a function that receives a vector of dates and a vector of times, both in string or char format, and outputs a single datetime vector.

[dpb]

I think it's the wrong approach entirely -- either fix the input file to output the date/time field as one or read the file correctly using the appropriate formatting as it exists.

We haven't been provided the starting point here but I'm betting it's a case where the .csv file has been created so the date and the time are, indeed, two separate fields.

The only problem OP ran into is that he didn't understand (and the doc is really all that clear about) how to merge those two fields back together and so made the not illogical attempt to do so by catenation and various other machinations instead of just adding the time as a duration to the date as datetime.

[AgonW]

This would not be as efficient as I am reading from 9 different locations each of which had a separate csv file for every month dating from November 2014 to January 2020 which would result in 550+ files each of 144 rows which needed to be changed individually

[Adam Danz]

In this section of your answer, it appears that Data1 is a table with columns Date and Time and you're looping through each row to combine the date and time into a char array and then converting that to datetime. That approach is fine. I'm just suggesting it would be more efficient to convert the entire Date column to datetime, convert the entire Time column to duration, and then combine them using dpb's approach (or the dateshift version described in the comments). That could be done in 1 line of code (although it may be more readable in 3 lines) and it certainly wouldn't require a loop.

    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');

[Guillaume]

"I think it would be useful to have a function that receives a vector of dates and a vector of times, both in string or char format, and outputs a single datetime vector."

Yes, alternatively, since the typical reason users get a separate date and time is usually because they're two separate columns in a text file maybe readtable and co. could have an option to tell it to automerge a date and time column. Or maybe convertvars could do this.

T = readtable(somefile, 'MergeDateTime', {'DateVar', 'TimeVar'})  %or [DateColumn, TimeColumn]

would be nice.

[Guillaume]

"This would not be as efficient as I am ..."

Whatever it is you're doing, it's extremely likely that it can be done more efficiently, with simpler, clearer and more importantly more robust code than what you wrote. However, the first step in us showing you that code is for us to understand the format of your files. For that, as has been repeatedly said, we need an example. It doesn't have to have real data, all confidential stuff can be replaced by dummy data as long as the format is preserverd, and we only need the first few lines, enough to understand the format.

[Adam Danz]

That's a great idea, I'm sure it would become popular, and it would increase the use of datetime objects rather than older alternatives.

[dpb]

"This would not be as efficient as I am reading from 9 different locations each of which had a separate csv file for every month dating from November 2014 to January 2020 which would result in 550+ files each of 144 rows which needed to be changed individually"

If you don't provide sufficient information, how do you possibly expect somebody to provide an effective, efficient solution?

The Crystal Ball Toolbox is yet to be released.

[AgonW]

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]

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

Answer 2

[AgonW]

%% 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

[AgonW]

This is the solution I have come up with, it takes a few minutes to run but works

[dpb]

Looks like a lot of overhead there...but we are handicapped in not having an input file to look at to see what to do more efficiently; all we had to work with was what you showed in the original Q?

Attach a section of a file with the issue...

[AgonW]

I'm sorry I can't, the data is confidential and I don't have the clearance to share this

[dpb]

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

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.