Create datetime from two columns of a table
Show older comments
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!
7 Comments
Mathieu NOE
on 13 Jan 2021
hello Valentine
maybe you should share your data / code ...
tx
Valentine Godin
on 13 Jan 2021
Mahmooduz Zafar
on 13 Jan 2021
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
on 13 Jan 2021
Mathieu NOE
on 13 Jan 2021
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
on 13 Jan 2021
@ 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
on 13 Jan 2021
Answers (1)
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)
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)
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)
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')
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.
1 Comment
Valentine Godin
on 13 Jan 2021
Categories
Find more on Tables in Help Center and File Exchange
on 13 Jan 2021
on 13 Jan 2021
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
