1

I have two files with duplicate ids. One file has 150 ids but since all of them exist as duplicates there are 300 ids. File 2 has 1500 ids but since all ids exist in duplicates it has 3000 ids. I want match the first occurrence of every id in file one with first occurrence of id in file 2 and 2nd occurrence of the same id with second accurate of id in file 2. If the value of id in 2nd column matches then print match other wise a mismatch.

File1:

1 12
1 13
2 15
2 16
4 15 
4 18

File2:

1 13
1 13
2 15
2 17
3 12
3 12
4 15 
4 18
5 14
5 14

Desired output (Id, col 2 from file 1, col 2 from file 2, match or mismatch):

1 12 13 mismatch
1 13 13 match
2 15 15 match
2 16 17 mismatch
4 15 15 match
4 18 18 match
  • What should happen with the remaining 2700? – muru Jul 3 '17 at 5:36
  • It should match all the ids in file 1 i.e 150 ids. Rest just ignore. – limd Jul 3 '17 at 5:38
  • And these entries are sorted as in the example? – muru Jul 3 '17 at 5:43
  • No, not sorted. – limd Jul 3 '17 at 5:44
1

Python3

#!/usr/bin/env python3

import sys

def make_id_dict(f):
  d = {}
  for line in open(f):
    k, v = line.split()
    if k in d:
      d[k] += [ v ]
    else:
      d[k] = [ v ]
  return d


filename1 = sys.argv[1]
filename2 = sys.argv[2]

dict1 = make_id_dict(filename1)
dict2 = make_id_dict(filename2)

for key in sorted(dict1):
  for i, value1 in enumerate(dict1[key]):
    value2 = dict2[key][i]
    if value1 == value2:
      result = 'match'
    else:
      result = 'mismatch'
    print(key, value1, value2, result)

Save as script match-files-by-ids.py then call:

$ python3 match-files-by-ids.py File1 File2
1 12 13 mismatch
1 13 13 match
2 15 15 match
2 16 17 mismatch
4 15 15 match
4 18 18 match
| improve this answer | |
  • I hammered this out pretty quick cause it's late. If anything's not clear, leave a comment and I'll get back to you tomorrow. – wjandrea Jul 3 '17 at 6:07
  • Apart from all the ' ' and () in the output ,worked perfectly! Thanks! – limd Jul 3 '17 at 7:02
  • @limd You're running it with Python 2 (python or python2). Run it with Python 3 (python3). – wjandrea Jul 3 '17 at 12:40
  • Or remove the parentheses from the print statement to run it with Python 2. I hadn't intended to make it 2-compatible, but I guess it is! – wjandrea Jul 3 '17 at 12:42
  • Yes, i realized it and moved to python3. Thanks alot! – limd Jul 3 '17 at 12:42
0

If the files:

  • are both sorted; and
  • each contain exactly zero or two occurrences of a given index

then if we join them, we will get four "matches" for each pairable ID i.e.

1st IDn from File1 against 1st IDn from File2
1st IDn from File1 against 2nd IDn from File2
2nd IDn from File1 against 1st IDn from File2
2nd IDn from File1 against 2nd IDn from File2

and so on. Unpairable lines will be ignored (unless we use the -a option). We can then simply pick out the first and fourth line from each match group, and compare their respective second fields:

$ join File1 File2 | 
  awk '!((NR-1)%4 && NR%4) {$4 = $3==$2 ? "match" : "mismatch"; print}'
1 12 13 mismatch
1 13 13 match
2 15 15 match
2 16 17 mismatch
4 15 15 match
4 18 18 match

where (NR-1)%4 is zero for lines 1,5,9 etc. and NR%4 is zero for lines 4,8 etc. so and-ing and inverting is true for 1,4;5,9...

| improve this answer | |
0

Here's an awk solution:

awk '
  NR==FNR {
    if ($1 in a) 
      b[$1]=$2; 
    else 
      a[$1]=$2; 
    next;
  } 
  ($1 in a) {
    print $1, a[$1], $2, $2 == a[$1] ? "match" : "mismatch";
    delete a[$1];
    next;
  }
  ($1 in b) {
    print $1, b[$1], $2, $2 == b[$1] ? "match" : "mismatch";
  }' File1 File2

Testing:

$ awk '
  NR==FNR {if ($1 in a) b[$1]=$2; else a[$1]=$2; next} 
  ($1 in a) {print $1, a[$1], $2, $2 == a[$1] ? "match" : "mismatch"; delete a[$1]; next; }
  ($1 in b) {print $1, b[$1], $2, $2 == b[$1] ? "match" : "mismatch";}
' File1 File2
1 12 13 mismatch
1 13 13 match
2 15 15 match
2 16 17 mismatch
4 15 15 match
4 18 18 match
| improve this answer | |

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.