AWK gaps between cells

Question

I have a problem, please could you help me? I have the .txt file I use the awk code to separate columns using the tab delimited function. I receive the align columns, but if some information missing, the whole row turn left and information are shifted to another column. Please could you help me? How could I incorporate to this code? Thank you so much..

#!/bin/bash

for f 
in *.vcf; 
do awk 'BEGIN {OFS = "\t"}
        /^##/ {next}
        /^#/ {sub(/^#/,"",$1)}
        {$1=$1; print}
    ' "$f" > "${f/%vcf/tsv}"
done


> INPUT:
> 
> CHROM    ID    REF   ALT 
  chr1    235     A     B 
  chr2     A      B
  chr3    225     B

OUTPUT:

  CHROM    ID   REF   ALT  
  chr1    235    A     B 
  chr2     .     A     B 
  chr3    225    .     B

You can check if the number of fields (NF) is equal to the one expected, otherwise skip the row. — Alessandro Russo, Oct 21 '13 at 12:13
btw, script coming from http://stackoverflow.com/a/19451399/1983854 — fedorqui, Oct 21 '13 at 12:32
Yes It is no my script, mr. glen is author, but is it a problem? If it is I am so sorry. — Vonton, Oct 21 '13 at 12:35
If you have problems with alignment, you might have a look at this http://unix.stackexchange.com/questions/47832/is-there-a-simple-command-for-outputting-tab-delimited-columns-in-bash — Håkon Hægland, Oct 21 '13 at 12:40
Yes I have to tab delimit an after this find the gaps (empty fields) and sort it to right column with right header. — Vonton, Oct 21 '13 at 13:35
You really need to add a small, clear example of an input and desired output. — hemflit, Oct 21 '13 at 15:02
How do you propose to figure out which field is missing? Are the ID's always numeric? Are the REF's always a single alpha? You need to clearly spell out your requirements if you want help from us. — glenn jackman, Oct 21 '13 at 17:44
ID is in format "rs2013202" and REF and ALT are often single alpha, but unfortunately sometimes have more alphas. — Vonton, Oct 22 '13 at 09:16

score 2 · Answer 1 · answered Oct 24 '13 at 07:07

The problem contains ambiguities. Looking at the data:

chr1    235     A     B 
chr2     A      B
chr3    225     B

perhaps in the chr2 row, we can guess that the ID column is missing because IDs are numbers. We are missing one column and it's the numeric one so it must be ID.

But in the third row, how do we know that the REF column is missing, rather than ALT?

If ALT is never missing, then it's simple. But if either could be missing, it may be impossible.

In any case, before you can write the program code to renormalize the data into proper columns, you have to be able to articulate the rules for identifying what columns are missing, or else recognize that it is impossible and give up.

You may simply have to go upstream and find a better source of the same data which doesn't have munged columns.

AWK gaps between cells

1 Answers1