Getting average value of grid point data

I have a grid point data column in the form of 0000 where first two digits is x-coordinates and last two digits is y-coordinates as shown in filename 'gridid.xlsx'. For each grid point data column, I have corresponding snow depth data column. The data is for 10 years.

I have locationid with x-coordinate and y-coordinates as shown in 'location.xlsx'. The locationid is located anywhere inside the grid. I would like to run for loop for each year and get a average snow depth for each locationid. The average is calculated from four nearest corner grid point snow depth data in which locationid is located. Could anybody help me to figure it out what is the necessary steps to do? Any advice is highly appreciated.

[dpb]

[OP Answer moved to Comment -- dpb]

Any advice or suggestion to start this?

[dpb]

Not at all clear to me how the integer 2-digit gridid is supposed to relate to coordinates and determine anything about what are corners since the coordinates seem essentially uncorrelated to the locationid.

[Yen Su]

Sorry for not making clear. The four digit such 0000 is x-coordinate 0 and y-coordinate 0, similary 0001 is x-coordinate 0 and y-coordinate 1 and so on.

The locationid has x-coordinate and y-coordinate which lies in grid data. The average snow depth is calculated for that location id which surrounds by the four coordinate points (four coordinate points has four snow depth data).

Hope this clarifies you. Any suggestions is appreciated.

[David Hill]

Average of 0000,0001,0100,0101 would correlate to average snow depth for 0000?

[dpb]

If a grid is (one-digit, not two)...

00 01 02 03 ...
10 11 12 13 ...
20 21 22 23 ...
30 31 32 33 ...
...

and want to average blocks of four, the simplest would be to build the 2D array of accumulations by location and then use convolution or 2D filter or, if have Image Proc toolbox, blockproc

While they're randomaly arrange, it'll be slow and a pain to code...

Answer 1

[David Hill]

a=readtable('gridid.xlsx');
snowgrid=zeros(9,9,10);
for k=1:10
    c=zeros(10);
    i=ismember(a.year,num2str(k));
    b=cell2mat(a.gridid(i))';
    x=str2num(b(1:2,:)')+1;
    y=str2num(b(3:4,:)')+1;
    idx=sub2ind([10,10],x,y);%if the idx does not change, this could be a constant outside the loop
    c(idx)=a.snowDepth(i);
    c=movsum(c,2);
    c=movsum(c(2:end,:)',2);
    snowgrid(:,:,k)=c(2:end,:)'/4;%produces average snow per year at each grid location
end
b=readtable('location.xlsx');
for k=1:10
    c=snowgrid(min(ceil(b.X_coordinate(k)),9),min(ceil(b.Y_coordinate(k)),9),:);%location data above 9 is outside the grid
    averageSnowAtgrid(k,:)=c(:)';%produces matrix of average snow at grid location (row) per year (column)
end

[Yen Su]

Hi David,

This helps alot for me. But, I see in the code, calculating average snow at each grid location rather than its actual snow. I think something is misunderstood.

I would like to have snow for each locationid (1,2,3...10) for each year by taking average of four sorrounding bounded by four grid location.

For e.g. for location id '1', x-coordinate is 0.5 and y-coordinate is 0.8 which lies in grid bounded by 0000,0001,0100 and 0101. So for location id '1' snow depth is average of four grid point snow depth (0.5+0.4+0.4+1.2)/4 (At 0000 snow is 0.5, at 0001 snow is 0.4, at 0100 snow is 0.4 and at 0101 snow is 1.2).

Similarly, For location id '2', x-coordinate is 4.4 and y-coordinate is 9.9 (assume 9) which lies in grid bounded by 0408, 0508, 0509 and 0409. So for location id '2' snow depth is average of four sorrounding grid point snow depth (i.e. (0.5+0.4+0.4+1.2)/4) and so on.

For location where coordinate is greater that 9, I want to assume coordinate is at 9.

Also, how do i label year, locationid and snow in output table?

[David Hill]

No, the second for-loop does what you want.

averageSnowAtgrid =

    0.6250    0.8500    0.9750    1.1250    0.6250    0.8500    1.3750    0.8750    0.7000    1.0500
    0.6250    0.8500    0.9750    1.1250    0.6250    0.8500    1.3750    0.8750    0.7000    1.0500
    0.8750    0.7250    0.6000    1.3750    0.8750    0.7000    1.0500    0.8750    0.9750    1.1000
    0.6500    0.7000    1.0500    1.1750    0.6500    0.7000    1.2000    0.8750    0.6250    0.8500
    0.8500    0.9750    1.1250    0.6250    0.8500    0.9750    0.8750    0.7250    1.0500    1.1750
    0.6250    0.8500    0.9750    1.1250    0.6250    0.6000    1.3750    0.6500    0.7000    1.1000
    1.2000    0.8750    0.9750    0.5250    1.2000    1.1250    0.6250    0.6000    1.3750    1.6500
    1.0500    1.1750    0.6500    0.7000    1.0500    1.1750    0.9750    0.5250    0.9750    1.1250
    0.6500    0.7000    1.0500    1.1750    0.6500    0.6250    1.2000    0.9750    0.6250    0.8500
    1.3750    0.8750    0.7250    0.6000    1.3750    0.6500    0.7000    1.2000    0.8750    1.1000

The above answer (matrix) is the average snow at each location (10 locations rows) vs. years (10 columns)

[Yen Su]

Got it.

How do i include locationid in final matrix and label it?

[David Hill]

t=array2table(averageSnowAtgrid);
t.Properties.VariableNames={'year1','year2','year3','year4','year5','year6','year7','year8','year9','year10'};
t.Properties.RowNames={'id1';'id2';'id3';'id4';'id5';'id6';'id7';'id8';'id9';'id10'};
display(t);

[Yen Su]

Hi David,

Is there a way to dynamically assign the variable names for the above code?