Change code to fit strings - Matlab

Question

I have the following code:

NI1=[NI{:,1} NI{:,2} NI{:,3}];
[~,NI2]=sort(NI1(:,2));
NI1=NI1(NI2,:);
NI1((NI1(:,3) == 0),:) = [];
NI1=unique(NI1(:,1:3),'rows');
NI3= unique(NI1(:,1:2),'rows')

for mj=1:size(NI3,1)
    NI3(mj,3)=sum(NI1(:,1) == NI3(mj,1) & NI1(:,2)==NI3(mj,2));
end

My initial cell-array NI1 has in collumns: 1) the year; 2) a code that corresponds to a bank 3) a code that corresponds to the workers of the bank. EXAMPLE:

c1      c2  c3
1997    3   850
1997    3   1024
1997    3   5792

My output NI3 counts how many analysts (c3), for the different years (c1) are working in each bank (c2), for instance:

c1      c2  c3
1997    3   14
1997    7   84
1997    11  15
1998    4   1
1998    15  10
1998    3   12
1999    11  17

Now I am trying to apply exactly the same code, but my last column (c3) is a string so initial cell array fir_ins is the following:

1997    3   'ACAD'
1997    3   'ADCT'
1997    3   'ADEX'

I want to obtain exactly the same output as in NI3, but I have to change the code, since my last column is a string.

I am only missing the last part, this is the code I have so far.

ESTIMA=num2cell(I{:,6});
ANALY=num2cell(I{:,7});
YEAR = num2cell(T_ANNDAT3);
fir_ins=[YEAR ESTIMA I{:,1}];
fir_ins= sortrows(fir_ins,2);
[~, in2,~] = unique(strcat(fir_ins(:,2),fir_ins(:, 3)));
fir_ins = fir_ins(in2,:);
fir_ins= sortrows(fir_ins,[1 2]);
fir_ins2=fir_ins(:,1:2);
fir_ins2=unique(cell2mat(fir_ins2(:,1:2)),'rows');

This part is not working:

for jm=1:size(fir_ins2,1)
    fir_ins2(jm,3)=sum(cell2mat(fir_ins(:,1))) == fir_ins2(jm,1) & cell2mat(fir_ins(:,2))==cell2mat(fir_ins2(jm,2));
end

score 2 · Accepted Answer · answered Jul 06 '14 at 19:43

You can perform this "aggregation" more efficiently with the help of accumarray function. The idea is to map the first two columns (row primary keys) into subscripts (indices starting from 1), then pass those subscripts to accumarray to do the counting.

Below is an example to illustrate. First I start by generating some random data resembling yours:

% here are the columns
n = 150;
c1 = sort(randi([1997 1999], [n 1]));           % years
c2 = sort(randi([3 11], [n 1]));                % bank code
c3 = randi(5000, [n 1]);                        % employee ID as a number
c4 = cellstr(char(randi(['A' 'Z']-0, [n,4])));  % employee ID as a string

% combine records (NI)
X = [c1 c2 c3];                                 % the one with numeric worker ID
X2 = [num2cell([c1 c2]) c4]; % {c1 c3 c4}       % the one with string worker ID

Note that for our purposes, it doesn't matter if the workers ID column is expressed as numbers or string; we won't be using them, only the first two columns that represent the "primary keys" of the rows are used:

% find the unique primary keys and their subscript mapping
[years_banks,~,ind] = unique([c1 c2], 'rows');

% count occurences (as in SQL: SELECT COUNT(..) FROM .. GROUPT BY ..)
counts = accumarray(ind, 1);

% build final matrix: years, bank codes, counts
M = [years_banks counts];

I got the following result with my fake data:

>> M
M =
        1997           3          13
        1997           4          11
        1997           5          15
        1997           6          14
        1997           7           4
        1998           7          11
        1998           8          24
        1998           9          15
        1999           9           1
        1999          10          22
        1999          11          20

The code like this is much more efficient. Thank you – user3557054 Jul 13 '14 at 21:14 — user3557054, Jul 13 '14 at 21:14

Change code to fit strings - Matlab

1 Answers1