MATLAB: Replace missing values in a row with the set of all possible values in that row for a data table.

Question

If I have a large dataset and there are some missing values in each row. I want to repalace missing values of a row with the set of all possible values of that row. How can I do so?

Answer 1

Hi,

the following script should do what you want:

% set options and import data
opts = spreadsheetImportOptions("NumVariables", 5);
opts.Sheet = "Sheet1";
opts.DataRange = "B2:F6";
opts.VariableTypes = ["double", "double", "double", "double", "double"];
Data = readtable("example.xlsx", opts, "UseExcel", false);
Data_new = table2cell(Data);
% manipulate data the way you wish
for k = 1:size(Data,2)
    [r, ~] = find(isnan(Data{:,k}));
    c = unique(Data{:,k});
    c(isnan(c)) = [];
    for d = 1:numel(r)
        Data_new{r(d),k} = c';
    end
end
% clean up
clear opts Data c d r k

The resulting cell array can be seen here:

Best regards

Stephan