Find unique groups in table with members both before and after date

Question

Marcus Glover 2022-9-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1808310-find-unique-groups-in-table-with-members-both-before-and-after-date

回答： Lola Davidson 2024-6-3

I have a table with strings in cells and a datetime. I need to find the unique groups of entries that occur before and after a particular date- June 1, 2022 in this case.

Here is my example table:

T=table({'Joe','Joe','Joe','Joe','Steve','Steve','Mary','Mary','Susan','Susan'}',...
    {'2022-02-03','2021-12-23','2022-08-01','2022-07-15','2021-02-08',...
    '2022-04-03','2022-09-03','2022-02-03','2022-08-13','2022-04-23'}',...
    {'Car','Van','Car','Van','Car','Car','Truck','Truck','Car','Van'}',...
        {'B213','G344','A342','G6767','N1356','A34','M235','A4567','G2345','A23'}',...
    'VariableNames',{'Name' 'Date' 'Type' 'Data'}); % my data comes as cells from a database
T.Date=datetime(T.Date,'InputFormat','yyyy-MM-dd'); % Converting to datetime
T = convertvars(T,@iscell,'categorical'); % Convert to categorical - preferred to cells? 
T
T = 10×4 table
    Name        Date        Type     Data 
    _____    ___________    _____    _____

    Joe      03-Feb-2022    Car      B213 
    Joe      23-Dec-2021    Van      G344 
    Joe      01-Aug-2022    Car      A342 
    Joe      15-Jul-2022    Van      G6767
    Steve    08-Feb-2021    Car      N1356
    Steve    03-Apr-2022    Car      A34  
    Mary     03-Sep-2022    Truck    M235 
    Mary     03-Feb-2022    Truck    A4567
    Susan    13-Aug-2022    Car      G2345
    Susan    23-Apr-2022    Van      A23  

Now I want to find the groups of matching Name and Type where at least one occurs after 01-Jun-2022 and at least on occurred before that date. I also need to carry the Data field with me. So the solution would be:

T_ans=T;
T_ans([5:6 9:10],:)=[];
sortrows(T_ans,[1 3])
ans = 6×4 table
    Name       Date        Type     Data 
    ____    ___________    _____    _____

    Joe     03-Feb-2022    Car      B213 
    Joe     01-Aug-2022    Car      A342 
    Joe     23-Dec-2021    Van      G344 
    Joe     15-Jul-2022    Van      G6767
    Mary    03-Sep-2022    Truck    M235 
    Mary    03-Feb-2022    Truck    A4567

With Steve elimanted becsue his matching Type dates were all before 01-Jun-2022, and Susan eliminated because she did not have 2 or more matching Types.

I'm not sure how to proceed- I am thinking of looping through all the unique 'Name' and 'Type' groups and seeing if there is one before and one after 01-Jun-2022, then listing all memebers if true.

I can;t figure out what to do after this though:

[a,b,c]=unique([T.Name,T.Type],'rows');
a
a = 6×2 categorical array
     Joe        Car   
     Joe        Van   
     Mary       Truck 
     Steve      Car   
     Susan      Car   
     Susan      Van   

Thanks for any help.

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

请先登录，再进行评论。

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

Answer 1

Chunru 2022-9-20

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1808310-find-unique-groups-in-table-with-members-both-before-and-after-date#answer_1056900

编辑：Chunru 2022-9-20

在 MATLAB Online 中打开

T=table({'Joe','Joe','Joe','Joe','Steve','Steve','Mary','Mary','Susan','Susan'}',...
    {'2022-02-03','2021-12-23','2022-08-01','2022-07-15','2021-02-08',...
    '2022-04-03','2022-09-03','2022-02-03','2022-08-13','2022-04-23'}',...
    {'Car','Van','Car','Van','Car','Car','Truck','Truck','Car','Van'}',...
        {'B213','G344','A342','G6767','N1356','A34','M235','A4567','G2345','A23'}',...
    'VariableNames',{'Name' 'Date' 'Type' 'Data'}); % my data comes as cells from a database
T.Date=datetime(T.Date,'InputFormat','yyyy-MM-dd'); % Converting to datetime
T = convertvars(T,@iscell,'categorical'); % Convert to categorical - preferred to cells? 
T
T = 10×4 table
    Name        Date        Type     Data 
    _____    ___________    _____    _____

    Joe      03-Feb-2022    Car      B213 
    Joe      23-Dec-2021    Van      G344 
    Joe      01-Aug-2022    Car      A342 
    Joe      15-Jul-2022    Van      G6767
    Steve    08-Feb-2021    Car      N1356
    Steve    03-Apr-2022    Car      A34  
    Mary     03-Sep-2022    Truck    M235 
    Mary     03-Feb-2022    Truck    A4567
    Susan    13-Aug-2022    Car      G2345
    Susan    23-Apr-2022    Van      A23  
uName = unique(T.Name);
uType = unique(T.Type);
idx = [];
for i=1:length(uName)
    for j=1:length(uType)
        T1 = T(T.Name == uName(i) & T.Type == uType(j), :);
        if (sum(T1.Date > datetime("01-Jun-2022")) >= 1) && (sum(T1.Date < datetime("01-Jun-2022")) >=1)
            idx =[idx; [i j]];
        end
    end
end
idx
idx = 3×2
     1     1
     1     3
     2     2
Tout = [];
for i=1:size(idx, 1)
    Tout = [Tout;  
            T((T.Name==uName(idx(i,1))) & (T.Type==uType(idx(i,2))), :)];
end
Tout
Tout = 6×4 table
    Name       Date        Type     Data 
    ____    ___________    _____    _____

    Joe     03-Feb-2022    Car      B213 
    Joe     01-Aug-2022    Car      A342 
    Joe     23-Dec-2021    Van      G344 
    Joe     15-Jul-2022    Van      G6767
    Mary    03-Sep-2022    Truck    M235 
    Mary    03-Feb-2022    Truck    A4567

3 个评论
显示 1更早的评论隐藏 1更早的评论

Chunru 2022-9-20

See the updated.

Marcus Glover 2022-9-20

Thanks!

In case anyone ever reads this with a similar issue, I did come up with a method using ismember, but this solution is much better.

请先登录，再进行评论。

Answer 2

Lola Davidson 2024-6-3

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1808310-find-unique-groups-in-table-with-members-both-before-and-after-date#answer_1466911

The groupfilter function was introduced in R2019b to simplify these types of workflows. Just specify which variables define the groups, which variable to send through a filter, and specify a filter function. Using groupfilter, you can do this in one function call.

myfilter = @(t)(sum(t > datetime("01-Jun-2022")) >= 1) && (sum(t < datetime("01-Jun-2022")) >=1);

T2 = groupfilter(T,["Name" "Type"],myfilter,"Date")

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

请先登录，再进行评论。

Find unique groups in table with members both before and after date

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

采纳的回答

3 个评论
显示 1更早的评论隐藏 1更早的评论

更多回答（1 个）

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

另请参阅

类别

标签

Community Treasure Hunt

Find unique groups in table with members both before and after date

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

采纳的回答

3 个评论 显示 1更早的评论隐藏 1更早的评论

更多回答（1 个）

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

另请参阅

类别

标签

Community Treasure Hunt

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

3 个评论
显示 1更早的评论隐藏 1更早的评论

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