How can I remove entire rows from a table based on NaN values in a specific column (cell data type) in a table?

Question

Jens Allaert 2018-11-15

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/430011-how-can-i-remove-entire-rows-from-a-table-based-on-nan-values-in-a-specific-column-cell-data-type

回答： Peter Perkins 2018-11-15

Hello,

I am looking for a solution on how to remove rows from a table based on NaN values in a specific column containing a cell data type. Below I illustrate an example table. I am using MATLAB 2018a.

var1 = [1;2;3;4];
var2 = num2cell(NaN(4,1));
var2(1:3,1) = {'yes'};
var3 = num2cell(NaN(4,1));
input = table(var1,var2,var3);

This results in the following table:

1 'yes' NaN

2 'yes' NaN

3 'yes' NaN

4 NaN NaN

I want to remove the rows containing NaN in the second column (which is a cell type column), so the output looks as follows:

1 'yes' NaN

2 'yes' NaN

3 'yes' NaN

Thanks in advance!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Peter Perkins 2018-11-15

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/430011-how-can-i-remove-entire-rows-from-a-table-based-on-nan-values-in-a-specific-column-cell-data-type#answer_347180

rmmissing. It's a one-liner.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 2

Luna 2018-11-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/430011-how-can-i-remove-entire-rows-from-a-table-based-on-nan-values-in-a-specific-column-cell-data-type#answer_347086

在 MATLAB Online 中打开

Hello Jens,

Please try this:

idx = find(cell2mat(cellfun(@(x) strcmp(num2str(x),'NaN'), input.var2, 'UniformOutput', false)));
newTableCell = table2cell(input);
newTableCell(idx,:) = [];
input = cell2table(newTableCell);

2 个评论
显示无隐藏无

Jens Allaert 2018-11-15

It works! Thanks a bunch! Altough this code has one limitation, beeing the original table column names that are removed in the output. Would it be possible to adjust the code, so it retains the original column names? My real data has over 50 columns.

Greetings,

Jens

Guillaume 2018-11-15

Sorry, but the whole code is way over complicated. find is not needed (logical arrays work just fine). comverting numbers to strings and comparing to 'NaN' string is going to be slow. isnan works just fine, and there's never any reason to convert a table to cell or matrix. You can always work on the table itself.

请先登录，再进行评论。

Answer 3

Guillaume 2018-11-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/430011-how-can-i-remove-entire-rows-from-a-table-based-on-nan-values-in-a-specific-column-cell-data-type#answer_347105

在 MATLAB Online 中打开

Note that it's not usually a good idea to mix textual and numeric content in the same column of a table. Additionally, since your column is a cell array, you can now store whole matrices in each row. Is that likely to happen? Your example only show scalar numeric NaN in the cell.

Assuming it's always going to be scalar numeric:

t = table((1:4)', {'yes';'yes';'yes';NaN}, NaN(4,1))  %don't use input as a variable name!
t(cellfun(@(v) isnumeric(v) && isnan(v), t.Var2), :) = []

If the element can be a matrix and you want to remove the row if all elements are NaN:

t = table((1:5)', {'yes';'yes';'yes';[NaN NaN NaN]; [NaN 3 2 NaN]}, NaN(5,1))
t(cellfun(@(v) isnumeric(v) && all(isnan(v(:))), t.Var2), :) = []

If it's if any element of the matrix is NaN, then replace the all by any.

Note that since R2018b, all(isnan(v(:))) can be replaced by all(isnan(v), 'all') (and any(isnan(v(:))) by any(isnan(v), 'all') since these functions can now operate on all dimensions at once.