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

7 次查看（过去 30 天）
Jens Allaert
Jens Allaert 2018-11-15
回答： 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!

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

回答（3 个）

Guillaume
Guillaume 2018-11-15
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.
Peter Perkins
Peter Perkins 2018-11-15
rmmissing. It's a one-liner.
Luna
Luna 2018-11-15
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
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
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Numeric Types 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by