How to extract maximum monthly data from a table based on month and years
4 ビュー (過去 30 日間)
古いコメントを表示
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
0 件のコメント
採用された回答
Voss
2024 年 1 月 17 日
編集済み: Voss
2024 年 1 月 17 日
T_original = readtable('Chakaliya.csv')
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"])
その他の回答 (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!