This question is closed. Reopen it to edit or answer.

average of each 10 values of an external file and save in new file

1 view (last 30 days)
Hello everybody,
i am having the following problem: I have a file containing a lot of data (over 100.000 rows and 7 columns). The first column shows the time, the other six columns show force data at this exact time points (Fx, Fy, Fz and Tx, Ty, Tz). The problem is now that i get 10 values for each force and torque for the exact same time points. The solution is now to average these 10 values and give them out in a new file, so the final file only contains 1/10th of data of the old file and only one value for each force and torque for each time. How am I supposed to program this?
I am not able to write this program on my own since there are so many things I'd have to learn and I do not have any time for much programming right now. It's for a very important project of mine and I would really appreciate it if any of you could in any form help me. I really do not have that much time and this is very inconvenient.
Thank you so much in advance! If you have any questions please tell me!
Greetings, Sören :)
Sören on 6 Jan 2015
Edited: Sören on 6 Jan 2015
thank you for the answers so far! yes i have multiple rows for the same timepoint. to be precise: 10 rows. Let me just attach an example file i quickly wrote to demonstrate the case. the final file has the same structure, only that there are way more rows

Answers (4)

Guillaume on 6 Jan 2015
Assuming that all timepoints are replicated 10 times and always together:
oldcontent = readtable('example11.xls');
data = table2array(oldcontent);
data = reshape(data, 10, [], 7);
meandata = squeeze(mean(data));
newcontent = array2table(meandata, 'VariableNames', oldcontent.Properties.VariableNames);
writetable(newcontent, 'example11mean.xls');

Image Analyst
Image Analyst on 6 Jan 2015
Edited: Image Analyst on 6 Jan 2015
Here is one way:
numbers = xlsread('D:\Temporary stuff\example11.xls')
timePoints = numbers(:,1); % Get column 1
uniqueTimePoints = unique(timePoints);
% Allocate array for all of the means for all columns and all time points.
means = zeros(length(uniqueTimePoints), size(numbers, 2)-1);
for k = 1 : length(uniqueTimePoints)
% Get this time point.
thisTimePoint = uniqueTimePoints(k);
% Find out what rows have that particular time point.
theseRows = timePoints == thisTimePoint;
% Get the mean of all columns for that time point.
theseMeans = mean(numbers(theseRows, :), 1);
% Add on to our array that has means for all time points.
means(k, :) = theseMeans(2:end);
% Print the means out to the command window.
fprintf('The means for time point %d are\n', thisTimePoint);
fprintf('%.3f, ', theseMeans);
In the command window:
The means for time point 1 are
1.000, 2.400, 2.600, 2.600, 2.600, 2.400, 2.700,
The means for time point 2 are
2.000, 4.600, 4.300, 4.700, 4.400, 4.300, 4.400,
The means for time point 3 are
3.000, 6.100, 6.300, 6.200, 6.200, 6.000, 6.300,
The means for time point 4 are
4.000, 7.800, 7.800, 7.500, 7.600, 7.600, 8.200,
Note that the code is robust enough to handle time points that are missing or not adjacent to each other.

Ajay Pherwani
Ajay Pherwani on 6 Jan 2015
If it is not necessary to do programming and u just want the results just use the excel marco's , should be easy !!

Andrei Bobrov
Andrei Bobrov on 6 Jan 2015
Edited: Andrei Bobrov on 6 Jan 2015
oldcontent = readtable('example11.xls');
data = table2array(oldcontent);
[a,~,c] = unique(data(:,1));
[ii,jj] = ndgrid(c,1:size(data,2)-1);
d = data(:,2:end);
out = [a,accumarray([ii(:),jj(:)],d(:),[],@mean)];
newcontent = array2table(out, 'VariableNames', oldcontent.Properties.VariableNames);
writetable(newcontent, 'example11mean.xls');
  1 Comment
Guillaume on 6 Jan 2015
This is the best solution if the number of repeated time points is not consistent or they're not consecutive and it's a clever way to apply accumarray to several columns at once.
If the number of repeated time points is always the same and consecutive a simple reshape + mean is a lot more efficient.

Community Treasure Hunt

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

Start Hunting!

Translated by