Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It only takes a minute to sign up.
Sign up to join this community
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:D5How can I achieve this?
title1 title2 title3 title4
A1 A2 A3 A4
B1 B2 B3 B4
C1 C2 C3 C4
D1 D2 D3 D4Have a look at GNU datamash which can be used like datamash transpose.
A future version will also support cross tabulation (pivot tables)
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.
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
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 " ").
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
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
grep -Ev "^$|title5" sample.txt | sed 's/:.*//' | sort -u | tr '\n' '\t';
echo
grep -Ev "^$|title5" a | sed 's/title[0-9]://g' | paste - - - -
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")"
GNU datamash utility
apt install datamash
datamash transpose < yourfile
Taken from this site, https://www.gnu.org/software/datamash/ and http://www.thelinuxrain.com/articles/transposing-rows-and-columns-3-methods
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.
To balance three awk answers, two datamash transpose answers, and one transpose answer, here's Miller in action:
% mlr --ixtab --ips : --opprint cat << END 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 END title1 title2 title3 title4 title5 A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 C1 C2 C3 C4 C5 D1 D2 D3 D4 D5 %
You are doing a conversion from XTAB format (-ixtab) to PPRINT format (-opprint).
If you really do want to eliminate title5 (which is not clear from the question), then simply pre-process the input to Miller through grep -v '^title5:'.
paste is probably your best bet. You can extract the relevant bits with cut, grep and awk like this:
(awk 'NR==1' RS= infile | cut -d: -f1; cut -sd: -f2 infile)
If the 5th column should be eliminated, append awk 'NR%5' like this:
(awk 'NR==1' RS= infile | cut -d: -f1; cut -sd: -f2 infile) | awk 'NR%5'
Now columnate with paste:
(awk 'NR==1' RS= infile | cut -d: -f1; cut -sd: -f2 infile) | awk 'NR%5' | paste - - - -
Output:
title1 title2 title3 title4
A1 A2 A3 A4
B1 B2 B3 B4
C1 C2 C3 C4
D1 D2 D3 D4
For just the transpose part, I had a similar problem recently and used:
awk -v fmt='\t%4s' '{ for(i=1;i<=NF;i++){ a[i]=a[i] sprintf(fmt, $i); } } END { for (i in a) print a[i]; }'
Adjust the fmt as needed. For each input line, it concatenates each fields onto an array element. Note that awk string concatenation is implicit: it happens when you write two things without any operator.
Sample I/O:
i mark accep igna utaal bta
-22 -10 -10 -20 -10 -10
-21 -10 -10 -20 -10 -10
-20 -10 -10 -20 -10 -10
-19 -10 0 -10 -10 -10
-18 0 0 -10 0 0
-12 0 0 -10 0 0
-11 0 0 -10 0 0
-10 0 0 -10 0 0
output:
i -22 -21 -20 -19 -18 -12 -11 -10
mark -10 -10 -10 -10 0 0 0 0
accep -10 -10 -10 0 0 0 0 0
igna -20 -20 -20 -10 -10 -10 -10 -10
utaal -10 -10 -10 -10 0 0 0 0
bta -10 -10 -10 -10 0 0 0 0
In this code file1 is the input table with blanks removed.
We first "reduce" and sort file1:
$ awk -F":" '{a[$1]=a[$1]" "$2} END {for (i in a) print i a[i]}' table1 | sort
title1 A1 B1 C1 D1
title2 A2 B2 C2 D2
title3 A3 B3 C3 D3
title4 A4 B4 C4 D4
title5 A5 B5 C5 D5
Transpose this with tab-separated output is trivial:
$ awk '{{OFS="\t"} for (i=1; i<=NF; i++) a[i]= (i in a?a[i] OFS :"") $i; }
END{ for (i=1; i<=NF; i++) print a[i] } $1'
title1 title2 title3 title4 title5
A1 A2 A3 A4 A5
B1 B2 B3 B4 B5
C1 C2 C3 C4 C5
D1 D2 D3 D4 D5
Using Raku (formerly known as Perl_6)
The solution is most easily accomplished by running 2 (or 3) successive one-liners on the input. The first one-liner below removes blank lines, while the second returns 5-column header-plus-data output:
raku -ne '.put if .chars;'
...and then:
raku -e 'my @a; @a.push( .split(":")) for lines; put [Z~] "table" xx 5, 1..5; .put for @a.map(*.[1]).rotor(5);'
The code above is pretty short and sweet, and can be shortened some more (for example, the statement put [Z~] "title" xx 5, 1..5; just recreates the Header line). Actually, the entire answer can be performed with one raku call as per below, which uses a ternary conditional:
raku -e 'my @a; for lines() {.chars ?? @a.push( .split(":")) !! next}; put [Z~] "title" xx 5, 1..5; .put for @a.map(*.[1]).rotor(5);'
To create \t tab-separated columns, simply join header and body on \t tab:
raku -e 'my @a; for lines() {.chars ?? @a.push( .split(":")) !! next}; \
put ([Z~] "title" xx 5, 1..5).join("\t"); .put for @a.map(*.[1]).rotor(5)>>.join("\t");'
Sample Output:
title1 title2 title3 title4 title5
A1 A2 A3 A4 A5
B1 B2 B3 B4 B5
C1 C2 C3 C4 C5
D1 D2 D3 D4 D5
Of course, there seems to be some confusion regarding the Question because the OP omitted the last "title5" column from the desired output. That's simple enough to remedy using Raku, take the output above and run:
~$ raku -ne '.words[0..3].put;' 5columns.txt
title1 title2 title3 title4
A1 A2 A3 A4
B1 B2 B3 B4
C1 C2 C3 C4
D1 D2 D3 D4
