divide year time data into days and nights

Hello,

I have a table of time data for entire year and I would like to divide them into days and nights. The table rows look like that (Time_begin Time_end other_variables), example of one row: (14-Mar-2017 12:10 14-Mar-2017 12:20 var1 var2 var3 ... ). I have a table of sunrises and sunsets (day_no sunrise sunset, Example: 1 01-Jan-2017 08:01:00 01-Jan-2017 16:10:00) for the entire year and I would like to use it for dividing the data table into tables with rows with day data and night data. The sunrise and sunset time change for every day. How can I do this? Thanks for you ideas.

[Harry Laing]

I think you need to be a bit clearer on what you mean by day and night data and how it's classified, especially in the case of posible crossover start and end times. Examples:

• If the start time is before sunrise and endtime is after sunset, does that count as day or night or both?
• If start time is after sunrise but the end time is after sunset, should that row be day or night?

Or are you wanting to classify as day or night based on whether the timespan of the data row is more in the day or night period?

Hope that makes sense.

[Petr Michalek]

Lets say, that we use only the start time for the decision whether it is day or night. The timespan between start time and end time is always 10 minutes, so the difference is in order of minutes and that is small enough for me to cope with.

Answer 1

[Eric Sofen]

Steve beat me to it, but here's another solution that uses the datetime isbetween function.

SunriseSunset = table((1:365)',datetime(2022,1,1:365,6,30,0)', datetime(2022,1,1:365,18,30,0)', VariableNames=["DoY", "Sunrise", "Sunset"]); % let's suppose sunrise and sunset don't change throughout the year
data = timetable(datetime(2022,1,1,0,0:90:10000,0)', rand(112,1), VariableNames="Data");

isday = isbetween(data.Time,SunriseSunset.Sunrise',SunriseSunset.Sunset');
% This takes advantage of implicit expansion and results in a big logical array
% of which particular day period the data's row time corresponds to. Just
% aggregate over rows to get a vector of which times are occuring during
% daytime.
data.isday = sum(isday,2)

data = 112×2 timetable
            Time              Data       isday
    ____________________    _________    _____

    01-Jan-2022 00:00:00      0.48027      0  
    01-Jan-2022 01:30:00      0.43233      0  
    01-Jan-2022 03:00:00     0.086928      0  
    01-Jan-2022 04:30:00      0.37172      0  
    01-Jan-2022 06:00:00      0.55905      0  
    01-Jan-2022 07:30:00      0.94576      1  
    01-Jan-2022 09:00:00      0.39536      1  
    01-Jan-2022 10:30:00      0.54901      1  
    01-Jan-2022 12:00:00      0.83405      1  
    01-Jan-2022 13:30:00      0.22782      1  
    01-Jan-2022 15:00:00      0.25985      1  
    01-Jan-2022 16:30:00    0.0092988      1  
    01-Jan-2022 18:00:00     0.058855      1  
    01-Jan-2022 19:30:00      0.16832      0  
    01-Jan-2022 21:00:00       0.4819      0  
    01-Jan-2022 22:30:00      0.43895      0  

[Petr Michalek]

Unfortunately this doesnt work for me. The original data table, which I would like to divide into two separate tables for day data and night data, is 52556x30 timetable (time_begin time_end var1 var2 var3 etc. ...). The sunrise and sunset table is 365x3 table (day_no sunrise sunset).

Matlab generated error: All inputs must have compatible sizes.

[Petr Michalek]

Update: Now it works, I forgot the apostrophes in the isbetween function parametres, but I dont get why they have to be there.

[Petr Michalek]

I used this code to divide table20 into table20day and table20night:

for i=1:height(table20)
    if table20.isday20(i) == 0
    table20night(i,:)=table20(i,:);
else
    table20day(i,:)=table20(i,:);
    end
end 

I get two tables sorted correctly into rows with day/night data, but there are a lot of empty rows beginning with NaT and the other values in the row are zero. In the other table for night data it is the same, but the rows with day data are NaT 0 0 0 ... How can I get rid of these rows? Thanks.

[Eric Sofen]

Presumably those NaTs are in your original data. Either missing timestamps or timestamps in a different format. Datetime can only parse one format at a time.

If you understand where the NaTs come from and just want to get rid of them, take a look at rmmissing.

Answer 2

[Steven Lord]

I'm going to use the sunrise and sunset times for Natick, MA for this example. I'll create some random datetime values for the next three days:

fmt = 'MMM dd, h:mm a';
threeDaysInMinutes = minutes(days(3));
data = datetime('today', 'Format', fmt) + minutes(randi([0 threeDaysInMinutes], 10, 1))

data = 10×1 datetime array
   Sep 22, 4:01 AM 
   Sep 24, 12:34 PM
   Sep 24, 10:25 AM
   Sep 22, 6:14 PM 
   Sep 24, 6:21 AM 
   Sep 23, 12:04 AM
   Sep 23, 4:29 AM 
   Sep 24, 7:07 PM 
   Sep 22, 1:15 AM 
   Sep 24, 6:31 AM 

Now I'll create the vector of sunset and sunrise values. Elements 1, 3, 5, etc. are sunset values and elements 2, 4, 6, etc. are sunrise values.

sunriseSunset = ["Sep 21, 2022 6:44 pm"; ...
    "Sep 22, 2022 6:32 am"; "Sep 22, 2022 6:42 pm"; ...
    "Sep 23, 2022 6:33 am"; "Sep 23, 2022 6:40 pm"; ...
    "Sep 24, 2022 6:34 am"; "Sep 24, 2022 6:39 pm"; ...
    "Sep 25, 2022 6:36 am"; "Sep 25, 2022 6:37 pm"; ...
    "Sep 26, 2022 6:37 am"];
bins = datetime(sunriseSunset, 'Format', fmt)

bins = 10×1 datetime array
   Sep 21, 6:44 PM
   Sep 22, 6:32 AM
   Sep 22, 6:42 PM
   Sep 23, 6:33 AM
   Sep 23, 6:40 PM
   Sep 24, 6:34 AM
   Sep 24, 6:39 PM
   Sep 25, 6:36 AM
   Sep 25, 6:37 PM
   Sep 26, 6:37 AM

By the way I constructed the sunriseSunset array, anything falling in odd numbered bins (bin 1 being between elements 1 and 2 of sunriseSunset, for example) is considered night and anything falling in even numbered bins is considered day.

whichBin = discretize(data, bins)

whichBin = 10×1

1 6 6 2 5 3 3 7 1 5

Let's look at the results in words rather than odd or even bin numbers. In each row, the value in the Dates variable falls after the value in the Begin variable and before the value in the End variable.

categories = categorical(["Night"; repmat(["Day"; "Night"], 4, 1)]); % Alternate Night and Day
result = table(data, bins(whichBin), bins(whichBin+1), categories(whichBin), ...
    'VariableNames', ["Dates", "Begin", "End", "Day or Night"])

result = 10×4 table
         Dates               Begin               End          Day or Night
    ________________    _______________    _______________    ____________

     Sep 22, 4:01 AM    Sep 21, 6:44 PM    Sep 22, 6:32 AM       Night    
    Sep 24, 12:34 PM    Sep 24, 6:34 AM    Sep 24, 6:39 PM       Day      
    Sep 24, 10:25 AM    Sep 24, 6:34 AM    Sep 24, 6:39 PM       Day      
     Sep 22, 6:14 PM    Sep 22, 6:32 AM    Sep 22, 6:42 PM       Day      
     Sep 24, 6:21 AM    Sep 23, 6:40 PM    Sep 24, 6:34 AM       Night    
    Sep 23, 12:04 AM    Sep 22, 6:42 PM    Sep 23, 6:33 AM       Night    
     Sep 23, 4:29 AM    Sep 22, 6:42 PM    Sep 23, 6:33 AM       Night    
     Sep 24, 7:07 PM    Sep 24, 6:39 PM    Sep 25, 6:36 AM       Night    
     Sep 22, 1:15 AM    Sep 21, 6:44 PM    Sep 22, 6:32 AM       Night    
     Sep 24, 6:31 AM    Sep 23, 6:40 PM    Sep 24, 6:34 AM       Night