Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I would like to merge specific columns from two txt files containing varying number of rows, but same number of columns (as shown below):

  file1:
  xyz   desc1   12
  uvw   desc2   55
  pqr   desc3   12

  file2:
  xyz   desc1   56
  uvw   desc2   88


  Preferred output:
  xyz   desc1   12  56
  uvw   desc2   55  88
  pqr   desc3   12   0

Currently I use the paste command using awk as:

  paste <(awk '{print $1}' file1) <(awk '{print $2}' file1) <(awk '{print $3}' file1) <(awk '{print $3}' file2) 

But this seems to merge only columns that overlap. Is there a way in awk to insert zeros instead of omitting the row itself?

I need to combine 100 files together such that my output file will contain 102 columns.

share|improve this question
1  
Does the order of columns matter? (The solution by @fedorqui always pads the line with zeros at the end, no matter from which file the value(s) came.) –  Dubu Jun 12 at 13:44
    
It would be interesting to get this comment in my answer so I can be aware of :) –  fedorqui Jun 13 at 8:32
    
@fedorqui That's why I mentioned you. But the OP must clarify his requirements, so I asked the question here. –  Dubu Jun 13 at 10:46
    
@Dubu note the notification didn't arrive to me, because there wasn't any comment from me in this section. –  fedorqui Jun 13 at 11:24
    
@fedorqui Ah, sorry, I didn't know that. –  Dubu Jun 13 at 12:32

3 Answers 3

up vote 3 down vote accepted

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

Output:

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

Edit:

If the output should be tab-separated, set the output field separator accordingly:

awk -f merge.awk OFS='\t' f[1-4]
share|improve this answer
    
This is perfect for me! Thanks a lot!!! :-) –  biobudhan Jun 13 at 8:34
    
Does this result in tab-separated column? If not how to make this as a tab separated column? –  biobudhan Jun 23 at 11:48
    
@biobudhan: No this produces space separated columns. I updated the answer to (more correctly) use OFS when generating the table. With this change you can specify the output separator by setting OFS on the command line, either awk -v OFS='\t' -f merge.awk FILES or as a post argument awk -f merge.awk OFS='\t' FILES. –  Thor Jun 23 at 13:36

Try this:

$ awk '
    FNR == NR { a[$1,$2] = $3; next }
    {
        print $0,(($1,$2) in a) ? a[$1,$2] : "0"
    }
' file2 file1
xyz   desc1   12 56
uvw   desc2   55 88
pqr   desc3   12 0
share|improve this answer
    
Sorry for not mentioning earlier. I have 100 files like this, how do I do this? –  biobudhan Jun 12 at 12:56
    
You compare each two files or compare 100 files? –  cuonglm Jun 12 at 12:58
    
compare 100 files –  biobudhan Jun 12 at 12:59

This is a bit long, but works:

$ cat file1 file2 | awk '{a[$1FS$2]=a[$1FS$2]FS$3; b[$1FS$2]++} END {for (i in b) max=max<b[i]?b[i]:max; for (i in a) {printf "%s %s", i, a[i]; for (j=b[i]; j<max; j++) printf "%s0", FS  printf "%s", RS}}' 
pqr desc3  12 0
xyz desc1  12 56
uvw desc2  55 88

The awk block can be formatted as this:

awk '{a[$1FS$2]=a[$1FS$2]FS$3; b[$1FS$2]++}
      END {for (i in b) max=max<b[i]?b[i]:max
          for (i in a) {printf "%s%s%s", i, FS, a[i]
                        for (j=b[i]; j<max; j++) printf "%s0", FS
                        printf "%s", RS}
          }'

The idea is to print all files and then catch the repeated values in the array a[$1 $2]. Also, b[$1 $2] contains the amount of times a pair ($1, $2) appeared.

In the END{} block we keep looping through values and completing with as much 0s as are missing from number of elements to maximum number of elements.

share|improve this answer
1  
Thanks a ton! Works like a charm!!:-) –  biobudhan Jun 12 at 13:08
1  
@biobudhan: Note that this solution does not preserve the columns, e.g. numbers from the pqr record will always end up in the first column no matter the order of the file arguments. This may or may not be a problem depending on what the you want. –  Thor Jun 12 at 18:15
    
Thor: columns should be preserved! :-) –  biobudhan Jun 13 at 8:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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