1

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
2

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
  • thanks @jacek , its pretty cool, can we have same code in loop for 405 columns rather than this 3, thanks in advance – a.k May 10 '17 at 12:06
  • 1
    It is possible but in that case Stéphane Chazelas answer is much better solution. – Jacek Szałęga May 10 '17 at 13:45
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.

  • thanks @Roman , but i a trying to add columns names too , any help – a.k May 10 '17 at 11:21
  • @a.k, ok, see my update – RomanPerekhrest May 10 '17 at 12:00
  • thanks a lot @Roman, can its works same for 405 columns rather than this 3 , in actual file i have 405 columns , i am trying to put loop over it but it does not work , can i have looping for 405 columns , thanks in advance. – a.k May 10 '17 at 12:15
  • @a.k, can you share at least few lines from the actual file with those 405 columns via free file hosting service? – RomanPerekhrest May 10 '17 at 13:18
2

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)

  • thanks @Stephane , it work well for 2 repetition , as output: pos name value 18691441 COL1 C 18691441 COL2 A 18691572 COL1 G 18691572 COL2 C 18691620 COL1 A 18691620 COL2 T 18691716 COL1 C 18691716 COL2 G what if it needs for 405 columns instead of 3 – a.k May 10 '17 at 12:29
  • @a.k, then it should work as long as the input file is in the same format (including the header line with its 405 column names) – Stéphane Chazelas May 10 '17 at 12:37
  • thanks a lot @Stephane , it works pretty well. can you just explain the steps. – a.k May 11 '17 at 6:01
1
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.
  • thanks @Rakesh Sharma , but i want to add columns names too. – a.k May 10 '17 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.