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

2 views (last 30 days)
Aramis on 10 Sep 2021
Commented: Aramis on 20 Oct 2021
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.

Jan on 10 Sep 2021
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 on 13 Oct 2021
@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).

Image Analyst on 29 Sep 2021
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 on 20 Oct 2021

R2021a

### Community Treasure Hunt

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

Start Hunting!

Translated by