Clear Filters
Clear Filters

Operator '>' is not supported for operands of type 'cell'.

3 views (last 30 days)
Can someone help with the logical operator " extract rows +/- 1 where columns T And V are not zero"? Thank you!
clear
close all
clc
T1= readtable("Performance history + distress ( d-3) reduced.xls",VariableNamingRule="preserve");
T2 = readtable("D-3 Road sections (HPMA) Tran red v2.xlsx",VariableNamingRule="preserve");
% Join tables
joinedData = innerjoin(T2,T1,"LeftKeys",["FROM_REFP","TO_REFP"],"RightKeys",...
["begin_refp","end_refp"]);
T = joinedData;
T.Properties.VariableNames = num2cell(char(64+(1:size(T,2))));
%Set the empty cell "iri" ==0
if isempty(T{:,22})
T(:,22) =0;
end
% set H = G where H == 0
idx = T.H == 0;
T.H(idx) = T.G(idx);
% extract rows +/- 1 where columns T And V are not zero
idx = T.T>0 | T.V >0;
idx = any([idx [false; idx(1:end-1)] [idx(2:end); false]],2);
TT= T(idx,:); %extracted_rows
writetable(TT, "Merged&CleanData.csv")
  6 Comments
Sanley Guerrier
Sanley Guerrier on 8 Dec 2023
I want to extract non empty rows in Column R that correspond to nonzero row in column T and add +/- 1 rows.

Sign in to comment.

Accepted Answer

Walter Roberson
Walter Roberson on 8 Dec 2023
Moved: Walter Roberson on 8 Dec 2023
filename = 'T.xlsx';
opt = detectImportOptions(filename);
opt = setvaropts(opt, [2:6,13:15,18:19], 'Prefixes', "'", 'Suffixes', "'");
T = readtable(filename, opt);
T.Properties.VariableNames = num2cell(char(64+(1:size(T,2))));
mask = T.T ~= 0 & ~strcmp(T.R, "");
mask = conv(mask, [1 1 1], 'same') > 0;
selected_rows = T(mask,:);
selected_rows
selected_rows = 0×23 empty table
  5 Comments
Sanley Guerrier
Sanley Guerrier on 8 Dec 2023
Moved: Voss on 9 Dec 2023
It is working perfectly fine.
Appreciate you, Voss.

Sign in to comment.

More Answers (2)

VBBV
VBBV on 8 Dec 2023
T is a table array/datatype, and you try to access variable inside the table with same name , this is not valid operation
idx = T.T>0 | T.V >0; %T.T is not valid operation
  7 Comments
Walter Roberson
Walter Roberson on 8 Dec 2023
Nearly the only variable name that you cannot use in a table is Properties .
T = table((1:5).', 'VariableNames', {'T'})
T = 5×1 table
T _ 1 2 3 4 5
T.T
ans = 5×1
1 2 3 4 5
Stephen23
Stephen23 on 8 Dec 2023
"T.T is not valid operation"
Why not?
T = array2table(pi, "VariableNames","T")
T = table
T ______ 3.1416
T.T % why do you think that this is "not valid operation" ?
ans = 3.1416

Sign in to comment.


Voss
Voss on 8 Dec 2023
T = readtable('T.xlsx');
T.Properties.VariableNames = num2cell(char(64+(1:size(T,2))))
T = 16×23 table
A B C D E F G H I J K L M N O P Q R S T U V W ___ _______ _____________ _____________ ____________________ _________ ____ ____ ____ ___ ___ ___ _____________ _______ _________ ____ ____ ______ _________ _____ __ __ __ 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1977 {''''} {''BAB''} 0 20 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1981 {''''} {''BAB''} 0 20 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1984 {''''} {''BAB''} 0 2 0 26 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1985 {''''} {''BAB''} 0 4 0 30 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1988 {''''} {''BAB''} 0 10 0 30 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1989 {''''} {''BAB''} 0 2 0 40 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1990 {''''} {''BAB''} 223 4 0 38 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1991 {''''} {''BAB''} 165 10 0 30 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1992 {''''} {''BAB''} 222 12 0 24 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1994 {''''} {''BAB''} 73 46 0 2 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1996 {''''} {''BAB''} 79 40 0 10 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1998 {''''} {''BAB''} 65 44 0 10 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2000 {''''} {''BAB''} 78 12 32 6 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2001 {''''} {''BAB''} 99.5 0 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2002 {''''} {''BAB''} 119.5 18 26 6 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2003 {''''} {''BAB''} 142 0 0 0
"extract rows +/- 1 where columns T And V are not zero"
idx = T.T ~= 0 & T.V ~= 0;
idx = any([idx [false; idx(1:end-1)] [idx(2:end); false]],2);
result = T(idx,:)
result = 5×23 table
A B C D E F G H I J K L M N O P Q R S T U V W ___ _______ _____________ _____________ ____________________ _________ ____ ____ ____ ___ ___ ___ _____________ _______ _________ ____ ____ ______ _________ _____ __ __ __ 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 1998 {''''} {''BAB''} 65 44 0 10 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2000 {''''} {''BAB''} 78 12 32 6 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2001 {''''} {''BAB''} 99.5 0 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2002 {''''} {''BAB''} 119.5 18 26 6 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2003 {''''} {''BAB''} 142 0 0 0
  3 Comments
Voss
Voss on 8 Dec 2023
Edited: Voss on 8 Dec 2023
T = readtable('T.xlsx');
T.Properties.VariableNames = num2cell(char(64+(1:size(T,2))));
"extract non empty rows in Column R that correspond to nonzero row in column T and add +/- 1 rows"
idx = T.T ~= 0 & ~strcmp(T.R,char([39 39]));
% idx = T.T ~= 0 & ~strcmp(T.R,''''''); % alternate, equivalent to the above
idx = any([idx [false; idx(1:end-1)] [idx(2:end); false]],2);
result = T(idx,:)
result = 12×23 table
A B C D E F G H I J K L M N O P Q R S T U V W ___ _______ _____________ _____________ ____________________ _________ ____ ____ ____ ___ ____ ___ _____________ _______ _________ ____ ____ _______________ _________ _____ __ __ __ 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2004 {'''' } {''BAB''} 138.5 6 24 26 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2005 {''BAB Rural''} {''BAB''} 55 0 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2006 {'''' } {''BAB''} 47 0 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2009 {'''' } {''BAB''} 54.5 0 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2010 {''Chip Seal''} {''BAB''} 60 26 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2011 {'''' } {''BAB''} 75.5 0 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2012 {'''' } {''BAB''} 69.5 38 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2013 {''Patching'' } {''BAB''} 72 0 0 0 107 {''U''} {''0+0.000''} {''0+0.151''} {''2010 RECON BIT''} {''BAB''} 2005 2013 4500 2.9 166 107 {''MN107-U''} {''U''} {''BAB''} 4500 2014 {'''' } {''BAB''} 88.5 38 4 0 238 {''U''} {''0+0.000''} {''0+0.302''} {''2014 BAB'' } {''BAB''} 2014 2014 1400 3.4 82.5 238 {''MN238-U''} {''U''} {''BAB''} 1400 2013 {'''' } {''BAB''} 293.5 10 20 12 238 {''U''} {''0+0.000''} {''0+0.302''} {''2014 BAB'' } {''BAB''} 2014 2014 1400 3.4 82.5 238 {''MN238-U''} {''U''} {''BAB''} 1400 2014 {''BAB Urban''} {''BAB''} 70.5 0 0 0 238 {''U''} {''0+0.000''} {''0+0.302''} {''2014 BAB'' } {''BAB''} 2014 2014 1400 3.4 82.5 238 {''MN238-U''} {''U''} {''BAB''} 1400 2015 {'''' } {''BAB''} 69.5 0 0 0
For this purpose, I considered the 1x2 character vector where each element is the single quote character (i.e., the character vector char([39 39])) to be the "empty" entries in column R you want to avoid. No doubt using setvaropts as in Walter's approach results in a table whose contents are easier to deal with.
Sanley Guerrier
Sanley Guerrier on 10 Dec 2023
Thanks a lot, Voss. Both approaches work perfectly great. Your comment is very helpful.

Sign in to comment.

Products

Community Treasure Hunt

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

Start Hunting!