Break array or timetable into smaller versions and find max value
Show older comments
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?
Accepted Answer
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_dates
% 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 Comments
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.
Andrei
on 3 Jan 2023
Voss
on 3 Jan 2023
You're welcome!
Andrei
on 12 Jan 2023
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_dates
% 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
on 12 Jan 2023
Voss
on 12 Jan 2023
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
on 12 Jan 2023
More Answers (1)
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"])
t1.GroupCount = [];
t2.GroupCount = [];
tfinal = outerjoin(t1,t2,"Keys","Year","MergeKeys",true)
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
Categories
Find more on Timetables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
