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 want to compare 2 files, locus_file.txt is a very large file and atrr.txt is a small file. I want to match the first 2 columns of the first file to the second column of atrr.txt and print the attributes together.

locus_file.txt:large file

LOC_Os02g47020, LOC_Os03g57840,0.88725114
LOC_Os02g47020, LOC_Os07g36080,0.94455624
LOC_Os02g47020, LOC_Os03g02590,0.81881344

attr.txt: attribute file

blue LOC_Os02g47020
red  LOC_Os02g40830
blue LOC_Os07g36080
yellow LOC_Os03g57840
red LOC_Os03g02590

Desired output:

LOC_Os02g47020, LOC_Os03g57840,0.88725114,blue, yellow
LOC_Os02g47020, LOC_Os07g36080,0.94455624,blue, blue
LOC_Os02g47020, LOC_Os03g02590,0.81881344,blue, red

Note that: for example, In the first line of desired output, the 4th column has the color of LOC_Os02g47020 from atrr.txt and the 5th column has the color of LOC_Os03g57840 from atrr.txt

share|improve this question
    
Is red LOC_Os02g40830, correct? Does it have , at the end of line? –  cuonglm May 11 at 15:47
    
Sorry, typo..I corrected it. –  user2012575 May 11 at 16:11
    
Is it important that some "," are followed by a " "? –  yeti May 11 at 16:26
    
No..there is no need for spaces –  user2012575 May 11 at 16:31

3 Answers 3

up vote 1 down vote accepted

The task smells like a job for awk:

$ cat locus_file.txt 
LOC_Os02g47020, LOC_Os03g57840,0.88725114
LOC_Os02g47020, LOC_Os07g36080,0.94455624
LOC_Os02g47020, LOC_Os03g02590,0.81881344
$ cat attr.txt 
blue LOC_Os02g47020
red  LOC_Os02g40830
blue LOC_Os07g36080
yellow LOC_Os03g57840
red LOC_Os03g02590
$ awk 'BEGIN { while(getline<"attr.txt">0) c[$2]=$1 ; FS=",[ ]*" ; OFS=", " } { print $1,$2,$3,c[$1],c[$2] }' locus_file.txt 
LOC_Os02g47020, LOC_Os03g57840, 0.88725114, blue, yellow
LOC_Os02g47020, LOC_Os07g36080, 0.94455624, blue, blue
LOC_Os02g47020, LOC_Os03g02590, 0.81881344, blue, red

If you want "," instead of ", " or something different, just change OFS:

$ awk 'BEGIN { while(getline<"attr.txt">0) c[$2]=$1 ; FS=",[ ]*" ; OFS="," } { print $1,$2,$3,c[$1],c[$2] }' locus_file.txt 
LOC_Os02g47020,LOC_Os03g57840,0.88725114,blue,yellow
LOC_Os02g47020,LOC_Os07g36080,0.94455624,blue,blue
LOC_Os02g47020,LOC_Os03g02590,0.81881344,blue,red
share|improve this answer
    
awk saved my day..thanks a lot –  user2012575 May 11 at 16:56

An awk solution:

$ awk '
FNR == NR {a[$2] = $1;next}
{
    split($1,f1,",");
    split($2,f2,",");
    print $0,a[f1[1]],a[f2[1]];
}' OFS=, attr.txt locus_file.txt
LOC_Os02g47020, LOC_Os03g57840,0.88725114,blue,yellow
LOC_Os02g47020, LOC_Os07g36080,0.94455624,blue,blue
LOC_Os02g47020, LOC_Os03g02590,0.81881344,blue,red
share|improve this answer

How about something like

declare -A attr

while read x y; do attr[$y]="$x"; done < attr.txt

Then

while IFS=' ,' read a b c; do 
  d=${attr[$a]}
  e=${attr[$b]} 
  printf "%s, %s,%s,%s, %s\n" "$a" "$b" "$c" "$d" "$e"
done < locus_file.txt
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.