Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

convert data 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 convert data into matrix- awk
# 1  
Old 02-27-2012
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

Code:
key    states    cell
key1    state1    cell1
key1    state2    cell1
key1    state3    cell1
key1    state4    cell1
key1    state5    cell1
key1    state6    cell1
key1    state7    cell1
key1    state8    cell1
key1    state9    cell1
key1    state10    cell1
key1    state11    cell1
key1    state12    cell1
key1    state13    cell1
key1    state14    cell1
key1    state15    cell1
key2    state1    cell1
key2    state2    cell1
key2    state3    cell1
key2    state4    cell1
key2    state5    cell1
key2    state6    cell1
key2    state7    cell1
key2    state8    cell1
key2    state9    cell1
key2    state10    cell1
key2    state11    cell1
key2    state12    cell1
key2    state13    cell1
key2    state14    cell1
key2    state15    cell1
key1    state1    cell2
key1    state2    cell2
key1    state3    cell2
key1    state4    cell2
key1    state5    cell2
key1    state6    cell2
key1    state7    cell2
key1    state8    cell2
key1    state9    cell2
key1    state10    cell2
key1    state11    cell2
key1    state12    cell2
key1    state13    cell2
key1    state14    cell2
key1    state15    cell2
key1    state1    cell3
key1    state14    cell3
key2    state1    cell3
key3    state1    cell3
key4    state1    cell3

output
Code:
cell    state1    state2    state3    state4    state5    state6    state7    state8    state9    state9    state10    state11    state12    state13    state14    state15
cell1    2    2    2    2    2    2    2    2    2    2    2    2    2    2    2
cell2    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1
cell3    4    0    0    0    0    0    0    0    0    0    0    1    0    1    0

# 2  
Old 02-27-2012
I think it is. are the values of column 2 & 3 static values you know about?
# 3  
Old 02-27-2012
yes
col2 has state1 to state15
col3 has cell1-cell9

and

col1 has many keys 1- several thousands
# 4  
Old 02-27-2012
maybe you can create a loop and concatenate the value of col2&3 to a name. this name can be used as variable and set.
for example:
1st row -> found key1, state3, cell5 -> $state3cell5 = "_key1"

before adding the key to the var check if the key(substring) already exists.
after the loop you can count in each variable matches of "/_key\d{1,3}/".
# 5  
Old 02-27-2012
I don't quiet get it. ?
# 6  
Old 02-27-2012
awk
Hi,

Try this one,

Code:
awk '{f1[$3]++;f2[$2]++;a[$3"^"$2]++;}END{ORS="";print "      ";for(j in f2){print j" ";c++;if(c>5){print " ";}}print "\n";for(i in f1){print i;for(k in f2){l=i"^"k;if(a[l] == ""){print "        ";}else{print "   "a[l]"    ";}}print "\n";}}' file

My Output is,

Code:
 
      state7 state8 state9 state10 state11 state12  state1  state13  state2  state14  state3  state15  state4  state5  state6
cell1   2       2       2       2       2       2       2       2       2       2       2       2       2       2       2
cell2   1       1       1       1       1       1       1       1       1       1       1       1       1       1       1
cell3                                                   4                       1

if you have gawk, you can sort the header with below code,

Code:
gawk '{f1[$3]++;f2[$2]++;a[$3"^"$2]++;}END{asort(f1);ORS="";print "      ";for(j in f2){print j" ";c++;if(c>5){print " ";}}print "\n";for(i in f1){print i;for(k in f2){l=i"^"k;if(a[l] == ""){print "        ";}else{print "   "a[l]"    ";}}print "\n";}}' file

i dont have gwk installed here, so i didn't used that.

Cheers,
RangaSmilie
These 2 Users Gave Thanks to rangarasan For This Post:
daWonderer quincyjones
# 7  
Old 02-27-2012
gawk is installed but not getting output

Code:
$ gawk '{f1[$3]++;f2[$2]++;a[$3"^"$2]++;}END{asort(f1);ORS="";print "      ";for(j in f2){print j" ";c++;if(c>5){print " ";}}print "\n";for(i in f1){print i;for(k in f2){l=i"^"k;if(a[l] == ""){print "        ";}else{print "   "a[l]"    ";}}print "\n";}}' rm2
      state12 state7 state13 state8 state14 state9  state15  state1  state2  states  state3  state4  state10  state5  state11  state6  
4                                                                                                                                
1                                                                                                                                
2                                                                                                                                
3

but awk version is working great.


Code:
key1    state1    cell3 
key1    state14    cell3 
key2    state1    cell3 
key3    state1    cell3 
key4    state1    cell3

Last edited by quincyjones; 02-27-2012 at 11:53 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

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 c1 c2 c3 c4 a 1 0 0 0 b 0 1 0 0 c 1 0 0 0 d 0 0 ... (5 Replies)
Discussion started by: quincyjones
5 Replies

2. Shell Programming and Scripting

Convert data into tabular matrix

Hi There, I want to convert the following data into tabular matrix, based on column 4th and 5th, and output the column 10th value chr1 2804449 2804450 NACpG_1 window1 + chr1 2804443 2804475 1 chr1 2804450 2804451 NACpG_1 window2 + chr1 2804443 ... (3 Replies)
Discussion started by: ChiragNepal
3 Replies

3. Shell Programming and Scripting

How order a data matrix using awk?

is it possible to order the following row clusters from ascending to descending. thanx in advance input 1 2 4 0 1 2 4 0 3 3 3 3 1 5 1 0 1 5 1 0 6 0 0 0 5 1 1 1... (4 Replies)
Discussion started by: quincyjones
4 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. 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

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. Shell Programming and Scripting

two-column data to matrix in AWK

Howdy, I need to convert an association data matrix, currently in a two-column format, into a matrix with numbers indicating the number of associations. I've been looking around for AWK code in the list, but could not find anything. Here's an example of what I want to perform: original... (10 Replies)
Discussion started by: sramirez
10 Replies

8. Shell Programming and Scripting

By using AWK can I convert matrice shaped data to a row ?

Hello, I have output in the matrice form , for example: 1 2 3 4 a b c d jim joe sue tom how can I convert this line-column data into a row as follows 1 2 3 4 a b c d jim joe sue tom thank you (14 Replies)
Discussion started by: rpf
14 Replies
Login or Register to Ask a Question