How can I group column of data with the same time stamp and get the row mean of each group?

from this table to this image where

x = Height

t = time

u = zonal wind

Answer 1

[Cris LaPierre]

The functions findgroup and splitapply allow you to group by similar time and take a mean of all data in each group. However, that works for column data (your raw data), and not for your summary tables (bottom images). There may be a way to create groups by hour instead of time so you don't have to combine columns.

[Teddy2018]

I have tried unstacking the data to be able to get the summary table below . The problem is I have 3 zonal wind data under one time (1:00 , 1:03, 1:33) while the rest have 2 data under one time (2:00 ,2:33). That is why I'm looking for other ways to group those data and get the mean for each hour.

[Teddy2018]

like this raw data

[Cris LaPierre]

Any chance you can post the data instead of a screenshot? Can you attach a mat file or spreadsheet of the sample data?

[Teddy2018]

Attached is the raw data.

[Cris LaPierre]

is there any need to separate by date, or is grouping by hour across multiple days ok?

[Teddy2018]

grouping by hours is ok.

[Cris LaPierre]

I'm thinking something like this. Grouping and getting the mean is fairly easy. Trying or organize it the way you need might require a bit more creativity. This should get you most of the way there.

load Teddy2018_Table_data.mat
tabU.t = datetime(tabU.t,'InputFormat',"HH:mm", 'Format','HH:mm');

% Group1 - elevation
gp1 = ceil(tabU.x/500)*500;
elev = unique(gp1);

% Group2 - time (hourly)
gp2 = hour(tabU.t);
hr = unique(gp2);

% Combine to a single number for each hour for each elevation
G = findgroups(gp1,gp2);
val = splitapply(@mean,tabU.u,G);

% Organize as shown in your table. Can't have numbers as variable names,
% but columns are time, 0-23 (24=0)
avg = flipud(reshape(val,length(hr),length(elev))');
out = table(avg,'RowNames',string(flipud(elev)))

A big caution that I've not checked the values to ensure they are correct. I leave that to you.

[Teddy2018]

Thank you very much Cris!! =)

[Teddy2018]

[Cris LaPierre]

That's pretty cool!

Answer 2

[Peter Perkins]

retime on a timetable is the simplest way to compute means per time bin:

>> tt = timetable(rand(10,1),'RowTimes',duration((sort(3*rand(10,1))),0,0))
tt =
  10×1 timetable
      Time       Var1  
    ________    _______
    00:02:08    0.35166
    00:09:42    0.83083
    00:13:39    0.58526
    00:23:22    0.54972
    01:00:40    0.91719
    01:24:29    0.28584
    01:35:32     0.7572
    01:42:23    0.75373
    02:20:15    0.38045
    02:48:07    0.56782
>> ttHourlyMean = retime(tt,'hourly','mean')
ttHourlyMean =
  3×1 timetable
      Time       Var1  
    ________    _______
    00:00:00    0.57937
    01:00:00    0.67849
    02:00:00    0.47413    

You can then unstack that if you want. It's not at all clear to me how you got to those regular heights, 3000, 2500, etc. Maybe those are height bins, and you're really doing 2-D grouping. retime won't do that, splitapply, groupsummary, or a grouped varfun is the way to go for that.

Answer 3

[Teddy2018]

the height bins are default height generated by the instrument.