9

How to create a new file merging selective columns from two separate files using awk? Without messing up the elements orders of BOTH files.

Exemple: File 3 may contain column 1,2,3 from File 1 and column 4 from File 2.

File 1
A   23  8   T
A   63  9   9
B   45  3   J

File 2
A   0
A   6   
B   5

File 3
A   23  8   0
A   63  9   6
B   45  3   5
4

Try this:

$ awk 'FNR==NR{a[FNR]=$2;next};{$NF=a[FNR]};1' file2 file1
A 23 8 0
A 63 9 6
B 45 3 5
  • Thanks! I also successfully tried this way out using gawk: pr -m -t -s\ File1.txt File2.txt | gawk '{print $1,$2,$3, $6}' > File3.txt – dovah Jul 21 '14 at 8:35
  • 2
    @Dovah: You can use paste file1 file2 and then print selected fields in awk. – cuonglm Jul 21 '14 at 8:50
  • This stores file2 in memory, which can be prohibitive if the files are large. There's a simpler way to do this without the memory overhead (see my answer). – Gilles Jul 21 '14 at 23:54
15

There's a dedicated tool for that: paste. It concatenates each full line from the first file with the corresponding line from the second file; you can remove unwanted columns before or after. For example, assuming that your columns are tab-delimited:

paste file1.txt file2.txt | cut -f 1,2,3,6

Here's a way to pre-filter both files that relies on ksh/bash/zsh process substitution.

paste <(<file1.txt sed 's/[[:space:]][[:space:]]*[^[:space:]]*$//') \
      <(<file1.txt sed 's/^[^[:space:]]*[[:space:]][[:space:]]*//')

Awk is primarily geared to processing one file at a time, but you can call getline to read from another file in parallel.

awk '
  BEGIN {file2=ARGV[2]; ARGV[2]="";}
  {$0 = $0 ORS getline(); print $1, $2, $3, $6;}
' file1.txt file2.txt

So far I've assumed that you want to match line 1 of file 1 with line 1 of file 2, line 2 of file 1 with line 2 of file 2, etc. If you want to match the contents of a column, that's a completely different matter. join will do the job provided that the column you want to match is sorted.

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.