Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Filter tab file based on column value

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Filter tab file based on column value
# 1  
Old 05-31-2017
Filter tab file based on column value
Hello
I have a tab text file with many columns and have to filter rows ONLY if column 22 has the value of '0', '1', '2' or '3' (out of 0-5).

If Column 22 has value '0','1', '2' or '3' (highlighted below), then remove anything less than 10 and greater 100 (based on column 5) AND remove anything less than 5.0 (this column has decimals) (based on column 13).

I tried the following codes but I am not sure how to insert the condition of do the following only if column 22 has the following values of 0 to 3 and retain the other rows (ie that has values 4 and 5) as it is in the output file

Code:
awk -F "\t"  'NR==1; NR>1 {if ($5 > 10 && $5<100 && $13>5.0) print $0}' input.txt > output.txt

Code:
awk -F'\t' 'NR==1 || 0<=$22 && $22<=3 && 10<$5 && $5<100 && 5<$13' input > output

My input file is as follows

and

Code:
 Column1    ...    Column5    ...    Column13    ..    Column22    
ID1    a1            5                 0                5                
ID2    a2            10              1.2                0
ID3    a3            4                5.6                1
ID4    a4            300            2.6                2                
ID5    a5            40              32                0
ID6    a6            200             4.6                3
ID7    a7            200             4.5                5                
ID8    a8            3456           4.9                4

and my desired output is

Code:
Column1 ..   Column5 ..   Column13 ..   Column22
ID1    a1            5                        0           5
ID5    a5            40                     32           0
ID7    a7            200                   4.5          5
ID8    a8            3456                 4.9         4

Any help is appreciated. Thank you
Last edited by nans; 05-31-2017 at 10:10 AM..
# 2  
Old 05-31-2017
How about
Code:
awk 'NR == 1 || !(($22 <= 3) && ($5 < 10 || $5 > 100 || $13 < 5)) ;' file
Column1        Column5        Column13        Column22    
ID1    a1            5                 0                5                
ID5    a5            40              32                0
ID7    a7            200             4.5                5                
ID8    a8            3456           4.9                4

This User Gave Thanks to RudiC For This Post:
nans
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Replace a column in tab delimited file with column in other tab delimited file,based on match

Hello Everyone.. I want to replace the retail col from FileI with cstp1 col from FileP if the strpno matches in both files FileP.txt ... (2 Replies)
Discussion started by: YogeshG
2 Replies

2. Shell Programming and Scripting

Filter Row Based On Max Column Value After Group BY

Hello Team, Need your expertise on following: Here is the set of data: C1|4|C1SP1|A1|C1BP1|T1 C1|4|C1SP2|A1|C1BP2|T2 C2|3|C2SP1|A2|C2BP1|T2 C3|3|C3SP1|A3|C3BP1|T2 C2|2|C2SP2|A2|C2BP2|T1 I need to filter above date base on following two steps: 1. Group them by column 1 and 4 2.... (12 Replies)
Discussion started by: angshuman
12 Replies

3. Shell Programming and Scripting

UNIX command -Filter rows in fixed width file based on column values

Hi All, I am trying to select the rows in a fixed width file based on values in the columns. I want to select only the rows if column position 3-4 has the value AB I am using cut command to get the column values. Is it possible to check if cut -c3-4 = AB is true then select only that... (2 Replies)
Discussion started by: ashok.k
2 Replies

4. Shell Programming and Scripting

Filter records based on 2nd file

Hello, I want to filter records of a file if they fall in range associated with a second file. First the chr number (2nd col of 1st file and 1st col of 2nd file) needs to be matched. Then if the 3rd col of the first file falls within any of the ranges specified by the 2nd and 3rd cols , then... (4 Replies)
Discussion started by: ritakadm
4 Replies

5. Linux

Filter a .CSV file based on the 5th column values

I have a .CSV file with the below format: "column 1","column 2","column 3","column 4","column 5","column 6","column 7","column 8","column 9","column 10 "12310","42324564756","a simple string with a , comma","string with or, without commas","string 1","USD","12","70%","08/01/2013",""... (2 Replies)
Discussion started by: dhruuv369
2 Replies

6. Shell Programming and Scripting

awk filter based on column value (variable value)

Hi, I have a requirement to display/write the 3rd column from a file based on the value in the column 3. Ex: Data in the File (comma delimited) ID,Value,Description 1,A,Active 1,I,Inactive 2,S,Started 1,N,None 2,C,Completed 2,F,Failed I need to first get a list of all Unique IDs in... (7 Replies)
Discussion started by: kiranredz
7 Replies

7. Shell Programming and Scripting

Filter the column and print the result based on condition

Hi all This is my output of the some SQL Query TABLESPACE_NAME FILE_NAME TOTALSPACE FREESPACE USEDSPACE Free ------------------------- ------------------------------------------------------- ---------- --------- ---------... (2 Replies)
Discussion started by: jhon
2 Replies

8. Shell Programming and Scripting

intent: df -kh | filter based on capacity (used space) column where % > 85

I want to accomplish this in sh, however if the capability exists only in other shells elsewhere that's acceptable. % df -kh Filesystem size used avail capacity Mounted on ... /dev/dsk/c0t0d0s1 103G 102G 23M 100% /export/DISK15 ... # output... (5 Replies)
Discussion started by: ProGrammar
5 Replies

9. Shell Programming and Scripting

filter out certain column from a file

Hi all, I have this file, how can i remove the ID Number and Cardholder Name from the file ?  Thanks CT (4 Replies)
Discussion started by: CamTu
4 Replies

10. Shell Programming and Scripting

filter based on column value

I have a file with colon separated values.. the sample is attached below. No of fields in each record/line is dependent on the value of field53. What I need to do is to design a special filter based on specific requirement of some match of values in particular column or combination of columns. ... (2 Replies)
Discussion started by: rraajjiibb
2 Replies
Login or Register to Ask a Question