Transpose Data form Different form

Shell Programming and Scripting
1

HI Guys,

I have data in File A.txt

RL03       RL03_A_1  RL03_B_1  RL03_C_1  
RL03       -119.8   -119.5     -119.5         
RL07       RL07_A_1  RL07_B_1  RL07_C_1  
RL07       -119.3    -119.5    -119.5         
RL15       RL15_A_1   RL15_C_1  
RL15       -120.5    -119.4         
RL16       RL16_A_1  RL16_B_1    
RL16       -119.4    -119.1

I want output Like Below :

RL03	RL03_A_1	-119.8
RL03	RL03_B_1	-119.5
RL03    RL03_C_1        -119.5 
RL07	RL07_A_1        -119.3
RL07	RL07_B_1	-119.5
RL07    RL07_C_1 	-119.5	
RL15	RL15_A_1	-120.5 
RL15	RL15_C_1	-119.4 
RL16 	RL16_A_1	-119.4
RL16 	RL16_B_1	-119.1

I know it's vary complex ....Thanks in advance :slight_smile:

2

The below code should work with data like in your example:

$ cat A.txt
RL03    RL03_A_1        RL03_B_1        RL03_C_1
RL03    -119.8  -119.5  -119.5
RL07    RL07_A_1        RL07_B_1        RL07_C_1
RL07    -119.3  -119.5  -119.5
RL15    RL15_A_1        RL15_C_1
RL15    -120.5  -119.4
RL16    RL16_A_1        RL16_B_1
RL16    -119.4  -119.1

$ perl -ane 'BEGIN{$lc=0;} if ($lc==0) {@r=@F;$lc++;} else {for $i (1 .. $#r) {print "$F[0]\t$r[$i]\t$F[$i]\n";}$lc=0;@r=();}' A.txt
RL03    RL03_A_1        -119.8
RL03    RL03_B_1        -119.5
RL03    RL03_C_1        -119.5
RL07    RL07_A_1        -119.3
RL07    RL07_B_1        -119.5
RL07    RL07_C_1        -119.5
RL15    RL15_A_1        -120.5
RL15    RL15_C_1        -119.4
RL16    RL16_A_1        -119.4
RL16    RL16_B_1        -119.1
1 Like
3

also:

awk '{
   if ($1 in a) ao[ac++]=$1;
   a[$1]=$1;
   ++l[$1];
   cnt[$1]=NF - 1;
   for (i=2; i<=NF; i++) {
      arr[$1 (l[$1]) (i-1)]=$i;
   }
}
END {
  for (i=0; i<ac; i++) {
    for (j=1; j<=cnt[ao]; j++) {
      printf("%s\t%s\t%s\n", ao, arr[ao 1 j], arr[ao 2 j]);
    }
  }
}' infile
1 Like