Merging multiple rows into a single one using awk

Question

I'm working with a very large data set in the format:

chr1 45162 . . C T 
chr1 45162 . . C T
chr1 45162 A . . T
chr1 45162 . . C T
chr1 45257 A . . T
chr1 45257 A . . T
chr1 45413 A . . T
chr1 46844 A . C .
chr1 72434 A G . .
chr1 72434 A G . .
chr1 72434 A G . .
chr1 72434 A G . .
chr1 72515 A . C .
chr1 72515 A . . T
chr1 77689 A G . .    

The output I would like is this:

chr1 45162 A . C T
chr1 45257 A . . T
chr1 45413 A . . T
chr1 46844 A . C .
chr1 72434 A G . .
chr1 72515 A . C T
chr1 77689 A G . .

Essentially, for each unique value in column 2, I need to see all the attributes it has, which might be stored on separate lines.

So, if I had:

chr1 100 A . C .
chr1 100 . G . T

I'd want the line in the output to be:

chr1 100 A G C T

I have a script that I believe is working correctly, but it is much too slow. I think there must be a much easier way to do this in a simple awk script.

My current version is here: http://ideone.com/e.js/ETBRz3 But, like I said, I think there should be an almost 1 liner version possible using only awk.

All suggestions welcome.

(If it simplifies things, I can go back and modify the script that generated this file such that empty fields are an empty string rather than a "." character.)

Answer 1

Here is one approach:

$ awk '$2!=old && NR>1 {for (i=1;i<=NF;i++) printf a[old","i]" "; print"";} {old=$2;for (i=1;i<=NF;i++) {if (a[$2","i]=="." ||  a[$2","i]=="") a[$2","i]= $i}} END{for (i=1;i<=NF;i++) printf a[old","i]" "; print"";}' file 
chr1 45162 A . C T 
chr1 45257 A . . T 
chr1 45413 A . . T 
chr1 46844 A . C . 
chr1 72434 A G . . 
chr1 72515 A . C T 
chr1 77689 A G . . 

How it works

• $2!=old && NR>1 {for (i=1;i<=NF;i++) printf a[old","i]" "; print"";}

  After the first line, for every time that we run into a new value for the second column, print out the results of the preceding value.

• old=$2;for (i=1;i<=NF;i++) {if (a[$2","i]=="." || a[$2","i]=="") a[$2","i]= $i}

  Update the array a with value from the current line.

  GNU awk has nice 2-D arrays. For compatibility, however, I am using the POSIX compatible substitute.

• END{for (i=1;i<=NF;i++) printf a[old","i]" "; print"";}

  After the last line, print out the info for the final section.

Answer 2

One more variant for unsorted lines:

awk '{
k[$2]=$1;
for(i=3;i<7;i++){
  if(l[$2,i]=="." || l[$2,i]=="")
    l[$2,i]=$i;
  }
}
END{
for(n in k){
  printf("%s %s ",k[n],n);
  for(m=3;m<7;m++)
    printf("%s ", l[n,m]);
  print "";
  }
 }' file

A short explanation:

Going through file scripts make two assotiative arrays: k with field#2 as index and l with index field#2,Next_fields_number. When all lines in file are passed script starts two loops to print 1st array + fields from second ones.

Answer 3

A perl solution:

$ perl -anle '
  for (2..$#F) {
    $h{join(" ",@F[0..1])}->{$_} ||= $F[$_];
    $h{join(" ",@F[0..1])}->{$_} = $F[$_] if $F[$_] ne ".";
  }
  END { print "$_ @{$h{$_}}{sort keys %{$h{$_}}}" for sort keys %h }
' file
chr1 45162 A . C T
chr1 45257 A . . T
chr1 45413 A . . T
chr1 46844 A . C .
chr1 72434 A G . .
chr1 72515 A . C T
chr1 77689 A G . .