1

I have three files file1.txt, file2.txt, file3.txt and they are of same format.

I want to select records from file1.txt which are present in both file2.txt and file3.txt based on column 2 and column 3 respectively to the output file out.txt.

Also, I need to create another file, out2.txt, with additional columns (column 4 from file2.txt, column 5 from file3.txt).

Sample Input:

file1.txt

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

file2.txt

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

file3.txt

 1. abc 1 a f31 f33 f34 
 2. abd 2 b f31 f34 f33  
 3. acf 5 s f33 f35 f39 
 4. abe 4 d f34 f36 f32

Desired output

out.txt

 1. abc 1 a f11 f13 f14 
 2. abe 4 d f13 f16 f12 

out2.txt

 1. abc 1 a f11 f13 f14 f21 f31
 2. abe 4 d f13 f16 f12 f24 f34
  • 1
    There are many instruments but for the right choice let show examples of records, columns, desired output and so on – Costas May 18 '15 at 15:09
  • Hi @Costas, I have included an example. Please suggest me a solution. Thanks ! – Prradep May 18 '15 at 15:41
  • As I see records abc 1 a and abe 4 d is present in all files. Is it correct behavior? – Costas May 18 '15 at 15:47
  • @Costas Yes, those two records having same values in col2(1,4), col3(a,d). So, the rows from file1.txt needs to be copied into out.txt – Prradep May 18 '15 at 15:57
2

Possible solution with awk (I will edit if needed because it is a little unclear from your question what are exact requirements):

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

We read the third file, create two arrays with keys column 2 and 3, first array filled with zeros, second with required value for file out2.txt. Then we read the second file and we check if key from column 2 and 3 exists in first array, if yes we change value from zero to one and we concatenate required value in the second array for file out2.txt. Finally we read the first file, check for existing key and we print interesting values in files out.txt and out2.txt so:

out.txt should contain:

1. abc 1 a f11 f13 f14
3. abe 4 d f13 f16 f12

out2.txt should contain:

1. abc 1 a f11 f13 f14 f21 f31
3. abe 4 d f13 f16 f12 f24 f34
  • Thank you, it was lot useful and served my requirements. I have a query in the command provided. Where are you reading the ARGV[3] file1.txt in the command ? – Prradep May 20 '15 at 10:00
  • It is in the last {} section. We just do not have to explicitly point it with FILENAME = ARGV[3]. – taliezin May 20 '15 at 10:09
  • I have tried the below piece of code o identify the unique rows in file1.txt which are not present in file2.txt, file3.txt but I was not successful. Could you please have a look at it ? awk 'FILENAME == ARGV[1] { m[$2 $3] = 0; z[$2 $3] = $5; next; } FILENAME == ARGV[2] { s = $2 $3; if (s not in m) { m[s] = 1; z[s] = $5 " " z[s]; } next; } { s = $2 $3; if (s not in m && m[s] == 1) { print $0 >"out.txt"; print $0 " " z[s] >"out2.txt"; } }' file3.txt file2.txt file1.txt – Prradep May 20 '15 at 10:32
  • Sorry, I am unable to format the post and the code. – Prradep May 20 '15 at 10:38
  • The answer is per your question and your desired output, it will be better if you edit your question with exact requirements because in the question the lines in desired output are presented in all three files. – taliezin May 20 '15 at 10:39
3

You might want to check this site about diff3 with this program you can compare 3 files as sample output:

$ diff3 parent.txt your.txt mine.txt

 ==== 

1:1,2c Hello, 

This is parent file.

2:1,2c Hello, 

This is your file. 

3:1,2c Hello, 

This is my file.

You can use

diff3 file1.txt file2.txt file3.txt > output.txt
  • I don't see how this could produce the desired output since diff tools operate on lines not on fields... – don_crissti May 18 '15 at 20:24
  • This might be useful for some other people and the OP's question was not clear first – TheRealProcyon May 18 '15 at 20:34
3

To select shared lines in all files you can use grep (out.txt)

grep -ho ' [0-9] [a-z] ' file3 | grep -Fof - file2 | grep -Ff - file1

select operatable field (as variant cut -d' ' -f3,4 file3) and use it to search it in next file2 and file1.

As usual for joining 2 file use join command (surprise!) (out2.txt)

join -j 3 <(sort -k3,4 file1 | sed 's/ /+/3') \
          <(join -j 3 <(sort -k3,4 file2 | sed 's/ /+/3') \
                      <(sort -k3,4 file3 | sed 's/ /+/3') \
                      -o '1.4 2.4 1.3') \
          -o '1.1,1.2,1.3,1.4,1.5,2.1,2.2' | sed 's/+/ /'

so to operate with 3nd and 4th fields together we have to concatenate it (by + sign e.g.). As join operates with sorted lines only so we do sort by 3rd and 4th fields.
Firstly join file2 and file3, than result will be joined with file1 and remove + sign by sed

  • Thank you, I am understanding your command and will use it. – Prradep May 20 '15 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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