In table, how to count a number of specific values by year?

I have a following table.

       Date        Value    win_repeat
    ___________    _____    __________

    20-Nov-2018    0.041        1     
    21-Nov-2018     0.04        2     
    29-Nov-2018    0.042        0     
    11-Feb-2019    0.039        0     
    21-Mar-2019    0.035        1     
    02-May-2019    0.048        0     
    21-Aug-2019    0.045        2     
    16-Sep-2019     0.04        0     
    03-Oct-2019    0.038        2     
    04-Oct-2019    0.037        1     
    10-Oct-2019    0.035        0     
    31-Oct-2019    0.034        2     
    20-Nov-2019    0.034        1     
    26-Nov-2019    0.034        1     
    28-Nov-2019    0.033        1     
    02-Dec-2019    0.033        2     
    14-Jan-2020    0.034        2     
    22-Jun-2020    0.022        1     
    30-Jul-2020    0.023        2     
    20-Aug-2020     0.03        0     
    
    

I like to count how many '0', '1', '2' are in the 'win_repeat' column for each year. The expected result is as below.

    year_Date   win_repeat  GroupCount_win_repeat
    2018            0           1   
    2018            1           1
    2018            2           1
    2019            0           4    
    2019            1           5
    2019            2           4
    2020            0           1
    2020            1           1
    2020            2           2

Firstly, I tried with 'groupcounts', but it only counts a number of rows in the table for each year.

x = groupcounts(T, 'Date', 'year')

x =

  3×3 table

    year_Date    GroupCount    Percent
    _________    __________    _______

      2018            3          15   
      2019           13          65   
      2020            4          20   

Next, I tried with 'groupsummary', I cannot intput 'count' for the 'method' variable (please note that 'mean' is input for the 'method' varialbe in the follwing example).

x = groupsummary(T, 'Date', 'year', 'mean', 'win_repeat')

x =

  3×3 table

    year_Date    GroupCount    mean_win_repeat
    _________    __________    _______________

      2018            3                1      
      2019           13                1      
      2020            4             1.25      

Please help with this. Thank you very much in advance.

[KSSV]

You have to use unique and then hist. Read about these functions.

An example: https://in.mathworks.com/matlabcentral/answers/164545-how-to-find-repetition-of-each-unique-array-in-matrix

[Doheon Lee]

I already have tried it. But the error comes up.

>> groupsummary(T, 'Date', 'year', @(x) histc(x, unique(x)), 'win_repeat')

Error using groupsummary (line 480)
Unable to apply method 'fun1' to data variable win_repeat.

Answer 1

[Cris LaPierre]

The key may be in realizing that you have 2 grouping criteria - year and win_repeat. I added your data to a txt file so I could run this example.

T = readtable("DoheonLeeTable.txt")

T = 20×3 table
       Date        Value    win_repeat
    ___________    _____    __________

    20-Nov-2018    0.041        1     
    21-Nov-2018     0.04        2     
    29-Nov-2018    0.042        0     
    11-Feb-2019    0.039        0     
    21-Mar-2019    0.035        1     
    02-May-2019    0.048        0     
    21-Aug-2019    0.045        2     
    16-Sep-2019     0.04        0     
    03-Oct-2019    0.038        2     
    04-Oct-2019    0.037        1     
    10-Oct-2019    0.035        0     
    31-Oct-2019    0.034        2     
    20-Nov-2019    0.034        1     
    26-Nov-2019    0.034        1     
    28-Nov-2019    0.033        1     
    02-Dec-2019    0.033        2     

% Use groupsummary
x = groupsummary(T, ["Date","win_repeat"],["year","none"])

x = 9×3 table
    year_Date    win_repeat    GroupCount
    _________    __________    __________

      2018           0             1     
      2018           1             1     
      2018           2             1     
      2019           0             4     
      2019           1             5     
      2019           2             4     
      2020           0             1     
      2020           1             1     
      2020           2             2     

% Or use groupcounts
y = groupcounts(T, ["Date","win_repeat"],["year","none"])

y = 9×4 table
    year_Date    win_repeat    GroupCount    Percent
    _________    __________    __________    _______

      2018           0             1            5   
      2018           1             1            5   
      2018           2             1            5   
      2019           0             4           20   
      2019           1             5           25   
      2019           2             4           20   
      2020           0             1            5   
      2020           1             1            5   
      2020           2             2           10   

[Doheon Lee]

It works perfectly!! I seems that your solution can be also applied for more analysis as below. Thank you very much Cris!! (Also I appreciate for your effort in making a text file)

x = groupsummary(T, ["Date","win_repeat"],["year","none"], 'mean', 'Value')

[Cris LaPierre]

Correct. You didn't mention needing to apply any function to the groups. This is what the 'method' input is for. You can use one of the predefined methods, or you can call a function you create (example here).

If you don't specify a datavars input, then the method(s) is/are applied to all table variables. Otherwise, it/they are only applied to the indicated variables. Use the same syntax shown for grouping multiple variables to apply multiple methods and to select multiple datavars.

[Doheon Lee]

Thank you for the more information. No, I did not meant to apply any funciton to the groups., but your reply helps me think further than what I tried to do at the beginning. Once again, thank you so much for the help. :)

Answer 2

[Yazan]

year = [repmat(2018, [3,1]); repmat(2019, [13,1]); repmat(2020, [4,1])];
win = [1;2;0;0;1;0;2;0;2;1;0;2;1;1;1;2;2;1;2;0];
T = table(year, win);
groupsummary(T, 'year', @(x) {histcounts(x)});