MATLAB Answers

grouping elements of a column that correspond to specific elements from another column

1 view (last 30 days)
antonet
antonet on 28 Jan 2013
Dear all, I issue the following commands in order to merge 3 excel files
clear all
fname = {'fgg.xlsx', 'sedf.xlsx','sddefff.xlsx'};
[data,text,a] = cellfun(@xlsread,fname,'un',0);
[m,n] = cellfun(@size,a);
mm = max(m);
mn = max(n);
out1 = arrayfun(@(x,y,z)[x{:},nan(y,mn-z)],a,m,n,'un',0);
out1 = cat(1,out1{:});
out1=out1(~cellfun(@(x)all(isnan(x)), out1(:,8)),:);% erase empty cells in the date vector
So the resulting outcome is out1 which is
out1={
'country' 'area' 'number' 'geographical codes'
'MN' [ 0] [1.2868] [ NaN]
'MN' [ 0] [2.9102] [ 0]
'MN' 'AER_KL1' [1.2868] 'B1'
'MN' 'AER_KL1' [2.9102] 'B1'
'MN' 'AER_KL1' [1.2868] 'B1'
'MN' 'AER_KL1' [1.2868] 'B1'
'MN' 'AER_KL1' [3.0740] 'B1'
'MN' 'AER_KL1' [3.0740] 'B2'
'MN' 'AER_KL1' [3.0740] 'B2'
'MN' 'AER_KL1' [3.0740] 'B2'
'MN' 'AER_KL1' [3.0740] 'B2'
'MN' 'AER_KL1' [3.0740] 'B2'
'MN' 'AER_KL1' [3.0740] 'B2'
'MN' 'AER_KL2' [3.0740] 'B2'
'MN' 'AER_KL2' [3.0740] 'B2'
'MN' 'AER_KL2' [3.0740] 'B2'
'MN' 'AER_KL2' [3.0740] 'B2'
'MN' 'AER_KL2' [3.0740] 'B2'
'MN' 'AER_KL2' [3.0740] 'B2'
'MN' 'AER_KL2' [3.0740] 'B2'
'MN' 'AER_KL2' [28.2414] 'B2'
'MN' 'AER_KL3' [3.0740] 'B2'
'MN' 'AER_KL3' [3.0740] 'B2'
'MN' 'AER_KL3' [3.0740] 'B2'
'MN' [ 0] [28.2414] [ 0]
'MN' [ 0] [3.0740] [ 0]
'MN' [ 0] [3.0740] [ 0]
'MN' [ 0] [29.6135] [ 0]
'MS' [ 0] [29.6135] [ 0]
'MS' [ 0] [3.0740] [ NaN]
'MS' 'AER_KL1' [3.0740] 'KS 3001'
'MS' 'AER_KL1' [3.0740] 'KS 3001'
'MS' 'AER_KL1' [3.0740] 'KS 3001'
'MS' 'AER_KL1' [3.0740] 'KS 3001'
'MS' 'AER_KL1' [3.0740] 'KS 3001'
'MS' 'AER_KL1' [29.6135] 'KS 3001'
'MS' 'AER_KL1' [28.2414] 'KS 3001'
'MS' 'AER_KL1' [3.0740] 'KS 3001'
'MS' 'AER_KL1' [3.0740] 'KS 3001'
'MS' 'AER_KL1' [28.2414] 'KS 3001'
'MS' 'AER_KL1' [29.6135] 'KS 3001'
'MS' 'AER_KL2' [3.0740] ' SDIRNR+3000 '
'MS' 'AER_KL2' [28.2414] ' SDIRNR+3000 '
'MS' 'AER_KL2' [3.0740] ' SDIRNR+3000 '
'MS' 'AER_KL2' [3.0740] ' SDIRNR+3000 '
'MS' 'AER_KL2' [3.0740] ' SDIRNR+3000 '
'MS' 'AER_KL2' [3.0740] ' SDIRNR+3000 '
'MS' 'AER_KL2' [29.6135] ' SDIRNR+3000 '
'MS' 'AER_KL2' [3.0740] ' SDIRNR+3000 '
'MS' 'AER_KL2' [3.0740] ' SDIRNR+3000 '
'MS' 'AER_KL2' [3.0740] ' SDIRNR+3000 '
As you can see I have 2 countries (MN and MS -first column) and for each country I have a specific number of areas-second column- (for MN I have 'AER_KL1', 'AER_KL2' and 'AER_KL3' and for MS I have 'AER_KL1' and 'AER_KL2' ). Each area is assigned a geographical code (last column)
I want to select first only MN and then find the geographical codes that correspond to this country. Then select 'MS' and find the geographical codes that correspond to this country and so on…using some loop For example I want to get
'MN' 'B1'
'MN' 'B2'
'MN' 'B3'
'MS' 'KS 3001'
'MS' 'SDIRNR+3000'
I would like to find a code that will produce this simplified matrix
Thanks in advance
PS: In my real data I have 40 countries and the number of areas (or geographical codes) varies across countries

  0 Comments

Sign in to comment.

Accepted Answer

Cedric Wannaz
Cedric Wannaz on 28 Jan 2013
Edited: Cedric Wannaz on 28 Jan 2013
I would go for something like that:
>> cName = 'MN' ;
>> flagCountry = cellfun(@(cntry)strcmp(cntry, cName), out1(:,1)) ;
>> flagValid = cellfun(@(code)ischar(code), out1(:,end)) ;
>> unique(out1(flagCountry&flagValid,end))

  2 Comments

antonet
antonet on 29 Jan 2013
thanks for providing some code. It works but the output is in a messy condition. IS it possible to modify this code so as to obtain the output that I wrote in my question? namely, the follwing format
'MN' 'B1'
'MN' 'B2'
'MS' 'KS 3001'
'MS' 'SDIRNR+3000'
Cedric Wannaz
Cedric Wannaz on 29 Jan 2013
You just need to loop over unique country/region codes:
cntryCodes = unique(out1(:,1)) ;
for ii = 1:numel(cntryCodes)
flagCountry = cellfun(@(cntry)strcmp(cntry, cntryCodes{ii}), ...
out1(:,1)) ;
flagValid = cellfun(@(code)ischar(code), out1(:,end)) ;
regionCodes = unique(out1(flagCountry&flagValid,end)) ;
for jj = 1:numel(regionCodes)
fprintf('%s\t%s\n', cntryCodes{ii}, regionCodes{jj}) ;
end
fprintf('\n') ;
end

Sign in to comment.

More Answers (1)

Sean de Wolski
Sean de Wolski on 28 Jan 2013
Looks like you want to use unique() with the 'rows' flag and then something else to remove zeros and nans.

  0 Comments

Sign in to comment.

Sign in to answer this question.