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

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 :)

[Image Analyst]

I'm not sure how you're getting 10 values from a data file that has one column for time and 6 for other numbers. Can you attach a file that has 2 or 3 different time points attached? Do you have multiple rows with the same timepoint? Can you read the file? If so, how, like csvread() or readtable() or xlsread() or importdata()?

[per isakson]

"The problem is now that i get 10 values for each force and torque for the exact same time points." &nbsp I don't understand this sentence

[Sören]

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

Answer 1

[Guillaume]

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');

Answer 2

[Image Analyst]

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);
  fprintf('\n');
end

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.

Answer 3

[Ajay Pherwani]

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

Answer 4

[Andrei Bobrov]

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');

[Guillaume]

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.