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

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?

[Bob Thompson]

I want to repalace missing values of a row with the set of all possible values of that row.

I'm a little confused. Do you want to replace each value with a set of values, replace all missing values with an equal number of possible values, or just attach all possible values at the end?

[jassi singh]

Suppose, I have 5*5 dimension data table. In 1st row, all possible values are either 1 or 2. Some values are missing in 1st row (NaN). I want to replace NaN with {1,2}. Similarly, in 2nd row also some values are missing and possible values for the row are 1 or 2 or 3.So in 2nd row, NaN is replaced by {1,2,3} and so on.

I have attached the xlsx file in which original and replaced data tables are given. How can I get this replaced datatable into matlab?

Answer 1

[Stephan]

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