MATLAB: Getting average value of grid point data

averagegridgriddatax coordinatey-coordinate

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.

Best Answer

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
Related Question