Create datetime from two columns of a table

Hi,

I have two dataset with different time and hours, I have created a timetable variable and would like to merge them togehter (matlab therefore matching the timestamp of each dataset in the right row of the timetable). In order to do this I am first trying to create a datetime from the imported data which comes in two columns in the format 'dd-MM-yyyy' in the first column and 'HH-mm' in the second column. Despite many trials I consistently get an error and I was wondering if someone could help me with this?

I tried create a new variable time with the {} syntax but I get an error 'Undefined operator '+' for input arguments of type 'cell'.'

If I transform the table to an array then use strjoin then it creates a variable with the dates but fails to import the time.

I have also tried with a for loop

for i=1:length(datav2)
    datav3{i,1}=datav2{i,1:2};
end

but here my datav3 then only shows the date and does not import the second time characters...

Essentially I would like one table with the first column 'dd-MM-yyyy hh:mm:ss' in datetime format (that i can then convert to timetable) and then a second column with data from Var31.

Any idea?

Thank you in advance for the help!

[Mathieu NOE]

hello Valentine

maybe you should share your data / code ...

tx

[Valentine Godin]

Hi,

thank you, here I have just uploaded it, it comes from a csv (very ugly) which I imported with readtable in the format '%s'.

[Mahmooduz Zafar]

Hello Valentine,

May be you can use below lines for your code.

for i=1:length(datav2)
    datav3{i,1}=datetime([datav2{i,1} ' ' datav2{i,2}],'InputFormat','dd-MM-yy HH:mm');
end

Regards,

M M Zafar

[Valentine Godin]

It worked! Brilliant, thank you very much for the help and trick!

[Mathieu NOE]

ok

so this is my first attempt to generate a first timetable from data1 - tried two options but the second one is cleraly slower.

load('matlab_data.mat');
tic

%% option 1 :  horizontal concatenation of cell arrays 

for k = 1:size(datav1,1)
    sep{k,1} = ' '; % creates the spacer separator between datav1.Var5 and datav1.Var25
    trail{k,1} = ':00'; % creates the "seconds" missing values 
end
time_array = cellfun(@horzcat, datav1.Var5 , sep, datav1.Var25, trail, 'UniformOutput', false)
% Elapsed time is 0.136016 seconds.

%% option 2 :  a good old for loop... if anything else works (?)
% time_array = cell(size(datav1,1),1);
% for k = 1:size(datav1,1)
%    time_array{k} = [num2str(datav1.Var5{k,1}),' ',num2str(datav1.Var25{k,1}), ':00'];
% end
% % Elapsed time is 2.771115 seconds. oops !!

toc

MeasurementTime = datetime(time_array);
data = (datav1.Var31); % what about the double quotes ? 
TT = timetable(MeasurementTime,data)

[Mathieu NOE]

@ Mahmooduz

nothing against your solution , but making lot of call to timetable in a for loop is time consuming

Elapsed time is 5.448764 seconds.

consider doing the hard work (cell array manipulation) first and then pass it then to timetable

my 2 cents

[Valentine Godin]

Hi,

Yes, I am not sure that it was what you were referring to but I realised that the cell format was extremely inconvenient to work with in my case and so managed to transform my data into double... Here is the code that I used (I am not sure if it was the most straight forward method but it did work and was the best method I found to deal with that double quoting...)

raw2= readtable('csv_weatherstationhourlydataannu2020.csv', 'Format','%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q%q');

%create table with relevant values 
datav1= [ raw2(:,5) raw2(:,25) raw2(:,26) raw2(:,27) raw2(:,28) raw2(:,29) raw2(:,30) raw2(:,31)];

%transform cell array of characters to cell
datav1.Var31= cellfun(@str2num, datav1.Var31, 'UniformOutput', false);
datav1.Var30= cellfun(@str2num, datav1.Var30, 'UniformOutput', false);
datav1.Var29= cellfun(@str2num, datav1.Var29, 'UniformOutput', false);
datav1.Var28= cellfun(@str2num, datav1.Var28, 'UniformOutput', false);
datav1.Var27= cellfun(@str2num, datav1.Var27, 'UniformOutput', false);
datav1.Var26= cellfun(@str2num, datav1.Var26, 'UniformOutput', false);

% convert cell to numeric 
% datav1.Var5=char(datav1.Var5);
% datav1.Var25=char(datav1.Var25);
datav1.Var31=cell2mat(datav1.Var31);
datav1.Var30=cell2mat(datav1.Var30);
datav1.Var29=cell2mat(datav1.Var29);
datav1.Var28=cell2mat(datav1.Var28);
datav1.Var27=cell2mat(datav1.Var27);
datav1.Var26=cell2mat(datav1.Var26);

Thank you for your input !

Answer 1

[Steven Lord]

I would use synchronize for this task. Let's start with some table data.

v = (0:9).';
datePart = repmat("02-01-20", 10, 1);
timePart = v + ":00";
T = table(datePart, timePart, v.^2)

T = 10x3 table
     datePart     timePart    Var3
    __________    ________    ____

    "02-01-20"     "0:00"       0 
    "02-01-20"     "1:00"       1 
    "02-01-20"     "2:00"       4 
    "02-01-20"     "3:00"       9 
    "02-01-20"     "4:00"      16 
    "02-01-20"     "5:00"      25 
    "02-01-20"     "6:00"      36 
    "02-01-20"     "7:00"      49 
    "02-01-20"     "8:00"      64 
    "02-01-20"     "9:00"      81 

Convert the datePart and timePart variables in T into a datetime array and build a timetable using that array and the numeric data from T. You could use table2timetable to create TT1 but then you'd need to adjust the synchronize call to only interpolate on the numeric variables in TT1.

dateAndTime = datetime(T.datePart + " " + T.timePart, 'InputFormat', 'MM-dd-yy hh:mm');

TT1 = timetable(T.Var3, 'RowTimes', dateAndTime)

TT1 = 10x1 timetable
            Time            Var1
    ____________________    ____

    01-Feb-2020 00:00:00      0 
    01-Feb-2020 01:00:00      1 
    01-Feb-2020 02:00:00      4 
    01-Feb-2020 03:00:00      9 
    01-Feb-2020 04:00:00     16 
    01-Feb-2020 05:00:00     25 
    01-Feb-2020 06:00:00     36 
    01-Feb-2020 07:00:00     49 
    01-Feb-2020 08:00:00     64 
    01-Feb-2020 09:00:00     81 

Make a second timetable with only part of the data. In reality the RowTimes of the two timetable arrays probably wouldn't be so nicely aligned, but it's good enough for purposes of this example.

TT2 = timetable(dateAndTime(1:2:end), v(1:2:end).^3)

TT2 = 5x1 timetable
            Time            Var1
    ____________________    ____

    01-Feb-2020 00:00:00      0 
    01-Feb-2020 02:00:00      8 
    01-Feb-2020 04:00:00     64 
    01-Feb-2020 06:00:00    216 
    01-Feb-2020 08:00:00    512 

Now synchronize the two timetable arrays. The RowTimes of the result should be the union of the RowTimes of TT1 and TT2, and we should fill in gaps using linear interpolation.

TT3 = synchronize(TT1, TT2, 'union', 'linear')

TT3 = 10x2 timetable
            Time            Var1_TT1    Var1_TT2
    ____________________    ________    ________

    01-Feb-2020 00:00:00        0           0   
    01-Feb-2020 01:00:00        1           4   
    01-Feb-2020 02:00:00        4           8   
    01-Feb-2020 03:00:00        9          36   
    01-Feb-2020 04:00:00       16          64   
    01-Feb-2020 05:00:00       25         140   
    01-Feb-2020 06:00:00       36         216   
    01-Feb-2020 07:00:00       49         364   
    01-Feb-2020 08:00:00       64         512   
    01-Feb-2020 09:00:00       81         660   

The elements in odd numbered rows (1st, 3rd, etc.) of Var1_TT2 in TT3 come directly from TT2. The elements in even numbered rows (2nd, 4th, etc.) are the result of linearly interpolating those values from TT2.Var1.

[Valentine Godin]

Hi,

Thank you so much!!! This did work like a charm!! And I do think it does the job and handles the data much quicker than previous method (especially given I have +10 000 lines), the issue that I mainly faced at first to apply your method was that with my variable (Var1) imported from the csv came within double quotes..

In the end, I used readtable 'Format' , '%q' to import it and then used the cellfun(str2num) followed by cell2mat and it did the work, I ended up with a variable as a double (and get rid of that cell format).

It then made my life much easier and I was able to follow your method and successfully merged the 5 different datasets !

Thank you very much for the help!