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 have a file with the lines like below.

title1:A1
title2:A2
title3:A3
title4:A4
title5:A5

title1:B1
title2:B2
title3:B3
title4:B4
title5:B5

title1:C1
title2:C2
title3:C3
title4:C4
title5:C5

title1:D1
title2:D2
title3:D3
title4:D4
title5:D5

How can I achieve this?

title1    title2     title3    title4
A1         A2         A3         A4
B1         B2         B3         B4
C1         C2         C3         C4
D1         D2         D3         D4

share|improve this question

4 Answers 4

You could use awk to process the data then paste and column to format it.

Here I assume title1 is only an example in your post, and that data does not contain : except as separator between header + data.

n signifies how many columns to print (should match dashes in paste).

awk -F":" -v n=4 \
'BEGIN { x=1; c=0;} 
 ++c <= n && x == 1 {print $1; buf = buf $2 "\n";
     if(c == n) {x = 2; printf buf} next;}
 !/./{c=0;next}
 c <=n {printf "%s\n", $2}' datafile | \
 paste - - - - | \
 column -t -s "$(printf "\t")"

If you want to make it more flexible and easy to maintain you could write it as a script. Here is an example using bash wrapper for awk and piped to column. This way you could also do more data checking like e.g. making sure headers is correct throughout all rows etc.

Used typically as:

$ ./trans -f data -c 4
title one  title two  title three  title four
A1         A2         A3           A4
B1         B2         B3           B4
C1         C2         C3           C4
D1         D2         D3           D4

If headers always is shorter then data you could also save header widths, then printf with %-*s and skip column all together.

#!/bin/bash

trans()
{
    awk -F":" -v ncol="$1" '
    BEGIN {
        level = 1 # Run-level.
        col   = 1 # Current column.
        short = 0 # If requested to many columns.
    }
    # Save headers and data for row one.
    level == 1 {
        head[col] = $1
        data[col] = $2
        if (++col > ncol) { # We have number of requested columns.
            level = 2
        } else if ($0 == "") { # If request for more columns then available.
            level = 2
            ncol  = col - 2
            short = 1
        } else {
            next
        }
    }
    # Print headers and row one.
    level == 2 {
        for (i = 1; i <= ncol; ++i)
            printf("%s\t", head[i])
        print ""
        for (i = 1; i <= ncol; ++i)
            printf("%s\t", data[i])
        level = 3
        col = ncol + 1
        if (!short)
            next
    }
    # Empty line, new row.
    ! /./ { print ""; col = 1; next }
    # Next cell.
    col > ncol {next}
    {
        printf "%s%s", $2, (col <= ncol) ? "\t" : ""
        ++col
    }
    END {print ""}
    ' "$2"
}

declare -i ncol=4  # Columns defaults to four.
file=""            # Data file (or pipe).

while [[ -n "$1" ]]; do
    case "$1" in
    "-c") ncol="$2"; shift;;
    "-f") file="$2"; shift;;
    *) printf "Usage: %s [-c <columns>] [-f <file> | pipe]\n" \
        "$(basename $0)" >&2;
        exit;;
    esac
    shift
done

trans "$ncol" "$file" | column -t -s "$(printf "\t")"
share|improve this answer
    
Nice answer! @JoelDavis and I have been hacking on this, but your answer is terrific! –  slm Jun 17 '13 at 11:19
    
@slm: When I first start … ;) SE is dangerous. –  Sukminder Jun 17 '13 at 11:37

There's probably a more succinct way of formulating this but this seems to accomplish the general effect:

[jadavis84@localhost ~]$ sed 's/^title[2-9]://g' file.txt | tr '\n' '\t' | sed 's/title1:/\n/g' ; echo

A1  A2  A3  A4  A5      
B1  B2  B3  B4  B5      
C1  C2  C3  C4  C5      
D1  D2  D3  D4  D5  
[jadavis84@localhost ~]$ 

Multiple sed invocations doesn't feel right (and I'm pretty sure sed can do the new line translation as well) so it probably isn't the most straight forward way to do it. Also, this strips the would-be headers, but you can generate those manually once you have the rows/fields formatted properly.

A better answer would probably distill that effect down to just using sed or awk to do this so that you only have one thing going on at a time. But I'm tired so this is what I was able to put together.

share|improve this answer
    
Joel - I made the same mistake and just noticed it, he doesn't want the title5 column in the output. –  slm Jun 17 '13 at 3:23
    
Ah, well running through awk at the last should fix that. But it looks like Sukminder's posted a complete solution. –  Bratchley Jun 17 '13 at 11:13

Here's a quick way to put the file into the format you want:

$ grep -Ev "^$|title5" sample.txt | sed 's/title[0-9]://g' | paste - - - -
A1  A2  A3  A4
B1  B2  B3  B4
C1  C2  C3  C4
D1  D2  D3  D4

If you want the column headers:

$ grep -Ev "^$|title5" sample.txt | sed 's/:.*//' | sort -u | tr '\n' '\t'; \
    echo ""; \
    grep -Ev "^$|title5" a | sed 's/title[0-9]://g' | paste - - - -
title1  title2  title3  title4  
A1      A2      A3      A4
B1      B2      B3      B4
C1      C2      C3      C4
D1      D2      D3      D4

How the 2nd command works

printing the banner
grep -Ev "^$|title5" sample.txt | sed 's/:.*//' | sort -u | tr '\n' '\t';
putting a return after the banner in
echo
printing the rows of data
grep -Ev "^$|title5" a | sed 's/title[0-9]://g' | paste - - - -
share|improve this answer

Outside of rolling a custom solution to transpose rows with columns from a command line the only tool I've ever seen that can do this is a tool called ironically transpose.

Installation

Unfortunately it's not in any repo so you'll need to download and compile it. This is pretty straightforward since it has no additional libraries that it's dependent on. It can be accomplished like so:

$ gcc transpose.c -o transpose

Usage

It can handle straightforward text files with ease. For example:

$ cat simple.txt 
X column1 column2 column3
row1 0 1 2
row2 3 4 5
row3 6 7 8
row4 9 10 11

Can be transposed using this command:

$ transpose -t --fsep " " simple.txt 
X row1 row2 row3 row4
column1 0 3 6 9
column2 1 4 7 10
column3 2 5 8 11

This command is transpose to transpose (-t) and the field separator to use is a space (--fsep " ").

Your example

Since your sample data is in a slightly more complex format it needs to be dealt with in 2 phases. First we need to translate it into a format that transpose can deal with.

Running this command, will put the data in a more horizontally friendly format:

$ sed 's/:/ /; /^$/d' sample.txt \
    | sort | paste - - - - -
title1 A1   title1 B1   title1 C1   title1 D1   title2 A2
title2 B2   title2 C2   title2 D2   title3 A3   title3 B3
title3 C3   title3 D3   title4 A4   title4 B4   title4 C4
title4 D4   title5 A5   title5 B5   title5 C5   title5 D5

Now we just need to remove the secondary occurrences of the title1, title2, etc.:

$ sed 's/:/ /; /^$/d' sample.txt \
    | sort | paste - - - - - | sed 's/\ttitle[0-9] / /g'
title1 A1 B1 C1 D1 A2
title2 B2 C2 D2 A3 B3
title3 C3 D3 A4 B4 C4
title4 D4 A5 B5 C5 D5

It's now in a format that transpose can deal with. The following command will do the entire transposition:

$ sed 's/:/ /; /^$/d' sample.txt \
    | sort | paste - - - - - | sed 's/\ttitle[0-9] / /g' \
    | transpose -t --fsep " "
title1 title2 title3 title4
A1 B2 C3 D4
B1 C2 D3 A5
C1 D2 A4 B5
D1 A3 B4 C5
A2 B3 C4 D5
share|improve this answer

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.