Creating hourly average data with geographic coordinates to produce 3D matrix

I have 4 column matrix, which include irregular time, temperature, latitude and longitude. I want to creat 3-D matrix such that I have hourly averages of temperature within particular latitude and longitude grid. Thereby producing a 3D matrix of latitude x longitude x hourly averaged temperature.

Please find the attached dataset.

Thanks in advance!

[KSSV]

How are the times given? In what units they are? IT can be achieved, it is not a tough task.

[dpb]

Are there missing hours that would have to be interpolated/filled first (or if missing ignored)?

[Aristo Pacino]

Hi @KSSV, please find the attached data file with this reply. They are using hourly decimal units.

Hello @dpb, they are sometimes missing hours and can be considered as NaN. I have attached the data files with this reply .

Thanks for your interest in the my question.

Answer 1

[dpb]

rowfun to the rescue yet again... :)

tData=readtable();
longrid=-180:10:180;
latgrid=-90:10:90;
[~,~,~,tData.lon_g,tData.lat_g]=histcounts2(tData.lon,tData.lat,longrid,latgrid);
tGroup=rowfun(@mean,tData,'InputVariables','temp', ...
                          'GroupingVariables',{'hour','lon_g','lat_g'}, ...
                          'OutputVariableNames','temp');

produces:

>> [head(tGroup,5);tail(tGroup,5)]
ans =
  10×5 table
    hour    lon_g    lat_g    GroupCount     temp 
    ____    _____    _____    __________    ______
      0       2       14          1          89.94
      0       3        5          1          99.68
      0       3        7          1          87.31
      0       3        9          1         100.86
      0       3       15          1          71.35
     23      35       14          2          89.96
     23      35       15          1         101.81
     23      35       16          1         107.89
     23      35       18          1          101.6
     23      36        9          1           91.3
>> 

[dpb]

ADDENDUM:

A check point I looked at to be sure was what thought was...

>> tData(iswithin(tData.lon,-170,-160)&tData.hour==0,:)
ans =
  1×6 table
    hour     lat       lon      temp     lon_g    lat_g
    ____    _____    _______    _____    _____    _____
     0      41.56    -160.76    89.94      2       14  
>>

The second lon bin for the zero hour had only one element in the array -- and it matches the result found...

[dpb]

Oh, once last nicety -- the default table uses the group indices; to get the midpoints of the bins for visual cues and lookup purposes later,

lon=movmean(longrid,2,'Endpoints',"discard").';
lat=movmean(latgrid,2,'Endpoints',"discard").';
tGroup.lat=lat(tGroup.lat_g);
tGroup.lon=lon(tGroup.lon_g);
tGroup=tGroup(:,[4 1 6 7 5]);

to rearrange and keep the coordinates only rather than bins...leaves with something like

>> head(tGroup)
ans =
  8×5 table
    GroupCount    hour    lat    lon      temp 
    __________    ____    ___    ____    ______
        1          0       45    -165     89.94
        1          0      -45    -155     99.68
        1          0      -25    -155     87.31
        1          0       -5    -155    100.86
        1          0       55    -155     71.35
        1          0      -45    -145     102.3
        1          0      -35    -145    102.49
        1          0      -25    -145    101.53
>> 

[Aristo Pacino]

Wow @dpb! Thanks for such a comprehensive code of yours. Really appreciate it!

However, the main objective of this question is to somehow create a 3D matrix in order to achieve a spatial correlation with another matrix as stated in this answer of this query: https://de.mathworks.com/matlabcentral/answers/358844-spatial-correlation-between-two-variables#answer_294248.

I am really sorry @dpb, I am asking for too much. I am struggling with this for quite sometime.

Thanks once again for such an phenomenal work from you.

[dpb]

That's a trivial exercise, too...although need the other array.

Presuming you want this also as in that response by hour (which was in a plane of a 3D array in that case by day, apparently), just use the hour grouping variable again.

If you have corr2 from the Image Processing TB, it's the simplest since it is set up to expect a 2D array, but if you don't it doesn't matter since it's a global correspondence it's calculating and so is the same thing as corr(X(:),Y(:)) (*)

You don't have the other array in the table so can't use rowfun directly, but the findgroups splitapply pair does the same thing or, even, venerable accumarray

(*) ILLUSTRATION EQUIVALENCE

I=imread('corn.tif',1);         % get an image to play with for data
I=double(I(:)); J=medfilt1(I);  % convert double and 1D vector
Im=I-mean(I); Jm=J-mean(J);     % subtract mean from each
R=dot(Im,Jm)/sqrt(dot(Im,Im)*dot(Jm,Jm))  % compute R from definition of corr2
R =
    0.6890
>> corr(Im,Jm)      % compare to corr()
ans =
    0.6890
>> 

https://www.mathworks.com/help/images/ref/corr2.html#f1-227958 gives definition of corr2

(Obviously, from the above machinations I don't have the Image TB, not being into image processing...)

[Aristo Pacino]

Thanks @dpb, but I would like to have gridwise [latitude x longitude] correlation coefficient so as to finally have a contour plot!

[dpb]

Well, that's not the Answer of the link... <VBG>

Then see

doc xcorr2

It's in Signal Processing TB

Answer 2

[KSSV]

T = readtable('New Text document.txt') ; 

t = T.hour ; 
lon = T.lon ; 
lat = T.lat ;
temp = T.temp ; 

[c,ia,ib] = unique(t) ;
n = length(c) ; 
iwant = cell(n,1) ; 
for i = 1:n
    iwant{i} = [t(ib==i) lon(ib==i) lat(ib==i) temp(ib==i)] ; 
end

[Aristo Pacino]

Hi @KSSV, thank you very much but this is not what I desired. Sorry, I might be unclear but I wanted something for e.g within the longitude [-180 -170] and latitude [0 10] grid, I wanted the temperature to be sampled every one hour. So that I have some what like n x m x z matrix, where n is longitudinal size with grid size of 10 degrees, m is latitudinal size with grid size every 10 degrees and z is temperature size at every one hour sampling frequency.

I would be very happy if you could help me further but much thanks for your effort.

Cheers