Info

Questa domanda è chiusa. Riaprila per modificarla o per rispondere.

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

1 visualizzazione (ultimi 30 giorni)
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 :)
  3 Commenti
per isakson
per isakson il 6 Gen 2015
"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
Sören il 6 Gen 2015
Modificato: Sören il 6 Gen 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

Risposte (4)

Guillaume
Guillaume il 6 Gen 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 il 6 Gen 2015
Modificato: Image Analyst il 6 Gen 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);
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.

Ajay Pherwani
Ajay Pherwani il 6 Gen 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 il 6 Gen 2015
Modificato: Andrei Bobrov il 6 Gen 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 Commento
Guillaume
Guillaume il 6 Gen 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