Separate time series data in Individual Months

Hi ALL,

I have a large time series of data sampled every 5 minutes starting '01-Oct-2021 00:00:00' and ending '28-Feb-2023 23:54:59'. There are two columns one is datetime(dd:MM:yyyy:HH:mm:ss) and the other is a variable called Volts A-N. It goes as follows

Time Volts A-N

'01-Oct-2021 04:59:59' 120

'01-Oct-2021 05:04:59' 130

'01-Oct-2021 05:09:59' 123

'01-Oct-2021 05:14:59' 145

'01-Oct-2021 05:19:59' 123

'01-Oct-2021 05:24:59' 133

'01-Oct-2021 05:29:59' 132

'01-Oct-2021 05:34:59' 134

'01-Oct-2021 05:39:59' 134

'01-Oct-2021 05:44:59' 132

'01-Oct-2021 05:49:59' 123

etc until

'28-Feb-2023 23:54:59' 157

Is there an easy way to split the data into individual months by finding the index of the data value corresponding to the start and end of each month? I want to be able to calculate the mean and standard deviation of the data in each individual month. Obviously if the months were all the same lengths I could simply use reshape() function but they are not. Any help would be appreciated.

Thanks

[Ben Whitby]

Thank you, This works.

[Star Strider]

As always, my pleasure!

Answer 1

[Star Strider]

It’s not necessary to determine the beginning and dne of each month to separate them.

This approach uses the ymd function with findgroups to separate the dates by month and year, and then uses accumarray to do the ‘heavy lifting’ to separate the resulting table by month and year.

Try something like this —

Start = datetime('01-Oct-2021 00:00:00');
End = datetime('28-Feb-2023 23:54:59');
DTV = (Start : minutes(5) : End).';                                                         % Date Time Vector
DateVolts = table(DTV, randi([120 160],size(DTV)), 'VariableNames',{'Time','Volts A-H'})    % Create Data Table

DateVolts = 148607×2 table
            Time            Volts A-H
    ____________________    _________

    01-Oct-2021 00:00:00       142   
    01-Oct-2021 00:05:00       160   
    01-Oct-2021 00:10:00       140   
    01-Oct-2021 00:15:00       152   
    01-Oct-2021 00:20:00       139   
    01-Oct-2021 00:25:00       155   
    01-Oct-2021 00:30:00       155   
    01-Oct-2021 00:35:00       122   
    01-Oct-2021 00:40:00       158   
    01-Oct-2021 00:45:00       120   
    01-Oct-2021 00:50:00       143   
    01-Oct-2021 00:55:00       120   
    01-Oct-2021 01:00:00       150   
    01-Oct-2021 01:05:00       148   
    01-Oct-2021 01:10:00       127   
    01-Oct-2021 01:15:00       132   

[y,m,d] = ymd(DateVolts.Time);                                                              % Return Year, Month, & Day
[G,IDm,IDy] = findgroups(y,m);                                                              % Create Group Reference
MonthsByYear = accumarray(G,(1:size(DateVolts,1)).', [], @(x){DateVolts(x,:)})              % Segment By Month & Year

MonthsByYear = 17×1 cell array
    {8928×2 table}
    {8640×2 table}
    {8928×2 table}
    {8928×2 table}
    {8064×2 table}
    {8928×2 table}
    {8640×2 table}
    {8928×2 table}
    {8640×2 table}
    {8928×2 table}
    {8928×2 table}
    {8640×2 table}
    {8928×2 table}
    {8640×2 table}
    {8928×2 table}
    {8928×2 table}

MonthsByYear{1}([1 end],:)                                                                  % First Month In Series

ans = 2×2 table
            Time            Volts A-H
    ____________________    _________

    01-Oct-2021 00:00:00       142   
    31-Oct-2021 23:55:00       156   

MonthsByYear{17}([1 end],:)                                                                 % Last Month In Series

ans = 2×2 table
            Time            Volts A-H
    ____________________    _________

    01-Feb-2023 00:00:00       146   
    28-Feb-2023 23:50:00       120   

There are other functions as well that can do this. I prefer accumarray because I’m used to it.

.