Divide table in days and then in hours.

Hello everyone! I'm not very practical with matlab, so I hope that someone can help me. I have a big table [351496 x 5] that it contains 6 months of monitoring. Below the first five rows of my table.

The first column is the categorical variable (they are the name of my PIR sensors) the last column (ColumnDateTime) is the date time array. I want to divide my table for days, so as to create daily subtables. then, every daily subtable I would divide it by hours, creating 4 additional tables (which correspond to the times of the day: morning, afternoon, evening, night). Then I would like to analyze if there are 2 PIR sensors that detect movements in two different rooms (the different rooms are recognized in the first column, through the names of the sensors) in a time <= to 2 seconds. Thanks in advance.

Answer 1

[Stephan]

Hi,

if you use a timetable for storing your data the function that might help you is

retime

With this function you can aggregate your data among other things. There are also examples given on the retime documentation site.

Best regards

Stephan

[Erica Corradi]

thanks for your help. I used timetable, but I could not aggregate the rows through the retime function. For example, if I want to divide my timetable in night and day, for the night I want to consider the interval between 21 and 07, while for the day I want to consider the interval between 07:01 and 20:59. In this way I want to create 2 timetable for the 6 months of monitoring. How can I do?

Best regards

[Stephan]

Hi Erica,

You could also try the timerange function:

S = timerange(startTime,endTime)

which allows you to build new timetables which contain only data from the specified range of time. Maybe this will help you to solve your problem.

If that doesnt work for your purposes, please attach a part of your data to work with. It is easier to see what goes wrong, if the data is accessible.

Best regards

Stephan

[Erica Corradi]

I wrote the code in this way:

for row = 1:size(ita9TT)
  S1 = timerange('21:00:00','07:00:00');
      ita9_night = ita9TT(S1,:);
  S2 = timerange('07:00:00','21:00:00');
      ita9_day = ita9TT(S2,:);
end

Is it right for my purpose? Because my data start on 12-Sep-2017 14:58:33 and end on 13-Mar-2018 12:20:36, so how can I divide my table in night and day? My for loop certainly is wrong because I did not used the word 'row' inside the loop... Sorry but I can't attach the file because is very big (also with the zip).

[Stephan]

Hi,

i could not find a solution for your problem using the timerange command (which does not necessarily mean that there is none) - but i found another approach which should work for you:

% get table without night / day
load('tab.mat');

% Extract datetime values from your table
t = tab.t;

% extract hours, minutes and seconds from the datetime 
hours = hour(t);
mins = minute(t);
secs = second(t);

% Preallocate category vector
category_day_night = (categorical({'Day', 'Night'}))';

% Assign categories to the rows depending from hours, mins & secs
for k = 1:length(hours)
    if hours(k) >= 6 && hours(k) < 18
        category_day_night(k) = 'Day'; % 1 means DAY
    else
        category_day_night(k) = 'Night'; % 2 means NIGHT
    end
end

% build a timetable from those above
tab_new_all = timetable(t,tab.values,category_day_night);

% Assign names to the vars
tab_new_all.Properties.VariableNames = {'Value', 'Category'};

% Table with the day-times
tab_day = tab_new_all(category_day_night == 'Day',:);

% Table with the night-times
tab_night = tab_new_all(category_day_night == 'Night',:);

At first i import the tab.mat file (attached to this comment) and extract the the datetime vector t. From this i get the values for hours , minutes and seconds and use them as conditions in an if/else loop to generate a vector for the difference between night and day. In fact to show the approach i only use hours, but you can extend/adapt this for your purpose as needed.

Once i got this i put the original data together with the generated category variable in a new table and then generate 2 seperated tables for the category wanted.

Im pretty sure there are more efficient and more elegant ways to to this, but this one seems to work.

Maybe someone who has a better approach reads this, gives a helpful hint and we both learn something more.

Please let me know if that worked for you - it is an interesting problem for me.

Best regards

Stephan

[Erica Corradi]

O my God!!!!! Thank you very much!!!! You solved my problem! The code works as I wanted!!Thanks thanks thanks. P.S.: sorry if I answer now, but only now I tried your code.

[Stephan]

Nice to hear... have fun

Answer 2

[Razvan Carbunescu]

Depending on the final goal here could use some of the functionality within groupsummary (If using R2018a) and findgroups / splitapply models to help achieve some of the final modeling.

>> groupsummary(tab,'t',hours(6),'mean','values')

ans =
12×3 table

                       disc_t                       GroupCount    mean_values
    ____________________________________________    __________    ___________

    [01-Jun-2018 00:00:00, 01-Jun-2018 06:00:00)        6           58.833   
    [01-Jun-2018 06:00:00, 01-Jun-2018 12:00:00)        6           61.167   
    [01-Jun-2018 12:00:00, 01-Jun-2018 18:00:00)        6           41.167   
    [01-Jun-2018 18:00:00, 02-Jun-2018 00:00:00)        6             56.5   
    [02-Jun-2018 00:00:00, 02-Jun-2018 06:00:00)        6           59.667   
    [02-Jun-2018 06:00:00, 02-Jun-2018 12:00:00)        6           42.167   
    [02-Jun-2018 12:00:00, 02-Jun-2018 18:00:00)        6           50.333   
    [02-Jun-2018 18:00:00, 03-Jun-2018 00:00:00)        6           30.667   
    [03-Jun-2018 00:00:00, 03-Jun-2018 06:00:00)        6               40   
    [03-Jun-2018 06:00:00, 03-Jun-2018 12:00:00)        6           43.833   
    [03-Jun-2018 12:00:00, 03-Jun-2018 18:00:00)        6           69.333   
    [03-Jun-2018 18:00:00, 04-Jun-2018 00:00:00]        6           62.167