Delete rows of excel if any empty cell found
Show older comments
Dear everyone,
I have an excel file containing some Stations defined in the first column (attached). Here, as shown below, I have 4 stations. Some stations contain no value (empty cell) like Station 1; and some stations not all cells have values.
Does anyone know how to delete the row if there's any cell with no value detected? In this case, there will be 3 stations left and the rows with empty cells in those 3 stations will be omited :
best regards
3 Comments
Adi Purwandana
on 13 Oct 2023
Walter Roberson
on 13 Oct 2023
I do not see any height information in that table?
If you are trying to sort by depth see sortrows
Adi Purwandana
on 13 Oct 2023
Edited: Adi Purwandana
on 13 Oct 2023
Attached file is the excel file. So, it must be ordered from the shorter (shallower) station to the longer one and so on... Any suggestions? I tried using sortrows but it doesn't solve.
Accepted Answer
I am not certain how you want to sort the ‘Station’ by ‘shortest to the longest’.
Try this —
T1 = readtable('datax.xlsx', 'VariableNamingRule','preserve')
T1{:,end} = cellfun(@(x)datetime(x, 'InputFormat','yyyy-MM-dd''T''HH:mm:ss', 'Format','yyyy-MM-dd HH:mm:ss'), T1{:,end}, 'Unif',0)
T1 = rmmissing(T1)
Stations = accumarray(T1{:,1}, (1:size(T1,1)).', [], @(x){T1(x,:)})
[sz,ix] = sort(cellfun(@(x)size(x,1), Stations));
valid = [sz(sz~=0) ix(sz~=0)]
Stations_Sorted = cat(1,Stations{valid(:,2)}) % Reordered
.
10 Comments
Adi Purwandana
on 13 Oct 2023
Edited: Adi Purwandana
on 13 Oct 2023
Adi Purwandana
on 13 Oct 2023
Star Strider
on 13 Oct 2023
As always, my pleasure!
I was away for a few minutes. That is exactly the approach I would have taken!
Adi Purwandana
on 13 Oct 2023
Edited: Adi Purwandana
on 13 Oct 2023
This will delete ‘Station’ table arrays with no ‘Depth’ value greater than 8 and will retain ‘Station’ table arrays with at least one ‘Depth’ value greater than 8, regardless of what the other ‘Depth’ values are. It also re-numbers the stations after using rmmissing so they begin at 1.
The revised version —
T1 = readtable('datax.xlsx', 'VariableNamingRule','preserve')
T1{:,end} = cellfun(@(x)datetime(x, 'InputFormat','yyyy-MM-dd''T''HH:mm:ss', 'Format','yyyy-MM-dd HH:mm:ss'), T1{:,end}, 'Unif',0)
T1 = rmmissing(T1)
[Su,~,s] = unique(T1.Station,'stable');
T1.Station = s % Renumber 'Station'
Stations = accumarray(T1{:,1}, (1:size(T1,1)).', [], @(x){T1(x,:)}); % Segment Table By Station
% Stations = Stations(cellfun(@(x)~isempty(x), Stations)) % Delete Empty Cells
Lv = cellfun(@(x)any(x.Depth > 8), Stations); % Logical Vector To Select Cells With At Least One 'Depth' > 8
Stations = Stations(Lv); % Select Cells With At Least One 'Depth' > 8
[sz,ix] = sort(cellfun(@(x)size(x,1), Stations)); % Sort By Row Size
Stations_Sorted = cat(1,Stations{ix}) % Reordered
.
Adi Purwandana
on 14 Oct 2023
Great as always!
My curious question... If I want to re-numbering the stations again (here for example I only consider the station with depth >6m), it gives:
Do you know the line I should modify if I want to re-numbering the stations again, so that:
Station 1 --> Station 1
Station 3 --> Station 2
Thank you!
Adi Purwandana
on 14 Oct 2023
Thank you!
As always, my pleasure!
The only thing that comes to mind is to mpve these assignments:
[Su,~,s] = unique(T1.Station,'stable');
T1.Station = s % Renumber 'Station'
to later in the code.
This re-numbers them and then re-sorts them —
T1 = readtable('datax.xlsx', 'VariableNamingRule','preserve')
T1{:,end} = cellfun(@(x)datetime(x, 'InputFormat','yyyy-MM-dd''T''HH:mm:ss', 'Format','yyyy-MM-dd HH:mm:ss'), T1{:,end}, 'Unif',0)
T1 = rmmissing(T1)
% [Su,~,s] = unique(T1.Station,'stable');
% T1.Station = s % Renumber 'Station'
Stations = accumarray(T1{:,1}, (1:size(T1,1)).', [], @(x){T1(x,:)}) % Segment Table By Station
Stations = Stations(cellfun(@(x)~isempty(x), Stations)) % Delete Empty Cells
Lv = cellfun(@(x)any(x.Depth > 6), Stations) % Logical Vector To Select Cells With At Least One 'Depth' > 8
Stations = Stations(Lv); % Select Cells With At Least One 'Depth' > 8
Stations
[sz,ix] = sort(cellfun(@(x)size(x,1), Stations)); % Sort By Row Size
Stations_Sorted = cat(1,Stations{ix}) % Reordered
[Su,~,s] = unique(Stations_Sorted.Station,'stable');
Stations_Sorted.Station = s % Renumber 'Station'
.
Adi Purwandana
on 15 Oct 2023
Star Strider
on 15 Oct 2023
As always, my pleasure!
More Answers (0)
Categories
Find more on Matrices and Arrays in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
