How to repeat a row for each value of its corresponding column

Question

I want to repeat a row for all its corresponding values any help

my input file is like this

    pos     COL1  COL2  COL3
    18691441    C   A   G
    18691572    G   C   G
    18691620    A   T   G
    18691716    C   G   C

i want output like this

pos COL1    
18691441    COL1 C  
18691441    COL2 A  
18691441    COL3 G  
18691572    COL1 G  
18691572    COL2 C  
18691572    COL3 G  
18691620    COL1 A  
18691620    COL2 T  
18691620    COL3 G  
18691716    COL1 C  
18691716    COL2 G  
18691716    COL3 C

i am trying to repeat a row but it simply makes them duplicate , i am using

while read line; do for i in {1..3}; do echo "$line"; done; done < real2.txt

and gives output:

pos       COL1 COL2 COL3
18691441    C   A   G
18691441    C   A   G
18691441    C   A   G
18691572    G   C   G
18691572    G   C   G
18691572    G   C   G
18691620    A   T   G
18691620    A   T   G
18691620    A   T   G
18691716    C   G   C
18691716    C   G   C
18691716    C   G   C

then i extracted pos from input 1.txt file and make 1_pos.txt and write something like this:

     pos
    18691441
    18691572
    18691620
    18691716
    for i in `cat 1_post.txt`;
    do
   x=$(grep -i "^$i" 1.txt | awk 'FNR == 1 {print $1"\t""COL1""\t"$2}' ) ;
   y=$(grep -i "^$i" 1.txt | awk 'FNR == 1 {print $1"\t""COL2""\t"$3}' ) ;
   z=$(grep -i "^$i" 1.txt | awk 'FNR == 1 {print $1"\t""COL3""\t"$4}' ) ;

    echo -e "$x""\n""$y""\n""$z";
    done  

this gives me output, with column information, but what if i have 405 columns instead of three i do not want to repeat lines for each column 405 times, i try to put this in loop but it does not work :

18691441    COL1    C
18691441    COL2    A
18691441    COL3    G
18691572    COL1    G
18691572    COL2    C
18691572    COL3    G
18691620    COL1    A
18691620    COL2    T
18691620    COL3    G
18691716    COL1    C
18691716    COL2    G
18691716    COL3    C

Answer 1

How about:

while read line col1 col2 col3; 
do 
    if [[ "$line" = "pos" ]]; then
        echo "pos COL"
        continue    
    fi
    echo "$line COL1 $col1"  
    echo "$line COL2 $col2"  
    echo "$line COL3 $col3"  
done < real2.txt

output:

pos COL
18691441 COL1 C
18691441 COL2 A
18691441 COL3 G
18691572 COL1 G
18691572 COL2 C
18691572 COL3 G
18691620 COL1 A
18691620 COL2 T
18691620 COL3 G
18691716 COL1 C
18691716 COL2 G
18691716 COL3 C

Answer 2

awk approach:

awk 'BEGIN{OFS="\t";print "pos" OFS "COL1"}{if(NR==1){for(f=2;f<=NF;f++) c[f]=$f;}
     else{for(i=2;i<=NF;i++) print $1,c[i],$i}}' real2.txt

The output:

pos     COL1
18691441        COL1    C
18691441        COL2    A
18691441        COL3    G
18691572        COL1    G
18691572        COL2    C
18691572        COL3    G
18691620        COL1    A
18691620        COL2    T
18691620        COL3    G
18691716        COL1    C
18691716        COL2    G
18691716        COL3    C

---

OFS="\t" - output field separator

print "pos" OFS "COL1" - prints header line

if(NR==1){for(f=2;f<=NF;f++) c[f]=$f; - collecting column names from the first/header line

for(i=2;i<=NF;i++) print $1, c[i], $i - printing each column (COL...) value "rowwise" regarding to respective pos column value and its corresponding column name.

Answer 3

Don't use shell loops to process text.

Here, awk is the right tool for the task. But you only need to call it once:

awk -v OFS='\t' '
  NR == 1 {print $1, "name", "value"; split($0, header); next}
  {for (i = 2; i < NF; i++) print $1, header[i], $i}' < your-file

_{^{(a variation on Roman's answer)}}

Answer 4

while IFS= read -r l; do
   read -r -a A <<<"$l"
   case $l in
      'pos'[\ \   ]* )
         echo "${A[@]:0:2}"
         C=("${A[@]:1}")
         ;;

      * )
         p=0 x=${A[0]}
         for e in "${A[@]:1}"; do
            echo "$x ${C[$p]} $e"
            ((p++))
         done
         ;;
   esac
done < yourfile


sed -E '
   /\n/bloop

   y/\t/ /;s/  +/ /g;s/^ +//;s/ +$//

   1{
      h
         s/ /\n/2
      x
         s/ /\n/;s/.*\n//
      x
         s/\n.*//
      b
   }

   G;s/\n/ &/

   :loop
      #  1     2     3   4   5
      s/^(\S+ )(\S+) (.*)(\n)(\S+) ?/\1\5 \2\4\1\3\4/
      /\n$/{
         /\n.*\n/!d
      }
       P
      /\n.*\n/D
   tloop

' yourfile

---

Results

pos COL1
18691441 COL1 C
18691441 COL2 A
18691441 COL3 G
18691572 COL1 G
18691572 COL2 C
18691572 COL3 G
18691620 COL1 A
18691620 COL2 T
18691620 COL3 G
18691716 COL1 C
18691716 COL2 G
18691716 COL3 C

---

Explanation

• First off, we convert any residual TABs to spaces, then squeeze multiple spaces, and finally trim any leading/trailing spaces.
• We do a special handling of the first line:
  • a) make a copy of the line.
  • b) mark the end of the 2nd column for later.
  • c) interchange this marked line with the copy stored in hold space.
  • d) strip the first column, then revert back & in that display cols1,2.
• For all the other lines, (2 to eof) we append the column names to the line.
• Then setup a do-while loop in which in every iteration we rearrange the fields in the manner shown so that the column name alongwith it's value is printed out. We stop when we see a line having \n at it's end AND it's the only \n char remaining in the line. Otherwise, we just chop off the the leading portion and branch back to the loop beginning.