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.

File1.txt

    id                            No
    gi|371443199|gb|JH556661.1| 7907290
    gi|371443198|gb|JH556662.1| 7573913
    gi|371443197|gb|JH556663.1| 7384412
    gi|371440577|gb|JH559283.1| 6931777

File2.txt

 id                              P       R       S
 gi|367088741|gb|AGAJ01056324.1| 5       5       0
 gi|371443198|gb|JH556662.1|     2       2       0
 gi|367090281|gb|AGAJ01054784.1| 4       4       0
 gi|371440577|gb|JH559283.1|     21      19      2

output.txt

 id                              P       R       S  NO
 gi|371443198|gb|JH556662.1|     2       2       0  7573913
 gi|371440577|gb|JH559283.1|     21      19      2  6931777

File1.txt has two columns & File2.txt has four columns. I want to join both files which has unique id (array[1] should match in both files (file1.txt & file2.txt) and give ouput only matched id (see output.txt).

I have tried join -v <(sort file1.txt) <(sort file2.txt). Any help with awk or join commands requested.

share|improve this question

2 Answers 2

up vote 11 down vote accepted

join works great:

$ join <(sort File1.txt) <(sort File2.txt) | column -t | tac
 id                           No       P   R   S
 gi|371443198|gb|JH556662.1|  7573913  2   2   0
 gi|371440577|gb|JH559283.1|  6931777  21  19  2

ps. does ouput column order matter?

if yes use:

$ join <(sort 1) <(sort 2) | tac | awk '{print $1,$3,$4,$5,$2}' | column -t
 id                           P   R   S  No
 gi|371443198|gb|JH556662.1|  2   2   0  7573913
 gi|371440577|gb|JH559283.1|  21  19  2  6931777
share|improve this answer
    
works great. column order doesn't matter –  jack Jul 18 '12 at 21:17
    
What's the reason for including tac? –  Michael Mrozek Jul 18 '12 at 21:25
    
That's because sort places header string at the end. Actually it's dirty solution. And in general case header may go into the middle of the output. However it works here. –  rush Jul 18 '12 at 21:32

One way using awk:

Content of script.awk:

## Process first file of arguments. Save 'id' as key and 'No' as value
## of a hash.
FNR == NR {
    if ( FNR == 1 ) { 
        header = $2
        next
    }   
    hash[ $1 ] = $2
    next
}

## Process second file of arguments. Print header in first line and for
## the rest check if first field is found in the hash.
FNR < NR {
    if ( $1 in hash || FNR == 1 ) { 
        printf "%s %s\n", $0, ( FNR == 1 ? header : hash[ $1 ] ) 
    }   
}

Run it like:

awk -f script.awk File1.txt File2.txt | column -t

With following result:

id                           P   R   S  NO
gi|371443198|gb|JH556662.1|  2   2   0  7573913
gi|371440577|gb|JH559283.1|  21  19  2  6931777
share|improve this answer
    
+65535 for keeping the original line order. :-) –  zeekvfu Aug 7 at 9:37
    
+65535 for keeping the original line order. :-) –  zeekvfu Aug 7 at 9:37

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.