9

I have a file with several columns. I want to remove entire rows from this file at which the first and the second columns show the same value.

For instance, my file is as the following:

Variant         rsid         chr     pos
1:10177_A_AC    rs367896724     1    10177
1:10352_T_TA    rs201106462     1    10352
1:10511_G_A     rs534229142     1    10511
1:10616_CCGCCGTTGCAAAGGCGCGCCG_C        1:10616_CCGCCGTTGCAAAGGCGCGCCG_C        1       10616

I want to remove the line in which the value at Variant column is equal to rsid column, so I would like to obtain a final file such as the following:

Variant         rsid         chr     pos
1:10177_A_AC    rs367896724     1    10177
1:10352_T_TA    rs201106462     1    10352
1:10511_G_A     rs534229142     1    10511

I've tried to run the following commands:

awk '$1==$2{sed -i} input.file > output.file

awk -F, '$1==$2' input.file > output.file

But none of them worked.

How could I solve it by using awk and/or sed?

2
  • 5
    awk '$1==$2{sed -i} is like trying to ride your bicycle while driving your car. They're 2 completely different tools.
    – Ed Morton
    Jul 12 at 20:37
  • Are columns actually space separated (as in the source here)? Or are they TAB-separated? Jul 16 at 16:01
20

You almost make it

awk '$1!=$2' input.file > output.file

This will keep line where first and second field are different (thus removing when equal).

  • -F, is wrong because , is not your field separator and setting it that way will make awk misinterpret the line content
  • '$1==$2{sed -i} is neither a awk, nor a sed function
0
12

You already have the best, general answer, but in your specific case, you can also simply select all lines where the second field starts with rs:

$ awk '$2 ~ /^rs/' file
Variant         rsid         chr     pos
1:10177_A_AC    rs367896724     1    10177
1:10352_T_TA    rs201106462     1    10352
1:10511_G_A     rs534229142     1    10511
4

There may come a day (and this day may never come) when you need to do something "clever" in your file manipulation where the Awk solution becomes exceedingly complicated. Rather than write out a script for a one-shot task, you want something that builds on your Awk experience. You want a Perl One-liner.

Here's the equivalent of the above command (the -a flag says do it like awk)

perl -anE 'print if $F[0] ne $F[1]' input.file > output.file

or

perl -anE 'print if $F[1] =~ /^rs/' input.file > output.file

If you want to do that file change in place,

perl -i.bak -anE 'print if $F[0] ne $F[1]' input.file

will make changes to input.file and leave a backup in input.file.bak

If you'd like to execute some code after you finish the file, try the "kissing" operator }{

perl -i.bak -anE 'if ($F[0] ne $F[1]) {print} else {$del++} }{ $del ||= 0; say "Deleted $del lines"' input.file

The author of Perl One-liners explained has also written explainers on Awk and Sed which you might find useful.

3

If you don't want to use awk and perl, you can use plain bash:

cat input.file | while read line; do a=($line); if [ "${a[0]}" != "${a[1]}" ]; then echo "$line"; fi; done

Line by line:

cat input.file | while read line # read input.file line by line
do
   a=($line)                     # array assignment
   if [ "${a[0]}" != "${a[1]}" ] # compare the columns
      echo "$line"               # use quotes to preserve original spacing
   fi
done

If you're not interested in preserving the lines exactly in terms of spacing, this also works and is, in my opinion, more mnemonic:

cat input.file | while read variant rsid char pos # read columns into variables
do 
   if [ "$variant" != "$rsid" ]  # compare the columns by name
   then
      echo "$variant     $rsid   $char   $pos" # replaced spaces with single tab
   fi
done

More about array assignments: https://riptutorial.com/bash/example/1550/array-assignments

Bash uses IFS (Internal Field Separator) to split columns when using read and for the array assignment. See: https://linuxhint.com/use-ifs-in-bash/

1
  • Use while IFS=$'\t' read -ra a; do to split the line into fields, instead of relying on an unquoted parameter expansion to work correctly.
    – chepner
    Jul 14 at 17:09
1
#!/usr/bin/python
m=open('filename','r')
import re
h=re.compile(r'\s+')
for b in m:
    fg=re.sub(h," ",b)
    rt=fg.split(' ')
    if (rt[0] != rt[1]):
        print fg

output

Variant rsid chr pos 
1:10177_A_AC rs367896724 1 10177 
1:10352_T_TA rs201106462 1 10352 
1:10511_G_A rs534229142 1 10511 
1

Using Raku (formerly known as Perl_6):

raku -ne '.put unless .words[0] eq .words[1];'

OUTPUT:

Variant         rsid         chr     pos
1:10177_A_AC    rs367896724     1    10177
1:10352_T_TA    rs201106462     1    10352
1:10511_G_A     rs534229142     1    10511

For readability the keyword combination unless/eq was used. You could just as easily use if/ne (ne mnemonic: not equal ). The code above uses the eq string-equality operator. If you want to test numeric-equality, use == (remember to skip any non-numeric header line).

Note: You'll find many languages (Raku included) will balk at odd (introduced) lines, such as blank lines. For a solution that works even with internal/trailing blank lines (and removes them), use the following:

raku -ne 'if .chars {.put unless .words[0] eq .words[1]};'

[ Language insight: "Hey, what's with all the leading periods?" Raku make extensive use of a 'topic' variable $_, so you can alway track--and not have to repeatly denote--the name of the data structure you're manipulating (e.g. a 'topic' line from a text file). However even this $_ can be abbreviated, thus $_.words[0] (meaning 'the first whitespace-separated element of the topic line') can be shortened further to .words[0] ].

https://docs.raku.org/language/operators
https://raku.org/

0
grep -Ev '^(\S+)\s+\1\s' input.file >output.file

\1 - back reference to pattern (\S+). the contents of both templates must match.
-v inversion flag - do not display lines matching the pattern.

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.