awk to convert table-by-row to matrix table


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to convert table-by-row to matrix table
# 1  
Old 01-02-2014
awk to convert table-by-row to matrix table

Hello,
I need some help to reformat this table-by-row to matrix?
Code:
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,r

Code:
outfile:

SITE     o p q r s t v u w x y z      
site1    A A A A A A T C T - C -
site2    - A G A G A A C A - C A
site3    A - A - A A T A T T C A

It seems AWK may do the job easily with array, but I am not sure. Thanks in advance!

Last edited by yifangt; 01-02-2014 at 01:24 PM.. Reason: Text are missing!
# 2  
Old 01-02-2014
Try this adaption from an earlier post ( https://www.unix.com/302738343-post7.html ; I'm sorry, I seem not to be able to enter clickable URLs any more ... ) :
Code:
awk     '{for (i=1; i<=LnCnt; i++) if ($1 == Ln[i]) break; if (i > LnCnt) Ln[++LnCnt]=$1}
         {for (k=3; k<=NF; k++) {for (j=1; j<=HdCnt; j++) if ($k == Hd[j]) break
                                 if (j > HdCnt) Hd[++HdCnt]=$k
                                 Mx[$1,$k] = $2}
         }
         END {                           printf "%10s", ""
                                         for (j=1; j<=HdCnt; j++)  printf "%3s", Hd[j]
                                         printf "\n";
              for (i=1; i<=LnCnt; i++)  {printf "%10s", Ln[i];
                                         for (j=1; j<=HdCnt; j++) printf "%3s", Mx[Ln[i], Hd[j]];
                                         printf "\n"
                                        }
             }
        ' FS="[ :,]" file
            o  p  q  r  s  t  y  u  v  w  x  z
     site1  A  A  A  A  A  A  C  C  T  T  -  -
     site2  -  A  G  A  G  A  C  C  A  A  -  A
     site3  A  -  A  -  A  A  C  A  T  T  T  A


Last edited by RudiC; 01-02-2014 at 05:12 PM..
This User Gave Thanks to RudiC For This Post:
# 3  
Old 01-02-2014
Hi,
Another solution in gawk:
Code:
gawk -F'[ :,]' '{PROCINFO["sorted_in"]="@ind_str_asc"; A[$1]=1;for (i=3;i<=NF;i++) {C[$i]=$i;B[$1,$i]=$2}}END{printf "SITE\t";for ( jj in C ) printf jj" ";print "" ; for ( ii in A ) {printf ii"\t";for ( jj in C ) {printf B[ii,jj]" "}; print ""}}' infile

Happy gnu year.
Regards.
# 4  
Old 01-02-2014
additional: mark missing data with -

Thank you both!
Amazing scripts!
disedorgue, Your script is what i was thinking to use, but need more digestion.
More challenging with real data, that some members (i.e. columns) of some site are with missing data as indicated with "-" in previous post, but not listed in the infile, which will be assigned with an empty cell and will cause confusion.
Code:
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
site3 A:o,q,s,t,u,z
site3 C:y
site3 T:v,w,x

Code:
output:
SITE    o p q r s t u v w x y z 
site1    A A A A A A C T T - C - 
site2     A G A G A C A A  C A 
site3    A  A  A A A T T T C A

How to add "-" to those "empty" cells so that the table is aligned? Thanks a lot again!

Last edited by yifangt; 01-02-2014 at 06:35 PM.. Reason: typo
# 5  
Old 01-02-2014
Try this:
Code:
$ awk -F'[ :,]' '{PROCINFO["sorted_in"]="@ind_str_asc"; A[$1]=1;for (i=3;i<=NF;i++) {C[$i]=$i;B[$1,$i]=$2}}END{printf "SITE\t";for ( jj in C ) printf jj" ";print "" ; for ( ii in A ) {printf ii"\t";for ( jj in C ) {if (B[ii,jj]) {printf B[ii,jj]" "} else {printf "- "}}; print ""}}' infile

Regards.
This User Gave Thanks to disedorgue For This Post:
# 6  
Old 01-03-2014
thanks

Thanks a lot!
Has the "Thank" option been removed for each post on the forum? I could not thank the reply by clicking the "Thank" button anymore.
# 7  
Old 01-03-2014
yifangt,
The SmilieThanks button is still there. Is it possible that you just didn't notice it because disedorgue's code was presented as a single long line and you didn't scroll far enough to the right to see it?
This User Gave Thanks to Don Cragun For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert rows into columns and create table with awk

Hello I've four fields . They are First Name, Last Name, Age, Country. So when I run a Unix command, I get below output with these fields comes every time in different order as you can see. Some times first name is the first row and other time last name is first row in the output and etc etc..... (9 Replies)
Discussion started by: rprpr
9 Replies

2. Shell Programming and Scripting

In php, Moving a new row to another table and deleting old row

Hi, I already succeed moving a new row to another table if the field from new row doesn't have the first word that I categorized (like: IRC blablabla, PTM blablabla, ADM blablabla, BS blablabla). But it can't delete the old row. Please help me with the script. my php script: INSERT INTO... (2 Replies)
Discussion started by: jazzyzha
2 Replies

3. Shell Programming and Scripting

How to sort matrix table in UNIX?

Hello All, i have a file sort.txt with below entries. 1 12 10 16 6 4 20 8 15 i need to sort these entries and the out put should come in a single line. 1 4 6 8 10 12 15 16 20 Can you please help me sort this out? (2 Replies)
Discussion started by: sureshk_85
2 Replies

4. Shell Programming and Scripting

Read in Table as a matrix

Dear forum users, i'm trying to read a table with 40x122 data in a array. Following this, i'd plot each rows again the header of the file in gnuplot. i was thinking for something like that #!/bin/bash # reads from the $ips file and assigns to $MYARRAY #IFS =";" split the line after the... (6 Replies)
Discussion started by: servuskelb
6 Replies

5. Shell Programming and Scripting

Table to Matrix

Hi, I have a table in the format: 1 0 -1 1 0 2 0 1 -1 0 0 0 3 0 1 1 0 0 0 0 0 0 etc. I am trying to input this to a program, however it is complaining about the fact that it is not in matrix format. How do I add 0's to end of the rows to make them even? Thanks in advance! (2 Replies)
Discussion started by: Rhavin
2 Replies

6. UNIX for Dummies Questions & Answers

Shell Script: Traverse Database Table Row by Row

Hello Everyone, My issue is that I want to traverse a database table row by row and do some action on the value retrieved in each row. I have gone through a lot of shell script questions/posts. I could find row by row traversal of a file but not a database table. Please help. Thanks &... (5 Replies)
Discussion started by: ahsan.asghar
5 Replies

7. UNIX for Dummies Questions & Answers

convert matrix to row and columns

Dear Unix Gurus, I have a sample data set that looks like this y1 y2 y3 y4 y5 x1 0.3 0.5 2.3 3.1 5.1 x2 1.2 4.1 3.5 1.7 1.2 x3 3.1 2.1 1.0 4.1 2.1 x4 5.0 4.0 6.0 7.0 1.1 I want to open it up so that I get x1 y1 0.3 x2 y1 1.2 x3 y1 3.1 x4 y1 5.0 x1 y2 0.5 x2 y2... (3 Replies)
Discussion started by: tintin72
3 Replies

8. Shell Programming and Scripting

Is it possible to draw table/matrix using shell script?

Hi all, I need to create a matrix of variable rows and columns. Right now i have 3 rows and two columns and following values. Output something like TypeA TypeB TestCase1 Pass Fail TestCase2 Pass ... (2 Replies)
Discussion started by: jakSun8
2 Replies
Login or Register to Ask a Question