averaging from excel file

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.

Answer 1

[Oleg Komarov]

% 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

Answer 2

[Walter Roberson]

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?

[Brandon]

Sorry, answered your question in the wrong place. There are five sections in each column and 29 columns.

Answer 3

[Brandon]

There are five sections in each column and 29 columns.

[Oleg Komarov]

Do you have the image processing toolbox?

[Brandon]

Not on my home computer. I have access to it at school.

Answer 4

[Norman Johnson]

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.

Answer 5

[Jan]

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

Answer 6

[Image Analyst]

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

Answer 7

[Brandon]

Thanks to everyone for the help! Really appreciate it. I was ready to throw my laptop across the room.

Brandon