Creating columns from text output but with multiple word columns

Question

I have the following output:

Person 49 
Persons Boss 3
HR 21 
CEO 93
System Administrator 2

Using column -t produces the following output:

Person   49
Persons  Boss           3
HR       21
CEO      93
System   Administrator  2

as you can see this does not look good. I need the output to look like this:

Person               49
Persons Boss          3 
HR                   21
CEO                  93
System Administrator  2

Anyone know how this can be produced?

Answer 1

Change the column separator to e.g. Tab than use column:

output | sed 's/\S\+\s*$/\t&/' | column -t -s $'\t'

Answer 2

Here's a solution in awk that dynamically determines the required column widths. It can take any number of fields on each line, but it assumes the final field is an integer. The integers are formatted to line up on the right, as desired.

format.awk

#!/usr/bin/awk -f

# Format text and numbers into two columns
# Written by PM 2Ring 2015.04.16

#Gather data, line by line
{
    #Split name from number
    match($0, /^(.*) ([0-9]+)[[:space:]]*$/, a)
    #printf "%d: %s -> [%s] [%s]\n", NR, $0, a[1], a[2]

    #Store name & number into arrays
    name[NR] = a[1]
    num[NR] = a[2]

    #Update field widths
    namelen = length(a[1])
    if (namelen > maxname) 
        maxname = namelen

    numlen = length(a[2])
    if (numlen > maxnum) 
        maxnum = numlen
}

#Print re-formatted data
END{
    for(i=1; i<=NR; i++)
        #Use printf's '*' modifier to specify field widths 
        printf "%-*s %*d\n", maxname, name[i], maxnum, num[i]
}

output

Person               49
Persons Boss          3
HR                   21
CEO                  93
System Administrator  2

You can call the script like this:

$ awk -f format.awk data_filename

You can also pipe the text data into it. And if you use chmod to give it give it execute permissions you can run it like any other command.

Obviously, this is a bit more elaborate than Costa's solution that uses sed to pre-process the input to column. So I'd suggest using that, unless you really want the numbers to be right-justified. :) OTOH, although this script looks bigger than Costa's one-liner, awk is very fast, so I expect that this solution would be comparable in speed to Costa's, even with really large data files.

Answer 3

You can do it with a one-liner by saving the last filed to a new variable, and then set it original pointer to a empty string, and then print $0 and last-filed variable you saved.

(echo a b c 10; echo x y 2 ; echo x 1) \
       | awk '{lastf=$NF ; $NF="" ; print $0,", "lastf}' \
       | column -ts,

# output
a b c     10
x y       2
x         1