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

3 次查看（过去 30 天）
muhammad choudhry
muhammad choudhry 2022-7-14
评论： Jon 2022-7-15
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 个评论
Benjamin Thompson
Benjamin Thompson 2022-7-14
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 2022-7-14
编辑：muhammad choudhry 2022-7-14
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.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Jon
Jon 2022-7-14
编辑：Jon 2022-7-14
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 个评论
显示 2更早的评论
muhammad choudhry
muhammad choudhry 2022-7-15
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 2022-7-15
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');

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by