MATLAB: Finding sum of large array based on row values

matrix manipulation

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?

Best Answer

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

Related Solutions

MATLAB: Rows mean collapse in big matrix

If the number of rows of M was an integer multiple of N then this would be easy to achieve by simply reshaping the matrix M. Because your data does not fit this we have to take into account that there may be from 1 to N last rows to be "collapsed" together. This can be achieved using mat2cell to split the matrix into groups, and then performing the mean inside a cellfun call:

% Number of rows to "collapse" together:
N = 3;
% Your data:
M = [...
    92    99     1     8    15    67    74    51    58    40
    98    80     7    14    16    73    55    57    64    41
     4    81    88    20    22    54    56    63    70    47
    85    87    19    21     3    60    62    69    71    28
    86    93    25     2     9    61    68    75    52    34
    17    24    76    83    90    42    49    26    33    65
    23     5    82    89    91    48    30    32    39    66
    79     6    13    95    97    29    31    38    45    72
    10    12    94    96    78    35    37    44    46    53
    11    18   100    77    84    36    43    50    27    5];
% vector of row counts:
row = N*ones(1,ceil(size(M,1)/N));
row(end) = mod(size(M,1)-1,N)+1;
% scalar of column count:
col = size(M,2);
% split data into cell, with the specified rows and columns in groups:
C = mat2cell(M,row,col);
% calculate mean of each group and concatenate back into one matrix:
out = cell2mat(cellfun(@(m)mean(m,1),C,'UniformOutput',false));

and the output in the command window:

>> out
out =
    64.667       86.667           32           14       17.667       64.667       61.667           57           64       42.667
    62.667           68           40       35.333           34       54.333       59.667       56.667           52       42.333
    37.333       7.6667           63       93.333       88.667       37.333       32.667           38       43.333       63.667
        11           18          100           77           84           36           43           50           27            5

MATLAB: How to make each pair of pixels from the matrix in a row major order

Hello Subroto. I have used a couple of steps to convert A into a vector of paired up pixels and stored it in A_paired.

>> A=[43   36   31   35   35   29   29   25
      33   33   32   29   25   25   22   22
      33   33   31   32   31   27   25   29
      28   33   35   27   25   29   32   51
      23   26   30   38   31   43   47   57
      27   29   30   37   40   52   63   61
      23   24   32   42   52   62   72   66
      24   30   44   54   65   71   70   74 ];
>> A_transpose = A';
>> A_vector = A_transpose(:);
>> A_paired = reshape(A_vector, 2, length(A_vector) / 2)'
A_paired =
    43    36
    31    35
    35    29
    29    25
    33    33
    32    29
    25    25
    22    22
    33    33
    31    32
    31    27
    25    29
    28    33
    35    27
    25    29
    32    51
    23    26
    30    38
    31    43
    47    57
    27    29
    30    37
    40    52
    63    61
    23    24
    32    42
    52    62
    72    66
    24    30
    44    54
    65    71
    70    74

I hope this was helpful. :)

Related Question