Rearrange cell content by groups

Question

dpb 2017-12-31

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/375089-rearrange-cell-content-by-groups

评论： dpb 2018-12-23

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.

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

dpb 2018-12-23

It's been a year since I posted...I had to drop the problem for many months owing to more pressing matters until this past week I returned (with yet another year's data to add to the mix!) still with individual entries only documented as comments...

However, I did get the initial "stripper" completed last night and now do have all those from all years and all months from the inception of the capaital campaign initial gift so I will be able to then got through entry by entry and discover where the disconnect first occurred and, hopefully, that will point to the data entry error...

MC & HNY to all!!!, just wanted to let know that the input did bear fruit, eventually... :)

请先登录，再进行评论。

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

Answer 1

Sean de Wolski 2018-1-5

2
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/375089-rearrange-cell-content-by-groups#answer_298770

编辑：Sean de Wolski 2018-1-5

在 MATLAB Online 中打开

dpbtable.mat

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})]

13 个评论
显示 11更早的评论隐藏 11更早的评论

dpb 2018-1-8

编辑：dpb 2018-1-9

If the data are tabular and reasonably regular, one can generally get there from here. However, the particular spreadsheets aren't so much so; the basic data are, but there are multiple sections within each sheet and small but significant differences between sections plus each month may have a different number of sections/data per section as new funds and/or investment pools are created or removed. The only way I've found to deal with these programmatically(*) is to just start with the raw data and find the various pieces via lookup for keys. I've made a local version of XLSREAD that doesn't do the trimming on the numeric and text returns the builtin version does, but returns the same-sized array for each output; this makes lookups between the data types consistent in locations that helps a bunch; in the original there is always a (variable) offset from (say) the text array to raw and/or numeric arrays so locating where one particular element was within the spreadsheet is problematic. This is particularly a pit(proverbial)a(ppendage) since none of the search functions are tolerant of different data types within the target; hence one has to do string searches in the text array or go through excessive gyrations to remove convert all non-text elements from the raw array first (or vice versa if searching for numerics). Simply indexing on locations doesn't work as then the result of the location search is based on the resulting logical addressing vector length which has the aforementioned issue of being inconsistent in its origin relative to the actual location in the worksheet.

I just ran into another thing with readtable this AM that was going to post to the "What's Missing" thread Walter started -- it's lacking a number of the options needed; particularly in this case for the text side rather than spreadsheet and that is the 'CommentStyle' parameter for one...

(*) And, since there's one of these workbooks for each calendar month and am interested in multiple years, the option of interactively importing each is not attractive at all. Within each month the way it has been organized is that there are also some six working sheets of the same format, each with a different level of processing through the accounting month. (And, yes, this is no way to run a railroad, but it is the system that has evolved over some 20 years and while we're working on transitioning to a true accounting system, that will take some time yet before we can get there).

dpb 2018-1-10

编辑：dpb 2018-1-11

Well, it dawned on me it wasn't too difficult to write a script after all, here's a sample of two sheets selected from two different years pasted together into one spreadsheet. The names of donors have been randomized but I left in the subsection headings and some of the generic fund descriptions/names so it makes a little sense in reading what is there; totally randomized even I couldn't figure out what I was looking at!!! :)

What I am interested in now most specifically is the data from the capital campaign pools as the way the bookkeeper chose to enter gifts was by adding comments in the cell instead of creating a separate table or other way. Hence, if there's a balancing problem as it is now the only way to track it down is by manually reading the comment which is only visible by hovering over the particular cell or by showing all comments which the occludes almost the whole rest of the sheet. I was stripping out the comment fields(*) and building a usable database from them -- oh, and that alone kills readtable because it won't return comments anyway.

But, if you could illustrate readtable on the data as it exists, that would be good too as I will eventually be able to remove the comments from having needed actual data.

(*) That's the other thing the local copy of XLSREAD does; it returns another optional cell array the size of the others that contains the comments from each cell; I couldn't make the optional function argument work for that purpose with the original.

Sean de Wolski 2018-1-16

importfile.m

readtable, even with supplied options, is embarrassingly slow for those sheets. I'll be reporting it to dev.

Using the import tool, I was able to pull all of the data into a table pretty quickly and generate the function to automate it for any sheet that looks like this. The advantage is that the data come in as strings, numeric, and categorical automatically and you could perhaps take advantage of exclusion/default rules.

Unfortunately, I don't know that this offers much over just managing xlsread/raw yourself, especially if the sheets have changing columns.

The generated function is attached.

dpb 2018-1-16

在 MATLAB Online 中打开

OK, again thanks for taking a look, Sean. Hopefully if nothing else it can serve as useful fodder for dev. to improve the routine.

The penchant for importdata to build functions with hardcoded column numbers is an issue; over the years additional columns have been inserted here and there to either contain some additional information or more often than not, just add some spacing for aesthetics. Consequently, I'm reduced to having to search for column headers to locate which is where. That's not too bad; at least the headings of the data columns that are carried over haven't changed over the time frame over which I've had to work and that goes back a number of years.

Below is a snippet that really is all I've done uniquely; everything else is the home-brew xlsread routine that I just copied in order to add the optional TRIM flag to keep the consistency between various types.

% parse data into numeric and string arrays, keep sizes commensurate
trimData=false;
[numericData, textData] = SplitNumericAndText(rawData,trimData);
commentData=xlsComments(Excel,rangeObject);
% Would be more efficient to find stuff inside the range rather than read
% all, but Matlab syntax is much simpler when don't know VBA/Object Model
% So, first find the Pool heading rows, columns
[ir,ic]=cellfun(@(txt) findtxtincell(txt,textData),pools,'uniform',0);
 for i=1:length(ir)                   % for each pool we found
   rr=[];                             % initialize accumulator for funds
   j=ir{i}+1;                         % begin with row after pool name
   while ~isempty(textData{j,2})      % find account rows in pool
     rr=[rr; j];                      % any none-empty row is an account
     j=j+1; 
   end 
  rows{i,1}=[ir{i}; rr];              % save row indices for each pool
end
idx=cell2mat(rows);                   % get indices as addressing vector
textData=textData(idx,:);
numericData=numericData(idx,:);
rawData=rawData(idx,:);
commentData=commentData(idx,:);
if ~isempty(customFun)
  customOutput=customOutput(idx,:);
end

The section returns the desired Pool subset(s) plus the comments associated with them. At this point it is then trivial to build the table representation as the returned output is now regular and includes the pieces wanted by identifiable section indices.

It took me a while to work out the VBA-equivalent for the COM interface to get at the comments but I posted a Question and the subsequent solution on that separately.

Again, thanks a lot for the time and interest...

请先登录，再进行评论。

Answer 2

Cedric 2017-12-31

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/375089-rearrange-cell-content-by-groups#answer_298235

编辑：Cedric 2017-12-31

在 MATLAB Online 中打开

Hi dpb,

Assuming that you have a cell array (and that you are using this table type of output just for a display purpose), here is a "neither-too-elegant-nor-too-satisfactory" approach:

data = {'10/8/17','A',10,100; '10/8/17','B',8,30; ...
        '11/8/17','E',17,70;  '11/8/17','A',14,80;  '11/8/17','C',5,110;  ...
        '12/8/17','B',9,50} ;
[groupId, dates] = findgroups( data(:,1) ) ;
% - Build row/col IDs in output array.
rowId = reshape( (groupId * ones(1,3)).', [], 1 ) ;
colId = arrayfun( @(n)1+(1:3*n), accumarray(groupId, 1), 'Unif', 0 ) ;
colId = [colId{:}].' ;
% - Build output array.
outData = cell( max(rowId), max(colId) ) ;
outData(:,1) = dates ;
outData(sub2ind( size(outData), rowId, colId )) = reshape( data(:,2:end).', [], 1 ) ;

where the input data is:

 >> data
 data =
  6×4 cell array
    {'10/8/17'}    {'A'}    {[10]}    {[100]}
    {'10/8/17'}    {'B'}    {[ 8]}    {[ 30]}
    {'11/8/17'}    {'E'}    {[17]}    {[ 70]}
    {'11/8/17'}    {'A'}    {[14]}    {[ 80]}
    {'11/8/17'}    {'C'}    {[ 5]}    {[110]}
    {'12/8/17'}    {'B'}    {[ 9]}    {[ 50]}

and the output is:

 >> outData
 outData =
  3×10 cell array
    {'10/8/17'}    {'A'}    {[10]}    {[100]}    {'B'       }    {[       8]}    {[      30]}    {0×0 double}    {0×0 double}    {0×0 double}
    {'11/8/17'}    {'E'}    {[17]}    {[ 70]}    {'A'       }    {[      14]}    {[      80]}    {'C'       }    {[       5]}    {[     110]}
    {'12/8/17'}    {'B'}    {[ 9]}    {[ 50]}    {0×0 double}    {0×0 double}    {0×0 double}    {0×0 double}    {0×0 double}    {0×0 double}

Hoping that you'll get a more elegant approach ..

Happy new year!

Cedric

EDIT 1 : Using SPLITAPPLY can help to some extent:

 >> outData = splitapply( @(x){reshape(x.',[],1).'}, data(:,2:end), groupId )
 outData =
  3×1 cell array
    {1×6 cell}
    {1×9 cell}
    {1×3 cell}

but then we must implement padding for the concatenation.

7 个评论
显示 5更早的评论隐藏 5更早的评论

dpb 2017-12-31

在 MATLAB Online 中打开

Cedric -- Yes, you read what was sorta' said that the initial point is a cell array and the table was just convenient output format.

I was working along those lines but got lost in the weeds before I got the result. :) It seems like there should be a parallel accumcell to accumarray that would let one build a set of indexing vectors from the group indices and use them directly.

It seems to me that the grouping computations/arranging, etc., are woefully inadequate without way too much detailed gyrations. It's been a long time now since I actually used it, but ISTR that didn't have nearly the issues in SAS with doing such but maybe it's just the haze of distance that makes it seem as though it was simpler and I've just forgotten -- or mayhaps I had just learned it more thoroughly than I've yet mastered this part of Matlab.

Anyways, I had coded it up just using a looping construct but just seemed as though it should be "straightahead" vectorized solution. (Atho I ended up convert to a table for the named access from the cell by the time I got here...)

    % Rearrange and consolidate by date; first strip out redundant info into table
    EdJW=cell2table([EdJWeeast(:,1), strip(EdJWeeast(:,3)), EdJWeeast(:,5),  EdJWeeast(:,7)],'VariableNames',hdrs);
    [GD,IDD]=findgroups(EdJW.Date);                           % grouping variable by date
    [GF,IDF]=findgroups(EdJW.Fund);                           % and by fund name
    nGD=histcounts(GD,length(IDD));                           % counts for each date
    T=cell(length(IDD),1+3*length(IDF));                      % empty cell array to fill
    ixF=0;                                                    % counter for funds index
    for i=1:length(IDD)                                       % each date found
      T(i,1)={IDD(i)};
      for j=1:nGD(i)
        ixF=ixF+1;
        T(i,3*GF(ixF)-1)=EdJW.Fund(ixF);
        T(i,3*GF(ixF)  )=EdJW.Shares(ixF);
        T(i,3*GF(ixF)+1)={EdJW.Value(ixF)};
      end
    end

dpb 2018-1-1

编辑：dpb 2018-1-1

Thanks, Cedric, the arrayfun over accumarray step is where I got myself flummoxed in my first attempt at something very similar that prompted me to post the query...very good!!

HNY!!! --dpb

PS. I think I will submit the enhancement request for accumcell and see where it goes...

I also realized overnight that with SAS one doesn't have to deal with the intimacies of the internal data manipulations nearly so much; you can create/have such a disparate DATASET as in the ML table and just PROC it with BY clauses and it all just happens. Not sure if there's ever the possibility of TMW getting to that kind of integration or not; the two fundamental bases for what the end intent was were so far removed from each other at inception it's hard to see how. ML has "just growed" into something far removed from its initial goal but to retain those features is somewhat akin to the problems integrating OOP and other modern constructs into Fortran yet retain backward compatibility there as well...choices that could be made for new features just can't be accepted because it would totally invalidate too much extant code.

Cedric 2018-1-3

编辑：Cedric 2018-1-3

在 MATLAB Online 中打开

I have the same feeling, there should be a better approach!

About SPLITAPPLY, I think that it is not intuitive because, in the beginning, we have a hard time guessing how the input array is split and to what the function passed as 1st arg is applied. A good way to check that out is to DISP it with no output arg:

 >> splitapply( @disp, data(:,2:end), groupId )
    'American International Growt…'    '67.958 shares'    [1.9796e+03]
    'American Mutual Fund'              '54.930 shares'    [1.9973e+03]
    'Capital Income Builder'            '34.843 shares'    [1.9972e+03]
    'Europacific Growth Fund'           '41.528 shares'    [1.9888e+03]
    'Income Fund of America Fund'       '95.102 shares'    [2.0019e+03]
    'American International Growt…'    '67.958 shares'    [1.9366e+03]
    'American Mutual Fund'              '54.930 shares'    [1.9521e+03]
    'Capital Income Builder'            '34.843 shares'    [1.9642e+03]
    'Europacific Growth Fund'           '41.528 shares'    [1.9411e+03]
    'Income Fund of America Fund'       '95.102 shares'    [1.9753e+03]
    'American International Growt…'    '67.958 shares'    [1.8471e+03]
    'American Mutual Fund'              '54.930 shares'    [1.8945e+03]
    'Capital Income Builder'            '34.843 shares'    [1.9396e+03]
    'Europacific Growth Fund'           '41.528 shares'    [      1823]
    'Income Fund of America Fund'       '95.102 shares'    [1.9304e+03]
    'American International Growt…'    '67.958 shares'    [ 44.0200]
    'Europacific Growth Fund'           '41.528 shares'    [101.2100]
    'American International Growt…'    '67.958 shares'    [ 44.3000]
    'Europacific Growth Fund'           '41.528 shares'    [103.3500]
    'American International Growt…'    '67.958 shares'    [ 44.3000]
    'Europacific Growth Fund'           '41.528 shares'    [103.3500]

Seeing this, it becomes obvious why I used:

@(x){reshape(x.',[],1).'}

in my previous comment, and why I pass all columns but the first.

dpb 2018-1-4

Yeah, I often do that disp "trick" with all the various XXXXfun incantations to discover just what the argument list passed to the (often anonymous) function really is--it isn't always all that intuitive as you point out.

请先登录，再进行评论。

Rearrange cell content by groups

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

采纳的回答

13 个评论
显示 11更早的评论隐藏 11更早的评论

更多回答（1 个）

7 个评论
显示 5更早的评论隐藏 5更早的评论

另请参阅

类别

标签

产品

Community Treasure Hunt

Rearrange cell content by groups

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

采纳的回答

13 个评论 显示 11更早的评论隐藏 11更早的评论

更多回答（1 个）

7 个评论 显示 5更早的评论隐藏 5更早的评论

另请参阅

类别

标签

产品

Community Treasure Hunt

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

13 个评论
显示 11更早的评论隐藏 11更早的评论

7 个评论
显示 5更早的评论隐藏 5更早的评论