MATLAB: Rearrange cell content by groups

groupingMATLAB

There's got to be a simple way to do this…
Have a cell array of data by date wherein there are N (or fewer) instances of M quantities for each date. I need to rearrange to reflect each of the M quantities for a given date on a single row. For example given
       Date                            Fund                             Shares         Value 
    ___________    _____________________________________________    _______________    ______
    30-Nov-2015    'American International Growth & Income Fund'    '67.958 shares'    1979.6
    30-Nov-2015    'American Mutual Fund'                           '54.930 shares'    1997.3
    30-Nov-2015    'Capital Income Builder'                         '34.843 shares'    1997.2
    30-Nov-2015    'Europacific Growth Fund'                        '41.528 shares'    1988.8
    30-Nov-2015    'Income Fund of America Fund'                    '95.102 shares'    2001.9
    31-Dec-2015    'American International Growth & Income Fund'    '67.958 shares'    1936.6
    31-Dec-2015    'American Mutual Fund'                           '54.930 shares'    1952.1
    31-Dec-2015    'Capital Income Builder'                         '34.843 shares'    1964.2
    31-Dec-2015    'Europacific Growth Fund'                        '41.528 shares'    1941.1
    31-Dec-2015    'Income Fund of America Fund'                    '95.102 shares'    1975.3
    31-Jan-2016    'American International Growth & Income Fund'    '67.958 shares'    1847.1
    31-Jan-2016    'American Mutual Fund'                           '54.930 shares'    1894.5
    31-Jan-2016    'Capital Income Builder'                         '34.843 shares'    1939.6
    31-Jan-2016    'Europacific Growth Fund'                        '41.528 shares'      1823
    31-Jan-2016    'Income Fund of America Fund'                    '95.102 shares'    1930.4
    ...
    30-Sep-2017    'American International Growth & Income Fund'    '67.958 shares'     44.02
    30-Sep-2017    'Europacific Growth Fund'                        '41.528 shares'    101.21
    31-Oct-2017    'American International Growth & Income Fund'    '67.958 shares'      44.3
    31-Oct-2017    'Europacific Growth Fund'                        '41.528 shares'    103.35
    30-Nov-2017    'American International Growth & Income Fund'    '67.958 shares'      44.3
    30-Nov-2017    'Europacific Growth Fund'                        '41.528 shares'    103.35
one observes there are five Funds for each of the first few dates but only two at the end. The desired output would be
       Date        Fund1                Shares1          Value1  Fund2                Shares2          Value2  ...
    ___________    _________________    _______________  ______  _________________    _______________  ____________  ...
    30-Nov-2015    'American Int...'    '67.958 shares'  1979.6  'American Mut...'    '54.930 shares'    1997.3 ...
    31-Dec-2015    'American Int...'    '67.958 shares'  1936.6  'American Mut...'    '54.930 shares'    1952.1 ...
    ...
    30-Nov-2017    'American Int...'    '67.958 shares'    44.3  ''                    ''                     ''  ...
where I truncated the lines showing only the first two of five. Of course, there would be empty cells at the end where there are only the two of five still extant.
It's easy enough to get grouping indices; I just haven't figured out the simple way to process them to build the output array from the grouping numbers.

Best Answer

If you use the table datatype, which I strongly encourage over cells(!), then this is a simple one-liner. You can also specify the new variable names as an option if the defaults don't suit.
unstack(dpbtable, {'Shares', 'Value'}, 'Fund')
Attached is a MAT file with dpbtable.
More: Just for fun and because accumarray is my favorite MATLAB function, here's a cell version that gets roughly what you want:
dpbcell = table2cell(dpbtable)
% Unique of each date and fund
[udate,~,idxdate] = unique(vertcat(dpbcell{:,1}));
[ufund,~,idxfund] = unique(vertcat(dpbcell{:,2}));
 % Accumulate date x fund
[num2cell(udate), accumarray([idxdate, idxfund], (1:numel(idxfund))', [], @(x){dpbcell(x, 2:4)}, {nan})]
Related Question