Transpose table with awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transpose table with awk
# 1  
Old 12-11-2017
Transpose table with awk

I am trying to format the table below to the output
input:
Code:
cand  week  sub1   sub2   sub3   sub4 
joe   1     94.19  70.99  43.93  60.14
joe   2     94.07  51.02  41.07  38.92
joe   3     26.24  30.95  44.56  67.67
joe   4     72.36  60.92  40.78  83.25
joe   5     51     70.01  44.66  82.22
jane  1     10.00  51.75  24.72  79.97
jane  2     11.01  94.73  24.28  42.35
jane  3     92.70  55.80  23.11  10.83
jane  4     88.88  22.74  22.09  71.56
jane  5     100.0  56.7   88.79  98.01

output
Code:
           1      2      3      4      5
joe  sub1  94.19  94.07  26.24  72.36  51
joe  sub2  70.99  51.02  30.95  60.92  70.01
joe  sub3  43.93  41.07  44.56  40.78  44.66
joe  sub4  60.14  38.92  67.67  83.25  82.22
jane sub1  10.00  11.01  92.70  88.88  100.0
jane sub2  51.75  94.73  55.80  22.74  56.7
jane sub3  24.72  24.28  23.11  22.09  88.79
jane sub4  79.97  42.35  10.83  71.56  98.01

tried this, but not working:

Code:
awk '
{ 
    for (p=3; p<=NF; p++)  {
        a[$1][$2][p] = $p
    }
}
END {    
    for (i in a){ 
	   for (j in a[i]){
	   	  for (k in a[i][j]) {
            print a[i][j][k]
			}
    }
	}
}'


Last edited by aydj; 12-11-2017 at 12:17 PM..
# 2  
Old 12-11-2017
Looks like your awk version (gawk?) allows for real multidimensional arrays - mine doesn't. Still, you might use this as a starting point:

Code:
awk '
function PRT()  {for (i=3; i<=MXC; i++) {printf "%s\t%s", LAST, SUB[i]
                                         for (j=1; j<=LNR; j++) printf "\t%s", DATA[i,j]
                                         printf RS
                                        }
                }

NR == 1         {for (i=3; i<=NF; i++) SUB[i] = $i
                 MXC = NF
                 next
                }
LAST &&
$1 != LAST      {if (!HD)       {printf "\t"
                                 for (i=1; i<=LNR; i++) printf "\t%s", i
                                 printf RS
                                }
                 HD = 1
                 PRT()
                }
END             {PRT()
                }
                {for (i=3; i<=NF; i++) DATA [i,$2] = $i
                 LAST = $1
                 LNR  = $2
                }
' file

Please be aware that Jane's sub3's week 5 value (88 79) is interpreted as TWO columns and thus disturbes the corresponding output .
# 3  
Old 12-11-2017
Edited, my error
# 4  
Old 12-13-2017
Quote:
Originally Posted by RudiC
Looks like your awk version (gawk?) allows for real multidimensional arrays - mine doesn't. Still, you might use this as a starting point:

Code:
awk '
function PRT()  {for (i=3; i<=MXC; i++) {printf "%s\t%s", LAST, SUB[i]
                                         for (j=1; j<=LNR; j++) printf "\t%s", DATA[i,j]
                                         printf RS
                                        }
                }

NR == 1         {for (i=3; i<=NF; i++) SUB[i] = $i
                 MXC = NF
                 next
                }
LAST &&
$1 != LAST      {if (!HD)       {printf "\t"
                                 for (i=1; i<=LNR; i++) printf "\t%s", i
                                 printf RS
                                }
                 HD = 1
                 PRT()
                }
END             {PRT()
                }
                {for (i=3; i<=NF; i++) DATA [i,$2] = $i
                 LAST = $1
                 LNR  = $2
                }
' file

Please be aware that Jane's sub3's week 5 value (88 79) is interpreted as TWO columns and thus disturbes the corresponding output .
Can anyone suggest GNU Awk 4.2.0 implementation?
# 5  
Old 12-13-2017
In what way does the code RudiC suggested not work with gawk version 4.2.0?

What diagnostics does it produce when you run it?

Does it produce output, but the wrong output? If so, show us the output it produced and the output you were hoping to get!

Or, are you saying it works perfectly, but you want us to spend time writing a non-portable version that will work only on the version of gawk that you're running?
# 6  
Old 12-14-2017
Quote:
Originally Posted by Don Cragun
In what way does the code RudiC suggested not work with gawk version 4.2.0?

What diagnostics does it produce when you run it?

Does it produce output, but the wrong output? If so, show us the output it produced and the output you were hoping to get!

Or, are you saying it works perfectly, but you want us to spend time writing a non-portable version that will work only on the version of gawk that you're running?
It does not work when the week is changed to dates, like below:
Code:
cand  date         sub1   sub2   sub3   sub4 
joe   10122017     94.19  70.99  43.93  60.14
joe   11122017     94.07  51.02  41.07  38.92
joe   12122017     26.24  30.95  44.56  67.67
joe   13122017     72.36  60.92  40.78  83.25
joe   14122017     51     70.01  44.66  82.22
jane  10122017     10.00  51.75  24.72  79.97
jane  11122017     11.01  94.73  24.28  42.35
jane  12122017     92.70  55.80  23.11  10.83
jane  13122017     88.88  22.74  22.09  71.56
jane  14122017     100.0  56.7   88.79  98.01

# 7  
Old 12-14-2017
And how do you hope GNU Awk 4.2.0 will handle that? And, what should the output look like?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Transpose using awk

Hi Friends, Very urgent requirement please do needful ASAP.. Input: |1||1|1||1|3||3|2||2|4||4|2||2|3||3|NA||0|5||5|NA||0|4||4|3||3 output: |1||1 |1||1 |3||3 |2||2 |4||4 |2||2 |3||3 |NA||0 |5||5 (4 Replies)
Discussion started by: bharat1211
4 Replies

2. Shell Programming and Scripting

Transpose data as rows using awk

Hi I have below requirement, need help One file contains the meta data information and other file would have the data, match the column from file1 and with file2 and extract corresponding column value and display in another file File1: CUSTTYPECD COSTCENTER FNAME LNAME SERVICELVL ... (1 Reply)
Discussion started by: ravlapo
1 Replies

3. Shell Programming and Scripting

awk to transpose every 7 rows into columns

input: a1 a2 a3 a4 a5 a6 a7 b1 b2 b3 .. b7 .. z1 .. z7 (12 Replies)
Discussion started by: ux4me
12 Replies

4. Shell Programming and Scripting

awk to convert table-by-row to matrix table

Hello, I need some help to reformat this table-by-row to matrix? infile: site1 A:o,p,q,r,s,t site1 C:y,u site1 T:v,w site1 -:x,z site2 A:p,r,t,v,w,z site2 C:u,y site2 G:q,s site2 -:o,x site3 A:o,q,s,t,u,z site3 C:y site3 T:v,w,x site3 -:p,routfile: SITE o p q r s t v u w x y... (7 Replies)
Discussion started by: yifangt
7 Replies

5. Shell Programming and Scripting

Complex transpose awk script

Hello to all in forum, Maybe an awk expert could help me with this complex task for me. I have the input shown below and I would like to get the output as follow: - I would like the output separated by commas. - The header is fixed and will be the same always. - For the lines containing... (22 Replies)
Discussion started by: Ophiuchus
22 Replies

6. Shell Programming and Scripting

awk transpose rows to column

Need to transpose in awk rows to column like this: input: A1,6,5,4 3,2,1, A2,8,7,9,10,11,12,13,14 A3,1,2,3,5,7,8,9 A4,9,4,8,1,5,3, output: A1,1 A1,2 A1,4 ... A2,7 A2,8 ... A3,1 A3,2 ... A4,1 A4,3 (5 Replies)
Discussion started by: sdf
5 Replies

7. Shell Programming and Scripting

How can i transpose this rerult by using awk?

From>>> ATOM 1 ca 2 o 3 h 4 h 5 o dE/dx 0.2057422D-01 0.2463722D-01-0.1068047D-01-0.1495280D-01-0.3725362D-02 dE/dy -0.7179106D-02-0.1554542D-01 0.1016889D-01 0.3268502D-02-0.4888578D-01 dE/dz -0.5600872D-02 0.3110649D-01-0.4088230D-02-0.2295107D-01-0.2832048D-01 ATOM 6 h 7 h 8 o 9 h 10 h... (1 Reply)
Discussion started by: wanchem
1 Replies

8. Shell Programming and Scripting

Transpose using awk

I have a requirement to transpose the below xml which is in a text file on unix: <?xml version="1.0" ?> <REQUEST> <ID>XXX</ID> <TIMESTAMP>20090720062610</TIMESTAMP> <FLAG>Y</FLAG> <TO_FLAG>Y</TO_FLAG> </REQUEST> to <?xml version="1.0"... (13 Replies)
Discussion started by: new_ds_man
13 Replies

9. Shell Programming and Scripting

How to transpose a table of data using awk

Hi. I have this data below:- v1 28 14 1.72414 1.72414 1.72414 1.72414 1.72414 v2 77 7 7.47126 6.89655 6.89655 6.89655 6.89655 v3 156 3 21.2644 21.2644 20.6897 21.2644 20.6897 v4 39 3 1.72414 1.72414 1.72414 1.72414 1.72414 v5 155 1 21.2644 23.5632 24.1379 23.5632 24.1379 v6 62 2 2.87356... (2 Replies)
Discussion started by: ahjiefreak
2 Replies

10. Shell Programming and Scripting

How to transpose data elements in awk

Hi, I have an input data file :- Test4599,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,2,2,Rain Test90,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,1,0,Not Rain etc.... I wanted to transpose these data to:-... (2 Replies)
Discussion started by: ahjiefreak
2 Replies
Login or Register to Ask a Question