MATLAB: Averaging values in a table in succession

averageMATLABspreadsheettable

Hi,
I want to average a portion of data from a speadsheet everytime a condition is met, the condition is that every time a 0 appears on the 'velocity' and 'displacement' column, I want to get the average of the data below it up until to the next 0 value. Is it possible to do it in matlab to save time because doing it manually on excel takes a lot of time. Help is really appreciated.
Attached here is an image and and the excel file for reference

Best Answer

t=readtable('nim123.xlsx');          % read the data
t(all(isnan(t{:,:}),2),:)=[];        % remove the blank lines at beginning
ix=find(t.velocity==0);              % locate zero row locations
ix=[ix;height(t)];                   % augment with end of file record number
mn=arrayfun(@(i) mean(t{ix(i):ix(i+1)-1,1:6}),(1:numel(ix)-1),'UniformOutput',false).'; % compute group means
NB: Pass 1:length(ix)-1 to not run off the end of the indexing vector.
For first little of your file, reproduces results above...
>> format bank   % stop auto-scale so see couple decimal places all values
>> mn=cell2mat(arrayfun(@(i) mean(t{ix(i):ix(i+1)-1,1:6}),(1:4),'UniformOutput',false).'); % first four groups
>> mn
mn =
       1034.93       -128.30        -50.31         18.33        455.72        142.53
       1040.27       -131.56        -48.39         38.88        444.44        137.31
       1043.06       -135.68        -48.12         40.65        451.27        140.81
       1010.54       -132.21        -39.69         40.60        435.18        134.20
>>
where went ahead and converted the cell array to array of doubles.
The location relative to the original data is just ix(1:end-1) of course.
Related Question