How to append data for a variable column in a matrix?

I load an excel sheet using xlsread command. The excel sheet can have any number of rows and columns.

Example of the matrix (3*2)from the excel sheet will look like this:

System Code

AEA AEA00

AW WP000

The above matrix can have any length. Based on the column dimension, I want to concatenate the rows with a '-'. For e.g. the second row after concatenation can look like AEA-AEA00.

Could you suggest a way out for implementing the same?

Answer 1

[Cedric]

Assuming

 D = {'AEA', 'AEA00'; 'AW', 'WP000'}

you can concatenate e.g. row 1 with

 >> sprintf( '%s-', D{1,:} )
 ans =
       AEA-AEA00-

and remove the last dash afterwards. For performing this on the whole array:

 nRows = size( D, 1 ) ;
 Dcat  = cell( nRows, 1 ) ;
 for k = 1 : nRows
    tmp = sprintf( '%s-', D{k,:} ) ;
    Dcat{k} = tmp(1:end-1) ;
 end

This works with an arbitrary number of rows and columns. In the present case, it outputs

 >> Dcat
 Dcat = 
    'AEA-AEA00'
    'AW-WP000'

Notes:

• You might want to truncate the first row if it contains 'System Code'.
• This solution assumes that each row has the same number of non-empty columns. Let me know if it isn't the case; it is easy to add a line which selects only non-empty cells before the call to SPRINTF.

[Ashish]

Thanks Cendric for the reply. It works perfectly and you did get my point. However, as you pointed out in your reply, there are some rows in which the column 2 is empty whereas there is a value in column 1.

What is the line which needs to be added in that case?

[Cedric]

Are you always dealing with at most two columns? You say in the statement that there can be any number of columns though. If there are more than 2 columns, is it always the last one that can be empty, or any column?

[Cedric]

If you can have an arbitrary number of columns, here is how you can eliminate empty cells:

 % Test data set.
 D = {'AEA', 'AEA00', 'ABC'; 'AW', [], 'WP000'; 'AB', [] , []} ;

 nRows = size( D, 1 ) ;
 Dcat  = cell( nRows, 1 ) ;
 for k = 1 : nRows
    isE = cellfun( 'isempty', D(k,:) ) ;
    tmp = sprintf( '%s-', D{k,~isE} ) ;
    Dcat{k} = tmp(1:end-1) ;
 end

Running this, you get:

 >> Dcat
 Dcat = 
    'AEA-AEA00-ABC'
    'AW-WP000'
    'AB'

[Ashish]

No, there can be more than 2 columns. The thing is that the 1st column will always be there. Rest, any other column can be empty!

[Ashish]

Perfect!

Thanks for the example and understanding my doubt clearly.

[Cedric]

My pleasure!