Find and replacing elements in cell array

23 views (last 30 days)
Davide Conti
Davide Conti on 31 Oct 2019
Edited: Adam Danz on 4 Nov 2019
Hi,
I have this data table:
Immagine.png
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.
  2 Comments
Adam Danz
Adam Danz on 1 Nov 2019
Edited: Adam Danz on 4 Nov 2019
@ Davide Conti, any problems implementing the proposed answer?

Sign in to comment.

Answers (1)

Adam Danz
Adam Danz on 31 Oct 2019
Edited: Adam Danz on 31 Oct 2019
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

Categories

Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by