Divide table in days and then in hours.

Question

Erica Corradi 2018-6-16

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/405946-divide-table-in-days-and-then-in-hours

评论： Stephan 2018-6-26

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Stephan 2018-6-22

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/405946-divide-table-in-days-and-then-in-hours#answer_325881

在 MATLAB Online 中打开

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

6 个评论
显示 4更早的评论隐藏 4更早的评论

Stephan 2018-6-25

编辑：Stephan 2018-6-26

在 MATLAB Online 中打开

tab.mat

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 2018-6-26

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 2018-6-26

Nice to hear... have fun

请先登录，再进行评论。

Answer 2

Razvan Carbunescu 2018-6-26

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/405946-divide-table-in-days-and-then-in-hours#answer_326383

在 MATLAB Online 中打开

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