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

Question

jassi singh 2019-2-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/442718-replace-missing-values-in-a-row-with-the-set-of-all-possible-values-in-that-row-for-a-data-table

回答： Stephan 2019-2-2

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?

2 个评论
显示无隐藏无

Bob Thompson 2019-2-1

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 2019-2-2

example.xlsx

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 2019-2-2

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/442718-replace-missing-values-in-a-row-with-the-set-of-all-possible-values-in-that-row-for-a-data-table#answer_359217

在 MATLAB Online 中打开

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