Transpose columns to rows in bash

Question

I have the following bash command:

df -htgfs | awk '{print $5,$4}'| grep "^/"

Gives me the following output:

/VAULT10 48%
/VAULT11 39%
/VAULT12 59%
/VAULT13 48%
/VAULT14 38%

However, I'd like it in the format below:

/VAULT10 /VAULT11 /VAULT12 /VAULT13 /VAULT14  
48% 39% 59% 48% 38%

But I'm not sure how to achieve it easily in bash (can be space, tab or comma seperated, it's only going to end up being copied/pasted into an excel spreadsheet anyway).

For clarity, the raw df -htgfs output is as follows (this box runs GFS disk clustering).

Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/pool_gfs-pool_gfs
                      506G  368G  138G  73% /VAULT10
/dev/mapper/pool_gfs1-pool_gfs1
                      506G  202G  304G  40% /VAULT11
/dev/mapper/pool_gfs2-pool_gfs2
                      506G  200G  306G  40% /VAULT12
/dev/mapper/pool_gfs3-pool_gfs3
                      506G  260G  246G  52% /VAULT13
/dev/mapper/pool_gfs4-pool_gfs4
                      506G  237G  269G  47% /VAULT14

Any ideas?

Related: http://stackoverflow.com/questions/13634816/using-awk-to-transpose-column-to-row — fedorqui, Sep 10 '13 at 09:19

Mickaël Bucas · Accepted Answer · 2013-09-10T13:45:04.477

1

awk can take care of that :

df -htgfs | awk '$5~/^\//{name=name sep $5; size=size sep $4; sep=","} END{print name; print size}'

edited Sep 10 '13 at 13:45

answered Sep 10 '13 at 09:26

Mickaël Bucas

683
5
20

That just gives me two blank rows? – Graham Gold Sep 10 '13 at 09:36
Seeing the output of `df -htgfs`, I understand : I've put a filter on the first caracter of the line to be /, but it should be filtered on the 5th field, see modified command. – Mickaël Bucas Sep 10 '13 at 11:28
nigh on perfect - one wee question, because I'm actually going to be running this remotely from a windows box, using `plink`: how do I change the output field seperator from space to something else? (the spaces don't showing in the response in `plink` but do through a `putty` session (I've tried slotting in `OFS=","` but clearly in the wrong place.) – Graham Gold Sep 10 '13 at 12:09
I've put the separator directly in the command, but you can change it. – Mickaël Bucas Sep 10 '13 at 13:42

score 1 · Answer 2 · answered Sep 10 '13 at 10:16

A general purpose solution for transposing a file, Bash only. The file content goes into an array line after line and will be written out in another order.

declare -a array                    # file content
IFS=' '                             # use $'\t' for tabs
ROWS=0                              # counts rows
COLS=0                              # counts columns

while read -a line ; do
  array+=( ${line[@]} )
  ((ROWS++))
done < "$infile"

COLS=$(( ${#array[@]}/ROWS ))       # get number of column

for (( row = 0; row < COLS; row++ )); do
  line=()
  for (( col = row; col < ROWS*COLS; col += COLS )); do
    line+=( ${array[col]} ) 
  done
  printf "%s\n" "${line[*]}" 
done

I was hoping for something short enough to go on the commandline, as I'm going to be calling this from `plink` on a windows box, but I guess I could make it a two stage process, getting the initial output into a file then calling a bash script per what you've done above, I'll have a look. — Graham Gold, Sep 10 '13 at 10:43

choroba · Answer 3 · 2013-09-10T11:32:39.670

1

Perl to the rescue:

df -h | perl -lane \
    'push @n,$F[-1] and push @p, $F[-2] if $#F and $F[-1] =~ m{^/} }{ $, = "\t"; print @n; print @p'

edited Sep 10 '13 at 11:32

answered Sep 10 '13 at 10:19

choroba

231,213
25
204
289

@GrahamGold: Try adding the `tgfs` part back to `df`. – choroba Sep 10 '13 at 10:41
@GrahamGold: You should probably provide the output of `df -htgfs` as well in your question, as it seems different from what `df` returns on my system. – choroba Sep 10 '13 at 10:51
Nearly there, edited `df -h` to be `df-htgfs` to only display gfs volumes. However, the order of the vaults is no longer as it was (now showing /VAULT11 /VAULT14 /VAULT10 /VAULT12 /VAULT13) – Graham Gold Sep 10 '13 at 11:22
@GrahamGold: OK, another update, using arrays instead of hashes. – choroba Sep 10 '13 at 11:32
Works great. I have one slight issue, but more because of how I'm doing this than anything else. I'm running the command remotely from a windows box using `plink` and with the perl solutions, I keep seeing `SCALAR(0x21345b43)` type entries between each part of each field in the `plink` response that I don't see when I just run the command in a `putty` session. – Graham Gold Sep 10 '13 at 12:11
@GrahamGold: Maybe a quoting/escaping issue? Not at a MSWin box ATM. – choroba Sep 10 '13 at 12:36

Transpose columns to rows in bash

3 Answers3