0

This question already has an answer here:

I have two files file1.txt, file2.txt. I want to select records which are common to both file1.txt and file2.txtbased on column 2 and column 3 and copy them to out.txt with additional column(column 4 from file2.txt).

Sample Input:

file1.txt

abc 1 a f11 f13 f14 
abd 2 b f12 f14 f13  
abe 4 d f13 f16 f12 
acf 5 s f14 f15 f19

file2.txt

abc 1 a f21 f23 f24 
abd 1 b f21 f24 f23  
abe 4 d f24 f26 f22 
acf 6 s f23 f25 f29

Desired output

out.txt

 abc 1 a f11 f13 f14 f21
 abe 4 d f13 f16 f12 f24

I have asked a similar question earlier on how to work with three files and extract similar output based on similar input for better understanding. I'm new to Unix world and been learning on how to do file processing. Based on that I have tried the below piece of code using awk but didn't work. Could someone please check and suggest, what went wrong ?

awk 'FILENAME == ARGV[1] {
    m[$2,$3] = 0; z[$2,$3] = $6;
    next;
}
{
    if (($2,$3) in m && m[$2,$3] == 1) {
        print $0 " " z[$2,$3] >"out.txt";
    }
}' file2.txt file1.txt

marked as duplicate by slm May 22 '15 at 22:16

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • In your example in out.txt shuld have 3 lines 6 s is also presented in the both of the files. – taliezin May 22 '15 at 20:24
  • @taliezin Thanks for pointing, I have corrected it :) – Prradep May 22 '15 at 20:28
  • NR==FNR is a more efficient and idiomatic way to check that you're processing the first file (unless the first file may be empty) – Stéphane Chazelas May 22 '15 at 20:38
  • @StéphaneChazelas Sorry, I am beginner to the unix world and tried to use awk for first time. Unfortunately, I couldn't understand what you meant to say ! – Prradep May 22 '15 at 20:43
  • You already asked the same question a few days ago. – lcd047 May 22 '15 at 20:45
1

In your example you are creating array m with values 0. You nowhere set it to 1, and you don't need to. You can just set value to entire row, e.g $0.
Try this:

awk 'FILENAME == ARGV[1] {
    m[$2,$3] = $0;
    next;
}
{
    if (($2,$3) in m) {
        print m[$2,$3] " " $4 >"out.txt";
    }
}' file1.txt file2.txt
2

awk

awk 'FNR==NR      {
                     a[$2, $3]=$4
                     next
                  }
     ($2, $3) in a{
                     print $0, a[$2, $3]
                  }
    ' file2.txt file1.txt > out.txt

join

join -j 2 \
    <(sort -k2,3 file2.txt | sed 's/ /+/2') \
    <(sort -k2,3 file1.txt | sed 's/ /+/2') \
    -o 1.1,1.2,1.3,1.4,1.5,2.3 |
        sed 's/+/ /' > out.txt
  • Thanks both worked well with my test cases. How to use join when we don't know the number of columns in first file ? to specify for the "-o" parameter. – Prradep May 24 '15 at 9:10
  • @Prradep Without -o join prints join field, remaining from first file, then from second. You are free to re-orginize it before joining or after with sed. – Costas May 24 '15 at 18:29

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