Text Processing – Convert Rows to Columns for a Block

awkcsplitsolaristext processing

I have a file containing lists on Solaris:

List A
hi
hello
hw r u

List B
Hi
Yes

List C
Hello

I need to transpose the lists as shown below:

List A    List B    List C
hi        Hi        Hello
hello     Yes
hw r u

How can I do this on Solaris?

Best Answer

GNU awk approach:

awk 'BEGIN{ max=0 }
     /^List/{ if(k && k>max) { max=k; idx=c } ++c; k=0 }
     NF{ a[c][++k]=$0 }
     END{ 
         for(i=1;i<=max;i++) 
             for(j=1;j<=c;j++) printf "%s%s",a[j][i],(j==c)?ORS:"\t" 
     }' file | column -ts$'\t'

The output:

List A  List B  List C
hi      Hi      Hello
hello   Yes
hw r u

Related Solutions

Lum – Transposing rows and columns

Have a look at GNU datamash which can be used like datamash transpose. A future version will also support cross tabulation (pivot tables)

UNIX paste columns and insert zeros for all missing values

If column-order is important, i.e. numbers from the same file should be kept in the same column, you need to add padding while reading the different files. Here is one way that works with GNU awk:

merge.awk

# Set k to be a shorthand for the key
{ k = $1 SUBSEP $2 }

# First element with this key, add zeros to align it with other rows
!(k in h) {
  for(i=1; i<=ARGIND-1; i++)
    h[k] = h[k] OFS 0 
}

# Remember the data element
{ h[k] = h[k] OFS $3 }

# Before moving to the next file, ensure that all rows are aligned
ENDFILE {
  for(k in h) {
    if(split(h[k], a) < ARGIND)
      h[k] = h[k] OFS 0
  }
}

# Print out the collected data
END {
  for(k in h) {
    split(k, a, SUBSEP)
    print a[1], a[2], h[k]
  }
}

Here are some test files: f1, f2, f3 and f4:

$ tail -n+1 f[1-4]
==> f1 <==
xyz desc1 21
uvw desc2 22
pqr desc3 23

==> f2 <==
xyz desc1 56
uvw desc2 57

==> f3 <==
xyz desc1 87
uvw desc2 88

==> f4 <==
xyz desc1 11
uvw desc2 12
pqr desc3 13
stw desc1 14
arg desc2 15

Test 1

awk -f merge.awk f[1-4] | column -t

Output:

pqr  desc3  23  0   0   13
uvw  desc2  22  57  88  12
stw  desc1  0   0   0   14
arg  desc2  0   0   0   15
xyz  desc1  21  56  87  11

Test 2

awk -f merge.awk f2 f3 f4 f1 | column -t