How to average a column's data when they have the same value in another 2 column of a matrix?

11 views (last 30 days)
Marmar on 20 Feb 2017
Answered: Walter Roberson on 20 Feb 2017
Hi All, I have some hourly meteorological data (like a matrix) for many years and I delete some of my data (rows) based on some criteria previously I mean not all my months have about 30 values. But I want an average for each month of each year. one of the columns shows the year and one shows the month. So I want to get an average of monthly tempreture in each year MEANS I want to average all values in the column 5 (temperature) that have the same values (at the same time) in column 1 (year) and column 2 (month). I should consider both values (year and month) simultaneously MEANS I need just an average temperature value for month 1 of year 1980 and so on. Any suggestion for an easy way?
Rik on 20 Feb 2017
What is it you are actually asking? What do you want your code to do? What have you tried already? What (unwanted) results did that yield?
Answering these questions will make it way easier for others to help you. Maybe I'm too tired, but I can't process your wall of text to a question I can answer.

Walter Roberson on 20 Feb 2017
If you use a timetable() data structure then there are direct commands to do this kind of summarization.
With a numeric data structure:
y = YourData(:,YearColumn);
m = YourData(:,MonthColumn);
temperature = YourData(:,TemperatureColumn);
[unique_years, ~, year_idx] = unique(y); %narrow down to unique years
%but we will assume that all months from 1 to 12 are represented
num_years = length(unique_years);
month_average = accumarray( [ym_idx, m], temperature, [num_years, 12], @mean );
output = [unique_years, month_average];
This output has the year in the first column and then 12 columns of monthly averages. If you want to get a year / monthnumber / average table then
output = [unique_years, repmat((1:12).', num_years, 1), month_average(:)];
Note: if any years happen to be missing completely then they will not appear in the table at all. This code does not assume the years are consecutive (but does assume that all 12 months are represented.)