Find and replacing elements in cell array

Hi,

I have this data table:

The headings of columns from 76 to 81 ('DS0'-'DS5') represent a value from 0 to 5 respectively ('DS0' = 0 , 'DS1' = 1, etc).

The columns from 83 to 110 are the ones I will have to fill.

My problem for example is the following:

Taking the array [3,5,6,8] in the position {2,76} of 'DS0', i have to put the value 0 in the columns 'M3', 'M5', 'M6', 'M8'.

I should to do this for every array and number of each row, except for the '-1' ('-1' = NaN);

I hope I was clear. Thanks.

[Adam Danz]

Is the image (1) a table that has already been imported to Matlab or (2) is it a spreadsheet?

If 1, why not use the headers as variableNames to your table instead of consuming the first row?

If 2, how are you loading the variables into Matlab? Are they in the form of (3) a table or (4) a cell array?

[Adam Danz]

@ Davide Conti, any problems implementing the proposed answer?

Answer 1

[Adam Danz]

This solution has the following assumptions.

1. The data has been read into Matlab in the form of a table. [hint: use readtable()]
2. The table contains headers 'DS0' and 'M1', 'M2' .... Mn [hint: when importing, read headers from your first row of data]
3. Headers M1 to Mn are in consecutive order with no other non-Mn columns in between.

If those assumptions are met, this solution does the following.

1. Retreives all column indices from DS0 into 1 vector and retreives the paired row indices.
2. Creates a temporary matrix of NaN values. These will be the default value for rows of Mn that do not contain 0. You can replace the NaN values with any other default value.
3. The column and row vectors, are converted to linear indices which are used to set the selected elements of the temporary matrix to 0.
4. The temporary matrix is then loaded back into the table.

Furthermore, the solution detects where your M-columns are so it is robust to their position within the table and it avoids loops.

See inline comments for details. The solution contains 8 lines of code.

% Create a demo table that is similar to the description in the question
T = cell2table(arrayfun(@(x)unique(randi(9,1,9)),1:10,'UniformOutput',false)', 'VariableNames',{'DS0'}); 
T = [T, array2table(nan(10,9),'VariableNames', compose('M%d',1:9))]; 

% Inspect first few rows of table
head(T)

% Get all column indices stored in DS0 and create the paired row indices
colInd = [T.DS0{:,:}]; Re
rowInd = cell2mat(arrayfun(@(r)repmat(r,size(T.DS0{r})),1:numel(T.DS0),'UniformOutput',false)); 

% Going forward, it is assumed that if you have n M-columns, the
% header names are M1 to Mn in consecutive order without any other
% columns in between the M columns.

% This line below counts the number of M-columns in your table.
numMcols = sum(cellfun(@sum,regexp(T.Properties.VariableNames,'^M\d+$'))); 

% Pre-allocate a temporary matrix with NaN values (or you can decide what your default value will be)
tempMat = nan(size(T,1),numMcols); 

% Replace the selected indices with 0
ind = sub2ind(size(tempMat),rowInd,colInd); 
tempMat(ind) = 0; 

% Put the tempMat back into the table
firstMCol = find(strcmpi(T.Properties.VariableNames, 'M1'),1);  %find col number of M1
T{:,firstMCol:firstMCol+numMcols-1} = tempMat; 

Results

head(T)
ans =
  8×10 table
        DS0         M1     M2     M3     M4     M5     M6     M7     M8     M9 
    ____________    ___    ___    ___    ___    ___    ___    ___    ___    ___
    {1×4 double}    NaN    NaN      0    NaN      0      0    NaN      0    NaN
    {1×5 double}      0    NaN      0    NaN      0    NaN      0      0    NaN
    {1×6 double}      0      0      0    NaN      0    NaN    NaN      0      0
    {1×6 double}      0    NaN    NaN      0      0    NaN      0      0      0
    {1×6 double}      0      0      0      0      0    NaN    NaN    NaN      0
    {1×5 double}    NaN      0      0    NaN      0      0    NaN    NaN      0
    {1×5 double}      0    NaN      0      0    NaN      0    NaN      0    NaN
    {1×7 double}      0      0    NaN      0      0      0      0      0    NaN
    
T.DS0{1:3}
ans =
     3     5     6     8
ans =
     1     3     5     7     8
ans =
     1     2     3     5     8     9