Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to convert 2 columns into matrix -awk?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to convert 2 columns into matrix -awk?
# 1  
Old 05-04-2015
How to convert 2 columns into matrix -awk?
How can i convert two columns in to o and 1 matrix. thnks

Input
a c1
b c2
c c1
d c3
e c4

output
Code:
        c1      c2      c3      c4
a       1       0       0       0
b       0       1       0       0
c       1       0       0       0
d       0       0       1       0
e       0       0       0       1

# 2  
Old 05-04-2015
You had a very similar thread some time ago. Any learnings from that?

However, try
Code:
awk     '       {LN[$1]; HD[$2]; MX[$1,$2]++}
         END    {               printf "%10s", ""; for (i in HD) printf "%10s", i; print "";
                 for (j in LN) {printf "%10s",j;   for (i in HD) printf "%10s", MX[j,i]+0; print ""}
                }
        ' file
                  c1        c2        c3        c4
         a         1         0         0         0
         b         0         1         0         0
         c         1         0         0         0
         d         0         0         1         0
         e         0         0         0         1

This User Gave Thanks to RudiC For This Post:
quincyjones
# 3  
Old 05-04-2015
you are right. however it was based on 3 columns and here it is just two.
I noticed that if the column names are longer for example instead of c1 c1_abajgdhd_1234x, the script is printing bit wrong because some header names were not separated by tab.
Last edited by quincyjones; 05-04-2015 at 07:50 AM..
# 4  
Old 05-04-2015
I guess that's due to the <CR> (0x0D, \r) windows line terminators that you use. Remove them beforehand.
# 5  
Old 05-04-2015
Thanks but I tried this and didn't work

Code:
tr -d '\r' <$1 | awk     '       {LN[$1]; HD[$2]; MX[$1,$2]++}
         END    {               printf "%10s", ""; for (i in HD) printf "%10s", i; print "";
                 for (j in LN) {printf "%10s",j;   for (i in HD) printf "%10s", MX[j,i]+0; print ""}
                }
        '

# 6  
Old 05-04-2015
Works perfectly for me. Try increasing the %10s format string.
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calculate percentage of columns greater than certain value in a matrix using awk

This matrix represents correlation values. Is it possible to calculate the percentage of columns (a1, a2, a3) that have a value >= |0.5| and report the percentage that has positive correlation >0.5 and negative correlation <-0.5 separately. thanx in advance! input name a1 a2 a3... (5 Replies)
Discussion started by: quincyjones
5 Replies

2. 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

3. UNIX for Dummies Questions & Answers

Convert nxm Matrix into columns of data

Dear Unixers, I'm having some difficulty in converting an n x m data matrix into a dataset of 3 columns and nxm rows. As an example I want to convert this dataset 2 3 4 5 2 0.0 0.0 0.1 0.1 6 -0.3 2.0 0.0 0.3 7 -0.6 -1.1 0.5 0.3 9 -0.9 -4.1 -0.7 0.5 ... (2 Replies)
Discussion started by: tintin72
2 Replies

4. Shell Programming and Scripting

Convert columns to row using awk

Hi I need to convert some columns form a html file to rows. I do manage to make it works without help (some proud :) ) For some reason the offline status is not in bold, so I do need to remove the <b> tag from the other field to make this to work. All fields are not needed, so I test and... (5 Replies)
Discussion started by: Jotne
5 Replies

5. Shell Programming and Scripting

convert data into matrix- awk

is it possible to count the number of keys based on state and cell and output it as a simple matrix. Ex: cell1-state1 has 2 keys cell3-state1 has 4 keys. Note: Insert 0 if no data available. input key states cell key1 state1 cell1 key1 state2 cell1 key1 ... (21 Replies)
Discussion started by: quincyjones
21 Replies

6. Ubuntu

How to convert full data matrix to linearised left data matrix?

Hi all, Is there a way to convert full data matrix to linearised left data matrix? e.g full data matrix Bh1 Bh2 Bh3 Bh4 Bh5 Bh6 Bh7 Bh1 0 0.241058 0.236129 0.244397 0.237479 0.240767 0.245245 Bh2 0.241058 0 0.240594 0.241931 0.241975 ... (8 Replies)
Discussion started by: evoll
8 Replies

7. UNIX for Dummies Questions & Answers

Convert rows to columns using AWK

Hi , I am struck while coding AWK script. Need your help to convert rows into columns. I should copy only those rows which are marked to Y in a file and ignore N rows. Please help me find a solution. input file 1|abc|Y 2|cdf|Y 3|efg|N 4|xyz|Y my output should be something like this... (2 Replies)
Discussion started by: rashmisb
2 Replies

8. 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
Login or Register to Ask a Question