Delete Empty Rows in a Cell Array

Question

bah327hp bah327hp 2017-6-26

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/346333-delete-empty-rows-in-a-cell-array

编辑： bah327hp bah327hp 2017-7-3

采纳的回答： Image Analyst

在 MATLAB Online 中打开

Hello. Here's what I am trying to do.

1. Read in data from Excel in a cell array.

2. Delete the empty rows (i.e., rows with no data).

3. Write the array to a sheet in Excel.

So far, my code deletes the first empty row but then goes no farther.

Here is what I have based on https://stackoverflow.com/questions/31818057/deleting-empty-rows-in-a-cell-array:

 [~, ~, DeleteEmptyWilliams]=xlsread('StimuliInExcel.xlsx', 'WilliamsList')
    for m = 1:124
        NewWilliams=DeleteEmptyWilliams(~all(cellfun('isempty',DeleteEmptyWilliams(:,1:8)), 2),:)
    end
 xlswrite('StimuliInExcel.xlsx',NewWilliams,'WilliamsListNoEmpty')

I have also tried the following code by adding (m,1:8), but it doesn't work either---instead, I get this error: "Index exceeds matrix dimensions". I expect this error occurred because I am deleting rows as I go, so the array's dimensions change.

 [~, ~, DeleteEmptyWilliams]=xlsread('StimuliInExcel.xlsx', 'WilliamsList')
for m = 1:124
    NewWilliams=DeleteEmptyWilliams(~all(cellfun('isempty',DeleteEmptyWilliams(m,1:8)), 2),:)
end
xlswrite('StimuliInExcel.xlsx',NewWilliams,'WilliamsListNoEmpty')

I tried the following code based on the suggestion at https://www.mathworks.com/matlabcentral/newsreader/view_thread/164617, but that did not work. I get this error: "Input array is empty".

 [~, ~, DeleteEmptyWilliams]=xlsread('StimuliInExcel.xlsx', 'WilliamsList')
for m = 1:124
    NewWilliams=DeleteEmptyWilliams(all(cellfun(@isempty,DeleteEmptyWilliams(:,1:8)), 2),:)
end
xlswrite('StimuliInExcel.xlsx',NewWilliams,'WilliamsListNoEmpty')

I would appreciate any help you can provide.

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

请先登录，再进行评论。

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

Answer 1

Image Analyst 2017-6-30

3
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/346333-delete-empty-rows-in-a-cell-array#answer_272533

编辑：Image Analyst 2017-6-30

Why not just use readtable() to read in the file, then use all() and ismissing() to remove totally empty rows?

Attach a workbook with the paperclip icon if you want a demo.

3 个评论
显示 1更早的评论隐藏 1更早的评论

Image Analyst 2017-7-3

在 MATLAB Online 中打开

Try this:

t = readtable('StimuliInExcel.xls', 'Sheet', 'WilliamsList')
% Extract columns 3-8
t = t(:, 3:8)
% Find out which entries are missing
notThere = ismissing(t)
% Find out what rows have every column as missing
badRows = all(notThere, 2)
% Extract only good rows
t = t(~badRows, :)

bah327hp bah327hp 2017-7-3

在 MATLAB Online 中打开

When I use this code, it works perfectly. I really appreciate your help!

I added Name-Value pair arguments to readtable() and writetable() so that the first row of my data would not be read or written as variable names.

The entire code looks like this:

 t=readtable('StimuliInExcel.xls', 'Sheet', 'WilliamsList', 'ReadVariableNames', false)
 notThere=ismissing(t)
 badrows=all(notThere,2)
 t=t(~badrows,:)
 writetable(t, 'StimuliInExcel.xls', 'Sheet', 'WilliamsListNoEmpty', 'WriteVariableNames', false)

请先登录，再进行评论。

Answer 2

dpb 2017-6-26

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/346333-delete-empty-rows-in-a-cell-array#answer_272020

编辑：dpb 2017-6-27

在 MATLAB Online 中打开

[~,~,raw]=xlsread('StimuliInExcel.xlsx', 'WilliamsList');
raw=raw(~all(cellfun(isempty,raw),2),:);
xlswrite('StimuliInExcel.xlsx',raw,'WilliamsListNoEmpty')

14 个评论
显示 12更早的评论隐藏 12更早的评论

dpb 2017-6-29

编辑：dpb 2017-6-29

在 MATLAB Online 中打开

No. NaN isn't empty, it's "not a number". If they were empty you would see simply a set of empty [].

Illustration...

>> c=num2cell(nan(2,3))  % a cell array of nan's
c = 
  [NaN]    [NaN]    [NaN]
  [NaN]    [NaN]    [NaN]
>> cellfun(@isempty,c)     % is it empty??  (No)
ans =
   0     0     0
   0     0     0
>> c=cell(2,3)             % an empty cell array 
c = 
  []    []    []
  []    []    []
>> cellfun(@isempty,c)     % shows each cell is (empty, that is) (&)
ans =
   1     1     1
   1     1     1
>> c=num2cell([nan(2,3); randn(1,3)])  % a mixed array
c = 
  [    NaN]    [    NaN]    [    NaN]
  [    NaN]    [    NaN]    [    NaN]
  [-1.1471]    [-1.0689]    [-0.8095]
>> all(cellfun(@isfinite,c),2)   % which rows are all ok (finite)
ans =
   0
   0
   1
>> c(all(cellfun(@isfinite,c),2),:)  % save those only
ans = 
  [-1.1471]    [-1.0689]    [-0.8095]
>> cell2mat(c(all(cellfun(@isfinite,c),2),:))
ans =
 -1.1471   -1.0689   -0.8095
>>

NB: the last which is what you really may want to do before writing back out to convert from the cell array to a numeric native double array.

Doing these kinds of exercises at the keyboard/command window will go a long ways towards learning how to manipulate cell arrays in particular and to figure out how to accomplish goals such as this.

Using small subsets that are able to see results in toto on one screen helps too; the size of real datasets often overwhelms the key lessons by just the sheer volume of output but the syntax is universal irrespective of size.

(&) Note that the cell array itself is not empty; it's only the individual cell content within the array. Hence the need for cellfun --

> c=cell(2,3)   % the same cell array 
c = 
  []    []    []
  []    []    []
>> isempty(c)     % check if the array is empty (no!)
ans =
   0
>>

You note the array itself is NOT empty; it contains six cells. It just so happens that all six cells are empty, but the container isn't--big difference.

dpb 2017-7-3

在 MATLAB Online 中打开

[d,t,r]=xlsread('StimulilnExcel.xls');

returns for r

>> r
r = 
    [1]    [NaN]    'RunExperiment'    'Scene1_ABS_CON'     '1_CUP'                [1]    [0]    [ 1]    '.'
    [1]    [NaN]    'RunExperiment'    'Scene2_ABS_CON'     '1_CUP'                [0]    [1]    [ 1]    '.'
    [1]    [NaN]    'RunExperiment'    'Scene1_ABS_CON'     '2_TOILET_PAPER'       [0]    [1]    [ 1]    '.'
    [1]    [NaN]    'RunExperiment'    'Scene2_ABS_CON'     '2_TOILET_PAPER'       [1]    [0]    [ 1]    '.'
    [1]    [NaN]    'RunExperiment'    'Scene3_ABS_CON'     '3_BOWL'               [1]    [0]    [ 2]    '.'
    [1]    [NaN]    'RunExperiment'    'Scene4_ABS_CON'     '3_BOWL'               [0]    [1]    [ 2]    '.'
    [1]    [NaN]    'RunExperiment'    'Scene3_ABS_CON'     '4_GLOVE'              [0]    [1]    [ 2]    '.'
...

There are no records with missing values for the full record; the second column is all [NaN] and it looks like the last column isn't of any value, either, but the records are all complete with data in every column except 2.

Hence, you're barking up the wrong tree here...what was the real problem??

bah327hp bah327hp 2017-7-3

If you visually inspect the Excel file, the CompleteList sheet has no missing values, but the WilliamsList and PalmerList sheets do. Image Analyst provided a great answer.

bah327hp bah327hp 2017-7-3

编辑：bah327hp bah327hp 2017-7-3

Thank you for all your help and the information you provided!

请先登录，再进行评论。

Delete Empty Rows in a Cell Array

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

采纳的回答

3 个评论
显示 1更早的评论隐藏 1更早的评论

更多回答（1 个）

14 个评论
显示 12更早的评论隐藏 12更早的评论

另请参阅

类别

标签

Community Treasure Hunt

Delete Empty Rows in a Cell Array

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

采纳的回答

3 个评论 显示 1更早的评论隐藏 1更早的评论

更多回答（1 个）

14 个评论 显示 12更早的评论隐藏 12更早的评论

另请参阅

类别

标签

Community Treasure Hunt

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

3 个评论
显示 1更早的评论隐藏 1更早的评论

14 个评论
显示 12更早的评论隐藏 12更早的评论