Divide the data according to DateTime column

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)

Answer 1

[Siddharth Bhutiya]

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,:);

Links:

timetable

timerange

other useful functions

[Ali]

well that is great answer

[Ali]

@ Siddharth Bhutiya . Apart from great answer.I would be greatful if you recommend any solution for below items:

1).From the attach file (data.csv)i want to Store each house data in single column along the time series as shown in attached figure.

2.) Then all data should be stored in one table like this way:

% Form table
Col 1                 Col 2             Col 3         ...................... Col 26
Date and time         House A (data)      House b (data).................... House z data     

[Siddharth Bhutiya]

In the attached csv file I see a column named "ID" and then a bunch of columns named "E_XXXX_WH". I'm assuming that these "E_XXXX_WH" columns correspond to House A, House B data that you mentioned. But I dont see any date and time columns there. Where will the Date and Time come from ?

[Ali]

Yes,i generated aartifical dates in one column by doing below:

Now i want to group the unique ID (responents) in different columns as mentioned in previous comment.e,g House A(Id 8927) all data in one colun, House b (Id 6520) in column2 etc

clc
clear all

dn0=datenum(2012,4,1,0,00,0);  % start date: "exact" datenum
dn1=datenum(2014,3,31,23,40,0);  % ditto end
dn=[dn0:30/(24*60):dn1];         % naive 5-minute increments
dnD=datenum(2012,4,1,0,00+[0:30:(dn1-dn0)*24*60],0);  % roll over minuts internal%% 
 
 Final_time = datetime(dnD,'ConvertFrom','datenum','Format','dd-MMM-yyyy HH:mm');

[Siddharth Bhutiya]

In the CSV file, if the IDs are associated with the houses i.e. House A is ID 8927, House B is ID 6520 and so on, then what do all the columns "E_XXXX_WH" mean. I am not sure if I clearly understand what you are trying to do.

If you could create a small and simple example from your original file and also mention the exact output you would like to generate, then that would help me understand the problem a bit better and I might be able to suggest something.

[Ali]

"E_XXXX_WH" shows time period of one day (24hrs) with 30 mins interval.For instance, 00:00, 00:30 , 01:00 -------------23:30) It means there are 48 coloumns for "E_XXXX_WH". What i want to do is merge all the data of unique id in 1 column and save it into seperate excel file. For instance, merge all the data of ID 8927 into single column and save it into seperate excel file oven .mat format.

without merage: ID 8927 size= 48 x 730
after merging into single column=  1x 35050 (This is what i want to do)

[Siddharth Bhutiya]

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
        out = addvars(out,data(:));
        
        % Or something like below if you would like to use the id as variable name
        % out.(num2str(ids(i))) = data(:);
    end
end