# Algorythm for Average of excel data

4 Ansichten (letzte 30 Tage)
SATYA PAL am 15 Feb. 2024
Bearbeitet: Jon am 23 Feb. 2024
Dear Sir please suggest .. How can I get average of my attached data in excel at an interval of every 5 datas. like at time interval 0-0.5 sec then 0.6-1 sec, 1-1.5 sec and so on....
##### 1 Kommentar-1 ältere Kommentare anzeigen-1 ältere Kommentare ausblenden
Jon am 21 Feb. 2024
Bearbeitet: Jon am 23 Feb. 2024
You have a lot of what look like helpful answers to your question. Unless, there is something that has not been addressed in these answers, it would be good for you to now select one of them as the answer. This will allow the question to be marked as answered so that others will know that an answer is available.

Melden Sie sich an, um zu kommentieren.

### Akzeptierte Antwort

Dyuman Joshi am 15 Feb. 2024
Bearbeitet: Dyuman Joshi am 15 Feb. 2024
data = 5758×3 table
Time A B ____ ________ _________ 0.1 -0.54042 0.0005 0.2 -0.54042 0.0013958 0.3 -0.54042 0.0022292 0.4 -0.50863 0.0030625 0.5 -0.50863 0.0038958 0.6 -0.50863 0.0046042 0.7 -0.50863 0.0054792 0.8 10.792 0.0062708 0.9 19.677 0.0071042 1 31.439 0.0080208 1.1 48.049 0.0088958 1.2 61.957 0.0097292 1.3 74.72 0.010563 1.4 87.818 0.011396 1.5 99.977 0.012229 1.6 112.66 0.013062
%define bins to distribute bins in
idx = 0:0.5:0.5*ceil(max(data.Time)/0.5);
%Get the mean of the rest of the columns for the specified bins
out = groupsummary(data, 1, idx, @mean, 'IncludedEdge', 'right')
out = 1152×4 table
disc_Time GroupCount fun1_A fun1_B _________ __________ _______ _________ [0, 0.5] 5 -0.5277 0.0022167 (0.5, 1] 5 12.178 0.0062958 (1, 1.5] 5 74.504 0.010562 (1.5, 2] 5 136.88 0.014729 (2, 2.5] 5 196.86 0.018896 (2.5, 3] 5 256.22 0.023062 (3, 3.5] 5 314.36 0.027229 (3.5, 4] 5 371.88 0.031396 (4, 4.5] 5 430.15 0.035562 (4.5, 5] 5 488.54 0.039733 (5, 5.5] 5 548.08 0.043904 (5.5, 6] 5 607.7 0.048062 (6, 6.5] 5 668.74 0.052229 (6.5, 7] 5 730.91 0.056396 (7, 7.5] 5 794.33 0.060562 (7.5, 8] 5 859.02 0.064729
##### 3 Kommentare1 älteren Kommentar anzeigen1 älteren Kommentar ausblenden
SATYA PAL am 21 Feb. 2024
I used this approach and its working nicely
Dyuman Joshi am 22 Feb. 2024

Melden Sie sich an, um zu kommentieren.

### Weitere Antworten (4)

Jon am 15 Feb. 2024
Bearbeitet: Jon am 15 Feb. 2024
If you have the Statistics and Machine learning toolbox you could do it like this
% Parameters
grpIncr = 0.5 % time increment for group averages
grpIncr = 0.5000
% Read the data into a matrix
dat = 5758x3
0.1000 -0.5404 0.0005 0.2000 -0.5404 0.0014 0.3000 -0.5404 0.0022 0.4000 -0.5086 0.0031 0.5000 -0.5086 0.0039 0.6000 -0.5086 0.0046 0.7000 -0.5086 0.0055 0.8000 10.7924 0.0063 0.9000 19.6775 0.0071 1.0000 31.4395 0.0080
% Provide grouping variable that makes elements within a specified sampling
% interval have the same group value
grp = floor(dat(:,1)/0.5);
[dat grp]
ans = 5758x4
0.1000 -0.5404 0.0005 0 0.2000 -0.5404 0.0014 0 0.3000 -0.5404 0.0022 0 0.4000 -0.5086 0.0031 0 0.5000 -0.5086 0.0039 1.0000 0.6000 -0.5086 0.0046 1.0000 0.7000 -0.5086 0.0055 1.0000 0.8000 10.7924 0.0063 1.0000 0.9000 19.6775 0.0071 1.0000 1.0000 31.4395 0.0080 2.0000
% Calculate mean of each group
stats = grpstats(dat(:,2:3),grp)
stats = 1152x2
-0.5325 0.0018 5.7888 0.0055 60.7967 0.0097 124.6993 0.0139 184.9874 0.0181 244.3314 0.0222 302.8520 0.0264 360.3649 0.0306 418.3420 0.0347 476.8403 0.0389
##### 3 Kommentare1 älteren Kommentar anzeigen1 älteren Kommentar ausblenden
SATYA PAL am 21 Feb. 2024
I used this approach also and its also working nicely
SATYA PAL am 21 Feb. 2024
Thanks a lot

Melden Sie sich an, um zu kommentieren.

Voss am 15 Feb. 2024
Maybe something like this:
filename = 'S1IA.csv';
T.Time = seconds(T.Time);
T = table2timetable(T,'RowTimes','Time');
new_t = T.Time(1):seconds(0.5):T.Time(end);
T = retime(T,new_t,'mean')
T = 1152x2 timetable
Time A B _______ _______ _________ 0.1 sec -0.5277 0.0022167 0.6 sec 12.178 0.0062958 1.1 sec 74.504 0.010562 1.6 sec 136.88 0.014729 2.1 sec 196.86 0.018896 2.6 sec 256.22 0.023062 3.1 sec 314.36 0.027229 3.6 sec 371.88 0.031396 4.1 sec 430.15 0.035562 4.6 sec 488.54 0.039733 5.1 sec 548.08 0.043904 5.6 sec 607.7 0.048062 6.1 sec 668.74 0.052229 6.6 sec 730.91 0.056396 7.1 sec 794.33 0.060562 7.6 sec 859.02 0.064729
##### 0 Kommentare-2 ältere Kommentare anzeigen-2 ältere Kommentare ausblenden

Melden Sie sich an, um zu kommentieren.

Mathieu NOE am 15 Feb. 2024
hello again
well, this is quite the same as my answer to your other post
t = data(:,1);
dt = mean(diff(t));
%% home made solution (you choose the amount of overlap)
buffer_size = round(0.5/dt); % how many samples for 0.5 seconds buffer ?
overlap = 0; % overlap expressed in samples
%%%% main loop %%%%
[new_time,data_out] = my_movmean(t,data(:,2:3),buffer_size,overlap);
figure(2),
plot(t,data(:,2),new_time,data_out(:,1),'*-r');
title('A');
legend('raw data','0.5s mean');
xlabel('Time(s)');
figure(3),
plot(t,data(:,3),new_time,data_out(:,2),'*-r');
title('B');
legend('raw data','0.5s mean');
xlabel('Time(s)');
%%%%%%%%%% my functions %%%%%%%%%%%%%%
function [new_time,data_out] = my_movmean(t,data_in,buffer_size,overlap)
% NB : buffer size and overlap are integer numbers (samples)
% data (in , out) are 1D arrays (vectors)
shift = buffer_size-overlap; % nb of samples between 2 contiguous buffers
[samples,~] = size(data_in);
nb_of_loops = fix((samples-buffer_size)/shift +1);
for k=1:nb_of_loops
start_index = 1+(k-1)*shift;
stop_index = min(start_index+ buffer_size-1,samples);
x_index(k) = round((start_index+stop_index)/2);
data_out(k,:) = mean(data_in(start_index:stop_index,:),1,'omitnan'); %
end
new_time = t(x_index); % time values are computed at the center of the buffer
end
##### 1 Kommentar-1 ältere Kommentare anzeigen-1 ältere Kommentare ausblenden
SATYA PAL am 21 Feb. 2024
Thanks a lot sir

Melden Sie sich an, um zu kommentieren.

Alexander am 15 Feb. 2024
A very easy approach (as allways):
%Algorythm for Average of excel data
clear; close all;
t = data(:,1);A = data(:,2);B = data(:,3);
dy = floor(length(A)/5)
t = t(1:dy*5); % maximum 4 samples lost!
tr = reshape(t,5,dy);
trMean = mean(tr);
A = A(1:dy*5); % maximum 4 samples lost!
Ar = reshape(A,5,dy);
ArMean = mean(Ar);
B = B(1:dy*5); % maximum 4 samples lost!
Br = reshape(B,5,dy);
BrMean = mean(Br);
subplot(211)
plot(trMean ,ArMean); grid minor; title('A')
subplot(212)
plot(trMean, BrMean); grid minor; title('B')
@SATYA PAL beautifying is up to you.
##### 1 Kommentar-1 ältere Kommentare anzeigen-1 ältere Kommentare ausblenden
SATYA PAL am 21 Feb. 2024
Thanks a lot sir

Melden Sie sich an, um zu kommentieren.

### Kategorien

Mehr zu Historical Contests finden Sie in Help Center und File Exchange

### Community Treasure Hunt

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

Start Hunting!

Translated by