how to import average of monthly data from excel file?

1 view (last 30 days)
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

Accepted Answer

Mathieu NOE
Mathieu NOE on 16 Nov 2020
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 Comments
Mathieu NOE
Mathieu NOE on 17 Nov 2020
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

Sign in to comment.

More Answers (1)

Cris LaPierre
Cris LaPierre on 16 Nov 2020
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 Comments
Image Analyst
Image Analyst on 16 Nov 2020
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
Cris LaPierre on 16 Nov 2020
Edited: Cris LaPierre on 16 Nov 2020
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.

Sign in to comment.

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Products


Release

R2017a

Community Treasure Hunt

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

Start Hunting!