Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have two files

Original: (5000 entries)

Chr Position
chr1    879108
chr1    881918
chr1    896874
…

and a file with allele frequencies (2000 entries)

Chr Position MAF
chr1 881918  0.007   
chr1 979748  0.007   
chr1 1120377 0.007  
chr1 1178925 0.036  

I would like the original file matched with the allele frequencies and print out the output file with 5000 entries. Can this be done with awk or sed?

Chr Position MAF
chr1    879108
chr1    881918 0.007
chr1    896874
…
share|improve this question

2 Answers 2

up vote 2 down vote accepted

I could obtain what you need using join and awk

$ cat original 
chr1    879108
chr1    881918
chr1    896874
$ cat freq 
chr1 881918  0.007   
chr1 979748  0.007   
chr1 1120377 0.007  
chr1 1178925 0.036 
$ join -a1 -j 2 --nocheck-order original freq | awk '{print $2, $1, $4}'
chr1 879108 
chr1 881918 0.007
chr1 896874 

Where -a1 means to print all lines of the first file, -j 2 matches on the second field, and --nocheck-order does not check the sorted input. The pipe to awk is just to process the columns to a shape that you need.

share|improve this answer
2  
Be aware that "join" assumes that the input files are already sorted on the join-field. –  Johan Mar 8 '13 at 12:03
    
After sorting, run: join -a1 -a2 -j2 -o 0,2.1,2.3 original freq ... Note: The sorting must be text sort, not numerical sort. –  Johan Mar 8 '13 at 12:18
    
@Johan I assumed that the second column was already sorted nicely numerically. Then there is no need to sort again, or check for sorting by join. Therefor I added the --nocheck-order flag. I am not familiar with the -o flag for output control, but apparently it is possible to skip the awk-step here. Thanks for this addition. –  Bernhard Mar 8 '13 at 12:28
    
And I wasn't familiar with the nocheck-order flag, which is apparently needed because the input is not sorted in the way join likes it. –  Johan Mar 8 '13 at 12:30
    
Also possibly worth mentioning ... sorting will deal fairly well with the blank lines in the OP's sample input files, particularly sort -u –  Johan Mar 8 '13 at 12:32

You could also try:

awk 'NR==FNR{A[$1,$2]=$3; next} {$3=A[$1,$2]}1' file2 file
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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