Calculation of the average of 80X80 blocks of 800X1280 Excel sheet data

Hi community,

I have an excel file including 800X1280 data cells. I want to calculate the average of every 80X80 blocks of this sheet (160 blocks in total). Starting from A1 to A80 for columns and 1 to 80 for rows as one block and have the average of it, and then move to next block, A81 to A160 for columns and 1 to 80 for rows and so on. At the end, I should have 10X16 average numbers.

I appreciate your help and answers for learning and understanding it.

Answer 1

[Jan]

Import the data. Then you have a [800 x 1280] matrix. It does not matter, if the data have been stored in an Excel file. Then:

data   = rand(800, 1280);
s      = size(data);
R      = reshape(data, 80, s(1) / 80, 80, s(2) / 80);
M      = sum(sum(R, 1), 3);
result = reshape(M, s / 80) / 6400;

This build blocks of the length 80 in the first and third dimension. Then the elements are summed up and divided by the block size. Reshaping remove the two singleton dimensions at the end.

[Aramis]

Hey Jan, Thanks for you reply.

I added xlswrite('average.xlsx' ,result); to get my result in excel and it worked great for a set of numbers 10X16 (well done, mate!)

Can we make it more fancy to read straight from excel files as well?

[Jan]

If the input is in Excel and the output must appear in Excel also, it would be the best idea to perform the operation in Excel, not in Matlab.

I'd prefer to skip Excel and to perform all operations inside Matlab. I have some good reasons to avoid using Excel for scientific work.

[Aramis]

Thanks for the reply again. That makes sense for working into Matlab, despite of having my raw data in Excel output. But there seems to be an issue for the code above:Here I calculated 2 blocks of data (80 X 80 cells) to check the code and it looks like the average numbers for manual calculation (sum of 80X80 numbers devided by 6400) are different. I tried to read the code again but not able to find the issue. I appreciate your reply, Jan.

[Jan]

I've tested the bove code and it works reliably.

For a faster processing see also: https://www.mathworks.com/matlabcentral/fileexchange/24812-blockmean

[Aramis]

Hi Jan, Thanks again for the info. May I ask you to explain these 5 lines of codes (5 equations) in details for learning purpose? I want to learn and use it for more examples for myself. I appreciate your time and help.

[Aramis]

For example I'd like to find the Max value within each of those 80X80 blocks. Or finding the gradient of changing the values in each of the blocks.

[Jan]

Getting the maximum value or a gradient are two completely different questions.

An explanation of the code in my answer:

data   = rand(800, 1280);  % Creation of some test data
s      = size(data);       % Get the dimensions, of course this is [800, 1200]

% Now the matrix is reshaped such that it has 4 dimensions:
R      = reshape(data, 80, s(1) / 80, 80, s(2) / 80);
% R has the size [80, X, 80, Y] now. X and Y depends on the original size.
% The number of elements does not change.

% To get the average, the sum over the 1st and 3rd dimension is calculated:
M      = sum(sum(R, 1), 3);
% M has the size [1, X, 1, Y] now.

% Divide M by 80*80=6400 and make it a [X, Y] matrix:
result = reshape(M, s / 80) / 6400;

[Aramis]

Thanks for the details, Jan! You are a very good tutor and this looks like a learning session for me and absolutely a joyful process. that "Or" means or another example is. I meant "I'd like to find other values for each blocks, such as max value and the gradient".

I have 2 questions: the code above, where it keep moving and jumps to another block in the lines?

What do you suggest to add in above code to find the gradient?

Thanks

[Jan]

You are welcome.

You can try it with a short example and a smaller window:

data   = randi(20, 4, 6);  % Some test data
win    = 2;
s      = size(data);
R      = reshape(data, win, s(1) / win, win, s(2) / win);
M      = sum(sum(R, 1), 3);
result = reshape(M, s / win) / (win * win);
data

data = 4×6

9 3 4 10 6 1 13 10 13 6 17 12 13 19 7 14 2 5 1 13 11 16 20 11

result

result = 2×3

8.7500 8.2500 9.0000 11.5000 12.0000 9.5000

How is the gradient between blocks defined mathematically?

[Aramis]

Hi Jan, Thanks for the example.

My purpose for the gradient of the blocks is as below: Consider each single block (total is still 80 X 80), if deviding one block into half, I want to calculate the gradient from the edge of the matrix toward the center. I illustrate it for a better visual understanding here.

[Aramis]

BTW, you code of the gradient worked perfectly beautiful. I have added a few lines to read data and then print result and it looks gorgeous. Thanks, Jan!

[Aramis]

Hi Jan, did you have any chance to check my answer to your question for defining the gradient between blcoks?

[Jan]

@Aramis: I do not understand, which mathematical operation you need. If the input is e.g. rand(4, 4), how is the "gradient" calculated?

[Aramis]

Let me try my purpose in a simple way: I would like to see the gradient of change for the values from the 48 edge blocks to one block inward. Example: Block 1 1 (top left corner) to block 2 1, how rapid the value changes? . Now block 1 2 to block 2 2, and so on.

I hope it makes sense. : ))

[Image Analyst]

@Aramis, not really. You accepted the answer so it seems like you got it all figured out. However your last comment makes it seem like it's not figured out yet. So I'm confused as to whether it's solved or not. If it's not, check out my Answer below where I use the function meant for this type of operation: blockproc().

[Jan]

@Aramis: I do not know a mathematical definition of "gradient of change" for a 80x80 matrix. So what do you want to calculate? Do you mean an elementwise difference?

[Aramis]

@Image Analyst the problem solved by Jan and it made me to make the original question even more complicated to find an answer.

Hi @Jan, Thanks for your reply. Here is the definition in other way, hope it makes sense to you: I want to find how fast the values change from the outer row of blocks to the inner blocks (in all 4 edges). Refering to the photo uploaded above, if I add one more inner rows of blocks, now moving prependicular from outer block to the inner, I'd like to calculate the gradient (how fast changes for each block).

Answer 2

[Image Analyst]

You can use blockproc() if you have the IMage Processing Toolbox. It will process a matrix in blocks. You can either have the blocks move over a certain distance (so they overlap), but the usual way is to have the processing window "jump" to the next block location over.

I'm attaching several blockproc demos. I'm using them to process images but it can be just a general purpose matrix instead.

You can have the operation be whatever you want : mean, median, standard deviation, gradient, or some custom function of your own.

In short, here's the relevant snippet for taking the mean in 80x80 blocks:

%===============================================================================================================================
% MEAN of 80 pixel by 80 pixel block
% Block process the image to replace every pixel in the 
% 80 pixel by 80 pixel block by the mean of the pixels in the block.
% Image will be smaller since we are not using ones() and so for each block
% there will be just one output pixel, not a block of 80 by 80 output pixels.
% Define a filter function that will give 1 pixel as output for every 2-D block of input.
meanFilterFunction = @(theBlockStructure) mean(theBlockStructure.data(:));
blockSize = [80, 80];
blockyImage = blockproc(grayImage, blockSize, meanFilterFunction);
[rows, columns] = size(blockyImage) % Let's see what size we got out

[Aramis]

Thanks fo your comment. Very helpful and interesting.