Regrid data from 2x2 to 1x1 resolution

I am trying to regrid this data from excel. I have long, lat, time and sst. I want to regrid from 2x2 to 1x1, i am getting the error feedback

"Subscripting a table using linear indexing (one subscript) or multidimensional indexing (three or more subscripts) is not supported. Use a row subscript and a variable subscript."

Please any idea how to navigate through this? The file is attached and the code i tried is written below. Thanks in advance.

t = readtable('C:\Users\hp\Desktop\DESKTOP DOCUMENTS\INTERPOLATION AND GRIDDING(MATLAB)\interp\example.xlsx')
lon=t(:,1);  % Load Longitude from excel
lat=t(:,2);  % Load Latitude from excel
date=t(:,3);  % Load time from excel
sst=t(:,4);  % Load sst from excel
new_lon=[120:1:180-1]; %120 degrees and 180 degrees 
new_lat=[20:1:60]';
for date = 1: size(sst, 3) %loop through each date
    new(:,:,date) =interp2(lon,lat,sst(:,:,date)',new_lon,new_lat);
end

[KSSV]

You have data of only one day in the file.

[CHARLES ADDEY]

Hi KSSV, i have gotten rid of the time, as i think it is not important, i have also tried another code which seems to have regridded the data, but i am not getting the output properly. I want the output to be like that of the excel file, such that i have lon, lat, and sst. Please take a look at the code and attached files

new code

clear all
file=dir('C:\Users\hp\Desktop\DESKTOP DOCUMENTS\INTERPOLATION AND GRIDDING(MATLAB)\interp\example.xlsx')
for I=1:length(file)
    data_insitu=xlsread('example.xlsx');
    [X Y]=meshgrid(120:1:170,25:1:60)
    x=data_insitu(:,1);
    y=data_insitu(:,2);
    z=data_insitu(:,3);
    Z(:,:,I)= griddata(x,y,z,X,Y);
    data(:,I)=reshape(Z(:,:,I),[36*51,3]);
end

I want the output to be like that of the excel file, such that i have lon, lat, and sst.

An output like this

lon lat sst

120 26 18.98

121 26 19.2

122 26 19.8

120 27 17.7

121 27 18.9

122 27 20.1

Thank you

Answer 1

[CHARLES ADDEY]

After much troubleshooting, this problem has been resolved using this code, I will close the case now. Thanks to all contributors.

clear all
file=dir('C:\Users\hp\Desktop\DESKTOP DOCUMENTS\INTERPOLATION AND GRIDDING(MATLAB)\interp\example.xlsx')
for I=1:length(file)
    data_insitu=xlsread('example.xlsx');
    [X Y]=meshgrid(120:1:170,25:1:60)   %Set grid    
    x=data_insitu(:,1);  % Load Longitude from excel
    y=data_insitu(:,2);  % Load Latitude from excel
    z=data_insitu(:,3);  % Load sst from excel
    Z=griddata(x,y,z,X,Y);   % interpolates data
    data_X=reshape(X,[1836,1]); %Reshape Longitude into 1836x1 
    data_Y=reshape(Y,[1836,1]); %Reshape Latitude into 1836x1
    data_Z=reshape(Z,[1836,1]); %Reshape sst into 1836x1
    XYZ = [data_X(:), data_Y(:),data_Z(:)]; %Merge the columns of lon, lat and sst to get a 1836x3 matrix
end

file = 

  0×1 empty struct array with fields:

    name
    folder
    date
    bytes
    isdir
    datenum

[Peter Perkins]

Charles, I'm late to this party, and if that code works for you, that's great. But I have a couple of observations.

xlsread is not recommended any more. readtable was the right way to go (see next). readmatrix might have worked if you specify a range that expludes the date column in your file, but that's kind of a pain to do because it's in the middle.

The problem in your original code was in getting the lat/lon out of the table. This

lon=t(:,1)

creates a table with one variable, which interp2 is not going to be happy with. What you wanted was lon = t.lon, and in fact, you don't even need to extract it:

new(:,:,date) = interp2(t.lon,t.lat,...)

Except that t.lat and t.lon, as in your file, are "columnized" versions of what are really matrices. So maybe either

interp2(reshape(t.lon,n,m),reshape(t.lat,n,m)...)

or

interp2(unique(t.lon),unique(t.lat),...)

Except that your file is 158 rows, so ... lat and lon do not have the right number of values to be n-by-m. You've used griddata, which is for scattered data. Maybe that's why. It's not clear that using griddata on data that are already on a grid is a good idea, because although it works, interp2 would use that knowledge. Maybe it's fine, I'm not sure how griddata works internally. So I don't know what your real data look like. It helps to provide something accurate.

Then there's SST. your file only has one day, while your original code loops over days. If your intent is to upsample each day separately, then you do need a loop as in your original code. Actually, this seems like a candidate for a grouped rowfun. Tables often make things easier, no exception here.

What you need first is a function to do the upsampling on one day's data. To use interp2, it would look something like:

function [latNew,lonNew,sstNew] = fun(lat,lon,sst)
[latNew,lonNew] = meshgrid(120:1:170,25:1:60);
lat = reshape(lat,29,6);
lon = reshape(lon,29,6);
sst = reshape(sst,29,6);
sstNew = interp2(lat,lon,sst,latNew,lonNew);
latNew = latNew(:);
lonNew = lonNew(:);
sstNew = sstNew(:);

but of course your sample data are the wrong length so that won't work. Using griddata, it's probably as simple as

function tNew = tmp1
t = readtable("example.xlsx");
tNew = rowfun(@fun,t,"GroupingVariable","date","OutputVariableNames",["lat" "lon" "sst"]);

function [latNew,lonNew,sstNew] = fun(lat,lon,sst)
[latNew,lonNew] = meshgrid(120:1:170,25:1:60);
latNew = latNew(:);
lonNew = lonNew(:);
sstNew = griddata(lat,lon,sst,latNew,lonNew);

except of course that returns mostly NaNs because your sample data don't span the lat/lon range it interpolates to.

[CHARLES ADDEY]

@Peter Perkins Thanks for your very detailed and explicit comment.

Answer 2

[Matt J]

Don't read in the imported data as a table. Read it in as a matrix.

[Walter Roberson]

Also, after either readtable or readmatrix, there is no way that any of the variables can end up 3d. Both of these functions return column vectors for each variable, with the columns possibly being cells to hold text. (Exception: if you use specific configuration steps, a variable could end up being a 2d character array instead of a cell array of character vectors. But none of the options allow the functions to read a variable as 3d)

[CHARLES ADDEY]

Thank you Matt and Walter, I have changed the importation style, and made some modification, please can you see my updated comment.