Converting data in rows to columns

In pure bash (requires bash 4.0 or newer):

#!/bin/bash

declare -A var

printf '\t\t\ta\tx\ty\tc\n'
while IFS=$'\t' read -ra fld; do
    var[a]=""  var[x]=""  var[y]=""  var[c]=""
    for ((i = 3; i < ${#fld[@]}; i += 2)); do
        var["${fld[i]}"]=${fld[i + 1]}
    done
    printf '%s\t' "${fld[@]:0:3}"
    printf '%s\t%s\t%s\t%s\n' "${var[a]}" "${var[x]}" "${var[y]}" "${var[c]}"
done < file

Assumptions:

• the four variable names (a/c/x/y in the sample input) are not known beforehand
• a variable always has a non-blank value following it
• the number of variable/value pairs (on a single input line) is not known beforehand
• OP is ok with printing the variable columns in alphabetical order (OP's desired output does not designate if/how the four variable columns are to be sorted)
• ordering of rows is to remain the same (input order == output order)
• host has enough memory to hold entire input file in memory (via awk arrays); this allows for a single pass of the input file; if memory were to be an issue (ie, input file cannot fit in memory) then a different coding/design would be needed (not addressed in this answer)

Another awk idea ... requiring GNU awk for the use of multi-dimensional arrays as well as the PROCINFO["sorted_in"] construct:

awk '
BEGIN { FS=OFS="\t" }                             # input/output field delimiters = <tab>

      { first3[FNR]=$1 OFS $2 OFS $3              # store first 3 fields

        for (i=4;i<=NF;i=i+2) {                   # loop through rest of fields, 2 at a time
            vars[$i]                              # keep track of variable names
            values[FNR][$i]=$(i+1)                # store the value for this line/variable combo
        }
      }

END   { PROCINFO["sorted_in"]="@ind_str_asc"      # sort vars[] indexes in ascending order

        printf "%s%s", OFS, OFS                   # start printing header line ...
        for (v in vars)                           # loop through variable names ...
            printf "%s%s", OFS, v                 # printing to header line
        printf "\n"                               # terminate header line

        for (i=1;i<=FNR;i++) {                    # loop through our set of lines ...
            printf "%s",first3[i]                 # print the 1st 3 fields and then ...
            for (v in vars)                       # loop through list of all variables ...
                printf "%s%s",OFS,values[i][v]    # printing the associated value; non-existent values default to the empty string ""
            printf "\n"                           # terminate the current line of output
        }
      }
' inputfile

NOTE: This design allows for a variable number of variables to be processed.

For demonstration purposes we'll use the following tab-delimited input files:

$ cat input4                                         # OP's sample input file w/ 4 variables
13435   830169  830264  a       95      y       16
09433   835620  835672  x       46
30945   838405  838620  a       21      c       19
94853   850475  850660  y       15
04958   865700  865978  c       16      a       98

$ cat input6                                         # 2 additional variables added to OP's original input file
13435   830169  830264  a       95      y       16
09433   835620  835672  x       46      t       375
30945   838405  838620  a       21      c       19
94853   850475  850660  y       15      j       127     t       453
04958   865700  865978  c       16      a       98

Running these through the awk script generates:

############# input4
                        a       c       x       y
13435   830169  830264  95                      16
09433   835620  835672                  46
30945   838405  838620  21      19
94853   850475  850660                          15
04958   865700  865978  98      16

############# input6
                        a       c       j       t       x       y
13435   830169  830264  95                                      16
09433   835620  835672                          375     46
30945   838405  838620  21      19
94853   850475  850660                  127     453             15
04958   865700  865978  98      16

If you know you have 4 variables a, x, y, c, and the file is formatted as a tab separated file and you want the exact format as output shown, you can simply use a "Brute Force" method where you check the contents of fields 4 and 6 for the variable name and output the value of field 5 or 7 formatted as shown using printf.

For example, knowing the variable names you can simply output the header row before then processing each records as follows:

awk -F"\t" '
  FNR==1 { 
    print "\t\t\t  a    x    y    c"
  }
  {
    printf "%-8s%8s%8s  ", $1, $2, $3
    
    if ($4=="a")
      printf "%-5s", $5
    else if ($6=="a")
      printf "%-5s", $7
    else
      printf "%-5s", " "
    
    if ($4=="x")
      printf "%-5s", $5
    else if ($6=="x")
      printf "%-5s", $7
    else
      printf "%-5s", " "
    
    if ($4=="y")
      printf "%-5s", $5
    else if ($6=="y")
      printf "%-5s", $7
    else
      printf "%-5s", " "
    
    if ($4=="c")
      printf "%-5s\n", $5
    else if ($6=="c")
      printf "%-5s\n", $7
    else
      print ""
  }
' tabfile

Example Use/Output

With your input in tabfile you would have:

$ awk -F"\t" '
>   FNR==1 {
>     print "\t\t\t  a    x    y    c"
>   }
>   {
>     printf "%-8s%8s%8s  ", $1, $2, $3
>
>     if ($4=="a")
>       printf "%-5s", $5
>     else if ($6=="a")
>       printf "%-5s", $7
>     else
>       printf "%-5s", " "
>
>     if ($4=="x")
>       printf "%-5s", $5
>     else if ($6=="x")
>       printf "%-5s", $7
>     else
>       printf "%-5s", " "
>
>     if ($4=="y")
>       printf "%-5s", $5
>     else if ($6=="y")
>       printf "%-5s", $7
>     else
>       printf "%-5s", " "
>
>     if ($4=="c")
>       printf "%-5s\n", $5
>     else if ($6=="c")
>       printf "%-5s\n", $7
>     else
>       print ""
>   }
> ' tabfile
                          a    x    y    c
13435     830169  830264  95        16
09433     835620  835672       46
30945     838405  838620  21             19
94853     850475  850660            15
04958     865700  865978  98             16

Which provides the desired output. This one-pass approach will also be quite efficient for 450,000 lines of input. Since this is a bit long for a command-line script, you can simply put it in an awk script and call it with the filename. Let me know if you have questions.

As A Script File

Using as a script file, simply put the contents in a file and make it executable, e.g.

#!/usr/bin/awk -f

BEGIN { FS="\t" }
FNR==1 { 
  print "\t\t\t  a    x    y    c"
}
{
  printf "%-8s%8s%8s  ", $1, $2, $3
  
  if ($4=="a")
    printf "%-5s", $5
  else if ($6=="a")
    printf "%-5s", $7
  else
    printf "%-5s", " "
  
  if ($4=="x")
    printf "%-5s", $5
  else if ($6=="x")
    printf "%-5s", $7
  else
    printf "%-5s", " "
  
  if ($4=="y")
    printf "%-5s", $5
  else if ($6=="y")
    printf "%-5s", $7
  else
    printf "%-5s", " "
  
  if ($4=="c")
    printf "%-5s\n", $5
  else if ($6=="c")
    printf "%-5s\n", $7
  else
    print ""
}

Saved as awkscript you would chmod +x awkscript and then run:

$ ./awkscript tabfile
                          a    x    y    c
13435     830169  830264  95        16
09433     835620  835672       46
30945     838405  838620  21             19
94853     850475  850660            15
04958     865700  865978  98             16

input="\
13435   830169  830264  a   95  y   16
09433   835620  835672  x   46
30945   838405  838620  a   21  c   19
94853   850475  850660  y   15
04958   865700  865978  c   16  a   98
"

with awk:

printf '\t\t\ta\tx\ty\tc\n'
echo -n "$input" |
awk -v vars='a x y c' '
  BEGIN {NV = split(vars,V)}
  {
     s = $1 "\t" $2 "\t" $3;
     delete a;
     for(i = 4; i < NF; i = i+2) a[$i] = $(i+1);
     for(i = 1; i <= NV; i++) s = s "\t" a[V[i]];
     print s
  }
'

with ruby:

printf '\t\t\ta\tx\ty\tc\n'
echo -n "$input" |
vars='a x y c' ruby -ane '
    BEGIN{v = ENV["vars"].split};
    h = Hash[*$F[3..-1]];
    puts $F[0..2].concat(v.map{|v| h[v]}).join("\t")
'

outputs:

            a   x   y   c
13435   830169  830264  95      16  
09433   835620  835672      46      
30945   838405  838620  21          19
94853   850475  850660          15  
04958   865700  865978  98          16

In perl:

$ perl -lane '
    BEGIN { print join("\t", "", "", "", "a", "x", "y", "c"); }
    my %vars = @F[3..$#F];
    print join("\t", @F[0..2], @vars{qw/a x y c/});
  ' input.tsv
                        a       x       y       c
13435   830169  830264  95              16
09433   835620  835672          46
30945   838405  838620  21                      19
94853   850475  850660                  15
04958   865700  865978  98                      16

The fourth column and all following are taken as key/value pairs for a hash table, and then the variable values that are present in it are extracted in the right order, along with the first three columns. Makes heavy use of slices.

Here is an awk to do this:

awk '
BEGIN{fmt="%s\t%s\t%s\t%s\t%s\t%s\t%s\n"}
NR==FNR{if ($4 && !($4 in seen)) {
            seen[$4]=++col; cols[col]=$4
        }
        if ($6 && !($6 in seen)) {
            seen[$6]=++col; cols[col]=$6
        }
    next
}
FNR==1{printf fmt, "\t","\t","\t",cols[1],cols[2],cols[3],cols[4]}
{   split("",fields)
    fields[seen[$4]]=$5; fields[seen[$6]]=$7
    printf fmt, $1,$2,$3,fields[1],fields[2],fields[3],fields[4]
}
' file file 

This will find the variables names and print them in the order first seen.

Prints:

                        a   y   x   c
13435   830169  830264  95  16      
09433   835620  835672          46  
30945   838405  838620  21          19
94853   850475  850660      15      
04958   865700  865978  98          16

If you know your variables in advance and want to state the order of the columns, you can do:

awk '
BEGIN{
    fmt="%s\t%s\t%s\t%s\t%s\t%s\t%s\n"
    seen["a"]=1;seen["x"]=2;seen["y"]=3;seen["c"]=4
}

FNR==1{printf fmt, "\t","\t","\t","a","x","y","c"}
{   split("",fields)
    fields[seen[$4]]=$5; fields[seen[$6]]=$7
    printf fmt, $1,$2,$3,fields[1],fields[2],fields[3],fields[4]
}
' file

Prints:

                        a   x   y   c
13435   830169  830264  95      16  
09433   835620  835672      46      
30945   838405  838620  21          19
94853   850475  850660          15  
04958   865700  865978  98          16