1

How can I keep .csv line data that only contains the words dog or cat and only check for them on the second column, if the word "dog" appears on any other column but not the 2nd then it should be ignored.

For example if I had:

email1.com dog
email2dog.com steve
email3.com cat

Expected output:

email1.com dog
email3.com cat

There maybe a long list of words to keep so if possible load them from another file.

4

With sed:

$ sed -n -e 's/dog$/&/p' -e 's/cat$/&/p' file
email1.com dog
email3.com cat

Or you can use awk:

awk '$2~/^dog|cat$/' file
  • None of these work, if the other columns have the word "dog" or "cat" in them they also remain. – Teddy291 Jun 17 '15 at 9:48
  • @Teddy291: Are you sure? Please give me an example. – cuonglm Jun 17 '15 at 9:50
  • "username random@email.co.uk Keith P Dogville" is still left when it shouldn't be (dog is not on the second column) – Teddy291 Jun 17 '15 at 9:57
  • @Teddy291: There's no way it's still left. Did you try my answer? – cuonglm Jun 17 '15 at 9:58
  • "awk '$2~/dog|cat/' newname.csv > new.csv" is the exact command I entered. "username ****isdubay2***@aol.com Des Dy Decatur Illinois 6***1 UNITED STATES" is still left when it shouldn't be (cat is on the forth column but not second) – Teddy291 Jun 17 '15 at 10:12
1

A few approaches:

  1. If the dog or cat is always the last word on the line:

    awk '/(dog|cat)$/' file
    grep -E '(dog|cat)$' file
    sed -n '/\(dog\|cat\)$/p' file
    perl -ne 'print if /(dog|cat)$/' file
    
  2. If the dog or cat can be anywhere in the second space-delimited field:

    awk '$2~/dog|cat/' file
    grep -E '^\S+\s+\S*(dog|cat)\S*\s*' file
    perl -ane 'print if $F[1]=~/dog|cat/' file
    sed -nr '/^\S+\s+\S*(dog|cat)\S*\s*/p' file
    sed -n '/^\S\+\s\+\S*\(dog\|cat\)\S*\s*/p' file
    
  3. To read the search patterns from a file, you can do:

    awk '{if(NR==FNR){a[$0]++; next}for(p in a){if($2~p){print}}}' patterns  file
    
  • None of these work, if the other columns have the word "dog" or "cat" in them they also remain. – Teddy291 Jun 17 '15 at 9:47
  • @Teddy291 what? You never said you wanted to remove the others! You just asked for commands that print lines whose second field matches cat or dog. If you want the rest removed, edit your question and tell us so. – terdon Jun 17 '15 at 9:49
  • "and only check for them on the second column." I thought I did sorry :( I will edit it. – Teddy291 Jun 17 '15 at 9:53
  • @Teddy291 all of the solutions in part 2 and the one in part 3 check the second space-delimited field. The ones in part 1, as stated in the answer, test the last field which works on the example you gave. Are you saying that you want to only print fields 1 and 2 of matching lines and remove all other fields? – terdon Jun 17 '15 at 10:03
  • If the word "dog" is in first, third or fourth column but not the second then that line needs to go. Only keep the whole lines of data that contain "dog" or "cat" in the second column. – Teddy291 Jun 17 '15 at 10:06
1
{   sed 's/[]$^&\./*[]/\\&/g;s/$/$/'|
    grep -f- ./greppedfile
}   <words

Would handle the case where you're dealing with a lot of match patterns in words which contained a pattern per line. It takes care to escape any possible regexp metacharacters and then anchors the resulting pattern - at the tail of the line - so any character in words will be matched literally by grep and its pattern will only be matched if word occurs at the tail of a line - which is what I think I understand is wanted.

Usually with grep we don't have the metachar escapes like that because we can use -Fixed-string patterns - which don't have any. But, that also means, we can't use the $ metachar to anchor the pattern, so we work with what we got.

0

I'd do it like this in perl:

#!/usr/bin/perl;
use strict;
use warnings;

my @words      = qw ( dog cat );
my $column_num = 1;                #perl starts arrays zero.

my $regex = join( "|", map {quotemeta} @words );
$regex = qr/$regex/;

while (<>) {
    print if ( split() )[$column_num] =~ $regex;
}

You 'load' a regular expression with @words - qw is simply 'quoted words' e.g. space delimited. You turn that into a regular expression.

Then using the diamond operator <> which either reads from <STDIN> or opens files as listed on the command line (so cat $file | ./myscript.pl or ./myscript.pl <filename> ) - we then read each line.

We check for the presence of that regular expression in the designated column number (split splits on whitespace by default).

0

Using grep with PCRE:

$ grep -P '^[^ ]+\s+dog|cat(?=\s|$)' file.txt 
email1.com dog
email3.com cat

This will print the lines where dog or cat appears only on the second column of the line.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.