Merge and Append columns for more than 2 files

awkpastesedtext processing

The files are tab separated.

I have "N" number of files whose contents are as shown in the screenshot above. I want to merge them and append the 5th column. The first 4 columns are the same.

I tried with the awk command below for files 1 and 2:

awk 'NR==FNR{a[NR]=$0;next} {print a[FNR] "\t",$5}' file1 file2

it only appends for 2 files, not more than 2.

How can I do this correctly using awk, paste or other tools?

Best Answer

If the first four columns are identical across files you could run something like

set -- file*
fields="-f-5,$(seq -s, 10 5 $((5*$#)))"
paste "$@" | cut ${fields%?} >outfile

This will paste all files then extract fields 1-5 and every 5th field after.

Related Solutions

UNIX paste columns and insert zeros for all missing values

If column-order is important, i.e. numbers from the same file should be kept in the same column, you need to add padding while reading the different files. Here is one way that works with GNU awk:

merge.awk

# Set k to be a shorthand for the key
{ k = $1 SUBSEP $2 }

# First element with this key, add zeros to align it with other rows
!(k in h) {
  for(i=1; i<=ARGIND-1; i++)
    h[k] = h[k] OFS 0 
}

# Remember the data element
{ h[k] = h[k] OFS $3 }

# Before moving to the next file, ensure that all rows are aligned
ENDFILE {
  for(k in h) {
    if(split(h[k], a) < ARGIND)
      h[k] = h[k] OFS 0
  }
}

# Print out the collected data
END {
  for(k in h) {
    split(k, a, SUBSEP)
    print a[1], a[2], h[k]
  }
}

Here are some test files: f1, f2, f3 and f4:

$ tail -n+1 f[1-4]
==> f1 <==
xyz desc1 21
uvw desc2 22
pqr desc3 23

==> f2 <==
xyz desc1 56
uvw desc2 57

==> f3 <==
xyz desc1 87
uvw desc2 88

==> f4 <==
xyz desc1 11
uvw desc2 12
pqr desc3 13
stw desc1 14
arg desc2 15

Test 1

awk -f merge.awk f[1-4] | column -t

Output:

pqr  desc3  23  0   0   13
uvw  desc2  22  57  88  12
stw  desc1  0   0   0   14
arg  desc2  0   0   0   15
xyz  desc1  21  56  87  11

Test 2

awk -f merge.awk f2 f3 f4 f1 | column -t