How to extract maximum monthly data from a table based on month and years

I have an Excel file comprising five columns. The initial column consists of dates in the format (01-Feb-91). This extensive dataset spans daily records up to the year 2010 (some in between data is missing also). My objective is to derive the monthly maximum values for each variable across all months for every year, utilizing the information in the first column. I have attached the .csv file with this querry also. I have written a code but still I am not able to find the monthly maximum value. As this dataset is big you can also try with short data by just taking data for 2 continous years.

CODE

filePath = "D:\OneDrive - IIT Bombay\APS-5 WORK\Climate\MAHI RIVER BASIN\Discharge data\1980-2010\Chakaliya.csv";
dataTable = readtable(filePath, 'Delimiter', '\t', 'ReadVariableNames', false);
% Convert the first column (assumed to be dates) to datetime using datenum
dateNumbers = datenum(dataTable.Var1, 'dd-mm-yy');
% Convert date numbers to datetime
dataTable.Var1 = datetime(dateNumbers, 'ConvertFrom', 'datenum');
% Load numeric data from the data variable
data = xlsread("Chakaliya.csv"); % Assuming data has the numeric values
% Create a new table with merged data
mergedTable = table(dataTable.Var1, data(:, 1), data(:, 2), data(:, 3), data(:, 4), 'VariableNames', {'Dates', 'Last10Year-AverageQ(Cumecs)', 'LastYear-Q(Cumecs)', 'CurrentYear-Q(Cumecs)', 'Level(m)'});
% Extract Year and Month from Dates
mergedTable.YearMonth = year(mergedTable.Dates) * 100 + month(mergedTable.Dates);
% Extract unique years and months
uniqueYears = year(mergedTable.Dates);
uniqueMonths = month(mergedTable.Dates);
% Initialize a matrix to store maximum values
maxValues = NaN(length(uniqueMonths), 1); % Initialize as empty
% Loop through each month
for m = 1:length(uniqueMonths)
    % Initialize a matrix for the current month
    currentMonthData = [];
    % Loop through each year
    for y = 1:length(uniqueYears)
        % Filter data for the current year and month
        currentData = mergedTable(year(mergedTable.Dates) == uniqueYears(y) & month(mergedTable.Dates) == uniqueMonths(m), 2:end);
        % Check if there is data for the current month
        if ~isempty(currentData)
            % Find the maximum value for each column
            currentDataArray = table2array(currentData);
            currentMonthData = [currentMonthData; currentDataArray];
        end
    end
    % Check if the current month has data
    if ~isempty(currentMonthData)
        % Find the maximum values for the current month
        %maxValues(m, 1:size(currentMonthData, 2)) = max(currentMonthData, [], 1, 'omitnan');
        maxValues(m) = max(currentMonthData, [], 'all', 'omitnan');
    end
end
% Convert the matrix to a table
maxValuesTable = array2table(maxValues, 'VariableNames', cellstr(strcat('Var', num2str((1:size(maxValues, 2))'))));

OUTPUT I WANT

Feb 1991 2.49 0 2.40 -

Mar 1991 2.18 0 2.186 181.68

--

--

--

Till

Sep 2010

Answer 1

[Voss]

T_original = readtable('Chakaliya.csv')

T_original = 7169×5 table
       Var1        Var2     Var3    Var4      Var5 
    ___________    _____    ____    _____    ______

    14-Feb-0091     2.49     0       2.49       NaN
    15-Feb-0091     2.49     0       2.49       NaN
    16-Feb-0091     2.49     0       2.49       NaN
    17-Feb-0091     2.49     0       2.49       NaN
    18-Feb-0091     2.49     0       2.49       NaN
    19-Feb-0091     2.49     0       2.49       NaN
    20-Feb-0091     2.49     0       2.49       NaN
    21-Feb-0091     2.49     0       2.49       NaN
    22-Feb-0091    2.439     0      2.439       NaN
    23-Feb-0091    2.355     0      2.355       NaN
    24-Feb-0091    2.262     0      2.262       NaN
    25-Feb-0091    2.262     0      2.262       NaN
    26-Feb-0091    2.262     0      2.262       NaN
    27-Feb-0091    2.262     0      2.262       NaN
    28-Feb-0091    2.186     0      2.186       NaN
    01-Mar-0091    2.186     0      2.186    181.68

T = T_original;
T.(1) = T.(1)+years(1900+100*(year(T.(1))<=10)); % 91->1991, ..., 0->2000, ..., 10->2010
T.Month = month(T.(1),'shortname');
T.Year = year(T.(1));
G = removevars(sortrows(groupsummary(T,["Month","Year"],"max"),"max_Var1"),["max_Var1","GroupCount"])

G = 236×6 table
     Month     Year    max_Var2    max_Var3    max_Var4    max_Var5
    _______    ____    ________    ________    ________    ________

    {'Feb'}    1991       2.49          0        2.49          NaN 
    {'Mar'}    1991      2.186          0       2.186       181.68 
    {'Apr'}    1991      0.649          0       0.649       181.54 
    {'May'}    1991       0.08          0        0.08          NaN 
    {'Jun'}    1991      24.67          0       24.67       182.06 
    {'Jul'}    1991       2583          0        2583       188.13 
    {'Aug'}    1991      623.8          0       623.8        184.4 
    {'Sep'}    1991         75          0          75        182.3 
    {'Oct'}    1991      8.667          0       8.667       181.78 
    {'Nov'}    1991      3.125          0       3.125       181.58 
    {'Dec'}    1991        2.3          0         2.3       181.52 
    {'Jan'}    1992      1.715          0       1.715       181.49 
    {'Feb'}    1992      1.675       2.49        1.05       181.45 
    {'Mar'}    1992      1.333      2.186        0.48       181.42 
    {'Apr'}    1992     0.3245      0.649           0       181.38 
    {'May'}    1992       0.04       0.08           0       181.35 

[Raj Arora]

Thanks Voss for your help.

[Voss]

You're welcome!