Exclude matched rows based on first column of two files

Question

I have two tab delimited text file. The larger main file 1 with multiple columns and the file 2, smaller file, that only share the first two columns for limited rows with file 2.

I want a file 3 as an output that exclude the rows that match between first column of these two files and print all other columns in file 1 as well.

I tried grep -vf but doesn't seem to work. please also note that I need match for both column.

file 1:

BP  CHR SNP REF ALT A1  OBS_CT  OR  LOG(OR)_SE  Z_STAT  P
1650048 1   rs112618790 C   T   T   12387   1.00246 0.0877604   0.0279678   0.977688
1856473 1   rs6684487   G   A   A   12387   1.02222 0.0836593   0.262689    0.79279

file 2:

BP  CHR
1650048 1
1650483 1

output (file 3):

BP  CHR SNP REF ALT A1  OBS_CT  OR  LOG(OR)_SE  Z_STAT  P
1856473 1   rs6684487   G   A   A   12387   1.02222 0.0836593   0.262689    0.79279

Answer 1

Assuming that the files are sorted on the first field, you may extract all records from the first file whose first field does not occur in the second file using

$ join -v 1 file1 file2
1856473 1 rs6684487 G A A 12387 1.02222 0.0836593 0.262689 0.79279

To retain the tab delimiter and the header:

$ head -n 1 file1; join -t $'\t' -v 1 file1 file2
BP      CHR     SNP     REF     ALT     A1      OBS_CT  OR      LOG(OR)_SE      Z_STAT  P
1856473 1       rs6684487       G       A       A       12387   1.02222 0.0836593       0.262689        0.79279

---

To match using both the first and second fields, create a new combined first field from these in both files and join on this field, then remove the temporary join field. This basically implements a transformation similar to a decorate-sort-undecorate, but with a relational JOIN operation in place for the sort.

The following code assumes a shell that understands process substitutions using <(...).

$ head -n 1 file1; join -t $'\t' -v 1 <( awk -F '\t' 'BEGIN { OFS=FS } { print $1 "_" $2, $0 }' file1 ) <( awk -F '\t' 'BEGIN { OFS=FS } { print $1 "_" $2, $0 }' file2 ) | cut -f 2-
BP      CHR     SNP     REF     ALT     A1      OBS_CT  OR      LOG(OR)_SE      Z_STAT  P
1856473 1       rs6684487       G       A       A       12387   1.02222 0.0836593       0.262689        0.79279

Or, with a helper shell function to make the command a bit nicer to read,

$ decorate () {  awk -F '\t' 'BEGIN { OFS=FS } { print $1 "_" $2, $0 }' "$1"; }
$ head -n 1 file1; join -t $'\t' -v 1 <( decorate file1 ) <( decorate file2 ) | cut -f 2-
BP      CHR     SNP     REF     ALT     A1      OBS_CT  OR      LOG(OR)_SE      Z_STAT  P
1856473 1       rs6684487       G       A       A       12387   1.02222 0.0836593       0.262689        0.79279

Answer 2

One option is to use awk:

awk '
    # set the input Field Seperator to a Tab
    BEGIN  { FS="\t" }
 
    # store column#1,column#2 of file2 into associated array bp_file2
    NR==FNR{ bp_file2[$1, $2]; next }

    # do not print lines of file1 if column#1 was in the array
                      # with FNR==1 we are printing the first header line too
    !(($1, $2) in bp_file2) || FNR==1

' file2 file1

Answer 3

A python solution. May be longer than others, but more readable too (for me only?):

file1 = '1'
file2 = '2'
separator = '\t'

list2=[]

# first building up a list of IDs from file2:
with open(file2) as f:
    for line in f:
        if line[0].isdigit(): # only process lines which start with number
            list2.append(line.split(separator)[0])

# then go through file1 and check IDs from the previously built list 
with open(file1) as f:
    print(f.readline(), end='') # printing out header
    for line in f:
        if not line.split(separator)[0] in list2:
            # print out line which IDs are not in list2 (not in file2)
            print(line, end='')

And run it like:

python3 file.py > 3

Answer 4

Here is a solution using Perl:

 1  #!/usr/bin/env perl
 2  use strict;
 3  use warnings;
 4  use Getopt::Long;
 5  my ($once, $x, %x);
 6  sub k() {
 7      my @F = split /\t/;
 8      chomp(my $k = join $", @F[0, 1]);
 9      return $k;
10  }
11  sub usage() { "Usage: solution.pl -x excludefile [inputfile ...]\n" }
12  GetOptions('x=s' => \$x) or die usage;
13  die usage unless $x;
14  open (my $xh, $x) or die;
15  <$xh>;  # discard excludefile header row
16  $x{k()}++ for <$xh>;
17  close $xh or die;
18  while (<>) {
19      next if /BP\tCHR/ && $once++;
20      next if $x{k()};
21      print;
22  }

Line 1 lets the shell know this is a Perl script.

Line 2 "The strict pragma disables certain Perl expressions that could behave unexpectedly or are difficult to debug, turning them into errors."

Line 3 "The warnings pragma gives control over which warnings are enabled in which parts of a Perl program."

Line 4 loads the Getopt::Long module and imports its default functions.

Line 5 declares local variables:

• $once is a one-shot for printing exactly one header row.

• $x holds the value of the exludefile option (e.g. "smaller file"; "file 2").

• %x is a lookup table of input data lines not to be printed.

Lines 6-9 declare a function k for computing the value of the lookup table key from either an excludefile line or from an input data line. k is prototyped to accept no arguments; the excludefile line or the input data line is passed via the default input argument special variable $_.

Line 7 uses the split builtin function to divide $_ into a list of fields, according to a regular expression for the field separator /\t/. The resulting list is saved in the array local variable @F.

Line 8 uses an array slice of @F and the join builtin function to assemble the first two fields into a string, with the list separator special variable $" in between. This string is saved into a local variable $k. The chomp builtin function removes any trailing newline.

Line 9 returns the lookup table key.

Line 11 declares a function usage that is prototyped to accept no arguments. As the code block does not contain any return builtin functions, usage will return the value of the last expression evaluated in the code block. In this case, the usage message string literal.

Alternatively, the usage message could have been put into a constant:

use constant USAGE => "Usage: solution.pl -x excludefile [inputfile ...]\n";

A scalar variable is easier to interpolate into strings:

my $usage = "Usage: solution.pl -x excludefile [inputfile ...]\n";

Line 12 uses a boolean expression and the low-precedence logical or binary operator to do error handling for a function that returns false on failure. First, logical or evaluates its left hand expression -- GetOptions('x=s' => \$x). GetOptions is a function imported from the Getopt::Long module. The 'x' portion of first argument specifies that GetOptions should look for an 'x' option flag in the command-line arguments. The '=s' portion of the first argument specifies that the option requires a string value. The second argument, \$x, is a reference to the $x variable, which specifies where to put the string value. If GetOptions encounters an error (omitting an option on the command-line is not an error), it prints a message to standard error and returns false. In this case, logical or will then evaluate its right hand expression -- die usage. usage is called, and the result is passed as an argument to die. die will print its arguments on standard error and cause the script to exit with non-zero status.

Line 13 uses an unless statement modifier to do option validation. If no '-x excludefile' option was provided on the command line, the usage message is printed on standard error and the script will exit with non-zero status.

Line 14 opens the excludefile using the logical or error handling idiom. The open builtin function is called to open excludefile, creating and saving the filehandle in the local variable $xh. If open fails, it sets the system error special variable $! and returns false. die will then print the system error on standard error and exit the script with non-zero status.

Line 15 applies the diamond operator to the excludefile filehandle <$xh> to read first excludefile line. This is presumed to contain a row of field names. Because the read is performed in null context, the header row is discarded.

Line 16 uses a for statement modifier to iterate through the remaining lines in excludefile and build the lookup table %x. The list of items to be iterated over (excludefile lines) is generated by applying the diamond operator to the excludefile filehandle <$xh> in list context. For each item, for will place its value into the default input special variable $_ and evaluate the statement $x{k()}++. Function k computes a lookup table key from the item value. The corresponding lookup table element $x{...} is autovivified and/or incremented by the auto-increment operator ++.

Line 17 closes the excludefile filehandle using the logical or idiom.

Lines 18-22 form a while compound statement.

Line 18 uses the diamond operator on the null filehandle <> for the while expression, making the script a Unix filter. For each input data line, while will assign its value to the default input special variable $_ and evaluate the code block.

Line 19 uses an 'if' statement modifier to print the input data header row once. The if expression uses the C-style logical and operator. The C-style logical and first evaluates its left hand expression -- /BP\tCHR/ -- which is a regular expression that matches header rows. If the match is a success, the C-style logical and evaluates its right hand expression -- $once++ -- which is a scalar variable with an auto-increment operator applied post-evaluation. On the first occurance of a header row, $once will be undefined; which is false. The if expression will be false, processing will continue with the next statement, and the header row will be printed by line 21. But on the second and subsequent occurances of a header row, $once will be true, the if expression wil be true, and the statement will be evaluated -- next. The next builtin function will cause while to stop processing the code block and proceed to the next iteration. This has the effect of not printing the second and subsequent header rows.

Line 20 calls next if the key for the current line of input corresponds to a true value in the lookup table. This has the effect of not printing input data lines whose first two fields match an entry in excludefile.

Line 21 prints the current line.

The solution produces the following output:

$ perl solution.pl -x file2.tsv file1.tsv 
BP  CHR SNP REF ALT A1  OBS_CT  OR  LOG(OR)_SE  Z_STAT  P
1856473 1   rs6684487   G   A   A   12387   1.02222 0.0836593   0.262689    0.79279

Answer 5

awk 'BEGIN{print "BP  CHR SNP REF ALT A1  OBS_CT  OR  LOG(OR)_SE  Z_STAT  P"}NR==FNR{a[$1];next}!($1 in a){print $0}' file2 file1

output

BP  CHR SNP REF ALT A1  OBS_CT  OR  LOG(OR)_SE  Z_STAT  P
1856473 1   rs6684487   G   A   A   12387   1.02222 0.0836593   0.262689    0.79279

Python

#!/usr/bin/python
import re
f1=open('/home/praveen/file1','r')
f2=open('/home/praveen/file2','r')
f3=open('/home/praveen/file3','w')
f1.readline()
f2.readline()

for i in f2:
    i_split=i.split(' ')
#    print i.split(' ')
    for j in f1:
        j_split=j.split(' ')
        if (i_split[0] != j_split[0]):
            str_append="BP  CHR SNP REF ALT A1  OBS_CT  OR  LOG(OR)_SE  Z_STAT  P"
            appnddata="{0}\n{1}\n".format(str_append,j.strip())
            f3.write(appnddata)

output

BP  CHR SNP REF ALT A1  OBS_CT  OR  LOG(OR)_SE  Z_STAT  P
1856473 1   rs6684487   G   A   A   12387   1.02222 0.0836593   0.262689    0.79279