deleting rows under a certain condition

Shell Programming and Scripting
1

there are 20 variables and I would like to delete the rows if 13th-20th columns are all NA.

Thank you!

FID IID aspirpre statihos fibrahos ocholhos arbhos betabhos alphbhos cacbhos diurehos numbcig.x toast1 toast2 toast3 toast4 ischoth1 ischoth2 ischoth3 ischoth4
101 101 1 1 1 1 1 2 1 2 1 15 1 0 0 0 0 0 0 0
102 102 2 0 0 0 0 0 0 0 0 NA NA NA NA NA NA NA NA NA
103 103 2 2 1 1 1 2 1 1 1 NA 0 0 0 0 0 0 0 0
104 104 1 0 0 0 0 0 0 0 0 15 NA NA NA NA NA NA NA NA
105 105 1 2 1 1 1 1 1 1 1 NA 0 0 1 0 0 0 0 0
106 106 2 1 1 1 1 1 1 1 1 12 NA NA NA NA NA NA NA NA
107 107 1 2 1 1 1 1 1 1 1 15 0 0 1 0 0 0 0 0
108 108 1 1 1 1 2 1 1 2 2 NA 0 0 1 0 0 0 0 0
109 109 2 2 1 1 2 1 1 1 2 25 0 0 0 1 0 0 0 0
110 110 1 0 0 0 0 0 0 0 0 5 NA NA NA NA NA NA NA NA
2 
awk '{ P=0 ; for(N=13; (!P)&&(N<=20); N++) if($N != "NA") P=1 } P' filename
1 Like
3

This is an example using 'sed':

$ cat test.txt
FID IID aspirpre statihos fibrahos ocholhos arbhos betabhos alphbhos cacbhos diurehos numbcig.x toast1 toast2 toast3 toast4 ischoth1 ischoth2 ischoth3 ischoth4
101 101 1 1 1 1 1 2 1 2 1 15 1 0 0 0 0 0 0 0
102 102 2 0 0 0 0 0 0 0 0 NA NA NA NA NA NA NA NA NA
103 103 2 2 1 1 1 2 1 1 1 NA 0 0 0 0 0 0 0 0
104 104 1 0 0 0 0 0 0 0 0 15 NA NA NA NA NA NA NA NA
105 105 1 2 1 1 1 1 1 1 1 NA 0 0 1 0 0 0 0 0
106 106 2 1 1 1 1 1 1 1 1 12 NA NA NA NA NA NA NA NA
107 107 1 2 1 1 1 1 1 1 1 15 0 0 1 0 0 0 0 0
108 108 1 1 1 1 2 1 1 2 2 NA 0 0 1 0 0 0 0 0
109 109 2 2 1 1 2 1 1 1 2 25 0 0 0 1 0 0 0 0
110 110 1 0 0 0 0 0 0 0 0 5 NA NA NA NA NA NA NA NA


$ sed '/NA NA NA NA NA NA NA NA$/d' test.txt
FID IID aspirpre statihos fibrahos ocholhos arbhos betabhos alphbhos cacbhos diurehos numbcig.x toast1 toast2 toast3 toast4 ischoth1 ischoth2 ischoth3 ischoth4
101 101 1 1 1 1 1 2 1 2 1 15 1 0 0 0 0 0 0 0
103 103 2 2 1 1 1 2 1 1 1 NA 0 0 0 0 0 0 0 0
105 105 1 2 1 1 1 1 1 1 1 NA 0 0 1 0 0 0 0 0
107 107 1 2 1 1 1 1 1 1 1 15 0 0 1 0 0 0 0 0
108 108 1 1 1 1 2 1 1 2 2 NA 0 0 1 0 0 0 0 0
109 109 2 2 1 1 2 1 1 1 2 25 0 0 0 1 0 0 0 0

if those are tabs(x'09') and not spaces between the columns you will need to use a '\t':

$ sed '/NA\tNA\tNA\tNA\tNA\tNA\tNA\tNA$/d' test.txt
FID     IID     aspirpre        statihos        fibrahos        ocholhos        arbhos  betabhos        alphbhos        cacbhos diurehos        numbcig.x   toast1   toast2  toast3  toast4  ischoth1        ischoth2        ischoth3        ischoth4
101     101     1       1       1       1       1       2       1       2       1       15      1       0       0       0       0       0       0       0
103     103     2       2       1       1       1       2       1       1       1       NA      0       0       0       0       0       0       0       0
105     105     1       2       1       1       1       1       1       1       1       NA      0       0       1       0       0       0       0       0
107     107     1       2       1       1       1       1       1       1       1       15      0       0       1       0       0       0       0       0
108     108     1       1       1       1       2       1       1       2       2       NA      0       0       1       0       0       0       0       0
109     109     2       2       1       1       2       1       1       1       2       25      0       0       0       1       0       0       0       0
1 Like