Clear Filters
Clear Filters

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

1 view (last 30 days)
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!
  2 Comments
Benjamin Thompson
Benjamin Thompson on 14 Jul 2022
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
muhammad choudhry on 14 Jul 2022
Edited: muhammad choudhry on 14 Jul 2022
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.

Sign in to comment.

Accepted Answer

Jon
Jon on 14 Jul 2022
Edited: Jon on 14 Jul 2022
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.
  4 Comments
muhammad choudhry
muhammad choudhry on 15 Jul 2022
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
Jon on 15 Jul 2022
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');

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!