Transpose field names from column headers to values in one column

Shell Programming and Scripting
1

Hi All,

I'm looking for a script which can transpose field names from column headers to values in one column.
for example, the input is:

IDa;IDb;IDc;PARAM1;PARAM2;PARAM3;
a;b;c;p1val;p2val;p3val;
d;e;f;p4val;p5val;p6val;
g;h;i;p7val;p8val;p9val;

into the output like this:

IDa;IDb;IDc;PARAM_HEADER;PARAM_VALUE;
a;b;c;PARAM1;p1val;
d;e;f;PARAM1;p4val;
g;h;i;PARAM1;p7val;
a;b;c;PARAM2;p2val;
d;e;f;PARAM2;p5val;
g;h;i;PARAM2;p8val;
a;b;c;PARAM3;p3val;
d;e;f;PARAM3;p6val;
g;h;i;PARAM3;p9val;

Thanks in advance for your help!!

2 
awk -F\; 'END {
  for ( m = 0; ++m <= 3; )
    printf "%s", h[m] FS
    print "PARAM_HEADER", "PARAM_VALUE"
  for ( j = 3; ++j < n; )
    for ( i = 1; ++i <= NR; ) {
      split( d, t )
        print t[1], t[2], t[3], h[j], t[j]  	
      }
  }
NR == 1 { 
  n = split( $0, h ) 
  next 
  }
{ 
  d[NR] = $0 
  }' OFS=\; infile
1 Like
3

Another approach:

awk '{++i;for(j=1;j<=(NF-1);j++){a[i,j]=$j}}
END{
for (c=1;c<j;c++){
   if( match(a[1,c],/PARAM/ ) ) {
      for (m=2;m<=i;m++){
         for (z=1;z<=3;z++){
            printf("%s"FS,a[m,z])
            }
         print a[1,c],a[m,c]FS}
         }
      }
}' FS=';' OFS=';' file
1 Like
4

Yep,
in my output the final semicolon was missing:

awk -F\; 'END {
  for ( m = 0; ++m <= 3; )
    printf "%s", h[m] FS
    print "PARAM_HEADER", "PARAM_VALUE", x
  for ( j = 3; ++j < n; )
    for ( i = 1; ++i <= NR; ) {
      split( d, t )
        print t[1], t[2], t[3], h[j], t[j], x  	
      }
  }
NR == 1 { 
  n = split( $0, h ) 
  next 
  }
{ 
  d[NR] = $0 
  }' OFS=\; infile
5

Hi Radoulov,

thanks for your reply, but the output is not complete with your last code, I get :

PARAM_VALUE;PARAM_HEADER;
p1val;;
p4val;;
p7val;;
p2val;;
p5val;;
p8val;;
p3val;;
p6val;;
p9val;;

I would like to get also the ID's and the parameters names like this:

a;b;c;PARAM1;p1val; d;e;f;PARAM1;p4val; g;h;i;PARAM1;p7val; a;b;c;PARAM2;p2val; d;e;f;PARAM2;p5val; g;h;i;PARAM2;p8val; a;b;c;PARAM3;p3val; d;e;f;PARAM3;p6val; g;h;i;PARAM3;p9val;

do you know where is the mistake?

---------- Post updated at 07:17 AM ---------- Previous update was at 07:14 AM ----------

sorry, the word wrap are missing in my output code. It should be:

a;b;c;PARAM1;p1val;
d;e;f;PARAM1;p4val;
g;h;i;PARAM1;p7val;
a;b;c;PARAM2;p2val;
d;e;f;PARAM2;p5val;
g;h;i;PARAM2;p8val;
a;b;c;PARAM3;p3val;
d;e;f;PARAM3;p6val;
g;h;i;PARAM3;p9val;
6

Could you please double check if the input file you posted corresponds to the input file you're using?

Given the following input:

IDa;IDb;IDc;PARAM1;PARAM2;PARAM3;
a;b;c;p1val;p2val;p3val;
d;e;f;p4val;p5val;p6val;
g;h;i;p7val;p8val;p9val;

I receive the following output:

IDa;IDb;IDc;PARAM_HEADER;PARAM_VALUE;
a;b;c;PARAM1;p1val;
d;e;f;PARAM1;p4val;
g;h;i;PARAM1;p7val;
a;b;c;PARAM2;p2val;
d;e;f;PARAM2;p5val;
g;h;i;PARAM2;p8val;
a;b;c;PARAM3;p3val;
d;e;f;PARAM3;p6val;
g;h;i;PARAM3;p9val;
7

Yes Radoulov, something was wrong with my input file.

Your script is working fine!! Thanks a lot!