MATLAB Answers

Create datetime from two columns of a table

33 views (last 30 days)
Valentine Godin
Valentine Godin on 13 Jan 2021
Commented: Valentine Godin on 13 Jan 2021
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

Show 4 older comments
Mathieu NOE
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
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
Valentine Godin on 13 Jan 2021
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 !

Sign in to comment.

Answers (1)

Steven Lord
Steven Lord on 13 Jan 2021
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.

  1 Comment

Valentine Godin
Valentine Godin on 13 Jan 2021
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!

Sign in to comment.

Products


Release

R2019b

Community Treasure Hunt

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

Start Hunting!