averaging from excel file
3 次查看(过去 30 天)
显示 更早的评论
Hello! What I have is an excel file that looks something like this:
1 0 3 0
2 0 3 0
5 0 2 1
8 3 0 6
3 2 0 2
0 5 0 8
0 4 0 6
0 0 1 0
0 0 2 0
3 0 3 0
4 2 7 0
6 9 2 0
8 3 0 0
0 2 0 5
0 0 0 6
0 0 0 8
0 0 0 3
What I want to do is average the sections of non-zero numbers together in each column, so I would get a matrix like the following:
3.8 3.5 2.67 4.6
5.25 4.0 3.0 5.5
I've tried several different ways to no avail. Any help would be appreciated.
0 个评论
采纳的回答
Oleg Komarov
2011-8-2
% Import data
data = xlsread('test.xlsx');
szData = size(data);
nblocks = 2;
% Loop per each column
out = zeros(nblocks,szData(2));
for c = 1:szData(2)
tmp = findseq(double(data(:,c) ~= 0));
out(1:end,c) = arrayfun(@(x) mean(data(tmp(x,2):tmp(x,3),c)),1:nblocks);
end
0 个评论
更多回答(6 个)
Walter Roberson
2011-8-2
Will there always be exactly two sections of non-zero numbers? Or will there always be exactly the same number of sections amongst each of the columns?
Norman Johnson
2011-8-2
I am not sure what you are trying to do. If you are looking for looking for the average of the non-zero values located in specific 'groups' I would use a series of for loops combined with switch-case (or if-then) commands. It is more tedious than difficult.
0 个评论
Jan
2011-8-2
Data = [1 0 3 0;
2 0 3 0;
5 0 2 1;
8 3 0 6;
3 2 0 2;
0 5 0 8;
0 4 0 6;
0 0 1 0;
0 0 2 0;
3 0 3 0;
4 2 7 0;
6 9 2 0;
8 3 0 0;
0 2 0 5;
0 0 0 6;
0 0 0 8;
0 0 0 3];
nBlock = 2;
[m, n] = size(Data);
Result = zeros(nBlock, n);
for c = 1:n
v = [true, transpose(Data(:, c))==0, true];
ini = strfind(v, [true, false]);
fin = strfind(v, [false, true]) - 1;
for i = 1:nBlock
Result(i, c) = mean(Data(ini(i):fin(i), c));
end
end
0 个评论
Image Analyst
2011-8-3
Brandon: If you want to use the Image Processing Toolbox, here's how to do it in 4 lines (ignoring comments, lines to create sample data, and a statement to print the results):
Data = [1 0 3 0;
2 0 3 0;
5 0 2 1;
8 3 0 6;
3 2 0 2;
0 5 0 8;
0 4 0 6;
0 0 1 0;
0 0 2 0;
3 0 3 0;
4 2 7 0;
6 9 2 0;
8 3 0 0;
0 2 0 5;
0 0 0 6;
0 0 0 8;
0 0 0 3];
% He has says he always has exactly 5 groups in each of 29 columns.
% For convenience in creating sample data, let's just say it's 4 groups in
% 28 columns instead.
Data = repmat(Data, [2 7]);
% Now we have our sample starting data - a 34 row by 28 column array.
% Preallocate the array
meansArray = zeros(4, size(Data, 2));
% Here are the key 4 statements!!
% Find the means for each group in each column:
for col = 1 : size(Data, 2)
% Measure the non-zero runs of data in each column.
measurements = regionprops(Data(:,col) ~= 0, Data(:,col), 'MeanIntensity');
meansArray(:,col) = [measurements.MeanIntensity];
end
disp(meansArray);
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!