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

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!

Answer 1

[Peter Perkins]

rmmissing. It's a one-liner.

Answer 2

[Luna]

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);

[Jens Allaert]

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]

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]

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.