Break array or timetable into smaller versions and find max value

I have a 10000x2 array which can be converted into a timetable. What I want to do is break the timetable into each year and find the maximum value for each year in column 1 and repeat in column 2.

data = randi(100, 10000, 2); %create example data
%Turn it into a timetable
data_timetable = array2timetable(data, 'StartTime', datetime(1990, 10, 1), 'TimeStep', caldays(1));
%Now find max value for each year in each column

I thought of doing something like this

ystart = 1990;
max_vals = [];
for i = 1 :28       %1 to 28 because it goes to 2018
    for j = 1:length(data)
        if year(data_timetable.Time(j)) == ystart && data(j,1) > max_vals
            max_vals = data(j,1);
        elseif year(data_timetable.Time(j)) == (ystart+i) && data(j,1) > max_vals
            max_vals = [max_vals, data(j,1)];
        end
    end
end

but I know the logic behind this code is wrong and it also shows the following error

Any suggestions?

EDIT:

data = randi(100, 10000, 2);
data_timetable = array2timetable (data, 'StartTime', datetime(1990, 10,1), 'TimeStep',caldays(1));
y = unique(year(data_timetable.Time));
c = cell(length(y),1);
for i = 1:length(c)
    c{i} = data_timetable(year(data_timetable.Time) == y(i),:);
end

I managed to separate the data into a cell containing multiple timetables, but how do I get the maximum values in each timetable and the date when they occur?

Answer 1

[Voss]

Here's one way. This finds the first maximum value in each year (for each column of data), and stores the value and the date it occurred.

data = randi(100, 10000, 2);
data_timetable = array2timetable (data, 'StartTime', datetime(1990, 10,1), 'TimeStep',caldays(1));

yy = year(data_timetable.Time); % store the years to avoid having to call year() inside the loop
y = unique(yy);
Ny = numel(y);
c = cell(Ny,1);
Ncols = size(data_timetable,2);
max_vals = zeros(Ny,Ncols);     % initialize max_vals
max_dates = NaT(Ny,Ncols);      % initialize the dates when max_vals occur
for i = 1:Ny
    c{i} = data_timetable(yy == y(i),:);
    [max_vals(i,:),idx] = max(c{i}{:,:},[],1); % max() of contents of c{i}, along 1st dimension, outputting index as well as max value
    max_dates(i,:) = c{i}.Time(idx);           % date of maximum value is c{i}.Time(idx)
end

max_vals

max_vals = 29×2

100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100

max_dates

max_dates = 29×2 datetime array
   21-Nov-1990   15-Oct-1990
   20-Jan-1991   08-Jan-1991
   01-Jan-1992   29-Jan-1992
   21-Mar-1993   20-May-1993
   18-Jan-1994   31-Mar-1994
   20-Mar-1995   22-Mar-1995
   22-Sep-1996   03-Jul-1996
   18-May-1997   11-Jan-1997
   01-Feb-1998   24-Jan-1998
   10-Apr-1999   01-Apr-1999
   03-May-2000   14-Apr-2000
   11-Jan-2001   22-Aug-2001
   22-May-2002   01-Jun-2002
   12-Jun-2003   01-Feb-2003
   17-Jan-2004   21-Jan-2004
   28-Jan-2005   03-Nov-2005
   21-Jan-2006   13-Mar-2006
   08-Feb-2007   10-Jan-2007
   03-Aug-2008   30-Aug-2008
   20-Mar-2009   03-Mar-2009
   16-May-2010   14-Apr-2010
   09-May-2011   25-Jan-2011
   14-Feb-2012   18-Apr-2012
   25-Mar-2013   19-Feb-2013
   09-Apr-2014   19-Jan-2014
   16-Jan-2015   30-Jan-2015
   02-Mar-2016   23-Mar-2016
   04-Jan-2017   23-May-2017
   05-Feb-2018   12-Jan-2018

% plot the data and maxima:
plot(data_timetable.Time,data_timetable{:,:})
hold on
plot(max_dates(:,1),max_vals(:,1),'mo',max_dates(:,2),max_vals(:,2),'ro')

% zoom in on a portion, so we can see the max values:
xlim(data_timetable.Time([401 1000]))
ylim([90 105])

[Andrei]

Is there also a way to add the maximum values for the corresponding dates so I can plot a time series and make the data easier to be interpreted?

EDIT:

I just realised the maximum values are stored in a different variable, sorry for the confusion. What I really meant to ask is if there is any possible way to add the dates of the max values to the corresponding points on the plot and change the x-axis to be the time.

[Voss]

@Andrei: I have edited my answer to include plotting commands.

[Andrei]

Thank you!

[Voss]

You're welcome!

[Andrei]

@Voss if I wanted to change the range of the year (so instead of starting from 1st Jan and ending on 31Dec change it to 1st Oct 1990 to 30th Sep 1991) how would you go about doing it?

I assume it has something to do with this line:

c{i} = data_timetable(yy == y(i),:);

My thought was to change it to

c{i} = data_timetable( (yy == y(i)) || (yy == y(i+1) && month(data_timetable.Time)<=10) ,:);

However, this is wrong as it firstly displays an error and secondly the logic behind it breaks after the 1st year as it would not stop adding data to the cell after 30th Sep 1991(would continue to 31st Dec 1991 instead of creating new cell from 1st Oct 1991 to 30th Sep 1992). Do you have any suggestions?

[Voss]

Use | and & to operate on arrays. || and && are for scalars only.

As for the logic, you need to include another condition alongside yy == y(i), namely that the month is >= 10. So the complete condition would be, in words: "if the year is the current year [y(i)] and the month is October or later, or the year is the next year [y(i)+1] and the month is September or earlier".

Note that, for "the next year", I use y(i)+1 instead of y(i+1). The (i+1)th element of y might be numerically equal to one more than the ith element of y in this data set, but that may not always be the case (i.e., you could have data that skips a year, so y(i+1) and y(i)+1 would not be the same). Also, consider what would happen if you try to access y(i+1) when i==Ny. That'd be y(Ny+1), which doesn't exist since y has only Ny elements, so you'd get an error.

Then there's another problem at the end of the dataset, where there is data from 2018 but it ends in February, so that when you try to get the data from October 1st, 2018, to September 30th, 2019, there is none. You need to handle that case. One way to do that is as I do below, which is to initialize max_vals with NaNs instead of zeros, then check if c{i} is empty inside the loop and if so do not perform the max calculation, then remove the rows of max_vals and max_dates where max_vals is all NaN after the loop. (Another way might be to remove the last year from consideration, i.e., remove the last element of y from y, if you know it will always have no data on an Oct-Sept basis.)

data = randi(100, 10000, 2);
data_timetable = array2timetable (data, 'StartTime', datetime(1990, 10,1), 'TimeStep',caldays(1));

yy = year(data_timetable.Time); % store the years to avoid having to call year() inside the loop
y = unique(yy);

Ny = numel(y);
c = cell(Ny,1);
Ncols = size(data_timetable,2);
max_vals = NaN(Ny,Ncols);       % initialize max_vals
max_dates = NaT(Ny,Ncols);      % initialize the dates when max_vals occur

% pre-compute the months, to avoid doing the same thing over and over inside
% the loop:
data_months = month(data_timetable.Time);

for i = 1:Ny
%     c{i} = data_timetable(yy == y(i),:);
    c{i} = data_timetable((yy == y(i) & data_months >= 10) | (yy == y(i)+1 & data_months <= 9), :);
    if isempty(c{i})
        continue
    end
    [max_vals(i,:),idx] = max(c{i}{:,:},[],1); % max() of contents of c{i}, along 1st dimension, outputting index as well as max value
    max_dates(i,:) = c{i}.Time(idx);           % date of maximum value is c{i}.Time(idx)
end

idx = all(isnan(max_vals),2);
max_vals(idx,:) = [];
max_dates(idx,:) = [];

max_vals

max_vals = 28×2

100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100

max_dates

max_dates = 28×2 datetime array
   27-Oct-1990   19-Jan-1991
   29-Feb-1992   28-Dec-1991
   10-Nov-1992   16-May-1993
   11-Mar-1994   15-Dec-1993
   22-Nov-1994   23-Nov-1994
   07-Oct-1995   09-Mar-1996
   22-Oct-1996   06-Nov-1996
   10-Nov-1997   05-Nov-1997
   24-Oct-1998   05-Oct-1998
   06-Oct-1999   23-Nov-1999
   18-Nov-2000   04-Oct-2000
   02-Nov-2001   01-Nov-2001
   13-Jul-2003   30-Oct-2002
   10-Apr-2004   26-Jan-2004
   26-Sep-2005   17-Oct-2004
   04-Oct-2005   08-Dec-2005
   01-Jan-2007   02-May-2007
   05-Nov-2007   26-Oct-2007
   11-Dec-2008   19-Oct-2008
   20-Sep-2010   03-Feb-2010
   30-Oct-2010   12-May-2011
   04-Oct-2011   22-Jan-2012
   07-Nov-2012   02-Nov-2012
   09-Oct-2013   15-Oct-2013
   22-Aug-2015   22-Dec-2014
   21-Oct-2015   06-Nov-2015
   06-Jan-2017   29-Apr-2017
   25-Dec-2017   21-Dec-2017

% plot the data and maxima:
plot(data_timetable.Time,data_timetable{:,:})
hold on
plot(max_dates(:,1),max_vals(:,1),'mo',max_dates(:,2),max_vals(:,2),'ro')

% zoom in on a portion, so we can see the max values:
xlim(data_timetable.Time([401 1000]))
ylim([90 105])

[Andrei]

Thank you so much! Do you have to initialise max_vals with NaNs instead of zeros because there is no way to check if c{i} is empty inside the loop otherwise? Because if you use zeros and an if statement to check for zeros and remove them, this would possibly cause problems within the data as some values might be 0. Also, the data does end in 30th Sep so I luckily won't run in the problem you explained above.

[Voss]

You're welcome!

"Do you have to initialise max_vals with NaNs instead of zeros because there is no way to check if c{i} is empty inside the loop otherwise?"

No, you can check if c{i} is empty regardless of how max_vals is initialized.

"Because if you use zeros and an if statement to check for zeros and remove them, this would possibly cause problems within the data as some values might be 0."

That's it exactly!

[Andrei]

Understood!

Answer 2

[Eric Sofen]

Normally, groupsummary or varfun should be the starting point for grouped calculations like this, but your particuar problem is a bit tricky because you also want the datetime when the max occurred. So you need rowfun, similar to the last example in the "Grouped Calculations" example.

data = randi(100, 10000, 2); %create example data
%Turn it into a timetable
data_timetable = array2timetable(data, 'StartTime', datetime(1990, 10, 1), 'TimeStep', caldays(1));
data_timetable.Year = data_timetable.Time.Year; % Need Year as an explicit grouping variable for rowfun
% We want Time as a first-class variable, so convert to table.
data_table = timetable2table(data_timetable);

t1 = rowfun(@findMax, data_table, "GroupingVariable","Year", "InputVariables",["Time","data1"], "OutputVariableNames",["Max1","Max1Time"]);
t2 = rowfun(@findMax, data_table, "GroupingVariable","Year", "InputVariables",["Time","data2"], "OutputVariableNames",["Max2","Max2Time"])

t2 = 29×4 table
    Year    GroupCount    Max2     Max2Time  
    ____    __________    ____    ___________

    1990        92         98     22-Nov-1990
    1991       365        100     05-Jul-1991
    1992       366        100     05-Oct-1992
    1993       365        100     03-Aug-1993
    1994       365        100     05-Jan-1994
    1995       365        100     20-Apr-1995
    1996       366        100     25-Jan-1996
    1997       365        100     24-Apr-1997
    1998       365        100     11-Apr-1998
    1999       365        100     28-Apr-1999
    2000       366        100     25-Mar-2000
    2001       365        100     22-Aug-2001
    2002       365        100     15-May-2002
    2003       365        100     02-Sep-2003
    2004       366        100     18-Feb-2004
    2005       365         99     09-May-2005

t1.GroupCount = [];
t2.GroupCount = [];
tfinal = outerjoin(t1,t2,"Keys","Year","MergeKeys",true)

tfinal = 29×5 table
    Year    Max1     Max1Time      Max2     Max2Time  
    ____    ____    ___________    ____    ___________

    1990    100     12-Nov-1990     98     22-Nov-1990
    1991    100     25-Jan-1991    100     05-Jul-1991
    1992    100     27-Jun-1992    100     05-Oct-1992
    1993    100     19-Jan-1993    100     03-Aug-1993
    1994    100     06-Mar-1994    100     05-Jan-1994
    1995    100     11-Jan-1995    100     20-Apr-1995
    1996    100     04-Feb-1996    100     25-Jan-1996
    1997    100     11-Jan-1997    100     24-Apr-1997
    1998    100     23-Aug-1998    100     11-Apr-1998
    1999    100     18-Jan-1999    100     28-Apr-1999
    2000    100     11-Jan-2000    100     25-Mar-2000
    2001    100     21-Mar-2001    100     22-Aug-2001
    2002    100     10-Feb-2002    100     15-May-2002
    2003    100     07-Jan-2003    100     02-Sep-2003
    2004    100     11-May-2004    100     18-Feb-2004
    2005    100     24-May-2005     99     09-May-2005

plot(data_timetable.Time,data_timetable{:,1:2})
hold on
plot(tfinal.Max1Time,tfinal.Max1,'go',tfinal.Max2Time,tfinal.Max2,'ro')
% zoom in on a portion, so we can see the max values:
xlim(data_timetable.Time([401 1000]))
ylim([90 105])

function [maxVal,maxTime] = findMax(times,vals)
    % Return time at which maximum element of vals occurred
    [maxVal,maxIndex] = max(vals);
    if ~isnan(maxVal)
        maxTime = times(maxIndex);
    else
        maxTime = NaT;
    end
end

[Andrei]

I guess this solution works too, however I am not familiar at all with groupsummary or varfun so this is unknown territory for me. Also, all of this code has to be inserted in a function and the function to output the maximum values and the dates when they occur.