I am trying to parse a table with variable number of delimiters to convert into a table of equal columns:

cluster=96\troot\tcellular organisms\tno_rank no_rank$

cluster=42\troot\tcellular organisms\tBacteria\tno_rank\tno_rank\tsuperkingdom$

cluster=362\troot\tcellular organisms\tBacteria\tProteobacteria\tno rank\tno rank\tsuperkingdom\tphylum$

cluster=12330\troot\tcellular organisms\tBacteria\tTerrabacteria\tFirmicutes\tClostridia\tClostridiales\tClostridiaceae\tClostridium\tno rank\tno rank\tsuperkingdom\tno rank\tphylum\tclass\torder\tfamily\tgenus$

The expected output is:

cluster=96\troot\tcellular organisms\t\t\t\t\t\t\t\tno_rank\tno_rank\t\t\t\t\t\t\t$

cluster=42\troot\tcellular organisms\tBacteria\t\t\t\t\t\t\tno_rank\tno_rank\tsuperkingdom\t\t\t\t\t\t$

cluster=362\troot\tcellular organisms\tBacteria\tProteobacteria\t\t\t\t\t\tno rank\tno rank\tsuperkingdom\tphylum\t\t\t\t\t$

cluster=12330\troot\tcellular organisms\tBacteria\tTerrabacteria\tFirmicutes\tClostridia\tClostridiales\tClostridiaceae\tClostridium\tno rank\tno rank\tsuperkingdom\tno rank\tphylum\tclass\torder\tfamily\tgenus$

"\t" is tab delimiter and "$" is end of line

  • How would anyone know where to add the extra columns? Do you know in advance how many columns you expect, or do you need to find the row with the most and then make everything match that? – Eric Renouf Nov 10 '16 at 12:40
  • Yes I want to add the columns with the max number of columns in the file – user3472737 Nov 10 '16 at 12:46
  • Is the conversion from underscores to spaces accidental? – Wildcard Nov 10 '16 at 13:06
  • sorry, there is not underscore – user3472737 Nov 10 '16 at 13:09
  • Your input still appears inconsistent (no_rank versus no rank). Also your title says "specific" but I don't see any actual specification - for example, how to decide how many of the additional tabs should go before the first no_rank and how many at the end? Are we supposed to count that or is there a maximum number of missing fields in each position (before and after no_rank)? – steeldriver Nov 10 '16 at 13:58

Using Awk with two passes:

awk -F'\t' -v OFS='\t' 'FNR==NR {if (NF>a) {a=NF}; next} NF<a{$a=""} 1' file file

This will add the extra tabs at the end of the lines with missing fields.

Based on your exact sample data, I think it is likely you are dealing with a problem impossible to solve programmatically. If you need extra tabs in an intermediate position to ensure the no_rank values end up in the right column, you will need some fancier logic.

  • yes the tabs should be inserted before the first occurrence of "no rank" and end of the line – user3472737 Nov 10 '16 at 13:18
  • @user3472737, edit your example text in your question, then. It doesn't match what you are saying. You show some tabs being added at the end of the line and some in the middle. – Wildcard Nov 10 '16 at 13:23
  • Actually there is 18 columns at max which is mainly separated at middle with "no rank". so I want to add missing columns before first "no rank" and end of line – user3472737 Nov 10 '16 at 13:26
  • @user3472737 that information should be edited into your question – steeldriver Nov 10 '16 at 13:45
  • I edited the above example is it clear or need to some more additions – user3472737 Nov 10 '16 at 13:50

Using Miller: I answered a similar question for CSV here but in that case the max length was already available in the header line so there was no need to search for it elsewhere.

In this case, where the max length may be elsewhere, the best I could do was:

cat in.dat | mlr --nidx --fs tab --nidx put -q '
  @maxnf = max(@maxnf, NF);
  @records[NR] = $*; # Retain
  @counts[NR] = NF;

  end {
    for (i in @records) {
      @record = @records[i];
      for (j = @counts[i] + 1; j <= @maxnf; j += 1) { # add extra fields
        @record["x".j] = "";
      emit @record;            # insert into the output record stream

Idea is to retain the record-list and max NF; then process in an end-block. Note that this uses features in head (not in the latest 4.5.0 release).

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.