How can i use a loop for text and numbers ?

Hello,

I have some data presented in 3 columns (Company_name/Bank_name/Bank_investment_in_company)

It looks like that:

    ['CompA','BankA',10;
    'CompA','BankB',30;
    'CompB','BankA',5;
    'CompB','BankD',8;
    'CompB','BankC',12;
    'CompC','BankB',15]

I would like to know for each Company (CompA,CompB,CompC) who invested the most money (BankA...) and present it in a table.

How can i do? I also want to present the result in a table.

Thank you very much!

Answer 1

[dpb]

A "brute force" engine...

>> x=[1 1 10;1 2 30;2 1 5;2 4 8; 2 3 12; 3 2 15];  % convert A,B,C,... --> numeric
>> C='A':'C';                                      % company IDs --> number
>> B='A':'D';                                      % ditto banks
>> [u,ia]=unique(x(:,1));                          % get unique companies, positions
>> ia=[0;ia];                             % and a 0 start location for first to begin loop
>> for i=1:length(u)                      % for each company
     [mx,ix]=max(x(ia(i)+1:ia(i+1),3));   % get banks invested values and find max, which in group
     ix1=ix+ia(i);                        % position in group to position in array
     sprintf('Comp%c  Bank%c  Invest $%2dM\n',C(i),B(x(ix1,2)),x(ix1,3))  % show output
   end
ans =
CompA  BankB  Invest $30M

ans = CompB BankC Invest $12M

ans = CompC BankB Invest $15M

>>

Above can be turned into anonymous function and arrayfun build the final array instead of just printing output on the fly. Similar can be done w/ data types of nominal variables but I have to futz with them to get syntax right; can't just write on-the-fly code. (OK, I will admit that the same logic loop occurred just earlier today in another answer so all I had to do was substitute variables and a second index into a previous answer...it took me a couple passes earlier to get the indirect references right. :) )

ADDENDUM

On the array directly building an output table...

>> for i=1:length(u)
     [mx,ix]=max([data{ia(i)+1:ia(i+1),3}]);
     ix1=ix+ia(i);
     out(i,1)={sprintf('%s  %s  Invest $%2dM\n', ...
                       data{ix1,1},data{ix1,2},data{ix1,3})};
     end
>> [out{:}]
ans =
CompA  BankB  Invest $30M
CompB  BankC  Invest $12M
CompC  BankB  Invest $15M

>>

Can't use an anonymous function, however, as can't return the optional second return value from max nor make the secondary fixup of the index so would have o have a helper two-liner function for arrayfun to work with.

Answer 2

[yem]

Thank you for your answer dpb,

I found an alternative in the statistic toolbox with max_investment_per_company= grpstats (Bank_investment_in_company,company_name,'max')

By any means, do you know how i can present the results (with dataset for example) to have this information summarized (grouped by CompanyA,B,C and by BankA,B,C,D)?

My problem is that dataset is not working because of the different number of rows...

Thanks for your precious help!

[dpb]

I've only used the dataset a very little and only for regular data so I don't have any insight there, sorry. I found grpstats problematical at best owing to a penchant I found for it to want to reorder variables on output on its own so I abandoned it and haven't gotten a more recent version to see if the bug has been fixed in later versions (I've R2012b)

NB: the above logic engine can be applied to the cell array, too...

>> data={'CompA','BankA',10;
  'CompA','BankB',30;
  'CompB','BankA',5;
  'CompB','BankD',8;
  'CompB','BankC',12;
  'CompC','BankB',15};
>> [u,ia]=unique(data(:,1))
u = 
  'CompA'
  'CompB'
  'CompC'
ia =
   2
   5
   6
>>

Those are the same indices ia as returned from the numeric-only version but with the cell string IDs already there so you can refer to the results directly.