How to get monthly average for ten years monthly data?
Ältere Kommentare anzeigen
Hey Everyone,
I have ten years of montly data, I want to get monthly average.
Thanks in advace,
Riyadh
6 Kommentare
Stephen23
am 3 Feb. 2026
Riyadh
am 3 Feb. 2026
Steven Lord
am 3 Feb. 2026
The groupsummary function lets you specify "month" as the groupbins input argument to summarize each month worth of data using whatever you specified as the method input argument ("mean" in the case of a monthly average.)
ttTen=retime(ttData,'monthly','mean');
Use whatever is your timetable variable in place of ttData, of course...
If you have need of additional statistics/calculations over same time period, don't overlook grpstats in Statistics TB
Riyadh
am 3 Feb. 2026
Steven Lord
am 3 Feb. 2026
Can you show us a small sample of your data and the way you called groupsummary or retime? It's possible a small modification to your call could achieve your ultimate goal.
Antworten (2)
Cris LaPierre
am 3 Feb. 2026
Bearbeitet: Cris LaPierre
am 3 Feb. 2026
Here's an example using airlinesmall.csv, which contains US domestic airline flight data from Oct 21, 1987 – Dec 31, 2008.
% Load data
setupExample("matlab/AddKeysValuesExample", pwd)
data = readtable('airlinesmall.csv');
% Create datetime variable
data.Date = datetime(data.Year, data.Month, data.DayofMonth);
data = movevars(data,'Date','Before',1)
I have data from 255 months. To get a monthly average, use the following.
% Average each month of the data
monthAvg = groupsummary(data,'Date','month','mean',["ArrDelay","DepDelay"])
However, you say you have 120 months of data, and want the result to be 12 months. That would be obtained by averaging by 'monthofyear' insteasd of 'month' (averaging the same month across years).
% Average each month of the data
monthAvg = groupsummary(data,'Date','monthofyear','mean',["ArrDelay","DepDelay"])
1 Kommentar
dpb
am 3 Feb. 2026
I was going to do something similar, @Cris LaPierre but I couldn't remember a longer time sample dataset and got sidetracked seeing if could download something...
The extensive list of choices in groupsummary is helpful, indeed. I've wondered why retime didn't get the same augmented list.
Umar
am 3 Feb. 2026
Bearbeitet: Walter Roberson
am 3 Feb. 2026
Hi @Riyadh,
I reviewed your question about calculating monthly averages from 10 years of data, and I understand the issue you're facing. You have 120 months of data and need to get 12 average values (one for each calendar month averaged across all 10 years), but the suggestions given so far are still returning 120 values instead of 12.
The key is using the 'monthofyear' binning option in groupsummary, which groups all Januaries together, all Februaries together, and so on, regardless of which year they're from. Here's the solution:
If your data is in a timetable (let's say it's called ttData with a time column and a data column called Temperature):
monthlyAvg = groupsummary(ttData, 'TimeColumnName', 'monthofyear', 'mean', 'Temperature');
This will give you exactly 12 rows - one for each month with the average across all 10 years.
If your data is in a regular table instead of a timetable, you can do this:
yourTable.Month = month(yourTable.DateColumn);
monthlyAvg = groupsummary(yourTable, 'Month', 'mean', 'YourDataColumn');
I've attached a MATLAB script (monthly_average_across_years.m) that demonstrates three different methods to solve your problem. The script uses synthetic data to show exactly how to go from 120 months to 12 monthly averages. When you run it, you'll see it successfully converts 120 data points into 12 averages (one per calendar month), and it includes visualizations showing both the original time series and the final monthly averages.
The script shows that all three methods produce identical results:
* Method 1: Using timetable with groupsummary and 'monthofyear'
* Method 2: Extracting month numbers and grouping
* Method 3: Using findgroups and splitapply for more control
To adapt the code for your actual data, you'll need to:
1. Replace 'monthlyDates' with your actual time variable name
2. Replace 'Temperature' with your actual data variable name
3. Make sure your date column is in datetime format (if it's not, convert it using datetime() function)
4. If you're using MATLAB Mobile, you can comment out the visualization section (lines starting with "figure")
The script will run as-is to show you how the solution works, then you can modify it for your specific dataset. The key takeaway is that 'monthofyear' is specifically designed to bin data by calendar month across multiple years, which is exactly what you need.
4 Kommentare
Umar
am 3 Feb. 2026
Sorry about formatting issues.
Riyadh
am 3 Feb. 2026
Assuming this will make it easier for everyone:
%% Solution for Riyadh: Calculate Monthly Averages Across 10 Years
% This script demonstrates how to get 12 monthly averages from 10 years of data
% (reducing 120 months to 12 average values - one per calendar month)
clear; clc;
%% Method 1: Using Timetable and groupsummary with 'monthofyear'
fprintf('=== Method 1: Timetable with groupsummary ===\n\n');
% Create synthetic monthly data for 10 years (120 months)
startDate = datetime(2014, 1, 1);
endDate = datetime(2023, 12, 1);
monthlyDates = (startDate:calmonths(1):endDate)';
% Generate synthetic data (e.g., temperature, sales, etc.)
% Using random data with seasonal pattern for realism
numMonths = length(monthlyDates);
seasonalPattern = 20 * sin(2*pi*(1:numMonths)'/12) + 50; % Seasonal variation
randomNoise = 5 * randn(numMonths, 1); % Random noise
dataValues = seasonalPattern + randomNoise;
% Create timetable
ttData = timetable(monthlyDates, dataValues, 'VariableNames', {'Temperature'});
% Display first few rows
fprintf('Original data (first 6 months):\n');
disp(ttData(1:6, :));
fprintf('Total months in dataset: %d\n\n', height(ttData));
% Calculate monthly average across all years using 'monthofyear'
monthlyAvg = groupsummary(ttData, 'monthlyDates', 'monthofyear', 'mean', 'Temperature');
% Display results
fprintf('Monthly averages across 10 years:\n');
disp(monthlyAvg);
%% Method 2: Using Regular Table (alternative approach)
fprintf('\n=== Method 2: Regular Table with month extraction ===\n\n');
% Convert to regular table
regularTable = timetable2table(ttData);
% Extract month number (1-12) from dates
regularTable.MonthNum = month(regularTable.monthlyDates);
% Group by month number and calculate mean
monthlyAvg2 = groupsummary(regularTable, 'MonthNum', 'mean', 'Temperature');
% Add month names for clarity
monthNames = {'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', ...
'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'};
monthlyAvg2.MonthName = monthNames';
% Reorder columns for better readability
monthlyAvg2 = monthlyAvg2(:, {'MonthNum', 'MonthName', 'GroupCount','mean_Temperature'});
fprintf('Monthly averages with month names:\n');
disp(monthlyAvg2);
%% Method 3: Manual approach using findgroups and splitapply
fprintf('\n=== Method 3: Manual approach (more control) ===\n\n');
% Extract dates and values as arrays
dates = ttData.monthlyDates;
values = ttData.Temperature;
% Get month numbers
monthNums = month(dates);
% Find groups and apply mean
[G, monthID] = findgroups(monthNums);
monthlyMeans = splitapply(@mean, values, G);
% Create result table
monthlyAvg3 = table(monthID, monthlyMeans, 'VariableNames', {'Month', 'AvgTemperature'});
monthlyAvg3.MonthName = monthNames';
fprintf('Monthly averages using findgroups/splitapply:\n');
disp(monthlyAvg3);
%% Visualization
fprintf('\n=== Creating Visualization ===\n');
figure('Position', [100, 100, 1000, 600]);
% Subplot 1: Original time series
subplot(2, 1, 1);
plot(ttData.monthlyDates, ttData.Temperature, '-o', 'LineWidth', 1.5, 'MarkerSize', 4);
xlabel('Date');
ylabel('Temperature');
title('Original Monthly Data (10 Years)');
grid on;
% Subplot 2: Monthly averages across years
subplot(2, 1, 2);
bar(monthlyAvg2.MonthNum, monthlyAvg2.mean_Temperature, 'FaceColor', [0.2 0.6 0.8]);
xlabel('Month');
ylabel('Average Temperature');
title('Average Temperature by Month (Averaged Across 10 Years)');
set(gca, 'XTick', 1:12, 'XTickLabel', monthNames);
grid on;
%% Summary
fprintf('\n=== SUMMARY ===\n');
fprintf('Starting with %d months of data (10 years)\n', numMonths);
fprintf('Calculated averages for %d calendar months\n', height(monthlyAvg));
fprintf('\nEach month average represents the mean across %d years\n', ...
floor(numMonths / 12));
%% Export results (optional - for MATLAB Mobile users)
% Uncomment if you want to save results
% writetable(monthlyAvg2, 'monthly_averages.csv');
% fprintf('\nResults saved to: monthly_averages.csv\n');
Riyadh
am 4 Feb. 2026
Kategorien
Mehr zu Data Preprocessing finden Sie in Hilfe-Center und File Exchange
am 4 Feb. 2026
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
