Finding sum of large array based on row values

6 views (last 30 days)
Hello, I have a big matrix where the 1st column varies for the year (1993 to 2014), 2nd column is month, 3rd column is day, 4th is hour, fifth min, sixth second and the last column is value. The value of 4th column varies from 0 to 23 hr, making a full day.
1993 4 2 0 0 0 2,44
1993 4 2 0 15 0 2,34
1993 4 2 0 30 0 2,21
1993 4 2 0 45 0 2,14
1993 4 2 1 0 0 2,04
1993 4 2 1 15 0 1,98
1993 4 2 1 30 0 1,92
1993 4 2 1 45 0 1,87
1993 4 2 2 0 0 1,79
1993 4 2 2 15 0 1,75
1993 4 2 2 30 0 1,73
1993 4 2 2 45 0 1,73
1993 4 2 3 0 0 1,72
1993 4 2 3 15 0 1,73
1993 4 2 3 30 0 1,78
1993 4 2 3 45 0 1,77
......................................................
......................................................
......................................................
2014 12 31 21 0 0 3,82
2014 12 31 21 15 0 3,89
2014 12 31 21 30 0 3,88
2014 12 31 21 45 0 3,89
2014 12 31 22 0 0 3,90
2014 12 31 22 15 0 3,85
2014 12 31 22 30 0 3,81
2014 12 31 22 45 0 3,74
2014 12 31 23 0 0 3,66
2014 12 31 23 15 0 3,56
2014 12 31 23 30 0 3,45
2014 12 31 23 45 0 3,34
I have to group the similar 4th row element (which varies from 0 to 23) and find out mean of the last column. So, the desired output would be:
1993 4 2 0 0 0 2.28
1993 4 2 1 0 0 1.95
1993 4 2 2 0 0 1.75
1993 4 2 3 0 0 1.75
.....................................................
.....................................................
2014 12 31 21 0 0 3,87
2014 12 31 22 0 0 3.82
Any help on this?

Accepted Answer

alice
alice on 11 Aug 2017
Edited: alice on 11 Aug 2017
Calling your matrix data:
[dateAndHour,~,index] = unique(data(:,1:4),'rows'); % find the hours present in the matrix and attribute each element of the matrix to one of these hours
hourlyMean = accumarray(index,data(:,7),[],@mean); % compute the hourly mean
output = [dateAndHour,zeros(size(dateAndHour,1),2),hourlyMean]; % build the output

More Answers (0)

Categories

Find more on Matrices and Arrays in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!