清空筛选条件
清空筛选条件

averaging from excel file

3 次查看（过去 30 天）
Brandon
Brandon 2011-8-2
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.

请先登录，再回答此问题。

采纳的回答

Oleg Komarov
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
Findseq can be found here

更多回答（6 个）

Walter Roberson
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?
  1 个评论
Brandon
Brandon 2011-8-2
Sorry, answered your question in the wrong place. There are five sections in each column and 29 columns.

请先登录，再进行评论。

Brandon
Brandon 2011-8-2
There are five sections in each column and 29 columns.
  2 个评论
Oleg Komarov
Oleg Komarov 2011-8-2
Do you have the image processing toolbox?
Brandon
Brandon 2011-8-2
Not on my home computer. I have access to it at school.

请先登录，再进行评论。

Norman Johnson
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.
Jan
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
Image Analyst
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);
Brandon
Brandon 2011-8-3
Thanks to everyone for the help! Really appreciate it. I was ready to throw my laptop across the room.
Brandon

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by