LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 01-18-2011, 04:51 PM   #1
captainentropy
Member
 
Registered: Mar 2010
Location: Berkeley
Distribution: Ubuntu, Mint, CentOS
Posts: 81

Rep: Reputation: 0
Delete rows based on values in a column using sed


Hi,

I have a lot of files with thousands of rows and usually two columns of data. Column 1 is a coordinate, column 2 is a score. I want to delete rows that have a value in column 2 over, in this case, 50.

For example, here's a clip from a file:

2999898 39
2999899 40.9
2999900 42.7
2999901 44.7
2999902 46.7
2999903 48.9
2999904 51.1
2999905 53.3
2999906 55.7

I want the output to be this:

2999904 51.1
2999905 53.3
2999906 55.7

I know how to use sed for very basic things like deleting rows or ranges of rows but I can't figure out this ostensibly easy command.

Thanks for anyone's help
 
Old 01-18-2011, 05:06 PM   #2
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 19,323

Rep: Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470
Quote:
Originally Posted by captainentropy View Post
Hi,

I have a lot of files with thousands of rows and usually two columns of data. Column 1 is a coordinate, column 2 is a score. I want to delete rows that have a value in column 2 over, in this case, 50.

For example, here's a clip from a file:

2999898 39
2999899 40.9
2999900 42.7
2999901 44.7
2999902 46.7
2999903 48.9
2999904 51.1
2999905 53.3
2999906 55.7

I want the output to be this:

2999904 51.1
2999905 53.3
2999906 55.7

I know how to use sed for very basic things like deleting rows or ranges of rows but I can't figure out this ostensibly easy command.

Thanks for anyone's help
Not sed, but it works:
Code:
cat <filename> | awk '{ if ($2 >= 50) print $1" "$2}' > newfile.name
 
Old 01-18-2011, 05:18 PM   #3
pwc101
Senior Member
 
Registered: Oct 2005
Location: UK
Distribution: Slackware
Posts: 1,847

Rep: Reputation: 128Reputation: 128
Quote:
Originally Posted by TB0ne View Post
Not sed, but it works:
Code:
cat <filename> | awk '{ if ($2 >= 50) print $1" "$2}' > newfile.name
No need to cat into awk, awk can read files all on its own:
Code:
awk '{if ($2 >= 50) print $1,$2}' infile > outfile
 
1 members found this post helpful.
Old 01-18-2011, 05:36 PM   #4
colucix
LQ Guru
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978
Using sed:
Code:
sed -r '/ [5-9][0-9]| [1-9][0-9]{2,}/!d' file
test the result, then add option -i if you want to edit the file in place. This assumes that there are not spaces before the first field, otherwise you have to add more items in the regular expression to strictly match the second field.
 
Old 01-18-2011, 06:49 PM   #5
captainentropy
Member
 
Registered: Mar 2010
Location: Berkeley
Distribution: Ubuntu, Mint, CentOS
Posts: 81

Original Poster
Rep: Reputation: 0
Thank you all! I used the awk command first and it worked like a charm. I knew awk would work but I know nothing on its usage. I can understand exactly what it's doing in this command though. I'll be able to adapt this to new uses for sure.

On a related question is there any guide to awk and/or sed that is really, really basic? I mean like a "For Dummies" book of "AWK For Dummies." I need to get a LOT better with awk and sed.

Thanks again
 
Old 01-18-2011, 07:01 PM   #6
colucix
LQ Guru
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978Reputation: 1978
http://www.grymoire.com/Unix/Sed.html
http://www.grymoire.com/Unix/Awk.html

and of course the GNU awk's official guide, a great piece of documentation:
http://www.gnu.org/manual/gawk/gawk.html
 
Old 01-19-2011, 09:59 AM   #7
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 19,323

Rep: Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470Reputation: 4470
Quote:
Originally Posted by captainentropy View Post
Thank you all! I used the awk command first and it worked like a charm. I knew awk would work but I know nothing on its usage. I can understand exactly what it's doing in this command though. I'll be able to adapt this to new uses for sure.

On a related question is there any guide to awk and/or sed that is really, really basic? I mean like a "For Dummies" book of "AWK For Dummies." I need to get a LOT better with awk and sed.

Thanks again
I think EVERYONE could always learn more about awk and sed.

There are lots of tutorials and books, but the amount of information on those two commands is massive. Good luck.

PS: pwc101, yes I know you don't have to use cat, but good catch for claritys sake, thanks.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete Selected Rows in Perl/CGI ashok.g Programming 5 04-06-2010 11:30 AM
How to convert 1 column into several rows in Linux? markraem Linux - Software 9 03-30-2010 12:24 PM
Bash: How to remove rows in CSVs where Price column is less than 30 guest Programming 10 04-11-2009 07:44 PM
Column into rows bharatbsharma Programming 1 10-25-2007 03:23 AM
Parsing rows and column data from a file using perl dav_y2k Programming 1 10-08-2006 12:57 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 01:20 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration