Algorythm for Average of excel data

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

[Jon]

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.

Answer 1

[Dyuman Joshi]

data = readtable('S1IA.csv')

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

[SATYA PAL]

Thanks a lot Sir

[SATYA PAL]

I used this approach and its working nicely

[Dyuman Joshi]

Glad to have helped!

Answer 2

[Jon]

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 = readmatrix('S1IA.csv')

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

[Jon]

Sorry, I just noticed you wanted groups "0-0.5 sec then 0.6-1 sec, 1-1.5 sec and so on..."

I assume that you actuallly mean groups 0.1-0.5 sec, 0.6-1 sec, etc, that is to be consistent the first group should start at 0.1.

The code above groups them 0-0.4, 0.5-0.9, 1-1.4, ...

If you want it to group them 0.1-0.5, 0.6-1.0 sec etc then modify the line of code that computes the group to

%grp = floor((dat(:,1)-0.1)/0.5);

So, this would give overall

% Parameters
grpIncr = 0.5 % time increment for group averages

grpIncr = 0.5000

% Read the data into a matrix
dat = readmatrix('S1IA.csv')

dat = 5758×3

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.1)/0.5);
[dat grp]

ans = 5758×4

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 0 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 1.0000

% Calculate mean of each group
stats = grpstats(dat(:,2:3),grp)

stats = 1152×2

-0.5277 0.0022 12.1784 0.0063 74.5042 0.0106 136.8809 0.0147 196.8606 0.0189 256.2237 0.0231 314.3628 0.0272 377.6948 0.0318 435.9921 0.0360 488.5387 0.0397

[SATYA PAL]

I used this approach also and its also working nicely

[SATYA PAL]

Thanks a lot

Answer 3

[Voss]

Maybe something like this:

filename = 'S1IA.csv';
T = readtable(filename);
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

Answer 4

[Mathieu NOE]

hello again

well, this is quite the same as my answer to your other post

Please share the algorithm of the particular data set. I want to find out average of the attached data of every 5 seconds. Th... - MATLAB Answers - MATLAB Central (mathworks.com)

adapted to your new data file , this becomes :

data = readmatrix('S1IA.csv'); % Time,A,B
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

[SATYA PAL]

Thanks a lot sir

Answer 5

[Alexander]

A very easy approach (as allways):

%Algorythm for Average of excel data

%https://de.mathworks.com/matlabcentral/answers/2082483-algorythm-for-average-of-excel-data

clear; close all;

data = dlmread('S1IA.csv',',',1,0);

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.

[SATYA PAL]

Thanks a lot sir