I have two text files:

file 1:

#CHROM  POS ID  REF ALT
1   61442   rs2531261   A   G
1   924629  rs28622096  A   G,T
1   974662  rs2465135   G   T
1   995481  rs9442393   T   G

file 2

#CHROM  POS ID  REF ALT FORMAT
1   61442   rs2531261   A   G   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   924629  rs28622096  A   G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   974662  rs2465135   G   T   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   987909  rs34235844  G   GTG GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   995481  rs9442393   T   G   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD

I would like to combine the files based on column 3 (ID). My output file should like:

output:

#CHROM  POS ID  REF ALT FORMAT
1   61442   rs2531261   A   G   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   924629  rs28622096  A   G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   974662  rs2465135   G   T   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   995481  rs9442393   T   G   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD

I want to use awk.

  • What information is in file 1 that is not already in file 2? It's not clear from your question. – waltinator Feb 1 at 20:12

It looks like you want to filter file2 based on the IDs in file 1 (rather than merge the files):

$ awk 'NR==FNR {a[$3] = 1; next} $3 in a' file1 file2
#CHROM  POS ID  REF ALT FORMAT
1   61442   rs2531261   A   G   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   924629  rs28622096  A   G,T GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   974662  rs2465135   G   T   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD
1   995481  rs9442393   T   G   GT:GL:GQ:DP:CGA_RDP:EHQ:HQ:CGA_CEGL:PS:CGA_CEHQ:AD

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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