Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

List to matrix

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers List to matrix
# 1  
Old 11-10-2014
List to matrix
I want to go from
Code:
1,a,1a
1,b,1b
1,c,1c
2,a,2a
2,b,2b
3,a,3a

to
Code:
 
  a,b,c
1,1a,1b,1c 
2,2a,2b,-
3,3a,-,-

Here is what I tried

Code:
 
awk -F, 'BEGIN {OFS = ","}
 {
    f = f ? (l != $1 ? f OFS $1 : f) : OFS $1
    y[$1] = y[$1] ? y[$1] OFS $NF : $NF
    l = $1
 } END {
    print f
   for (i in y) print i, y[i]
 }'

The results are different from what I want, please help modify this to achieve my goal. I have just made up the third column, in my large file they are strings like OX2345,AX9855 etc. I want to replace the missing values by '-'
# 2  
Old 11-10-2014
Hello Senhia83,

Could you please try following and let me know if this helps. As it is working for me for given code as well as with some small tests inputs. Please try in your code and let me know if you have any queries.

Code:
awk -F, '{A[$1]=A[$1]?A[$1] OFS $3:$3} {W[$2]} END{{for(i in W){S=S?S OFS i:"  "i}} {print S;gsub(/[[:space:]]/,Q,S);gsub(/[[:punct:]]/,Z,S);LEN=length(S);} for(j in A){{LEN1=length(A[j]);if(length(A[j]) == (3 * LEN -1)){print j OFS A[j]} else {while(LEN1 <= (3 * LEN - 1)){A[j]=A[j]?A[j] OFS "-":"-";LEN1+=3; if(LEN1==(3 * LEN -1)){print j OFS A[j]}}}}}}' OFS=, matrix_test

Output will be as follows.
Code:
  a,b,c
1,1a,1b,1c
2,2a,2b,-
3,3a,-,-

EDIT: Also tested in a following test input, if 1st column's order doesn't matter then we are good.
Let's say input file is as follows.
Code:
cat matrix_test
1,a,1a
1,b,1b
1,c,1c
2,a,2a
2,b,2b
3,a,3a
4,d,1a
4,d,1b
5,e,1a
5,e,1b

Then following is the result after running code.
Code:
awk -F, '{A[$1]=A[$1]?A[$1] OFS $3:$3} {W[$2]} END{{for(i in W){S=S?S OFS i:"  "i}} {print S;gsub(/[[:space:]]/,Q,S);gsub(/[[:punct:]]/,Z,S);LEN=length(S);} for(j in A){{LEN1=length(A[j]);if(length(A[j]) == (3 * LEN -1)){print j OFS A[j]} else {while(LEN1 <= (3 * LEN - 1)){A[j]=A[j]?A[j] OFS "-":"-";LEN1+=3; if(LEN1==(3 * LEN -1)){print j OFS A[j]}}}}}}' OFS=, matrix_test
  a,b,c,d,e
4,1a,1b,-,-,-
5,1a,1b,-,-,-
1,1a,1b,1c,-,-
2,2a,2b,-,-,-
3,3a,-,-,-,-

Thanks,
R. Singh
Last edited by RavinderSingh13; 11-10-2014 at 07:16 PM.. Reason: Added one more example for input file to test code
# 3  
Old 11-10-2014
Hi Ravinder,

The input that you provided seems to have duplicate 1st and 2nd col combinations.

Code:
 
cat matrix_test
1,a,1a
1,b,1b
1,c,1c
2,a,2a
2,b,2b
3,a,3a
4,d,1a
4,d,1b
5,e,1a
5,e,1b


I did some testing with the following input and the output is incorrect, would you please look into it?
I have highlighted the incorrect row.

Code:
 
1,a,1a
1,b,1b
1,c,1c
2,a,2a
2,b,2b
3,a,3a
4,d,1a
5,a,1b


The output that I`m getting

Code:
 
a,b,c,d
1,1a,1b,1c,-
2,2a,2b,-,-
3,3a,-,-,-
4,1a,-,-,-
5,1b,-,-,-

The correct output

Code:
 
a,b,c,d
1,1a,1b,1c,-
2,2a,2b,-,-
3,3a,-,-,-
4,-,-,-,1a
5,1b,-,-,-

Last edited by senhia83; 11-11-2014 at 12:41 AM..
# 4  
Old 11-11-2014
Hello Senhia83,

Got it, you haven't mentioned that indexing part, so I didn't include in my answer. Let me try something and get back to you. Smilie

Thanks,
R. Singh
# 5  
Old 11-11-2014
This request has been posted umpteen times in similar forms, as has the answer. Try
Code:
awk     '       {LN[$1]; HD[$2]; MX[$1,$2]=$3}
         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 ""}
                }
        ' FS=, file
                   a         b         c         d
         1        1a        1b        1c          
         2        2a        2b                    
         3        3a                              
         4                                      1a
         5        1b

, disregarding the formatting. If happy, try to adapt to larger files and get the formatting correct.
This User Gave Thanks to RudiC For This Post:
senhia83
 
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Weighted adjacency list to adjacency matrix

dear awk gurus, i would need a fast (therefore) awk solution for the reformation of an uncomplete weighted adjacency list to a complete sorted adjacency matrix. example (FS=OFS=,): a,d,0.33 a,b,0.25 b,c,0.11 should give: ,a,b,c,d a,1,0.25,0,0.33 b,0.25,1,0.11,0... (4 Replies)
Discussion started by: dietmar13
4 Replies

2. Shell Programming and Scripting

Randomize a matrix

--please have a look at my third post in this thread! there I explained it more clearly-- Hey guys. I posted a complex problem few days back. No reply! :| Here is simplified question: I have a matrix with 0/1: * col1 col2 col3 row1 1 0 1 row2 0 0 ... (5 Replies)
Discussion started by: @man
5 Replies

3. Shell Programming and Scripting

awk? adjacency matrix to adjacency list / correlation matrix to list

Hi everyone I am very new at awk but think that that might be the best strategy for this. I have a matrix very similar to a correlation matrix and in practical terms I need to convert it into a list containing the values from the matrix (one value per line) with the first field of the line (row... (5 Replies)
Discussion started by: stonemonkey
5 Replies

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

5. Shell Programming and Scripting

Matrix

Hi All I would like to merge multiple files with the same row and column size into a matrix format In a folder I have multiple files in the following format vi 12.txt a 1 b 5 c 7 d 0 vi 45.txt a 3 b 6 c 9 d 2 vi 9.txt a 4 (7 Replies)
Discussion started by: Lucky Ali
7 Replies

6. Shell Programming and Scripting

Matrix construction

Hi I have to construct a rectangular matrix with 6012 rows and 2221 columns. Here the rows and columns were given by alphanumeric ids in a file named row.txt and column.txt respectively. with this row nd column ids I have to construct a matrix ie 6012*2221 and compare the column ids with... (0 Replies)
Discussion started by: riyabio
0 Replies

7. Shell Programming and Scripting

Matrix construction

Hi experts How to construct a rectangular matrix for a text file with 6012 rows and 2221 columns. Thank You (1 Reply)
Discussion started by: riyabio
1 Replies

8. Shell Programming and Scripting

diagonal matrix to square matrix

Hello, all! I am struggling with a short script to read a diagonal matrix for later retrieval. 1.000 0.234 0.435 0.123 0.012 0.102 0.325 0.412 0.087 0.098 1.000 0.111 0.412 0.115 0.058 0.091 0.190 0.045 0.058 1.000 0.205 0.542 0.335 0.054 0.117 0.203 0.125 1.000 0.587 0.159 0.357... (11 Replies)
Discussion started by: yifangt
11 Replies

9. Programming

Converting distance list to distance matrix in R

Hi power user, I have this type of data (distance list): file1 A B 10 B C 20 C D 50I want output like this # A B C D A 0 10 30 80 B 10 0 20 70 C 30 20 0 50 D 80 70 50 0 Which is a distance matrix I have tried... (0 Replies)
Discussion started by: anjas
0 Replies
Login or Register to Ask a Question