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

Question

muhammad choudhry 2022-7-14

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1760100-how-to-take-the-average-of-7-rows-then-other-7-until-the-data-finished-and-save-into-new-tab-of-exce

评论： Jon 2022-7-15

Required_Average.xlsx

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

请先登录，再进行评论。

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

Answer 1

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更早的评论隐藏 2更早的评论

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 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');