how to import average of monthly data from excel file?

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

Answer 1

[Mathieu NOE]

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

[Engineer Batoor khan mummand]

Hi to every one: Thank you so much from your reply dear... My main goal is to plot monthly and weekly average solar radiation which is imported from Excel file which is attached with question?

[Mathieu NOE]

yeap

this is the output excel file

you can then plot whatever you want

[Engineer Batoor khan mummand]

Thank u so much dear... As you know if we take average of each month than output we will have only 12 values for one complete year but in this Excel file we have all those value which greater than 200 .... First: My question is to import average of each month. Second: import average of each week for complete year . Thank u so much dear

[Engineer Batoor khan mummand]

I hope you got my question . 1: average of each month. 2: average of each week .

[Mathieu NOE]

hope I got it right

so the output excel sheet has first weekly average values and in second sheet the monthly averaged values

[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);
n_days = m/24;
n_weeks = floor(n_days/7);
month = (min(data(:,2)):max(data(:,2)));

filename_out = 'test.xlsx';

% 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
   
    OUT1(1,:) = {'WEEK ', 'Solar rad average'}; % paramters in firts line
    OUT1(2:n_weeks+1,:) = num2cell([(1:n_weeks)' week_mean_data_selected(:)]);        % averaged data in second line


% 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
    OUT2(1,:) = {'MONTH ', 'Solar rad average'}; % paramters in firts line
    OUT2(2:length(month)+1,:) = num2cell([(1:length(month))' month_mean_data_selected(:)]);        % averaged data in second line

 % export to excel
    writecell(OUT1,filename_out,"Sheet" ,1); % save OUT1 to excel file
    writecell(OUT2,filename_out,"Sheet" ,2); % save OUT2 to excel file
    

[Engineer Batoor khan mummand]

Thank u so much dear now I have got main point once again thank u so much dear ...

[Engineer Batoor khan mummand]

hell:

i hope you will be fine and doing well dear .

i plot this code in matlab version 2017a but it gives me error below .

Undefined function or variable 'writecell'.

Error in bat11 (line 31)

writecell(OUT,filename_out,"Sheet" ,ci); % save OUT to excel file

[Mathieu NOE]

hello

yes I believe writecell was introduced in 2018

there should be an alternative for older matlab versions

see help xlswrite - and maybe more functions are available.

or look in the File Exchange section. Long time ago I have found a work around that works even on a very old Matlab 6 release.

[Mathieu NOE]

hello again

so this is a code that uses the older xlswrite function. I also avoided to use the num2cell function that I am not sure is available in your release

[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);
n_days = m/24;
n_weeks = floor(n_days/7);
month = (min(data(:,2)):max(data(:,2)));

filename_out = 'test.xlsx';

% 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
   
%  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


% 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

%  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

[Engineer Batoor khan mummand]

hell:

thank you so much dear from your reply .

u have solved my problem but one thing i want to mentation here tha is, i want to plot it direct here in matlab.

plot(monthly average)

plot(weekly average)

once agian thank you so much .

[Mathieu NOE]

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

[Engineer Batoor khan mummand]

thank you so much dear ...

i got it ...

Answer 2

[Cris LaPierre]

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.

[Image Analyst]

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]

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.