how to import average of monthly data from excel file?
1 次查看(过去 30 天)
显示 更早的评论
Engineer Batoor khan mummand
2020-11-16
评论: Engineer Batoor khan mummand
2020-11-17
hi to all:
i hope you all well be fine and doing well.
i want to import average solar radiation of each month from given excel file but the average of those data which is greather than 200 and same for weeks .
thanks
0 个评论
采纳的回答
Mathieu NOE
2020-11-16
hello
this is a simple code that will extract monthly data and look for solar radiation above 200
each month data will then be stored in a separate sheet in a excel file (so 12 sheets total)
[data, text, alldata] = xlsread('solar.xlsx');
% Year Month Day Hour solar_radiation
% i want to import average solar radiation of each month from given excel file
% but the average of those data which is greather than 200 and same for weeks .
% % ind of data that exceed 200 in solar_radiation
% ind = find(ndata(:,5) > 200)
[m,n] = size(data);
month = (min(data(:,2)):max(data(:,2)));
filename_out = 'test.xlsx';
% data selection
for ci = 1:length(month)
ind = find(data(:,2) == month(ci))
data_one_month = data(ind,:);
% ind of data that exceed 200 in solar_radiation
ind = find(data_one_month(:,5) > 200)
data_selected = data_one_month(ind,:);
[mm,nn] = size(data_selected);
% export to excel
OUT(1,1:n) = text; % paramters in firts line
OUT(2:mm+1,1:n) = num2cell(data_selected); % averaged data in second line
writecell(OUT,filename_out,"Sheet" ,ci); % save OUT to excel file
end
12 个评论
Mathieu NOE
2020-11-17
so here you are
I added export_to_excel to manage if you need to export the averaged data to excel or not so it will not overwrite the output file each time.
[data, text, alldata] = xlsread('solar.xlsx');
% Year Month Day Hour solar_radiation
export_to_excel = 0; % output data management : 0 = no export to excel, 1 = export to excel
filename_out = 'test.xlsx';
% i want to import average solar radiation of each month from given excel file
% but the average of those data which is greather than 200 and same for weeks .
% % ind of data that exceed 200 in solar_radiation
% ind = find(ndata(:,5) > 200)
[m,n] = size(data);
n_days = m/24;
n_weeks = floor(n_days/7);
month = (min(data(:,2)):max(data(:,2)));
% average per week
for ci = 1:n_weeks
samples = 24*7;
ind = (1:samples) + (ci-1)*samples;
data_one_week = data(ind,:);
% ind of data that exceed 200 in solar_radiation
ind = find(data_one_week(:,5) > 200);
week_mean_data_selected(ci) = mean(data_one_week(ind,5));
end
% plot
figure(1)
plot((1:n_weeks),week_mean_data_selected,'-*b');grid
title('Weekly Solar rad average');
xlabel('WEEK');
ylabel('Solar rad average');
if export_to_excel == 1
% export to excel / % Using several XLSWRITE commands:
col_header={'WEEK ', 'Solar rad average'}; %Row cell array (for column labels)
xlswrite(filename_out,[(1:n_weeks)' week_mean_data_selected(:)],'WEEK','A2'); %Write data
xlswrite(filename_out,col_header,'WEEK','A1'); %Write column header
end
% average per month
for ci = 1:length(month)
ind = find(data(:,2) == month(ci));
data_one_month = data(ind,:);
% ind of data that exceed 200 in solar_radiation
ind = find(data_one_month(:,5) > 200);
month_mean_data_selected(ci) = mean(data_one_month(ind,5));
end
% plot
figure(2)
plot((1:length(month)),month_mean_data_selected,'-*b');grid
title('Monthly Solar rad average');
xlabel('MONTH');
ylabel('Solar rad average');
if export_to_excel == 1
% export to excel / % Using several XLSWRITE commands:
col_header={'MONTH ', 'Solar rad average'}; %Row cell array (for column labels)
xlswrite(filename_out,[(1:length(month))' month_mean_data_selected(:)],'MONTH','A2'); %Write data
xlswrite(filename_out,col_header,'MONTH','A1'); %Write column header
end
更多回答(1 个)
Cris LaPierre
2020-11-16
You would either have to create a separte sheet in Excel that first calculates the monthly average, or you will need to import all the data into MATLAB and then find the monthly average
You might find groupsummary helpful, as it allows you to summarize the data by month using a method you specificy (sum, mean, max, etc), but you'll need to do all your filtering manually first.
2 个评论
Image Analyst
2020-11-16
Cris how does this groupsummary() function compare to grpstats() and splitapply()? They all seem like they do operations on identified groups of values. When would you use each instead of the other two?
Cris LaPierre
2020-11-16
编辑:Cris LaPierre
2020-11-16
Good question. I have a personal preference towards groupsummary when working with tables. I think that's just because I find it easier to use.
Age-wise, grpstats() is the oldest, and groupsummary() is the newest.
Some quick observations that may or may not hold true.
- grpstats can apply multiple stats to the input. It appears to handles tables the same as groupsummary. Said another way, groupsummary seems to be able to do everything grpstats can do.
- splitapply generally only likes a single input. It accepts tables as inputs, but each column is passed as a separte input to your summary function. If T has N variables, then func must accept N input arguments. It therefore does not run the same summary stat on every variable, which is more often the desired behavior. It also means it requires some additional work if your table contains mixed data types.
- groupsummary is perhaps an improved version of grpstats. It was designed for tables. I believe many of the capabilities have since been added to grpstats. I think a great feature of group summary is the ability to summarize by time intervals if your grouping variables is a datetime or duration.
Each of these probably has a specific use case where they may be preferred. A large part might just be what you are used to as well.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!