0

I was trying to figure out how to see if there were any matches between column 2 of file1 and column 6 of file2. If so, I wanted that match printed along with the respective string in column 14 of file2, saved onto a different file, file3.

file1:

  1 ENSCAFT00000037911
  1 ENSCAFT00000046425
  1 ENSC
  1 ENSCAFT00000009252
  1 ENSCAFT00000049028
  1 ENSC
  2 AFT00000000014
  2 ENSCAFT00000042968
  2 ENSCAFT000000

file2:

   1 13336837 13358107 -1 SERPINB7 AFT00000000014 ENSCAFG00000023436 34.51 13.70 16.30 7.80 18.85 10.21 0.50 
   1 14028702 14130819 -1 PHLPP1 ENSCAFT00000000123 ENSCAFG00000000070 1531.81 1090.63 518.08 559.90 581.70 1126.21 0.41 
   1 14702158 14789303 1 PIGN ENSCAFT00000000137 ENSCAFG00000000083 979.07 882.88 296.70 272.97 369.74 877.13 0.31 
   1 17154193 17210961 -1 ZNF532 ENSCAFT00000049028 ENSCAFG00000000103 31.62 58.98 13.83 23.80 27.50 15.38 0.42 
   1 17154246 17210961 -1 NA ENSCAFT00000000164 ENSCAFG00000000103 31.62 58.98 13.83 23.80 27.50 15.38 0.42 
   1 17491358 17563171 1 ALPK2 ENSCAFT00000000169 ENSCAFG00000000107 12.18 10.55 39.99 31.41 210.85 15.82 3.14 
   1 18214832 18277601 1 ATP8B1 ENSCAFT00000000203 ENSCAFG00000000123 751.98 827.35 373.91 394.89 571.98 711.16 0.49 

Desired Output:

    ENSCAFT00000049028 0.42
    AFT00000000014 0.50 

The order does not matter.

  • Oh okay, I didn't know about that. I'll check it out, thanks! – Justin Oct 9 '15 at 16:48
2

Simple with join:

join -1 2 -2 6 -o 1.2,2.14 <(sort -k2 file1) <(sort -k6 file2 )
  • -1 2 defines that in file1 field 2 is the join field.
  • -2 6 defines that in file2 field 6 is the join field.
  • -o 1.2,2.14 is the output format file1 field 2 followed by file2 field 14.
  • <(sort ...) the two files must be sorted at the join field -k for join to work properly.

Or with awk (doesn't need a sorted input):

awk 'FNR==NR{a[$2]=$0} FNR!=NR&&a[$6]{print $6,$14}' file1 file2
  • FNR==NR applies to the first file file1:
    • a[$2]=$0 fills the a array with the 2nd field $2 as index.
  • FNR!=NR applies to the second file file2:
  • a[$6] if the 6th field $6 is present in the array a.
    • print $6,$14 print the two values.
  • I've been using the join method and it works like a charm! Thank you so much! – Justin Oct 9 '15 at 17:17

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.