Identify the maximum values within fixed time intervals of a time series data set

Dear all fellow community members,

I have a matrix of dimensions 20x74932, signifying 20 variables within a time series sampled at 3000 Hz. Additionally, there is a corresponding time matrix sized 20x74932. The time matrix varies in accordance with the variables. In other words, only events are logged in the variables. For instance, for the first variable, a peak load occurs after 800 seconds, followed by the next peak load at 1550 seconds, then at 18000 seconds, and so forth.

For the second variable, the first peak load appears after 300 seconds, with the subsequent one at 1200 seconds, and so on.

My objective is to ascertain the highest peak load for each 20-minute interval throughout the entire time series. For example, if there are 2 peak loads within the first 0 to 20 minutes, I wish to retain only the highest. Subsequently, I want to keep the highest peak load from 20 minutes to 40 minutes and continue in this manner until the end of the time series.

Below is a hypothetical example for your reference:

Time=[4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 0 0 ; 1 2 3 4 5 6 7 8 9 10 11 12 0 0 0 0 0 0 0 0]; %Time vector, in minutes

x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0 0 0 0 0 15 0 1 -2 0 8 0 3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0]; %Variables of peak loads

As mentioned, my aim is to extract the highest peak load per 20-minute interval, which means I am seeking the following results;

0 min - 20min – 11 18 25

20min - 40min – 8 0 0

40min - 60min – 14 0 0

Any help is very appreciated! Thanks.

The example I provided was not 100% representative for my problem. I should have specified the time vector as a matrix, in order to demonstrate a varying time vector for each variable. I have therefore changed the time. This will also influence the results, which are also updated.

I really appreciate all your time and efforts, and apologize for providing an incorrect example to my actual problem.

Answer 1

[Star Strider]

Try this —

Time=[4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60]; %Time vector, in minutes
x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0  0 0 0 0 15 0 1 -2 0 8 0  3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0];

interval = ceil(Time/20)

interval = 1×20

1 1 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3 3 3 3

Rcell = accumarray(interval(:), (1:numel(interval)).', [], @(v){max(x(:,v).')});

Column_ = cell2mat(Rcell)

Column_ = 3×3

11 0 8 8 15 25 14 18 0

intv = 0:numel(Rcell);
rn = compose('% 2d min - %2d min —  ',[intv(1:end-1); intv(2:end)].'*20);
Results = array2table(Column_, 'RowNames',rn)

Results = 3×3 table
                         Column_1    Column_2    Column_3
                         ________    ________    ________

    0 min - 20 min —        11           0           8   
    20 min - 40 min —        8          15          25   
    40 min - 60 min —       14          18           0   

EDIT — (26 Jan 2024 at 15:20)

Added ‘Results’ table.

.

[Askeladden2]

Thank you for your response. I gave an incorrect example to my actual problem. I have updated the example in the original text, if you are still interested in helping me.

Thank you in advance.

[Star Strider]

The added zeros in ‘Time’ make that impossible to work with in my code. For my code to work, ‘Time’ must be monotonicallly increasing. I simply declared the zero values as NaN here.

A ‘Time’ matrix with three rows instead of one requires a loop in my code to accommodate each separate row.

However the time values you posted do not work to create the results table you want. For it to work like that, you would have to change the ‘Time’ matrix as I did in the second example here, to shift the third row to have appropriate times. My code then works as desired, although it required some additional minor tweaks to update it to work with the ‘shifted’ ‘Time’ matrix.

————— Part One —————

With the posted ‘Time’ matrix —

Time =[4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60; 2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  0  0;  1  2  3  4  5  6  7  8  9  10  11  12  0  0  0  0  0  0  0  0];x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0  0 0 0 0 15 0 1 -2 0 8 0  3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0];
x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0  0 0 0 0 15 0 1 -2 0 8 0  3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0];

Time(Time == 0) = NaN;                                  % Assume First 'Time' Entry Is Never Zero

interval = ceil(Time/20);

for k = 1:size(Time,1)
    L = nnz(~isnan(Time(k,:)));
    Rcell{k} = accumarray(interval(k,1:L).', (1:numel(interval(k,1:L))).', [], @(v){max(x(k,v).')});
    Rr = numel(Rcell{k});
    R(1:Rr,k) = cell2mat(Rcell{k});
end

Column_ = R;

intv = 0:numel(Rcell);
rn = compose('% 2d min - %2d min —  ',[intv(1:end-1); intv(2:end)].'*20);
Results = array2table(Column_, 'RowNames',rn)

Results = 3×3 table
                         Column_1    Column_2    Column_3
                         ________    ________    ________

    0 min - 20 min —        11          18          25   
    20 min - 40 min —        8           0           0   
    40 min - 60 min —       14           0           0   

————— Part Two —————

With the ‘shifted’ ‘Time’ matrix —

Time =[4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60; 2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  0  0;  1  2  3  4  5  6  7  8  9  10  11  12  0  0  0  0  0  0  0  0];x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0  0 0 0 0 15 0 1 -2 0 8 0  3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0];
x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0  0 0 0 0 15 0 1 -2 0 8 0  3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0];

Time(Time == 0) = NaN;                                  % Assume First 'Time' Entry Is Never Zero
Time(3,:) = Time(3,:) + 39                              % 'Shifted' 'Time' MAtrix

Time = 3×20

4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 NaN NaN 40 41 42 43 44 45 46 47 48 49 50 51 NaN NaN NaN NaN NaN NaN NaN NaN

interval = ceil(Time/20);

for k = 1:size(Time,1)
    L = nnz(~isnan(Time(k,:)));
    Rcell{k} = accumarray(interval(k,1:L).', (1:numel(interval(k,1:L))).', [], @(v){max(x(k,v).')});
    idx = cellfun(@isempty,Rcell{k});                   % Fill Any 'empty' Elements With Zero
    Rcell{k}(idx) = {0};                                % Fill Any 'empty' Elements With Zero
    Rr = numel(Rcell{k});                               % Effective Row Length Of 'Rcell'
    R(1:Rr,k) = cell2mat(Rcell{k});                     % Add Values To Result Matrix 'R'
end

Column_ = R;

intv = 0:numel(Rcell);                                  % 'Intervals' Vector
rn = compose('% 2d min - %2d min —  ',[intv(1:end-1); intv(2:end)].'*20);
Results = array2table(Column_, 'RowNames',rn)

Results = 3×3 table
                         Column_1    Column_2    Column_3
                         ________    ________    ________

    0 min - 20 min —        11          18           0   
    20 min - 40 min —        8           0           0   
    40 min - 60 min —       14           0          25   

The approach I use with the ‘Time’ vector changed to a ‘Time’ matrix is essentially the same as my original code. It required an added loop to accommodate the ‘Time’ matrix rows, and some other tweaks to accommodate the ‘Time’ matrix with a shifted row.

And with those tweaks, it works.

.

[Askeladden2]

Works like a charm! Thank you very much.

[Star Strider]

As always, my pleasure!

[Askeladden2]

Hi again,

In one time series, the code is working flawlessly, but in another, I am confronted with the following error message:

"Unable to perform assignment because the indices on the left side are not compatible with the size of the right side."

I must confess that I am somewhat perplexed by this issue, particularly given the challenge of identifying the problem within such extensive time series data.

Enclosed, you will find a MATLAB file that includes the variables where the code does not work. Would you be able to review it and provide insights into why I am receiving this error message?

Thank you very much in advance for your time and assistance.

[Star Strider]

I actually did not get that message when I ran my code with the ‘test.mat’ file. I did get an error with respect to the number of row names. I fixed that with:

intv = 0:size(R,1); % 'Intervals' Vector

So now it calculates the rows on the basis of the actual size of ‘R’ (as it should) rather than the counter that created it. With that change, it appears to work correctly with this file. (I also tested it with the previous ‘Time’ and ‘x’ values. It works with them as well.) I no longer have access to R2020a, so if it continues to throw errors, we will have to solve this iteratively. (I also slightly tweaked the ‘rn’ format string to accommodate larger values, although it does not appear to make any difference.)

Try this —

load('test.mat')

% Time
% x

% % Time =[4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60; 2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  0  0;  1  2  3  4  5  6  7  8  9  10  11  12  0  0  0  0  0  0  0  0];x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0  0 0 0 0 15 0 1 -2 0 8 0  3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0];
% % x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0  0 0 0 0 15 0 1 -2 0 8 0  3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0];

Time(Time == 0) = NaN;                                  % Assume First 'Time' Entry Is Never Zero
% % Time(3,:) = Time(3,:) + 39                              % 'Shifted' 'Time' Matrix

interval = ceil(Time/20);

for k = 1:size(Time,1)
    L = nnz(~isnan(Time(k,:)));
    Rcell{k} = accumarray(interval(k,1:L).', (1:numel(interval(k,1:L))).', [], @(v){max(x(k,v).')});
    idx = cellfun(@isempty,Rcell{k});                   % Fill Any 'empty' Elements With Zero
    Rcell{k}(idx) = {0};                                % Fill Any 'empty' Elements With Zero
    Rr = numel(Rcell{k});                               % Effective Row Length Of 'Rcell'
    R(1:Rr,k) = cell2mat(Rcell{k});                     % Add Values To Result Matrix 'R'
end

Column_ = R;

intv = 0:size(R,1);                                     % 'Intervals' Vector
rn = compose('% 4d min - % 4d min —  ',[intv(1:end-1); intv(2:end)].'*20);
Results = array2table(Column_, 'RowNames',rn)

Results = 9×8 table
                            Column_1    Column_2    Column_3    Column_4    Column_5    Column_6    Column_7    Column_8
                            ________    ________    ________    ________    ________    ________    ________    ________

    0 min -   20 min —            0           0           0           0           0           0           0           0 
    20 min -   40 min —      375.09           0           0           0           0      341.58      120.19           0 
    40 min -   60 min —           0           0      2285.5      2662.2           0           0      111.39      254.36 
    60 min -   80 min —      103.52           0           0           0           0           0           0           0 
    80 min -  100 min —      98.255           0      197.67      766.97           0           0           0           0 
    100 min -  120 min —          0           0           0           0       673.3           0           0       86.76 
    120 min -  140 min —          0           0           0           0           0           0      356.22           0 
    140 min -  160 min —          0      478.64           0           0           0           0           0           0 
    160 min -  180 min —          0           0      99.106           0      418.76           0           0           0 

.

[Askeladden2]

Hi again,

The new code is working perfectly. Filling the empty elements with zeros in 'Rcell' obviously did the trick using R2020a.

Thank you very much! I greatly appreciate your help.

[Star Strider]

As always, my pleasure!

That was the update required to use the offset vector in ‘Part Two’ earlier, where I added it originally.

My ‘Part Two’ code is much more robust that my original ‘Part One’ code.

Answer 2

[Catalytic]

Time=[4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60]; %Time vector, in minutes
x=[ 0 5 0 1 3 11 2 0 0 1 8 3 0 0 -5 12 2 -2 14 13; 0 0 0  0 0 0 0 15 0 1 -2 0 8 0  3 2 0 18 0 0; 0 8 -1 0 0 0 0 0 0 0 0 25 0 0 0 0 0 0 0 0]; %Variables of peak loads

T=timetable(minutes(Time(:)), x');

% Retime timetable
newTimetable = retime(T,"regular","max","TimeStep",minutes(20),...
                        'IncludedEdge','right')

newTimetable = 3×1 timetable
     Time          Var1     
    ______    ______________

    20 min    11     0     8
    40 min     8    15    25
    60 min    14    18     0

[Askeladden2]

Thank you very much for your response on an ingenious way to solve the problem. Must I have a constant time vector in this solution proposal of yours?

I was a bit hasty in my example, as I should have created a varying time vector for each variable. To simplify things, I am changing the time to the following matrix:

Time =[4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 0 0; 1 2 3 4 5 6 7 8 9 10 11 12 0 0 0 0 0 0 0 0];

NB! The zeros in rows 2 and 3 are added at the end to maintain equal size of the matrix.

This also influence the results;

0 min - 20min – 11 18 25

20min - 40min – 8 0 0

40min - 60min – 14 0 0

Thank you again for your time and effort!

Answer 3

[Morgan]

% INTERVAL TO SAMPLE
Interval = 20;

% TIME ARRAY
Time = [ 4 5 6 7 8 9 10 25 26 27 28 29 46 47 48 49 50 58 59 60 ];

% CALCULATE NUMBER OF INTERVALS
numIntervals = 1 + floor((Time(end) - Time(1))/Interval);

% CALCULATE INDICES FOR TIME ARRAY INTERVALS
startIdx = zeros(numIntervals,1);
endIdx   = zeros(numIntervals,1);
for n = 1 : numIntervals
    startIdx(n) = find(Time >= (n-1)*Interval, 1, 'first');
    endIdx(n)   = find(Time <      n*Interval, 1, 'last');
end

% VARIABLES OF PEAK LOADS
x = [ 0 5 0  1 3 11 2 0  0 1 8  3  0 0 -5 12 2 -2 14 13 ; ...
      0 0 0  0 0 0  0 15 0 1 -2 0  8 0  3 2  0 18 0  0  ; ...
      0 8 -1 0 0 0  0 0  0 0 0  25 0 0  0 0  0 0  0  0  ];

% CALCULATE NUMBER OF VARIABLES
nvars = size(x,1);

% CALCULATE ARRAY A MAXIMUM PEAK LOAD VALUES
% AT EACH INTERVAL
maxValues = zeros(nvars,numIntervals);
for n = 1 : numIntervals
    maxValues(:,n) = max(x(:,startIdx(n):endIdx(n)),[],2);
end

disp(maxValues)

    11     8    14
     0    15    18
     8    25     0

[Askeladden2]

Thank you for your response. I gave an incorrect example to my actual problem. I have updated the example in the original text, if you are still interested in helping me.

Thank you in advance.