Spatial-Temporal Contour plot from multi excel files

Hello there,

I have four groups of datasets (in excel files, attached). Let’s say they are: data_A1, data_A2, data_A3, data_A4. Each of them contains 3 parameters, namely ax, ay, and az. To make it easily imagined in space-time perspective, let’s say ax is the variable of time and ay is the variable of depth and az is the variable I'd like to plot its spatial-temporal variability. My intention is to get a contour plot of those datasets, representing the spatial-temporal evolution of variable az. So, the contour will plot data ax in x-axis, ay in y-axis, and az value presented by the colormap.

Note that:

• There may be a possibility of overlapping/similar values of ay (depth) between data_A1, data_A2, data_A3, data_A4. In this case, I just need the first record to be considered; or maybe if any other proper handling you could suggest, please welcome.
• There also may be blank values of az.

Thanks!

Answer 1

[Star Strider]

I am not certain what you want.

As a first approach, this presents them as surfaces —

files = dir('*.xlsx');
for k = 1:numel(files)
    filename = files(k).name
    T{k} = readtable(filename);
    T{k}
    [Tstart,Tend] = bounds(T{k}.ax)
    dn{k} = datenum(T{k}.ax);
    DN{k} = linspace(min(dn{k}), max(dn{k}), numel(dn{k}));
    AY{k} = linspace(min(T{k}.ay), max(T{k}.ay), numel(T{k}.ay))
end

filename = 'data_A1.xlsx'

ans = 619x3 table
      ay        az               ax         
    ______    ______    ____________________

     738.4     6.103    12-Feb-2000 16:50:00
    737.94    6.1639    12-Feb-2000 17:00:00
    737.97    6.2012    12-Feb-2000 17:10:00
    738.03    6.2016    12-Feb-2000 17:19:59
    737.97    6.2027    12-Feb-2000 17:30:00
    737.91    6.2093    12-Feb-2000 17:39:59
    737.98    6.2048    12-Feb-2000 17:50:00
       738     6.179    12-Feb-2000 18:00:00
       738    6.1685    12-Feb-2000 18:10:00
    737.91    6.1394    12-Feb-2000 18:20:00
    737.93    6.1043    12-Feb-2000 18:29:59
    737.86    6.0869    12-Feb-2000 18:40:00
    737.81    6.0737    12-Feb-2000 18:49:59
    737.83    6.0665    12-Feb-2000 19:00:00
    737.77    6.0663    12-Feb-2000 19:09:59
    737.79    6.0705    12-Feb-2000 19:20:00

Tstart = datetime
   12-Feb-2000 16:50:00

Tend = datetime
   16-Feb-2000 23:50:00

AY = 1x1 cell array
    {[736.2120 736.2302 736.2484 736.2666 736.2847 736.3029 736.3211 736.3393 736.3575 736.3757 736.3938 736.4120 736.4302 736.4484 736.4666 736.4848 736.5030 ... ] (1x619 double)}

filename = 'data_A2.xlsx'

ans = 616x3 table
      ay        az               ax         
    ______    ______    ____________________

     998.8    4.7603    12-Feb-2000 16:40:00
    997.14    4.7809    12-Feb-2000 16:50:00
    997.08    4.8233    12-Feb-2000 16:59:59
    997.15     4.828    12-Feb-2000 17:10:00
    997.24    4.8172    12-Feb-2000 17:19:59
    997.26    4.8216    12-Feb-2000 17:30:00
    997.26    4.8499    12-Feb-2000 17:39:59
    997.22    4.8599    12-Feb-2000 17:50:00
    997.26    4.8561    12-Feb-2000 18:00:00
    997.26    4.8569    12-Feb-2000 18:10:00
    997.26    4.8593    12-Feb-2000 18:20:00
    997.22    4.8634    12-Feb-2000 18:29:59
    997.13      4.89    12-Feb-2000 18:40:00
    997.13    4.8933    12-Feb-2000 18:49:59
    997.14    4.9276    12-Feb-2000 19:00:00
    997.07    4.9166    12-Feb-2000 19:09:59

Tstart = datetime
   12-Feb-2000 16:40:00

Tend = datetime
   16-Feb-2000 23:10:00

AY = 1x2 cell array
    {1x619 double}    {1x616 double}

filename = 'data_A3.xlsx'

ans = 634x3 table
      ay        az               ax         
    ______    ______    ____________________

    1407.9    3.2265    12-Feb-2000 16:40:00
    1408.4    3.2062    12-Feb-2000 16:50:00
    1408.8    3.1958    12-Feb-2000 16:59:59
      1409    3.1159    12-Feb-2000 17:10:00
    1409.1    3.0674    12-Feb-2000 17:19:59
    1409.1    3.1072    12-Feb-2000 17:30:00
    1409.2    3.1402    12-Feb-2000 17:39:59
    1409.2    3.1925    12-Feb-2000 17:50:00
    1409.2     3.093    12-Feb-2000 18:00:00
    1409.2    3.1666    12-Feb-2000 18:10:00
    1409.2    3.1888    12-Feb-2000 18:20:00
    1409.1    3.1839    12-Feb-2000 18:29:59
    1409.1    3.1962    12-Feb-2000 18:40:00
    1409.1    3.2176    12-Feb-2000 18:49:59
    1409.1    3.1954    12-Feb-2000 19:00:00
    1409.1    3.2809    12-Feb-2000 19:09:59

Tstart = datetime
   12-Feb-2000 16:40:00

Tend = datetime
   17-Feb-2000 02:10:00

AY = 1x3 cell array
    {1x619 double}    {1x616 double}    {1x634 double}

filename = 'data_A4.xlsx'

ans = 817x3 table
      ay        az               ax         
    ______    ______    ____________________

    1808.7    2.4468    12-Feb-2000 16:40:00
    1808.7    2.4444    12-Feb-2000 16:50:00
    1809.2     2.438    12-Feb-2000 16:59:59
    1809.5    2.4302    12-Feb-2000 17:10:00
    1809.7    2.4162    12-Feb-2000 17:19:59
    1809.7    2.4082    12-Feb-2000 17:30:00
    1809.8    2.3998    12-Feb-2000 17:39:59
    1809.8    2.4149    12-Feb-2000 17:50:00
    1809.8    2.4166    12-Feb-2000 18:00:00
    1809.9     2.422    12-Feb-2000 18:10:00
    1809.9     2.432    12-Feb-2000 18:20:00
    1809.8    2.4315    12-Feb-2000 18:29:59
    1809.8     2.448    12-Feb-2000 18:40:00
    1809.7    2.4747    12-Feb-2000 18:49:59
    1809.7    2.4836    12-Feb-2000 19:00:00
    1809.8    2.4857    12-Feb-2000 19:09:59

Tstart = datetime
   12-Feb-2000 16:40:00

Tend = datetime
   18-Feb-2000 08:40:00

AY = 1x4 cell array
    {1x619 double}    {1x616 double}    {1x634 double}    {1x817 double}

for k = 1:numel(files)
    Fcn{k} = scatteredInterpolant(dn{k}, T{k}.ay, T{k}.az);
    [AX{k},AY{k}] = ndgrid(DN{k},AY{k});
    AZ{k} = Fcn{k}(AX{k},AY{k});
end

% figure
% hold on
for k = 1:numel(files)
    figure
    surfc(AX{k}, AY{k}, AZ{k}, 'EdgeColor','none')
    colormap(turbo)
    colorbar
    xlabel('ax')
    ylabel('ay')
    zlabel('az')
    title(extractBetween(files(k).name,'_','.'))
end

% hold off

It is necessary to use datenum here because scatteredInterpolant does not work with datetime arrays.

If plotted on the same axes, these appear as flat ribbons with respect to ‘az’, and lose their depth (at least in a relative sense). You can of course plot them as contour plots, however I am at a loss as to how to present them in a ‘spatio-temporal perspective’. Animating them will not show here, and it would be difficult to interpolate them over a smooth time scale in any event, in part because their sizes are not the same (although that could be standardised). The other option is to subtract them serially.

.

[Adi Purwandana]

Thank you @Star Strider for your suggestion. What I meant with spatio-temporal perspective is just plotting in x (time) vs y (depth) with contour plot of z indeed. It's just 2D plot, not 3D. Sorry for such confusing term. Anyway, if I made the data sizes are the same, will it be possible to contour plot all of them? Please check the new datasets attached (_rev). I cut the rows so that the sizes of all datasets are the same.

[Star Strider]

My pleasure!

The sizes can be made the same by defining the same lengths for all the and arrays, for example:

N = 600;

DN{k} = linspace(min(dn{k}), max(dn{k}), N);

AY{k} = linspace(min(T{k}.ay), max(T{k}.ay), N)

My problem is that I do not understand how you want to define ‘spatio-temporal perspective’.

Hypothesizing an approach —

files = dir('*.xlsx');
for k = 1:numel(files)
    filename = files(k).name
    T{k} = readtable(filename);
    % T{k}
    [Tstart,Tend] = bounds(T{k}.ax);
    dn{k} = datenum(T{k}.ax);
    N = 600;
    DN{k} = linspace(min(dn{k}), max(dn{k}), N);
    AY{k} = linspace(min(T{k}.ay), max(T{k}.ay), N);
end

filename = 'data_A1.xlsx'

filename = 'data_A2.xlsx'

filename = 'data_A3.xlsx'

filename = 'data_A4.xlsx'

for k = 1:numel(files)
    filename = files(k).name
    Fcn{k} = scatteredInterpolant(dn{k}, T{k}.ay, T{k}.az);
    [AX{k},AY{k}] = ndgrid(DN{k},AY{k});
    AZ{k} = Fcn{k}(AX{k},AY{k});
    Check_Sizes = [size(AX{k}); size(AY{k}); size(AZ{k})]
end

filename = 'data_A1.xlsx'

Check_Sizes = 3×2

600 600 600 600 600 600

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

filename = 'data_A2.xlsx'

Check_Sizes = 3×2

600 600 600 600 600 600

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

filename = 'data_A3.xlsx'

Check_Sizes = 3×2

600 600 600 600 600 600

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

filename = 'data_A4.xlsx'

Check_Sizes = 3×2

600 600 600 600 600 600

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

%
for k = 1:numel(files)
    figure
    contourf(AX{k}, AY{k}, AZ{k})
    colormap(turbo)
    colorbar
    Ax = gca;
    xt = Ax.XTick;
    xtl = datetime(xt, 'ConvertFrom','datenum')
    Ax.XTickLabel = compose('%s',xtl);
    Ax.XTickLabelRotation = 30;
    xlabel('ax')
    ylabel('ay')
    zlabel('az')
    title(extractBetween(files(k).name,'_','.'))
end

xtl = 1x8 datetime array
Columns 1 through 7

   13-Feb-2000 00:00:00   13-Feb-2000 12:00:00   14-Feb-2000 00:00:00   14-Feb-2000 12:00:00   15-Feb-2000 00:00:00   15-Feb-2000 12:00:00   16-Feb-2000 00:00:00

Column 8

   16-Feb-2000 12:00:00

xtl = 1x8 datetime array
Columns 1 through 7

   13-Feb-2000 00:00:00   13-Feb-2000 12:00:00   14-Feb-2000 00:00:00   14-Feb-2000 12:00:00   15-Feb-2000 00:00:00   15-Feb-2000 12:00:00   16-Feb-2000 00:00:00

Column 8

   16-Feb-2000 12:00:00

xtl = 1x5 datetime array
   13-Feb-2000   14-Feb-2000   15-Feb-2000   16-Feb-2000   17-Feb-2000

xtl = 1x6 datetime array
   13-Feb-2000   14-Feb-2000   15-Feb-2000   16-Feb-2000   17-Feb-2000   18-Feb-2000

figure
hold on
for k = 1:numel(files)
    contourf(AX{k}, AY{k}, AZ{k})
    colormap(turbo)
    colorbar
    xlabel('ax')
    ylabel('ay')
    % zlabel('az')
    % title(extractBetween(files(k).name,'_','.'))
end
hold off
axis('padded')
Ax = gca;
xt = Ax.XTick;
xtl = datetime(xt, 'ConvertFrom','datenum')

xtl = 1x6 datetime array
   13-Feb-2000   14-Feb-2000   15-Feb-2000   16-Feb-2000   17-Feb-2000   18-Feb-2000

Ax.XTickLabel = compose('%s',xtl);
Ax.XTickLabelRotation = 30;

figure
hold on
for k = 1:numel(files)
    contour(AX{k}, AY{k}, AZ{k})
    colormap(turbo)
    colorbar
    xlabel('ax')
    ylabel('ay')
    % zlabel('az')
    % title(extractBetween(files(k).name,'_','.'))
end
hold off
axis('padded')
Ax = gca;
xt = Ax.XTick;
xtl = datetime(xt, 'ConvertFrom','datenum')

xtl = 1x6 datetime array
   13-Feb-2000   14-Feb-2000   15-Feb-2000   16-Feb-2000   17-Feb-2000   18-Feb-2000

Ax.XTickLabel = compose('%s',xtl);
Ax.XTickLabelRotation = 30;

The contour plots do not overlap because even though the matrix sizes are the same, the ‘ax’ and ‘ay’ variable values differ significantly.

.

[Adi Purwandana]

The last plot is almost the one I looking for indeed @Star Strider. Anyway, can the pcolor function (and some adjustments maybe) fix it to get interpolated contours between depths (ay)?

[Walter Roberson]

@Adi Purwandana

pcolor() is internally surf() and setting the z coordinates to 0 and setting the view to above.

Because it is surf(), it takes the input coordinates as being the coordinates of vertices and it interpolates the face color according the the color of four vertices.

pcolor() is not suitable for interpolating contours.

[Adi Purwandana]

Any other way, the plot of them separately was also looks make sense for me indeed if the range of values is so large difference to put them all into one frame. And I'm just thinking if we can grid the data value of az into cells of ax and ay (for example daily mean of ax), so that properly to look at.

Something like:

[Star Strider]

I am still not certain what you want, however the heatmap plot gave me the idea to use tiledlayout here.

See if this does what you want —

files = dir('*.xlsx');
for k = 1:numel(files)
    filename = files(k).name
    T{k} = readtable(filename);
    % T{k}
    [Tstart,Tend] = bounds(T{k}.ax);
    dn{k} = datenum(T{k}.ax);
    N = 600;
    DN{k} = linspace(min(dn{k}), max(dn{k}), N);
    ayv{k} = linspace(min(T{k}.ay), max(T{k}.ay), N);
end

filename = 'data_A1.xlsx'

filename = 'data_A2.xlsx'

filename = 'data_A3.xlsx'

filename = 'data_A4.xlsx'

for k = 1:numel(files)
    filename = files(k).name;
    Fcn{k} = scatteredInterpolant(dn{k}, T{k}.ay, T{k}.az);
    [AX{k},AY{k}] = ndgrid(DN{k},ayv{k});
    AZ{k} = Fcn{k}(AX{k},AY{k});
    % Check_Sizes = [size(AX{k}); size(AY{k}); size(AZ{k})]
end

%
for k = 1:numel(files)
    figure
    [cm{k},h] = contourf(AX{k}, AY{k}, AZ{k});
    LL{k} = h.LevelList
    colormap(turbo)
    colorbar
    Ax = gca;
    xt = Ax.XTick;
    xtl = datetime(xt, 'ConvertFrom','datenum')
    Ax.XTickLabel = compose('%s',xtl);
    Ax.XTickLabelRotation = 30;
    xlabel('ax')
    ylabel('ay')
    zlabel('az')
    title(extractBetween(files(k).name,'_','.'))
end

LL = 1x1 cell array
    {[5.3561 5.4000 5.6000 5.8000 6 6.2000 6.4000 6.6000 6.8000 7 7.2000]}

xtl = 1x8 datetime array
Columns 1 through 7

   13-Feb-2000 00:00:00   13-Feb-2000 12:00:00   14-Feb-2000 00:00:00   14-Feb-2000 12:00:00   15-Feb-2000 00:00:00   15-Feb-2000 12:00:00   16-Feb-2000 00:00:00

Column 8

   16-Feb-2000 12:00:00

LL = 1x2 cell array
    {[5.3561 5.4000 5.6000 5.8000 6 6.2000 6.4000 6.6000 6.8000 7 7.2000]}    {[3.6265 4 4.5000 5 5.5000 6]}

xtl = 1x8 datetime array
Columns 1 through 7

   13-Feb-2000 00:00:00   13-Feb-2000 12:00:00   14-Feb-2000 00:00:00   14-Feb-2000 12:00:00   15-Feb-2000 00:00:00   15-Feb-2000 12:00:00   16-Feb-2000 00:00:00

Column 8

   16-Feb-2000 12:00:00

LL = 1x3 cell array
    {1x11 double}    {[3.6265 4 4.5000 5 5.5000 6]}    {1x9 double}

xtl = 1x5 datetime array
   13-Feb-2000   14-Feb-2000   15-Feb-2000   16-Feb-2000   17-Feb-2000

LL = 1x4 cell array
    {1x11 double}    {[3.6265 4 4.5000 5 5.5000 6]}    {1x9 double}    {1x11 double}

xtl = 1x6 datetime array
   13-Feb-2000   14-Feb-2000   15-Feb-2000   16-Feb-2000   17-Feb-2000   18-Feb-2000

figure
tiledlayout(4,1, TileSpacing='none')
for k = 1:numel(files)
    nexttile
    contourf(AX{k}, AY{k}, AZ{k})
    colormap(turbo)
    Ax = gca;
    Ax.XTickLabel = [];
    Ax.YTickLabel = [];
    axis('square')
    fn = extractBetween(files(k).name,'_','.');
    text(min(DN{k}), mean(ayv{k}), sprintf('\\bf%s\\rm: %s  ', fn{:}, datetime(mean(DN{k}),ConvertFrom='datenum', Format='dd-MMM-yyyy HH:mm')), 'Horiz','right')
end

The tiledlayout funciton allows for different arrangements of the tiles. See the documentation for that. (Note that if you change them, the text call will also have to change, if you choose to use it.)

.

[Adi Purwandana]

Great! Thank you @Star Strider and @Walter Roberson for the advice.

[Star Strider]

As always, my pleasure!