using acummarray to average several columns at a time?
2 次查看(过去 30 天)
显示 更早的评论
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 个评论
采纳的回答
Razvan Carbunescu
2019-1-4
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
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
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
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 个评论
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...
Sean de Wolski
2019-1-3
A little bit of shameless blog promotion for that exact use case:
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 个评论
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...
另请参阅
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!