Visit Our UNIX and Linux User Community


search column and delete row if greater than value


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting search column and delete row if greater than value
# 1  
Old 09-13-2011
search column and delete row if greater than value

Hi, as the title states i need to find a way to search a column for values great than 1000, and if it is, then delete that row.

An example
Code:
  1   7.021   6.967 116.019 4 U   6.980E+07  0.000E+00 e 0     0     0     0
      2   8.292   7.908 118.063 3 U   1.440E+07  0.000E+00 e 0   821   814   825 #VC 0.50000 #QU 0.676 #SUP  0.92
                                                                1778  1771  1782 #VC 0.50000 #QU 0.737 #SUP  0.92
      3   7.994   7.056 118.728 3 U   6.570E+06  0.000E+00 e 0   682   686   689 #VC 0.50000 #QU 0.499 #SUP  0.77
                                                                1639  1643  1646 #VC 0.50000 #QU 0.548 #SUP  0.77
      4   7.999   7.956 118.787 3 U   4.600E+08  0.000E+00 e 0   682   682   689 #VC 0.50000 #QU 0.774 #SUP  0.95
                                                                1639  1639  1646 #VC 0.50000 #QU 0.774 #SUP  0.95
      5   7.999   7.956 118.787 3 U   4.600E+08  0.000E+00 e 0   682   682   689 #VC 0.50000 #QU 0.774 #SUP  0.95
                                                                1639  1639  1646 #VC 0.50000 #QU 0.774 #SUP  0.95
      6   8.023   7.980 118.984 3 U   5.760E+08  0.000E+00 e 0   682   682   689 #VC 0.25147 #QU 0.695 #SUP  0.99
                                                                 890   890   894 #VC 0.24853 #QU 0.687 #SUP  0.99
                                                                1639  1639  1646 #VC 0.25147 #QU 0.695 #SUP  0.99
                                                                1847  1847  1851 #VC 0.24853 #QU 0.687 #SUP  0.99
      7   8.023   7.980 118.984 3 U   5.760E+08  0.000E+00 e 0   682   682   689 #VC 0.25147 #QU 0.695 #SUP  0.99

TO
Code:
  1   7.021   6.967 116.019 4 U   6.980E+07  0.000E+00 e 0     0     0     0
      2   8.292   7.908 118.063 3 U   1.440E+07  0.000E+00 e 0   821   814   825 #VC 0.50000 #QU 0.676 #SUP  0.92
      3   7.994   7.056 118.728 3 U   6.570E+06  0.000E+00 e 0   682   686   689 #VC 0.50000 #QU 0.499 #SUP  0.77
      4   7.999   7.956 118.787 3 U   4.600E+08  0.000E+00 e 0   682   682   689 #VC 0.50000 #QU 0.774 #SUP  0.95
      5   7.999   7.956 118.787 3 U   4.600E+08  0.000E+00 e 0   682   682   689 #VC 0.50000 #QU 0.774 #SUP  0.95
      6   8.023   7.980 118.984 3 U   5.760E+08  0.000E+00 e 0   682   682   689 #VC 0.25147 #QU 0.695 #SUP  0.99
                                                                 890   890   894 #VC 0.24853 #QU 0.687 #SUP  0.99
      7   8.023   7.980 118.984 3 U   5.760E+08  0.000E+00 e 0   682   682   689 #VC 0.25147 #QU 0.695 #SUP  0.99

Thanks
# 2  
Old 09-13-2011
Code:
awk '{if($11 < 1000) print $0}' input > output.txt

# 3  
Old 09-13-2011
Code:
$
$
$ cat f21
      1   7.021   6.967 116.019 4 U   6.980E+07  0.000E+00 e 0     0     0     0
      2   8.292   7.908 118.063 3 U   1.440E+07  0.000E+00 e 0   821   814   825 #VC 0.50000 #QU 0.676 #SUP  0.92
                                                                1778  1771  1782 #VC 0.50000 #QU 0.737 #SUP  0.92
      3   7.994   7.056 118.728 3 U   6.570E+06  0.000E+00 e 0   682   686   689 #VC 0.50000 #QU 0.499 #SUP  0.77
                                                                1639  1643  1646 #VC 0.50000 #QU 0.548 #SUP  0.77
      4   7.999   7.956 118.787 3 U   4.600E+08  0.000E+00 e 0   682   682   689 #VC 0.50000 #QU 0.774 #SUP  0.95
                                                                1639  1639  1646 #VC 0.50000 #QU 0.774 #SUP  0.95
      5   7.999   7.956 118.787 3 U   4.600E+08  0.000E+00 e 0   682   682   689 #VC 0.50000 #QU 0.774 #SUP  0.95
                                                                1639  1639  1646 #VC 0.50000 #QU 0.774 #SUP  0.95
      6   8.023   7.980 118.984 3 U   5.760E+08  0.000E+00 e 0   682   682   689 #VC 0.25147 #QU 0.695 #SUP  0.99
                                                                 890   890   894 #VC 0.24853 #QU 0.687 #SUP  0.99
                                                                1639  1639  1646 #VC 0.25147 #QU 0.695 #SUP  0.99
                                                                1847  1847  1851 #VC 0.24853 #QU 0.687 #SUP  0.99
      7   8.023   7.980 118.984 3 U   5.760E+08  0.000E+00 e 0   682   682   689 #VC 0.25147 #QU 0.695 #SUP  0.99
$
$
$
$ perl -lne '$str=substr($_,63,18); $str=~s/^\s*//; $str=~s/\s*$//;
             @x = split/[ ]+/, $str; $c = grep {$_ <= 1000} @x; print if $c == 3
            ' f21
      1   7.021   6.967 116.019 4 U   6.980E+07  0.000E+00 e 0     0     0     0
      2   8.292   7.908 118.063 3 U   1.440E+07  0.000E+00 e 0   821   814   825 #VC 0.50000 #QU 0.676 #SUP  0.92
      3   7.994   7.056 118.728 3 U   6.570E+06  0.000E+00 e 0   682   686   689 #VC 0.50000 #QU 0.499 #SUP  0.77
      4   7.999   7.956 118.787 3 U   4.600E+08  0.000E+00 e 0   682   682   689 #VC 0.50000 #QU 0.774 #SUP  0.95
      5   7.999   7.956 118.787 3 U   4.600E+08  0.000E+00 e 0   682   682   689 #VC 0.50000 #QU 0.774 #SUP  0.95
      6   8.023   7.980 118.984 3 U   5.760E+08  0.000E+00 e 0   682   682   689 #VC 0.25147 #QU 0.695 #SUP  0.99
                                                                 890   890   894 #VC 0.24853 #QU 0.687 #SUP  0.99
      7   8.023   7.980 118.984 3 U   5.760E+08  0.000E+00 e 0   682   682   689 #VC 0.25147 #QU 0.695 #SUP  0.99
$
$
$

tyler_durden
# 4  
Old 09-13-2011
Can i some integrate this into a script so that i can call the script and the file name i want the awk or perl command to apply to?

an example i can type

Code:
script input

and the output would be what the awk or perl command (which is in the script) applies to any input file

---------- Post updated at 03:14 PM ---------- Previous update was at 02:16 PM ----------

i figured it out using the command
Code:
perl -MO=Deparse  -lne '$str=substr($_,63,18); $str=~s/^\s*//; $str=~s/\s*$//;
             @x = split/[ ]+/, $str; $c = grep {$_ <= 1000} @x; print if $c == 3
            '

and added the variable

Last edited by olifu02; 09-13-2011 at 03:23 PM..

Previous Thread | Next Thread
Test Your Knowledge in Computers #446
Difficulty: Medium
The Large Hadron Collider experiments at CERN run on Scientific Linux.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Search and delete a row which is delimited by |^

I have a sample text file like this. CampaignId|^CampaignCd|^InsertionOrderCd|^OwningAdvertiserCd|^CampaignName 998201|^T15-06|^T15|^|^GTA 160x160 998277|^T15-07|^T15|^TEST|^GTA 160x160 998297|^T15-07|^T15|^TEST2|^GTA 160x160 I want to delete the line only when the 4th field is empty. ... (2 Replies)
Discussion started by: Tuxidow
2 Replies

2. Shell Programming and Scripting

Bash - delete from csv all the row if the first column is length >

Hi guys, i have a csv file like: USERID;COG;DESCR;FIL;OFF user001;user;test1;001;A01 user002;user;test2;002;A02 user0003;user;test3;003;A03 user004;user;test4;004;A04 user0005;user;test5;005;A05 etc.. I need to read line for line and, if value of first column is > 7 char (in this example... (4 Replies)
Discussion started by: kamose
4 Replies

3. Shell Programming and Scripting

Search/grep on row and column wise

Hello, I have a comma seperate metadata as follows: CITY ,COUNTY,STATE,COUNTRY NEW_YORK,NYC ,NY ,USA NEWARK ,ESSEX ,NJ ,USA CHICAGO ,COOK ,IL ,USA SEATTLE ,MINER ,WA ,USA In my process, I get two key values ie CITY NAME (can be one of the... (7 Replies)
Discussion started by: calredd
7 Replies

4. Shell Programming and Scripting

Delete row if column matches

Hi, I have a long file in the format below. I want to delete the consecutive lines that contain the same value in column 1. I have tried awk '!x++' FS="," filename This has not worked. 14,MM709_BHP_DM,BHP,BHP_MC709_DM 19,OFFLINE,CHE,CHEV_MC773_DM 20,M33,BP,BP_MIM775_NS_DM ... (2 Replies)
Discussion started by: ndnkyd
2 Replies

5. Shell Programming and Scripting

Delete a row if either of column value is zero

Hi, My input file is this way 1.1 0.0 2.4 3.5 7.9 1.8 22.3 4.7 8.9 0.9 1.3 0.0 3.4 5.6 0.0 1.1 2.2 0.0 0.0 1.1 0.0 0.0 3.4 5.6 I would like to delete the entire row, if either of 2nd and 3rd columns are 0.0. Please note that my values are all decimal values. So, my output would... (4 Replies)
Discussion started by: jacobs.smith
4 Replies

6. Shell Programming and Scripting

delete a row with a specific value at a certain column

Hi, I want to delete rows that have 0 at column 4. The file looks like this: chr01 13 61 2 chr01 65 153 0 chr01 157 309 1 chr01 313 309 0 chr01 317 469 1 chr01 473 557 0 I want to delete all rows with a 0 at column 4 chr01 13 61 2 chr01 157 309 1 chr01 ... (3 Replies)
Discussion started by: kylle345
3 Replies

7. Shell Programming and Scripting

Delete row if a a particular column has more then three characters in it

Hi i have a data like hw:dsfnsmdf:39843 chr2 76219829 51M atatata 51 872389 hw:dsfnsmdf:39853 chr2 76219839 51M65T atatata 51 872389 hw:dsfnsmdf:39863 chr2 76219849 51M atatata 51 872389 hw:dsfnsmdf:39873 chr2 ... (3 Replies)
Discussion started by: bhargavpbk88
3 Replies

8. UNIX for Dummies Questions & Answers

Delete a row from a file if one column containing a date is greater than the current system date

Hello gurus, I am hoping someone can help me with the required code/script to make this work. I have the following file with records starting at line 4: NETW~US60~000000000013220694~002~~IT~USD~2.24~20110201~99991231~01~01~20101104~... (4 Replies)
Discussion started by: chumsky
4 Replies

9. Shell Programming and Scripting

Delete first row last column

Hi All, I am having following file and I want to delete 1 row last column. Current File Content: ================ procedure test421 put_line procedure test321 test421 procedure test521 test321 procedure test621 test521 Expected File Content: =========================== procedure... (3 Replies)
Discussion started by: susau_79
3 Replies

10. Shell Programming and Scripting

Delete a row that has a duplicate column

I'm trying to remove lines of data that contain duplicate data in a specific column. For example. apple 12345 apple 54321 apple 14234 orange 55656 orange 88989 orange 99898 I only want to see apple 12345 orange 55656 How would i go about doing this? (5 Replies)
Discussion started by: spartan22
5 Replies

Featured Tech Videos