using acummarray to average several columns at a time?

6 次查看（过去 30 天）
Osnofa
Osnofa 2018-12-29
评论： Razvan Carbunescu 2019-1-4
Hello
I have a data array (mat) with the following dimensions: 149016x93
The columns are
year | month | day | hour | data 1 | data 2 | data 3 | and so on until data 89
2001 | 1 | 1 | 0 | random numbers ...
... | ... | ... | ... | random numbers ...
2017 | 12 | 31 | 23 | random numbers ...
The data is random and it is what I want to average.
I found this example (MathWorks example) and it is fine, however I've been strugling in how to run it over column 5 to 93...
[ah,~,ch] = unique(mat(:,2:4),'rows');
hraverage = [ah,accumarray(ch,mat(:,5),[],@nanmean)];
My problem is that I'm not being able to have as an output the 8784x93 array, only an 8784* x 4, I've tried loops but i'm missing something that I am not aware of...
*The dataset has several years of data. I want the hour average for each each day of the year. So it's 366 days * 24hours = 8784
for the sake of example, please feel free to consider a smaller array.
thank you for the attention! will keep digging on this...
sample data in attachment. randomly generated:
4 first collumns are: year, month, day, hour, and columns 5 to 7 are data columns.
the final result should be a 8784x7 file.
  3 个评论
显示 1更早的评论
Osnofa
Osnofa 2018-12-29
编辑：Osnofa 2018-12-29
great question. missed that explanation in the opening post.
The dataset has several years of data. I want the hour average for each day of the year. So it's 366 days * 24hours = 8784
that is why I use:
[ah,~,ch] = unique(mat(:,2:4),'rows');
column 2 is month,3 is days and 4 is hours.
dpb
dpb 2018-12-29
Convert to timetable and use retime and/or findgroups/splitapply pair

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Razvan Carbunescu
There's a simpler way of doing this with groupsummary
I imported the sampledata and made the table with 7 columns: Year,Month,Day,Hour,VarName5,VarName6,VarName7
Then used the following commands to take advantage of binning in groupsummary and of being able to include empty groups:
sampledata.Time = datetime(sampledata.Year,sampledata.Month,sampledata.Day)
result = groupsummary(sampledata,{'Time','Hour'},{'dayofyear','none'},'mean',{'VarName5','VarName6','VarName7'},'IncludeEmptyGroups',1)
  2 个评论
Image Analyst
Image Analyst 2019-1-4
I didn't know about this function. So is this kind of like grpstats() but it's in base MATLAB so you don't need the Stats toolbox, and it has additional computations?
Razvan Carbunescu
Razvan Carbunescu 2019-1-4
It's a fairly new function from R2018a in base MATLAB. Yes it is very similar to grpstats but should be nicer to use for tables.
The extra options it has relate to binning, missing data and empty groups.

请先登录，再进行评论。

更多回答（2 个）

dpb
dpb 2018-12-30
t=readtable('sampledata.xls','ReadVariableNames',0); % read file to table
t.Properties.VariableNames={'Year','Month','Day','Hour','D1','D2','D3'}; % convenient names
tt=table2timetable(t(:,5:end),'RowTimes',datetime(t.Year,t.Month,t.Day,t.Hour,0,0)); % to timetable
mnDaily=retime(tt,'daily','mean'); % averages by day
See what we gots...
>> mnDaily(1:4,:)
ans =
4×3 timetable
Time D1 D2 D3
____________________ ______ ______ ______
01-Jan-2001 00:00:00 701 173.5 639.5
02-Jan-2001 00:00:00 223 614 484
03-Jan-2001 00:00:00 642.33 196 598.33
04-Jan-2001 00:00:00 318 243.25 534.5
>>
For real case with a very large number of variables, rather than naming them all sequentially, I'd sugget reading the spreasheet data as an array and build the table from it instead...
data=xlsread('sampledata.xls');
t=table(datetime(data(:,1),data(:,2),data(:,3),data(:,4),0,0),data(:,5:end));
t.Properties.VariableNames={'Date','Data'};
tt=table2timetable(t);
mnDaily=retime(tt,'daily','mean');
and will have same result excepting the means will be an array instead of individual variables.
  5 个评论
显示 3更早的评论
dpb
dpb 2019-1-2
编辑：dpb 2019-1-3
Ah...you beat me to it, Peter! It came to me while doing the other mind-numbing data cleanup task that had taken break from and just came back to comment on the "why"...
I didn't double-check for absolute certain of whether was one or none but there were a lot of NaN elements in the sample data set and I suspect there was at least one subset that turned out empty altho the symptom also fits the one-row scenario.
Hmm....that's an interesting result...
mean([])
returns NaN, not []. What's the logic in that?
Answers Self... :)
Makes the PP example work in returning vector result...

请先登录，再进行评论。

Image Analyst
Image Analyst 2018-12-29
Why not simply use grpstats() if you have the Statistics and Machine Learning Toolbox?
Attach your data if you need help.
  7 个评论
显示 5更早的评论
dpb
dpb 2018-12-30
As noted above, convert the date column data to datetimes and put into a timetable and then use findgroups/splitapply pair or retime
This sort of thing is precisely what they're for...
Osnofa
Osnofa 2018-12-30
will take a look into it, didn't notice yesterday. thanks for the reminder.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Repeated Measures and MANOVA 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by