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

2 visualizaciones (últimos 30 días)
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 comentarios
Benjamin Thompson
Benjamin Thompson el 14 de Jul. de 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 el 14 de Jul. de 2022
Editada: muhammad choudhry el 14 de Jul. de 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.

Iniciar sesión para comentar.

Respuesta aceptada

Jon
Jon el 14 de Jul. de 2022
Editada: Jon el 14 de Jul. de 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 comentarios
muhammad choudhry
muhammad choudhry el 15 de Jul. de 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 el 15 de Jul. de 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');

Iniciar sesión para comentar.

Más respuestas (0)

Etiquetas

Community Treasure Hunt

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

Start Hunting!

Translated by