1

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.)

  • Just use vim and hold down [Shift]+J ;) Oh, I guess you can't do that for automated processes. You could always use the butterflies + cosmic rays that flip bits in your drive platter. John1024's answer below is about as complicated as doing that anyway. – Dylan Nov 21 '14 at 3:27
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.

0

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.

0

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 . .

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.