0

I have tried various solutions given in this site for this kind of problem and none of these are helping me out.

I have two files (space-separated, contains columns). Both the files contain the same number of records as given in my examples below.

Let me call the columns as A, B, C ... (These are the column headers.)

File 1:

A  B  C  D
-----------
a1 b1 c1 d1
a2 b2 c2 d2
a3 b3 c3 d3
a4 b4 c4 d4
a5 b5 c5 d5

File 2:

E B  A  F
---------
1 b5 a5 f
2 b2 a2 f
1 a1 b1 f
1 a3 b3 f
2 a4 b4 f 

I want to produce the merged file:

A  B  C  D  E
-------------
a1 b1 c1 d1 1
a2 b2 c2 d2 2
a3 b3 c3 d3 1
a4 b4 c4 d4 2
a5 b5 c5 d5 1
9
  • Just to clarify, these are the headers of columns that you are showing. There is actual data beneath each column?
    – Kusalananda
    Nov 1, 2018 at 19:15
  • @Kusalananda I have clarified it in my edit. These are column headers. I am not providing the data. I can if you want.
    – Coder
    Nov 1, 2018 at 19:15
  • @JeffSchaller Yes, Jeff. I did it now. Sorry for the inconveniences.
    – Coder
    Nov 1, 2018 at 19:30
  • does the data in file2's matching columns always exist in file1, or would we have to fill in something like "n/a" for missing pairs/triplets?
    – Jeff Schaller
    Nov 1, 2018 at 19:35
  • @JeffSchaller Yes. both the files contain same records. So just need to have an extra 'E' column from File2 to file1.
    – Coder
    Nov 1, 2018 at 19:37

2 Answers 2

2

Does this come close to what you need (some formatting still to be done...)?

awk 'NR == FNR {T[$2,$3] = T[$3,$2] = $1; next} {print $0, T[$1,$2]}' file2 file1
A  B  C  D E
----------- 
a1 b1 c1 d1 1
a2 b2 c2 d2 2
a3 b3 c3 d3 1
a4 b4 c4 d4 2
a5 b5 c5 d5 1
0

a tedious join solution: first join on file1 col 1 and file2 col2, then join on file1 col 1 and file2 col 3:

{ 
    join -11 -22 -o 0,1.2,1.3,1.4,2.1 <(sed '1,2d' file1 | sort -k1,1) <(sed '1,2d' file2 | sort -k2,2) 
    join -11 -23 -o 0,1.2,1.3,1.4,2.1 <(sed '1,2d' file1 | sort -k1,1) <(sed '1,2d' file2 | sort -k3,3) 
} | sort -k 1,1 

outputs

a1 b1 c1 d1 1
a2 b2 c2 d2 2
a3 b3 c3 d3 1
a4 b4 c4 d4 2
a5 b5 c5 d5 1

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.