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've got some tab-delimited data coming out of a Unix pipe. I'd like to format this data into a compact human-readable table.

How can I expand these tabs into spaces, and automatically set the tab stops based on the width of the field data?

For example, my hypothetical generate_tsv script might produce (I'm using \t to represent actual tab characters) —

alpha\t1\t0.21085026\tok
beta\t4096\t0.0\tok
gamma\t\t-1.0\tinvalid

Using | expand -t 12, I'd get:

alpha       1           0.21085026  ok
beta        4096        0.0         ok
gamma                   -1.0        invalid

But I'd like something more compact (so there are exactly two spaces separating columns), like this:

alpha  1     0.21085026  ok
beta   4096  0.0         ok
gamma        -1.0        invalid

As suggested by @jw013, | column -t -s $'\t' is close, but not quite correct, as it collapses the empty cell:

alpha  1     0.21085026  ok
beta   4096  0.0         ok
gamma  -1.0  invalid
share|improve this question
    
Just a question: do you have tab characters in your files or literal \t's? –  jw013 Feb 9 '12 at 5:14
    
Tab characters (I just clarified that in the question). –  smokris Feb 9 '12 at 5:27
add comment

2 Answers

up vote 4 down vote accepted

If you have column(1), an old BSD tool, try column -t, for pretty-printing tables.

To ensure empty cells are displayed, you could try the approach of inserting a single space in each empty cell (recognizable by two consecutive tabs). The idea is column(1) should give the space character its own column but being a single character in width it should not affect the table dimensions or be visible in the output to humans.

generate_tsv | 
   awk '/\t\t/ { for (i = 0; i < 2; i++) gsub(/\t\t/, "\t \t") } 1' | 
   column -t -s $'\t'

The extra awk inserted in the pipeline does the inserting of spaces into each empty cell, as described. 2 passes are necessary to handle 2 consecutive empty cells (\t\t\t).

share|improve this answer
    
Oh cool, I hadn't seen that command. | column -t -s $'\t' does almost exactly what I want, except that it loses empty cells (a\t\tc apparently gets interpreted as cell a followed by cell c, instead of cell a, empty cell, cell c as I'd prefer). –  smokris Feb 9 '12 at 4:14
    
The Debian version of column(1) appears to have been patched with an additional -n flag that addresses this (runs of consecutive column delimiters). If you don't have that, I've edited an alternate approach into my answer. –  jw013 Feb 9 '12 at 5:28
    
Excellent. Thanks for your help, @jw013. –  smokris Feb 11 '12 at 7:03
add comment

This is a roundabout way to do it, but it works and has some features not found in column.
It tabulates, taking empty lines and empty cells into consideration.
It can join multiple files, side by side. eg. Prefix rows of "F1" with rows from "Nb"
Blank lines are displayed as empty cells (for multi-file paste)
The files can be of different lengths.

# Make test data file    
echo -e "alpha\t1\t0.21085026\tok
beta\t4096\t0.0\tnext F1 line is blank

gamma\t\t-1.0\tinvalid" >F1
sed -n '=' F1 >Nb
echo -e "\tB\tC\textra F1 line" >>F1

# Set the positional parameters $1, $2
set Nb F1  

{ echo -e "<html>\n<table border=1 cellpadding=0 cellspacing=0>"
  paste "$@" |sed -e 's#\(.*\)#\x09\1\x09#
                      s#\x09# </pre></td>\n<td><pre> #g
                      s#^ </pre></td>#<tr>#
                      s#\n<td><pre> $#\n</tr>#'
  echo -e "</table>\n</html>"
}|w3m -dump -T 'text/html'

The output of the tabulated version:

┌───┬───────┬──────┬────────────┬───────────────────────┐
│ 1 │ alpha │ 1    │ 0.21085026 │ ok                    │
├───┼───────┼──────┼────────────┼───────────────────────┤
│ 2 │ beta  │ 4096 │ 0.0        │ next F1 line is blank │
├───┼───────┼──────┼────────────┼───────────────────────┤
│ 3 │       │      │            │                       │
├───┼───────┼──────┼────────────┼───────────────────────┤
│ 4 │ gamma │      │ -1.0       │ invalid               │
├───┼───────┼──────┼────────────┼───────────────────────┤
│   │       │ B    │ C          │ extra F1 line         │
└───┴───────┴──────┴────────────┴───────────────────────┘

To get a non-framed version, it only requires the removal of the frame. This is easily done by piping the output one step further as shown by the following line of code, which is a replacement for the last line of the above tabulated version.

# the replacement "new" last line
}|w3m -dump -T 'text/html' |sed -r '/^┌|├|└/d; s/^│ //g; s/ │$//g; s/ │ /│/g' 

Here is the "new" out put: I've left the vertical dividers in place. If you don't want them, just change the last sed expression to whatever you like.

1│alpha│1   │0.21085026│ok                   
2│beta │4096│0.0       │next F1 line is blank
3│     │    │          │                     
4│gamma│    │-1.0      │invalid              
 │     │B   │C         │extra F1 line        

or, replacing the / │ / with / / (2 spaces), it looks this.

1  alpha  1     0.21085026  ok                   
2  beta   4096  0.0         next F1 line is blank
3                                                
4  gamma        -1.0        invalid              
          B     C           extra F1 line        

And, of course, the leading numbers are there only to demonstrate the side-by-side file "paste".

share|improve this answer
    
Interesting approach. Thank you. –  smokris Feb 11 '12 at 5:33
add comment

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.