How to subsample a dataset per week

Hi,

I would like to subsample a dataset in such a way as to keep only the data that was collected on the first sampled day of the week (I mean the first sampled day of the week here, not the first of the week). The desired out is desired_output.

How would I do that ?

Thank you,

j = [1 2 3 4 5 6 7 8 9]';
i = datetime({'2021-01-01', '2021-01-02', '2021-01-02' , '2021-01-04', '2021-01-04', '2021-01-08', '2021-01-09', '2021-01-15', '2021-01-21'})';
w = week(i)
t = table(j, i, w)

[a, b, c] = unique(t(:, [2:3]), 'rows'); 

desired_output = t([1,4:5,8:9],:)

Answer 1

[Shashank Gupta]

Hi,

I am assuming you have the table t and then you want to know first sampled data of the week. As far as I know there is no function to directly solve this, you might need to apply a brute force way, I have a reference script for you. it is unoptimised but you can refer and create one for your use case.

% Assuming we have table t with us. All the below processing done by considering we have t.

% idx will store all the first data sampled in each week.
idx = [];
uni = unique(t.w);
tt = 1;
for i=1:length(t.w)
    if(uni(tt)==t.w(i))
        idx = [idx i];
        tt=tt+1;
    end
end

% final_idx array will have the index which you required.
% final_idx is one step forward to idx array, it takes up
% all the data which can be sampled from the first day of the week.
final_idx = [];
for i=1:length(idx)
    final_idx = horzcat(final_idx,find(t.i==t.i(idx(i)))');
end

% desired output.
desired_output = t(final_idx,:);

I hope this helps.

Cheers.

[Blue]

Thank you

[Blue]

Actually it crashes if the length of the last week is longer than 1. This crashes for example, but if you remove the last entry of i and j then it works. Any ideas ?

Thank you,

j = [1 2 3 4 5 6 7 8 9 10 11 12]';
i = datetime({'2021-01-01', '2021-01-01', '2021-01-02', '2021-01-02' , '2021-01-04', '2021-01-04', '2021-01-08', '2021-01-09', ...
    '2021-01-15', '2021-01-21', '2021-09-09', '2021-09-09'})';
w = week(i)
t = table(j, i, w)

t = sortrows(t, 'i');

% Assuming we have table t with us. All the below processing done by considering we have t.
% idx will store all the first data sampled in each week.
idx = [];
uni = unique(t.w);
tt = 1;
for i=1:length(t.w)
    if(uni(tt)==t.w(i))
        idx = [idx i];
        tt=tt+1;
    end
end

% final_idx array will have the index which you required.
% final_idx is one step forward to idx array, it takes up
% all the data which can be sampled from the first day of the week.
final_idx = [];
for i=1:length(idx)
    final_idx = horzcat(final_idx,find(t.i==t.i(idx(i)))');
end
% desired output.
desired_output = t(final_idx,:)

[Blue]

This does the job:

j = [1 2 3 4 5 6 7 8 9 10 11 12]';
i = datetime({'2021-01-01', '2021-01-01', '2021-01-02', '2021-01-02' , '2021-01-04', '2021-01-04', '2021-01-08', '2021-01-09', ...
    '2021-01-15', '2021-01-21', '2021-09-09', '2021-09-09'})';
w = week(i)
t = table(j, i, w)

t = sortrows(t, 'i');

% Get unique values idx
[ia,ib,ic] = unique(t.w, 'first')

final_idx = [];
for i=1:length(ib)
    final_idx = horzcat(final_idx, find(t.i==t.i(ib(i)))');
end

% desired output.
desired_output = t(final_idx,:);