How can I remove entire rows from a table based on NaN values in a specific column (cell data type) in a table?
6 次查看(过去 30 天)
显示 更早的评论
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 个评论
回答(3 个)
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 个评论
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.
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.
0 个评论
另请参阅
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!