[SOLVED] awk or sed command for columns and rows selection from multiple files

Issue

Looking for a command for the following task:

I have three files, each with two columns, as seen below. I would like to create file4 with four columns.

The output should resemble a merge-sorted version of file1, file2 and file3 such that the first column is sorted, the second column is the second column of file1 the third column is the second column of file2 and the fourth column is the second column of file3.

The entries in column 2 to 3 should not be sorted but should match the key-value in the first column of the original files.

I tried intersection in Linux, but not giving the desired outputs.

Any help will be appreciated. Thanks in advance!!

$ cat -- file1                
A1     B5
A10    B2
A3     B15
A15    B6
A2     B10
A6     B19
$ cat -- file2
A10 C4
A4  C8
A6  C5
A3  C10
A12 C14
A15 C18
$ cat -- file 3
A3  D1
A22 D9
A20 D3
A10 D5
A6  D10
A21 D11

$ cat -- file 4
col1 col2    col3    col4
A1   B5
A2   B10
A3   B15      C10     D1
A4            C8 
A6   B19      C5      D10
A10  B2       C4      D5
A12           C14
A15  B6       C18
A20                   D3
A21                   D11
A22                   D9

Solution

Awk + Bash version:

( echo "col1, col2, col3, col4" &&
awk 'ARGIND==1 { a[$1]=$2; allkeys[$1]=1 } ARGIND==2 { b[$1]=$2; allkeys[$1]=1 } ARGIND==3 { c[$1]=$2; allkeys[$1]=1 }
    END{
        for (k in allkeys) {
            print k", "a[k]", "b[k]", "c[k]
        }
    }' file1 file2 file3 | sort -V -k1,1 ) | column -t -s ',' 

Pure Bash version:

declare -A a
while read key value; do a[$key]="${a[$key]:-}${a[$key]:+, }$value"; done < file1
while read key value; do a[$key]="${a[$key]:-, }${a[$key]:+, }$value"; done < file2
while read key value; do a[$key]="${a[$key]:-, , }${a[$key]:+, }$value"; done < file3

(echo "col1, col2, col3, col4" &&
for i in ${!a[@]}; do 
    echo $i, ${a[$i]}
done | sort -V -k1,1) | column -t -s ','

Explanation for "${a[$key]:-, , }${a[$key]:+, }$value" please check Shell-Parameter-Expansion

Answered By - SimZhou

Answer Checked By - Robin (WPSolving Admin)