Format table with frequency of unique combination

I have a table T, a part of this looks like:

    Code    Number1      Date         Number2
    ____    _______    __________     _______

    a       1          03-02-2017     18.4191 
    a       1          17-02-2017      4.4187 
    a       1          17-02-2017      4.4187 
    a       1          01-05-2017      8.2921 
    b       2          01-05-2017      8.2921 
    b       1          01-05-2017      3.6584 
    b       2          01-05-2017      3.6584 
    b       1          04-01-2017     44.4493 
    b       1          04-01-2017     51.2154 
    c       1          04-01-2017     56.1046

Now I want to make a new table with the codes as columnnames, the dates as rownames and the frequency of this combination in the table. In this case it would look like:

                  a      b      c 
                 ___    ___    ___

    04-1-2017    NaN      2      1
    03-2-2017      1    NaN    NaN
    17-2-2017      2    NaN    NaN
    01-5-2017      1      3    NaN

I have already calculated the frequency by:

[C,~,ib] = unique(T(:,[1 3]));
Frequency = histc(ib, 1:height(C));

But how can I format this table?

• The column 'Code' is categorical and ' Date' is datetime

Answer 1

[Peter Perkins]

This is the classic application of unstack, with the subtlety that you aren't actually unstacking the data, you're counting instances. So you need to use length as the aggregation function. I'm gonna demonstrate using a timetable, which isn't crucial, but still, these are timestamped data.

>> t = readtable('tmp1.csv');
>> t.Code = categorical(t.Code);
>> t.Date = datetime(t.Date,'InputFormat','dd-MM-yyyy')
t =
  10×4 table
    Code    Number1       Date        Number2
    ____    _______    ___________    _______
     a         1       03-Feb-2017    18.419 
     a         1       17-Feb-2017    4.4187 
     a         1       17-Feb-2017    4.4187 
     a         1       01-May-2017    8.2921 
     b         2       01-May-2017    8.2921 
     b         1       01-May-2017    3.6584 
     b         2       01-May-2017    3.6584 
     b         1       04-Jan-2017    44.449 
     b         1       04-Jan-2017    51.215 
     c         1       04-Jan-2017    56.105 
>> tt = table2timetable(t,'RowTimes','Date')
tt =
  10×3 timetable
       Date        Code    Number1    Number2
    ___________    ____    _______    _______
    03-Feb-2017     a         1       18.419 
    17-Feb-2017     a         1       4.4187 
    17-Feb-2017     a         1       4.4187 
    01-May-2017     a         1       8.2921 
    01-May-2017     b         2       8.2921 
    01-May-2017     b         1       3.6584 
    01-May-2017     b         2       3.6584 
    04-Jan-2017     b         1       44.449 
    04-Jan-2017     b         1       51.215 
    04-Jan-2017     c         1       56.105 
>> ttu = unstack(tt,'Number1','Code','AggregationFunction',@length)
ttu =
  4×3 timetable
       Date         a      b      c 
    ___________    ___    ___    ___
    03-Feb-2017      1    NaN    NaN
    17-Feb-2017      2    NaN    NaN
    01-May-2017      1      3    NaN
    04-Jan-2017    NaN      2      1

[Aletta Wilbrink]

Thank you for your help! But what does the column 'Number1' do in the code?:

ttu = unstack(tt,'Number1','Code','AggregationFunction',@length)