Sub-sampling with mean value of a datasets including time

Hello everyone,

I have an excel datasets containing parameters: data_x, data_y, and time in date format (see attached file). My intention is to get a sub-sampled datasets from those datasets, and plot them properly using scatter plot of data_x, data_y with colormap of the time. Please see the logic as follow:

Let's say, the new sub-sampled data is the mean of six rows as shown above. Anyone has suggestion to get it and also the scatter plots with time evolution of the colorbar?

Thank you!

Answer 1

[Star Strider]

I am not certain how the means whould be calculated. If you want to calculatee the means of every six rows, that is relatively straightforward.

Try this —

T1 = readtable('data_sheet.xlsx')

T1 = 125374x3 table
    data_x    data_y            time        
    ______    ______    ____________________

     693.6    5.9303    24-Sep-1900 16:21:41
    692.77    6.0661    24-Sep-1900 16:31:42
    692.53    5.9347    24-Sep-1900 16:41:42
    692.38    6.1196    24-Sep-1900 16:51:41
    692.41     6.388    24-Sep-1900 17:01:42
    692.46     6.457    24-Sep-1900 17:11:41
    692.45     6.509    24-Sep-1900 17:21:42
    692.47    6.5098    24-Sep-1900 17:31:41
    692.47    6.5127    24-Sep-1900 17:41:42
    692.56    6.4884    24-Sep-1900 17:51:41
    692.62    6.5359    24-Sep-1900 18:01:42
    692.73    6.5468    24-Sep-1900 18:11:42
    692.79    6.4673    24-Sep-1900 18:21:41
    692.87    6.4184    24-Sep-1900 18:31:42
    692.97    6.4411    24-Sep-1900 18:41:41
    693.06    6.6523    24-Sep-1900 18:51:42

VN = T1.Properties.VariableNames;
cols = size(T1,1)/6;
idx = ones(6,fix(cols)) .* (1:fix(cols));
numel(idx)

ans = 125370

T2 = T1(1:numel(idx),:);

T3xyc = accumarray(idx(:), (1:numel(idx)).',  [], @(x){mean(T2{x,1:2})});
T3xy = cell2mat(T3xyc);
T3dn = accumarray(idx(:), (1:numel(idx)).',  [], @(x)mean(datenum(T2{x,3})));
T3t = datetime(T3dn,'ConvertFrom','datenum');
T3 = table(T3xy(:,1), T3xy(:,2), T3t, 'VariableNames',VN)

T3 = 20895x3 table
    data_x    data_y            time        
    ______    ______    ____________________

    692.69    6.1493    24-Sep-1900 16:46:41
    692.55    6.5171    24-Sep-1900 17:46:42
    693.02    6.5385    24-Sep-1900 18:46:41
    693.65    6.5179    24-Sep-1900 19:46:41
    694.15     6.383    24-Sep-1900 20:46:42
    694.34    6.3639    24-Sep-1900 21:46:41
    693.87    6.4078    24-Sep-1900 22:46:41
    692.64    6.1501    24-Sep-1900 23:46:42
    691.97    6.1146    25-Sep-1900 00:46:41
    691.52    6.1638    25-Sep-1900 01:46:41
    691.76    6.0999    25-Sep-1900 02:46:42
    692.25    6.1637    25-Sep-1900 03:46:41
    692.54    6.1635    25-Sep-1900 04:46:41
    692.63    5.8761    25-Sep-1900 05:46:42
    693.07    6.3467    25-Sep-1900 06:46:41
    693.44     6.304    25-Sep-1900 07:46:41

[tmin,tmax] = bounds(T3.time)

tmin = datetime
   24-Sep-1900 16:46:41

tmax = datetime
   12-Feb-1903 06:46:41

doy = day(T3.time,  'dayofyear');
doy = doy + cumsum([0; diff(doy)<0])*max(doy);
doyHH = doy + hour(T3.time)/24 + minute(T3.time) + second(T3.time);     % Day (Of Year) + Hour (24 Hour) + Minute + Second

% figure
% plot(T1.t, doy)
% grid

figure
scatter(T3.data_x, T3.data_y, 10, doyHH, 's', 'filled')
grid
colormap(turbo)
hcb = colorbar;
cbtixidx = ismember(doyHH, hcb.Ticks);
hcb.TickLabels = string(T1.time(cbtixidx));
hcb.FontSize = 8;
xlabel('data\_x')
ylabel('data\_y')
title('Scatter Plot Coloured By Date')

figure
scatter3(T3.data_x, T3.data_y, doyHH, 10, doyHH, 's', 'filled')
grid on
colormap(turbo)
hcb = colorbar;
cbtixidx = ismember(doyHH, hcb.Ticks);
hcb.TickLabels = string(T1.time(cbtixidx));
hcb.FontSize = 8;
Ax = gca;
% Ax.ZTick
ztixidx = ismember(doyHH, Ax.ZTick);
tl = interp1(doyHH, T3.time, Ax.ZTick, 'linear','extrap');
Ax.ZTickLabel = string(tl);
Ax.ZAxis.FontSize = 8;
xlabel('data\_x')
ylabel('data\_y')
zlabel('Time')
title('3D Scatter Plot Coloured By Date')

EDIT — (12 Oct 2024 at 00:03)

Initially forgot to calculate the mean values of ‘T1’.

.

[Adi Purwandana]

Thanks @Star Strider. Here is my additional info: the initial datasets has a time resolution 10 minutes, so within an hour there were 6 data. My intention is sub-sampling them hourly, so there will be 1 data only per hour (this 1 data per hour is the mean value of 6 data then). With this treatment, I will reduce significantly the number of row/data so increase the readibility of the scatter plot. Is the code you have suggested in line with my intention?

Anyway, could you show the mm/yyyy date ticks in the colorbar?

Thank you.

[Star Strider]

If you want them hourly, that is straightforward, and actually easier. There are even functions (specifically retime) to do exactly that, and all of them should also work in R2022a, although I cannot be certain.

T1 = readtable('data_sheet.xlsx')

T1 = 125374x3 table
    data_x    data_y            time        
    ______    ______    ____________________

     693.6    5.9303    24-Sep-1900 16:21:41
    692.77    6.0661    24-Sep-1900 16:31:42
    692.53    5.9347    24-Sep-1900 16:41:42
    692.38    6.1196    24-Sep-1900 16:51:41
    692.41     6.388    24-Sep-1900 17:01:42
    692.46     6.457    24-Sep-1900 17:11:41
    692.45     6.509    24-Sep-1900 17:21:42
    692.47    6.5098    24-Sep-1900 17:31:41
    692.47    6.5127    24-Sep-1900 17:41:42
    692.56    6.4884    24-Sep-1900 17:51:41
    692.62    6.5359    24-Sep-1900 18:01:42
    692.73    6.5468    24-Sep-1900 18:11:42
    692.79    6.4673    24-Sep-1900 18:21:41
    692.87    6.4184    24-Sep-1900 18:31:42
    692.97    6.4411    24-Sep-1900 18:41:41
    693.06    6.6523    24-Sep-1900 18:51:42

VN = T1.Properties.VariableNames;
[tmin,tmax] = bounds(T1.time)

tmin = datetime
   24-Sep-1900 16:21:41

tmax = datetime
   12-Feb-1903 07:51:42

TT1 = table2timetable(T1, 'RowTimes','time')

TT1 = 125374x2 timetable
            time            data_x    data_y
    ____________________    ______    ______

    24-Sep-1900 16:21:41     693.6    5.9303
    24-Sep-1900 16:31:42    692.77    6.0661
    24-Sep-1900 16:41:42    692.53    5.9347
    24-Sep-1900 16:51:41    692.38    6.1196
    24-Sep-1900 17:01:42    692.41     6.388
    24-Sep-1900 17:11:41    692.46     6.457
    24-Sep-1900 17:21:42    692.45     6.509
    24-Sep-1900 17:31:41    692.47    6.5098
    24-Sep-1900 17:41:42    692.47    6.5127
    24-Sep-1900 17:51:41    692.56    6.4884
    24-Sep-1900 18:01:42    692.62    6.5359
    24-Sep-1900 18:11:42    692.73    6.5468
    24-Sep-1900 18:21:41    692.79    6.4673
    24-Sep-1900 18:31:42    692.87    6.4184
    24-Sep-1900 18:41:41    692.97    6.4411
    24-Sep-1900 18:51:42    693.06    6.6523

TT1r = retime(TT1, 'hourly','mean')

TT1r = 20896x2 timetable
            time            data_x    data_y
    ____________________    ______    ______

    24-Sep-1900 16:00:00    692.82    6.0127
    24-Sep-1900 17:00:00    692.47    6.4775
    24-Sep-1900 18:00:00    692.84    6.5103
    24-Sep-1900 19:00:00    693.48    6.5598
    24-Sep-1900 20:00:00    694.03    6.4283
    24-Sep-1900 21:00:00    694.37      6.37
    24-Sep-1900 22:00:00    694.06     6.402
    24-Sep-1900 23:00:00    693.05    6.1906
    25-Sep-1900 00:00:00    692.07    6.2303
    25-Sep-1900 01:00:00    691.62    6.0519
    25-Sep-1900 02:00:00    691.63    6.2197
    25-Sep-1900 03:00:00    692.09    6.0804
    25-Sep-1900 04:00:00    692.48      6.24
    25-Sep-1900 05:00:00    692.61    5.8237
    25-Sep-1900 06:00:00    692.85     6.126
    25-Sep-1900 07:00:00    693.44    6.4337

doy = day(TT1r.time,  'dayofyear');
doy = doy + cumsum([0; diff(doy)<0])*max(doy);
doyHH = doy + hour(TT1r.time)/24;     % Day (Of Year) + Hour (24 Hour) + Minute + Second

figure
scatter(TT1r.data_x, TT1r.data_y, 10, doyHH, 's', 'filled')
grid
colormap(turbo)
hcb = colorbar;
cbtixidx = ismember(doyHH, hcb.Ticks);
hcb.TickLabels = string(TT1r.time(cbtixidx));
hcb.FontSize = 8;
xlabel('data\_x')
ylabel('data\_y')
title('Scatter Plot Coloured By Date')

figure
scatter3(TT1r.data_x, TT1r.data_y, doyHH, 10, doyHH, 's', 'filled')
grid on
colormap(turbo)
hcb = colorbar;
cbtixidx = ismember(doyHH, hcb.Ticks);
hcb.TickLabels = string(TT1r.time(cbtixidx));
hcb.FontSize = 8;
Ax = gca;
% Ax.ZTick
ztixidx = ismember(doyHH, Ax.ZTick);
tl = interp1(doyHH, TT1r.time, Ax.ZTick, 'linear','extrap');
Ax.ZTickLabel = string(tl);
Ax.ZAxis.FontSize = 8;
xlabel('data\_x')
ylabel('data\_y')
zlabel('Time')
title('3D Scatter Plot Coloured By Date')

I did not code the colorbar ticks correctly earlier. That is fixeed now. (I initially wrote a version of this code to answer a question you previously posted several hours ago, although posted too late to be considered. I forgot to update the colorbar axis ticks to work with the timetable.)

.

[Adi Purwandana]

Awesome! Thank you very much @Star Strider!

[Star Strider]

As always, my pleasure!