Break array or timetable into smaller versions and find max value

Question

Andrei 2023-1-3

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1888012-break-array-or-timetable-into-smaller-versions-and-find-max-value

评论： Andrei 2023-1-12

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?

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

请先登录，再进行评论。

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

Answer 1

Voss 2023-1-3

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1888012-break-array-or-timetable-into-smaller-versions-and-find-max-value#answer_1140782

编辑：Voss 2023-1-3

在 MATLAB Online 中打开

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])

9 个评论
显示 7更早的评论隐藏 7更早的评论

Voss 2023-1-12

编辑：Voss 2023-1-12

在 MATLAB Online 中打开

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])

Voss 2023-1-12

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 2023-1-12

Understood!

请先登录，再进行评论。

Answer 2

Eric Sofen 2023-1-4

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1888012-break-array-or-timetable-into-smaller-versions-and-find-max-value#answer_1141317

在 MATLAB Online 中打开

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

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Andrei 2023-1-5

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.

请先登录，再进行评论。

Break array or timetable into smaller versions and find max value

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

采纳的回答

9 个评论
显示 7更早的评论隐藏 7更早的评论

更多回答（1 个）

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Break array or timetable into smaller versions and find max value

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

采纳的回答

9 个评论 显示 7更早的评论隐藏 7更早的评论

更多回答（1 个）

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

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

9 个评论
显示 7更早的评论隐藏 7更早的评论

1 个评论
显示 -1更早的评论隐藏 -1更早的评论