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

7 次查看（过去 30 天）
Raj Arora
Raj Arora 2024-1-17
评论： Voss 2024-1-17
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

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

采纳的回答

Voss
Voss 2024-1-17
编辑：Voss 2024-1-17
Ran in:
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

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by