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

2 visualizzazioni (ultimi 30 giorni)
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

Risposta accettata

Cedric
Cedric il 28 Gen 2013
Modificato: Cedric il 28 Gen 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 Commenti
antonet
antonet il 29 Gen 2013
Modificato: antonet il 29 Gen 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
Cedric il 29 Gen 2013
Modificato: Cedric il 29 Gen 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

Accedi per commentare.

Più risposte (1)

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

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by