How to take the average of 7 rows then other 7 until the data finished and save into new tab of excel

Hi,

I am struggling to make a code here which will take the average of 7 rows then next 7 rows of each column(data need to analyse on the weekly basis), there are 150 columns. I want the average data to be saved in new tabs or file so I can further analyse the data. Any help would be great!

[Benjamin Thompson]

If you look over the readtable documentation by typing "doc readtable" in MATLAB and follow one of the examples for loading an Excel spreadsheet what have you done so far?

[muhammad choudhry]

Struggling: First column is the date column and can be ignored

Code:

close all; clear all; clc;

t = readtable('Required_Average.xlsx', ...

'NumHeaderLines',1, ...

'PreserveVariableNames',true);

data = table2array(t);

Error:

Error using table2array (line 37)

Unable to concatenate the specified table variables.

Caused by:

Error using datetime/horzcat (line 1387)

All inputs must be datetimes or date/time character vectors or date/time strings.

Answer 1

[Jon]

Read the data in using readmatrix.

Once you have read the table into an m by 150 array, let's call it A, then just use movmean to calculate the moving mean

B = movmean(A,7,2); % last argument set it to compute mean columnwise

You can write it back to Excel using for example writematrix.

[Jon]

So a little more complete answer

% start reading at B3 to avoid headers and first column
A = readmatrix('Required_Average.xlsx','Range','B3'); 

% compute moving average (mean) columnwise
B = movmean(A,7,2);

% save the data back to the same file new sheet
writematrix(B,'Required_Average.xlsx','Sheet','Weekly Average')

[Jon]

Would even be better to read into a timetable if you want to keep track of the dates

T = readtimetable('Required_Average.xlsx');
Ts = smoothdata(T,'movmean',days(7));
writetimetable(Ts,'Required_Average.xlsx','Sheet','Weekly Average');

With either approach writematrix or writetimetable, be sure that Excel file is closed before trying to write to it or you will get an error.

[muhammad choudhry]

Hi, I did not understand what this code done up there. in the data: 1639 rows and 181 columns. if we take the mean of 7 values in a row for 181 columns would the mean data not be 234 (1639/7 as we are taking a mean of every 7 values) rows by 181 columns. B is producing the same amount of rows as A with different numbers I am not sure what is this code doing.

[Jon]

It seems I didn't understand what you wanted to calculate. The code I provided gives a moving average over 7 days. So each element in a given column is replaced by the average over the previous 7 day. Thus the number of rows doesn't change, but the values in each row are much smoother.

So I now understand that you want to create a weekly average for each week of data. So if I had data for a given column x(1),x(2),x(3),... x(n). You want a new column with entries y(1) = (x(1) + x(2) + ...x(7))/7, y(2) = (x(8) + x(9) + x(10) + ... x(14))/7, etc.

If this is what you want you can accomplish this easily using the retime function on the timetable

T = readtimetable('Required_Average.xlsx');
Tweekly = retime(T,'regular','mean','TimeStep',days(7))
writetimetable(Tweekly,'Required_Average.xlsx','Sheet','Weekly Average');