Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Convert data into tabular matrix

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 tabular matrix
# 1  
Old 01-16-2015
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

Code:
chr1    2804449    2804450    NACpG_1    window1    +    chr1    2804443    2804475    1
chr1    2804450    2804451    NACpG_1    window2    +    chr1    2804443    2804475    1
chr1    2804451    2804452    NACpG_1    window3    +    chr1    2804443    2804475    1
chr1    2804452    2804453    NACpG_1    window4    +    chr1    2804443    2804475    1
chr1    2804453    2804454    NACpG_1    window5    +    chr1    2804443    2804475    1
chr1    2804454    2804455    NACpG_1    window6    +    chr1    2804443    2804475    1
chr1    2804455    2804456    NACpG_1    window7    +    chr1    2804443    2804475    1
chr1    2804456    2804457    NACpG_1    window8    +    chr1    2804443    2804475    1
chr1    2804457    2804458    NACpG_1    window9    +    chr1    2804443    2804475    1
chr1    2804458    2804459    NACpG_1    window10    +    chr1    2804443    2804475    1
chr1    47235698    47235699    NACpG_2    window10    -    chr1    47235697    47235699    0.90
chr1    47235699    47235700    NACpG_2    window9    -    chr1    47235699    47235703    0.11
chr1    47235700    47235701    NACpG_2    window8    -    chr1    47235699    47235703    0.17
chr1    47235701    47235702    NACpG_2    window7    -    chr1    47235699    47235703    0.17
chr1    47235702    47235703    NACpG_2    window6    -    chr1    47235699    47235703    0.97
chr1    47235703    47235704    NACpG_2    window5    -    chr1    47235703    47235705    0.98
chr1    47235704    47235705    NACpG_2    window4    -    chr1    47235703    47235705    0.98
chr1    47235705    47235706    NACpG_2    window3    -    chr1    47235705    47235709    0.29
chr1    47235706    47235707    NACpG_2    window2    -    chr1    47235705    47235709    0.29
chr1    47235707    47235708    NACpG_2    window1    -    chr1    47235705    47235709    1
chr1    56927295    56927296    NANoCpG_3    window10    -    .    -1    -1    0
chr1    56927296    56927297    NANoCpG_3    window9    -    .    -1    -1    0
chr1    56927297    56927298    NANoCpG_3    window8    -    .    -1    -1    0
chr1    56927298    56927299    NANoCpG_3    window7    -    .    -1    -1    0
chr1    56927299    56927300    NANoCpG_3    window6    -    .    -1    -1    0
chr1    56927300    56927301    NANoCpG_3    window5    -    .    -1    -1    0
chr1    56927301    56927302    NANoCpG_3    window4    -    .    -1    -1    0
chr1    56927302    56927303    NANoCpG_3    window3    -    .    -1    -1    1
chr1    56927303    56927304    NANoCpG_3    window2    -    .    -1    -1    1
chr1    56927304    56927305    NANoCpG_3    window1    -    .    -1    -1    1

Desired output format
Code:
    window1 window2 window3 window4 window5 window6 window7 window8 window9 window10
NACpG_1    1    1    1    1    1    1    1    1    1    1
NACpG_2    1    0.29    0.29    0.98    0.98    0.97    0.17    0.17    0.11    0.9
NANoCpG_3    1    1    1    0    0    0    0    0    0

Previously, i was doing it using perl. As the current output is from command-line, i wanted to convert this output into required format using awk.

I tried, but did not get far:

Code:
cat Test | awk '{ f1[$4]++; f2[$5]++; } END { ORS=" ";print "      "; for ( i in f2) { print i }; "\n"}'


Any help will be appreciated. Thanks in advance.
Moderator's Comments:
Mod Comment Please use CODE tags for sample input and output as well as for sample code segments.
Last edited by Don Cragun; 01-16-2015 at 01:49 PM.. Reason: Add CODE tags.
# 2  
Old 01-16-2015
Hello ChiragNepal,

Welcome to forums, could you please code tags for commands/codes/inputs used by you in your posts as per forum rules.
Following may help you in same.
Code:
awk 'BEGIN{print "window1 window2 window3 window4 window5 window6 window7 window8 window9 window10"}{Y[$4]++;X[$4]=$NF OFS X[$4]} END{for(i in X){;print i OFS X[i]}}' Input_file

Output will be as follows.
Code:
window1 window2 window3 window4 window5 window6 window7 window8 window9 window10
NACpG_1 1 1 1 1 1 1 1 1 1 1
NANoCpG_3 1 1 1 0 0 0 0 0 0 0
NACpG_2 1 0.29 0.29 0.98 0.98 0.97 0.17 0.17 0.11 0.90

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
ChiragNepal
# 3  
Old 01-16-2015
Thank you RavinderSingh !!
# 4  
Old 01-16-2015
Please control your use of code tags!

You'll have to remove the DOS <CR> line terminators before any of these solutions work.
Try also
Code:
awk     '       {LN[$4]; HD[$5]; MX[$4,$5]=$NF}
         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]; print ""}
                }
        ' file
            window10   window1   window2   window3   window4   window5   window6   window7   window8   window9
   NACpG_1         1         1         1         1         1         1         1         1         1         1
   NACpG_2      0.90         1      0.29      0.29      0.98      0.98      0.97      0.17      0.17      0.11
 NANoCpG_3         0         1         1         1         0         0         0         0         0         0

This User Gave Thanks to RudiC For This Post:
ChiragNepal
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert text file to HTML tabular format.

Please provide script/commands to convert text file to HTML tabular format. No need of styles and colours, just output and a heading in table is required. Output file will be send via email and will be seen from outlook. (script required without using awk). output file content: (sar... (7 Replies)
Discussion started by: Veera_V
7 Replies

2. Shell Programming and Scripting

Convert data to a tabular format

How can i convert the below data to a simpler format :- cat tabular.txt User 1 Details :- First Name = Tom Middle Name = Last Name = Hanks Age = 40 Address = User 2 details :- First Name = Mike Middle Name = Last Name = Tyson Age = 50 Address = (2 Replies)
Discussion started by: lazydev
2 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

Generate tabular data based on a column value from an existing data file

Hi, I have a data file with : 01/28/2012,1,1,98995 01/28/2012,1,2,7195 01/29/2012,1,1,98995 01/29/2012,1,2,7195 01/30/2012,1,1,98896 01/30/2012,1,2,7083 01/31/2012,1,1,98896 01/31/2012,1,2,7083 02/01/2012,1,1,98896 02/01/2012,1,2,7083 02/02/2012,1,1,98899 02/02/2012,1,2,7083 I... (1 Reply)
Discussion started by: himanish
1 Replies

5. UNIX for Dummies Questions & Answers

Put data into tabular form

Hi I am having a file which is required to be presented in the under-noted output form. Please suggest. Input: Kapil: apple 4 banana 6 cherry 0 Manoj: apple 13 banana cheery 2 Output: apple banana cherry Kapil: 4 6 0 Manoj: 13 2 Thanks in... (4 Replies)
Discussion started by: vanand420
4 Replies

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

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

8. Shell Programming and Scripting

How to find max value in a tabular data?

FILE_NAME VER TYPE DELETED_DATE CREATED_DATE SIZE LOCATION hello 1 d 2010-08-09 2010-08-09 4096 /home/xxxxx/project/ hello 2 d 2010-08-09 2010-08-09 4096 /home/xxxxx/project/ hello 3 d 2010-08-09 2010-08-09 4096 /home/xxxxx/project/ hello 4 d 2010-08-09 2010-08-09 4096 /home/xxxxx/project/ ... (4 Replies)
Discussion started by: classic
4 Replies

9. UNIX for Dummies Questions & Answers

How to read tabular data?

Hello, I have a log file which contains data in tabular format(3 columns(total, posted, rejected) and 2 rows(close, total)) as below. TOTAL POSTED REJECTED CLOSE 3 3 0 TOTAL 3 3 0 I have to search for all Total... (1 Reply)
Discussion started by: akash028
1 Replies

10. UNIX for Dummies Questions & Answers

converting a tabular format data to comma seperated data in KSH

Hi, Could anyone help me in changing a tabular format output to comma seperated file pls in K-sh. Its very urgent. E.g : username empid ------------------------ sri 123 to username,empid sri,123 Thanks, Hema:confused: (2 Replies)
Discussion started by: Hemamalini
2 Replies
Login or Register to Ask a Question