# Divide the data according to DateTime column

16 views (last 30 days)
Ali on 30 Sep 2019
Commented: Siddharth Bhutiya on 28 Oct 2019
I have table having dates and numeric data (file attached).
I want to divide the data according to date/time.For instance:
Training data= 75%
Test data=25 %
My data is in below form: I tried below code:
% Create training set
trainInd = data2.Time < datenum('2013-12-31');
trainX = HomeA(trainInd,:);
trainY = data2.HomeA(trainInd);
% Create test set and save for later
testInd = data2.Time >= datenum('2013-12-31');
testX = Home(testInd,:);
testY = data2.HomeA(testInd);
testDates = dates(testInd)

Siddharth Bhutiya on 1 Oct 2019
Your will get an error with the above code, because your data contains a datetime and you are trying to compare it with a datenum which is essentially a double. You should always try to use datetimes when working with date and time data.
ind = data2.Time < datetime('2013-12-31');
% This would give you the HomeA values matching the above criteria
subHomeA = data2.HomeA(ind);
%This would give you the sub-table matching the above criteria
subData2 = data2(ind,:);
Since you are working with time-stamped data, I would suggest using timetable instead of a table as that would give you additional features specific to time related data. For example, if you want to get data between 30th May 2013 and 31 Dec 2013, then you can easily do that using timerange function
data = table2timetable(data2); % convert your table to a timetable
% This would give you a timerange which you can then use to subscript into your timetable
idx = timerange('2013-05-30','2013-12-31');
subData = data(idx,:);
Siddharth Bhutiya on 28 Oct 2019
If you wanted to group the data and apply some function that would reduce it to just one entry in the table (for example to calculate some statistics like sum, mean, etc) then that could be easily done using functions like splitapply or rowfun.
But in your case you want to transform the MxN data to 1xMN, so most likely you would have to do it using loops. One way that I could think of was as follows. You could try doing something similar for your case
>> t
t =
12×3 table
id d1 d2
____ __ __
1001 10 8
1001 4 10
1001 5 11
1002 2 6
1002 9 9
1002 7 4
1003 3 5
1003 11 1
1003 6 7
1004 12 3
1004 1 2
1004 8 12
>> rowtimes % This would be the artificial datetimes that you created
rowtimes =
6×1 datetime array
29-Oct-2019 11:07:39
30-Oct-2019 11:07:39
31-Oct-2019 11:07:39
01-Nov-2019 11:07:39
02-Nov-2019 11:07:39
03-Nov-2019 11:07:39
transform_data(t,rowtimes)
function out = transform_data(in,rowtimes)
% Create a empty timetable with desired row times
out = timetable(rowtimes);
ids = unique(in.id);
for i = 1:length(ids)
% For each house id get the MxN data
data = in{in.id == ids(i),2:end};
% Convert it into 1xMN using data(:) and add it to the new timetable
% Or something like below if you would like to use the id as variable name
% out.(num2str(ids(i))) = data(:);
end
end