Sub-sampling with mean value of a datasets including time

Question

Adi Purwandana 2024-10-11

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2159150-sub-sampling-with-mean-value-of-a-datasets-including-time

评论： Star Strider 2024-10-12

data_sheet.xlsx

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!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Star Strider 2024-10-11

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2159150-sub-sampling-with-mean-value-of-a-datasets-including-time#answer_1531020

编辑：Star Strider 2024-10-12

在 MATLAB Online 中打开

data_sheet.xlsx

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’.

.

4 个评论
显示 2更早的评论隐藏 2更早的评论

Star Strider 2024-10-12

在 MATLAB Online 中打开

data_sheet.xlsx

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 2024-10-12

Awesome! Thank you very much @Star Strider!

Star Strider 2024-10-12

As always, my pleasure!

请先登录，再进行评论。

Sub-sampling with mean value of a datasets including time

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论
显示 2更早的评论隐藏 2更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Sub-sampling with mean value of a datasets including time

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论 显示 2更早的评论隐藏 2更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论