Averaging data based on certain row

I have 186x2 data set, the first column is time and the second is height. Time is between 18 to 23 for 31 days. So, 1st to 6th of rows is 18 to 23, and then 7th to 12th of rows, again 18 to 23, and so on until 31 days. How do I get mean/average of data based on time. Average of 1-31 days for 18, 19,...23. And then plot hourly average for height with time as axis. The data looks like as below :

        18       267
        19       346
        20       333
        21       389
        22       390
        23       277
        18       333
        19       255
        20       345
        21       267
        22       322
        23       255
        19       322
        ...      ...
        ...      ...

Answer 1

[Jan]

If some hours can be missing (there is a missing 18 in your example):

[Group, Hour] = findgroups(A(:, 1));
Avg = splitapply(@mean, A(:, 2), Group);
plot(Hour, Avg);

When the data are dense:

Avg = mean(reshape(A(:, 2), 6, []), 2);
plot(A(1:6, 1), Avg);

[dydy]

Thank you for your answer, Jan Simon. But It did not work. The hour not missing, all of first column filled by 18, 19,..,23, 18, 19,...23, 18, 19,..23 (until 31 days). I need average for certain hour, for example, average for 18 will be counted by mean of 1st row, 7th row, 12th row, ..., 181st row. And then I will plot the average value with hour as an x-axis (18:00, 19:00, 20:00, 21:00, 22:00, 23:00) and height as y-axis.

[Jan]

This is exactly what the posted code should do. Please explain "did not work" with any details. Post the code you have used and either the error message or a description of the difference between the results and your expectations. If you provide some text data, which can be used by copy&paste, the readers could test their code.

The 2nd method works when I use your example data:

A = [ 18       267; ...
      19       346; ...
      20       333; ...
      21       389; ...
      22       390; ...
      23       277; ...
      18       333; ...
      19       255; ...
      20       345; ...
      21       267; ...
      22       322; ...
      23       255];
Avg = mean(reshape(A(:, 2), 6, []), 2);
plot(A(1:6, 1), Avg);

The only difference I see is that the X-axis gets the tick labels "18, 18.5, 19, ...", but this can be adjusted by using datetick and was not part of the original question. Therefore your "did not work" does not allow me to improve this suggestion.

[dydy]

Actually, I have 1116 x 5 data with time and height at 3th and 4th column. I have already made average every 6 rows. So, Now I have 186 x 5 data. From the latest result, I want to obtain average for every hour as I asked in this forum. This is my code I have :

%Hourly Average from every 10 minutes data
load CHUMPHON_2011.txt
data=CHUMPHON_2011(:,:); %arbitrary test data
for m=1:5
    for n=1:6:(floor(length(data)/6)*6)
        y(floor(n/6)+1,m)=mean(data(n:n+5,m));
    end
end
Avg = mean(reshape(y(:, 4), 6, []), 2);
plot(y(1:6, 3), Avg);

The plot looks like as below :

[Jan]

Without having the data, I cannot guess, what's going on. Can you provide the data file or post at least some working data created by rand?

With some random data it works:

data = rand(185, 5);
for m=1:5
  for n=1:6:(floor(length(data)/6)*6)
      y(floor(n/6)+1, m) = mean(data(n:n+5,m));
  end
end
Avg = mean(reshape(y(:, 4), 6, []), 2);
plot(y(1:6, 3), Avg);

And a nicer version of the code:

data  = rand(185, 5);
n     = size(data, 1);
dataX = data(1:(n - mod(n, 6)), :);  % Crop to multiple of 6
y     = squeeze(mean(reshape(dataX, 6, [], 5), 1));
Avg   = mean(reshape(y(:, 4), 6, []), 2);
plot(y(1:6, 3), Avg);
disp(y(1:6, 3))
disp(Avg);

Do your data contain NaNs? Then the plotted line would be invisible.

[dydy]

Thank you so much.