Transpose field names from column headers to values in one column


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transpose field names from column headers to values in one column
# 1  
Old 07-22-2011
Transpose field names from column headers to values in one column

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:

Code:
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:

Code:
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  
Old 07-22-2011
Code:
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[i], t )
        print t[1], t[2], t[3], h[j], t[j]  	
      }
  }
NR == 1 { 
  n = split( $0, h ) 
  next 
  }
{ 
  d[NR] = $0 
  }' OFS=\; infile

This User Gave Thanks to radoulov For This Post:
# 3  
Old 07-22-2011
Another approach:
Code:
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

This User Gave Thanks to Klashxx For This Post:
# 4  
Old 07-22-2011
Yep,
in my output the final semicolon was missing:

Code:
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[i], 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  
Old 07-22-2011
Hi Radoulov,

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

Code:
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:

Code:
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:

Code:
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  
Old 07-22-2011
Could you please double check if the input file you posted corresponds to the input file you're using?

Given the following input:

Code:
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:


Code:
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  
Old 07-22-2011
Yes Radoulov, something was wrong with my input file.

Your script is working fine!! Thanks a lot!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Transpose from row to column using timestamp in first column

Gents, Transpose from row to column, taking in consideration the first column, which contends the date. Input file 72918,111000009,111000009,111000009,111000009,111000009,111000009,111000009,111000009,111000009 72918,2356,2357,2358,2359,2360,2361,2362,2363,2364 72918,0,0,0,0,0,0,0,0,0... (12 Replies)
Discussion started by: jiam912
12 Replies

2. Shell Programming and Scripting

Sum column values matching other field

this is part of a KT i am going thru. i am writing a script in bash shell, linux where i have 2 columns where 1st signifies the nth hour like 00, 01, 02...23 and 2nd the file size. sample data attached. Desired output is 3 columns which will give the nth hour, number of entries in nth hour and... (3 Replies)
Discussion started by: alpha_1
3 Replies

3. Shell Programming and Scripting

Transpose from 2nd column till the last column

Hi I have 5 columns like this a b c d e f g h i j k l m n o From 2nd column till the 5th column of every record, I would like to transpose them as rows, so my output file contains only one row a b c d e f g h i j (9 Replies)
Discussion started by: jacobs.smith
9 Replies

4. Shell Programming and Scripting

Merge column headers and transpose

Hello Everyone! I am new on this forum and this is my first post. I wish to apologize for my, not canonical, English. I would like to solve this problem but I have no clue of how do it!I will be grateful if someone could help me! I have a table like this: gene TF1 TF2 TF3 TF4 gene1 1 2 3 4... (5 Replies)
Discussion started by: giuliangiuseppe
5 Replies

5. Shell Programming and Scripting

Sum up the column values group by using some field

12-11-2012,PNL,158406 12-11-2012,RISK,4564 12-11-2012,VAR_1D,310101 12-11-2012,VAR_10D,310101 12-11-2012,CB,866 12-11-2012,STR_VAR_1D,298494 12-11-2012,STR_VAR_10D,309623 09-11-2012,PNL,1024106 09-11-2012,RISK,4565 09-11-2012,VAR_1D,317211 09-11-2012,VAR_10D,317211 09-11-2012,CB,985... (7 Replies)
Discussion started by: manas_ranjan
7 Replies

6. Shell Programming and Scripting

Transpose timestamp based on column values and calculate time difference

Hello Expert, I need to transpose Date-Timestamp based on same column values and calculate time difference. The input file would be as below and required output is mentioned in the bottom INPUT File ======== 08/23/2012 12:36:09 JOB_5340 08/23/2012 12:36:14 JOB_5340 08/23/2012... (2 Replies)
Discussion started by: asnandhakumar
2 Replies

7. Shell Programming and Scripting

awk transpose row into 2 field column

Need to transpose every 2 fields of a row into a single 2 field column. input 4 135 114 76 217 30 346 110 5 185 115 45 218 85 347 125 6 85 116 130 220 65 352 95 11 30 117 55 221 42 355 75 16 72 118 55 224 37 357 430 17 30 119 55 225 40 358 62 21 52 120 65 232 480 360 180 ....... (8 Replies)
Discussion started by: sdf
8 Replies

8. Shell Programming and Scripting

eAdd two fields in a column if their previous field values are same

Hi All, I have two files file1: abc,def,ghi,5,jkl,mno pqr,stu,ghi,10,vwx,xyz cba,ust,ihg,4,cdu,oqw file2: ravi,def,kishore ramu,ust,krishna joseph,stu,mike I need two output file as follows If field3 in file1 is same as field3 in the next line then the field4 should add... (1 Reply)
Discussion started by: yerruhari
1 Replies

9. Shell Programming and Scripting

Print column names along with values from SQL

Hi, Can anyone tell me how to print the column name anong with the value from the table in shell script e.g #!/bin/ksh var=`sqlplus scott/tiger << -e set heading off feedback off select * from emp; quit; e` echo $var My output should be; ... (5 Replies)
Discussion started by: thana
5 Replies

10. Shell Programming and Scripting

Need help with switching field/column values

Hi all, I need some help on switching field/column values. For example I have a file name data.txt which contains: a b a b a b and I want to switch a and b and save it to the same file. the file data.txt then will have: b a b a b a The problem is, well, I know how to... (7 Replies)
Discussion started by: sonyd8
7 Replies
Login or Register to Ask a Question

Featured Tech Videos