How to transpose a repeating set of rows to columns using awk

Question

Ive a text file with data in 7 columns in this format:

18030   AAJ51   FTO rs9939609   C__30090620_10  A   T
18030   AAJ51   CAT rs1001179   C__11468118_10  C   C
18030   AAJ51   CCL2    rs1024611   C___2590362_10  G   G
18030   AAJ51   TAS2R38 rs10246939  C___9506826_10  C   C
20287   AAJ51   FTO rs9939609   C__30090620_10  A   T
20287   AAJ51   CAT rs1001179   C__11468118_10  C   C
20287   AAJ51   CCL2    rs1024611   C___2590362_10  A   G
20287   AAJ51   TAS2R38 rs10246939  C___9506826_10  T   T

The 2nd, 3rd 4th and 5th columns are constant and repeat.

The variables are the 1st, 6th and 7th columns.

I would like to transpose the data in this way:

        FTO       CAT       CCL2        TAS2R38
        rs9939609 rs1001179 rs1024611   rs10246939
18030   AT        CC        GG          AT
20287   AT        CC        AG          TT

Whilst the example shows 4 rows per ID (the 5-digit number in first column is the ID) the actual file has 128 rows per ID so performing a match or regex would not be practical and prefer a method that iterates over a number of rows.

I saw this example on converting n number of rows but am unsure how to modify for this application.

UPDATE: CRLF endings can cause formatting problems which can be resolved using a tool like dos2unix

RomanPerekhrest · Accepted Answer · 2018-04-03T13:57:54.147

3

GNU Awk solution:

awk '{ 
         if (!keys[$3]++) { b[++c] = $3; row1 = row1 OFS $3; row2 = row2 OFS $4 }
         line = groups[$1][$3];
         groups[$1][$3] = (line == ""? $6$7: line OFS $6$7) 
     }
     END{ 
         print row1 ORS row2; 
         for (i in groups) {
             r = i; 
             for (j in b) r = r OFS groups[i][b[j]];
             print r 
         } 
     }' OFS='\t' file | column -txn

The output:

       FTO        CAT        CCL2       TAS2R38
       rs9939609  rs1001179  rs1024611  rs10246939
18030  AT         CC         GG         CC
20287  AT         CC         AG         TT

edited Apr 03 '18 at 13:57

answered Apr 03 '18 at 07:22

RomanPerekhrest

88,541
4
65
105

Thanks Roman, I'm not able to understand the last line and implement your solution. could you please explain the last line – gungu Apr 03 '18 at 13:39
@gungu, the last line is `}' OFS='\t' file | column -txn` or you meant another one? – RomanPerekhrest Apr 03 '18 at 13:42
yes, }' OFS='\t' file | column -txn . Is 'file' the file being transposed and 'column' the new file generated? – gungu Apr 03 '18 at 13:48
@gungu, `file` is input file, but `column` is a command-line tool to make pretty-formatted columnar output. Thanks to `column` you see the good aligned output – RomanPerekhrest Apr 03 '18 at 13:59
i am getting a "column: illegal option --n" – gungu Apr 03 '18 at 15:47
usage: column [-tx] [-c columns] [-s sep] [file ...] – gungu Apr 04 '18 at 02:33
bash shell I am using does not have those column options - https://linux.die.net/man/1/column – gungu Apr 04 '18 at 05:18
@gungu, why taking so much care of `column` part?. If it doesn't work on your OS - just skip it – RomanPerekhrest Apr 04 '18 at 07:24
i thought it was an essential part of your solution. when i omit it (as ive already tried) $6$7 is printed as a column instead of a row – gungu Apr 04 '18 at 10:08

score 1 · Answer 2 · edited Apr 03 '18 at 05:03

1

Try using a last_seen variable and an array.

When the last_seen variable changes, then output the array as a row.
Store the values for the current row in the array.

edited Apr 03 '18 at 05:03

BozoJoe

6,117
4
44
66

answered Apr 03 '18 at 03:23

Zan Lynx

53,022
10
79
131

Thanks for the suggestion Zan. Would you be able to provide an example? – gungu Apr 03 '18 at 05:07

How to transpose a repeating set of rows to columns using awk

2 Answers2

Linked