3 column .csv --> correlation matrix; awk, perl?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting 3 column .csv --> correlation matrix; awk, perl?
# 1  
Old 07-10-2012
3 column .csv --> correlation matrix; awk, perl?

Greetings, salutations.

I have a 3 column csv file with ~13 million rows and I would like to generate a correlation matrix. Interestingly, you all previously provided a solution to the inverse of this problem. Thread title: "awk? adjacency matrix to adjacency list / correlation matrix to list" (I haven't posted 5 times yet - can't insert URL)

Brief description of data: columns 1 and 2 contain variables, column 3 contains their correlation. For simplicity and discussion, let's use this example:

Code:
a,a,1
a,b,0.8
a,c,0.5
b,a,0.8
b,b,1
b,c,0.2
c,a,0.5
c,b,0.2
c,c,1

(sidebar - forum users "birei" and "shamrock" provided excellent solutions to remove the a,b = b,a redundancy. Thread title: "awk with existence argument?")

desired output:

Code:
,a,b,c
a,1,0.8,0.5
b,0.8,1,0.2
c,0.5,0.2,1

I currently use the following python script, where the creation of the dictionary and subsequent line-by-line look up is painfully slow for large files.

Code:
import os, time

def matrix(f):
    
    if 'csv' in f and 'matrix' not in f:
        
        i = 0
        on = time.time()
        fp = open(os.path.join(os.getcwd(),f),'r')
        
        data = [x.split(',') for x in fp]
        data.pop(0)
        
        correlations = {}
        for x in data:
            correlations[x[0]] = {}
            variables = correlations.keys()
            variables.sort()
        
        for x in variables:
            for y in variables:
                if x == y:
                    correlations[x][y] = 1
                    i+=1
                else:
                    for row in data:
                        if x in row and y in row:
                            i+=1
                            correlations[x][y] = float(row[2])
        
        data = ''
        for y in variables:
            data += ',%s' %y
        data += '\n'
        
        for x in variables:
            data += '%s,' %x
            for y in variables:
                i+=1
                try:
                    data += '%s,' %correlations[x][y]
                except:
                    data += 'CORRELATION NOT FOUND!,'
            data += '\n'
        fw = open(os.path.join(os.getcwd(),f[:-4]+'_matrix.csv'),'w')
        fw.write(data)
        fw.close()
        
        s = (on - time.time()) / float(1000)
        print '%s complete: %i iterations, %s duration.' %(f,i,s)


for f in os.listdir(os.getcwd()):
    matrix(f)

I am not partial to any particular scripting language. I am, however, partial to speed Smilie. In my experience, awk and perl are the fastest at manipulating these .csv files, but I'm not savvy enough with either. Any suggestions would be appreciated.

Thanks in advance.

Moderator's Comments:
Mod Comment edit by bakunin: I edited the URL in for you. Please use CODE-tags not only for code but also for file content, terminal output, etc.. Thanks.


Will do, thanks bakunin.

Last edited by R3353; 07-10-2012 at 01:54 PM..
# 2  
Old 07-10-2012
Try this awk script...
Code:
awk -F, 'BEGIN {OFS = ","}
{
   t = t ? (l != $1 ? t OFS $1 : t) : OFS $1
   x[$1] = x[$1] ? x[$1] OFS $NF : $NF
   l = $1
} END {
   print t
   for (i in x) print i, x[i]
}' file

This User Gave Thanks to shamrock For This Post:
# 3  
Old 07-10-2012
Hi Shamrock,

Thanks very much for your help with this. I must be missing something fundamental when running your script. Whether I simplify it and run line by line or by making it an executable and run just as you posted, I get the following error:

Code:
context is
	awk -F, >>>  ' <<<

Attached is the simple 3column.csv for testing (note, I had to append ".txt" in order to upload). Does it run ok for you?

Thanks in advance.

***disregard the error message. I believe this was on my end and not related to shamrock's script***

Last edited by R3353; 07-11-2012 at 01:11 AM.. Reason: end user error (i.e. me)
# 4  
Old 07-11-2012
Quote:
Originally Posted by R3353
I have a 3 column csv file with ~13 million rows and I would like to generate a correlation matrix.
Let us first have some rough estimations about sizes:

How many rows/columns will this matrix have? Will there be empty matrix elements? The background is: there are some limitations which may or may not affect the solution: Unix files have maximum line lengths because text processing utilities like "sed", "awk", etc. can't handle longer ones (see MAXLINE in sys/limits.h. Shell arrays cannot have more than 1024 elements.

Then some questions: How to deal with multiple entries with the same indexes - add together, generate error, other?

My take would be to first put the lines in "normal form": the lines are of the form

Code:
keyA, keyB, value

As all keys are interchangeable, so that the following two entries would in effect be the same

Code:
x, y, value
y, x, value

and the matrix you are constructing is symetrical along the main diagonal the first step should be to sort the keys within the lines by some criteria, so that the first key in the line is consistently "lower or equal" or "higher or equal" than the second key in the line.

Than a simple sort over the first two fields will reduce the problem to a simple sort-of group-change: all lines with a given keyA will represent one row AND - because the matrix is symetrical - also one column.

I hope this helps.

bakunin
This User Gave Thanks to bakunin For This Post:
# 5  
Old 07-11-2012
Quote:
Originally Posted by shamrock
Try this awk script...
Code:
awk -F, 'BEGIN {OFS = ","}
{
   t = t ? (l != $1 ? t OFS $1 : t) : OFS $1
   x[$1] = x[$1] ? x[$1] OFS $NF : $NF
   l = $1
} END {
   print t
   for (i in x) print i, x[i]
}' file

Ok, so I was able to run this last night on a test file:
Code:
a,a,1
a,b,0.8
a,c,0.5
b,b,1
b,c,0.2
c,c,1

It returned:
Code:
,a,b,c
,0.5
,0.2
c,1

so the first line looks good, meaning 't' is correct but the subsequent line of code isn't quite right.

Running without the final x[i]:
Code:
awk -F, 'BEGIN {OFS = ","}
{
   t = t ? (l != $1 ? t OFS $1 : t) : OFS $1
   x[$1] = x[$1] ? x[$1] OFS $NF : $NF
   l = $1
} END {
   print t
   for (i in x) print i
}' file

returns:
Code:
,a,b,c
a
b
c

which is what we want for the first row and the first column.

Shamrock, do you have any thoughts on what we could do to the 'x' variable to create the correlation matrix?

Thanks very much for your help.

---------- Post updated at 10:11 AM ---------- Previous update was at 09:42 AM ----------

Quote:
Originally Posted by bakunin
How many rows/columns will this matrix have? Will there be empty matrix elements?
If we process our data in one batch, the matrix will have 5,122 rows and columns. If there are inherent line limitations that prevent this, we'll come up with ways to chunk the data into smaller batches. Presumably, there will be a handful of empty matrix elements where the original correlation analyses failed to converge.

Quote:
Originally Posted by bakunin
How to deal with multiple entries with the same indexes - add together, generate error, other?
There are no multiple entries with indexes of the same order - that is
Code:
x, y, value

will not repeat. However,
Code:
x, y, value
and
y, x, value

are both present if we do not reduce our data. shamrock and birei already provided means to remove these redundancies. Furthermore, shamrock's awk program in this thread that we are currently working on is more problematic if
Code:
x, y, value
y, x, value

are both present in the input data, even if the values are identical.

Quote:
Originally Posted by bakunin
My take would be to first put the lines in "normal form": the lines are of the form
Code:
keyA, keyB, value

As all keys are interchangeable, so that the following two entries would in effect be the same
Code:
x, y, value
y, x, value

and the matrix you are constructing is symetrical along the main diagonal the first step should be to sort the keys within the lines by some criteria, so that the first key in the line is consistently "lower or equal" or "higher or equal" than the second key in the line.

Than a simple sort over the first two fields will reduce the problem to a simple sort-of group-change: all lines with a given keyA will represent one row AND - because the matrix is symetrical - also one column.

I hope this helps.bakunin
Thanks bakunin, this does indeed help, as we will pursue something along these lines if we cannot get shamrock's awk program to make the correlation matrix.

Last edited by R3353; 07-11-2012 at 12:37 PM..
# 6  
Old 07-11-2012
I am not sure why you are getting incorrect results as the output on my system is exactly what you want...i changed the awk script slightly so it uses if else statements instead of the conditional operator...thogh i am not sure why that would fix things on your system but it helps if you copy and paste correctly...
Code:
awk -F, '{
    OFS = ","
    if (t) {
       if (l != $1)
          t = t OFS $1
    } else t = OFS $1
    x[$1] = x[$1] ? x[$1] OFS $NF : $NF
    l = $1
} END {
    print t
    for (i in x) print i, x[i]
}' file

This User Gave Thanks to shamrock For This Post:
# 7  
Old 07-11-2012
Quote:
Originally Posted by shamrock
...but it helps if you copy and paste correctly...
indeed Smilie

Which version of awk are you using?

Thanks.

Last edited by R3353; 07-11-2012 at 03:12 PM.. Reason: typo
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

2. Shell Programming and Scripting

Transpose matrix based on second column using awk

Hi, Is it possible to transpose the matrix like this using awk ? Many thanks in advance Input abc Name_1 0 abc Name_2 1 abc Name_3 2 abc Name_4 0.4 def Name_1 0 def Name_2 9 def Name_3 78 def Name_4 1 Output abc def Name_1 0 ... (4 Replies)
Discussion started by: quincyjones
4 Replies

3. Shell Programming and Scripting

Perl- creating a matrix from a 3 column file

Dear all, I'm new in perl scripting and I'm trying to creating a matrix from a 3 column file sorting data in a particular manner. In the final matrix I need to have the first column "IDs" on the header of the columns and the second column values on the header of each row. And the value fo the... (2 Replies)
Discussion started by: gabrysfe
2 Replies

4. Shell Programming and Scripting

Perl code to grep a particular column in CSV format

Hi I want to grep a column 6 & column 7 from a CSV Format file & then i have to find the difference between these columns as these both columns contains date & time in 7/7/2012 9:20 this format . So kindly help me out ASAP. But please kindly dis xls has to be converted in csv format as may... (5 Replies)
Discussion started by: Prateek@123
5 Replies

5. Shell Programming and Scripting

awk read column csv and search in other csv

hi, someone to know how can i read a specific column of csv file and search the value in other csv columns if exist the value in the second csv copy entire row with all field in a new csv file. i suppose that its possible using awk but i m not expertise thanks in advance (8 Replies)
Discussion started by: giankan
8 Replies

6. Shell Programming and Scripting

awk: Transpose csv row to column.

Hello, am I new to awk, and I am tryint to: INPUT FILE: "73423555","73423556","73423557","73423558","73423559" OUTPUT FILE: 73423555 73423556 73423557 73423558 73423559 My useless code so far: #!/bin/awk -F ',' BEGIN { i=0; } (8 Replies)
Discussion started by: drbiloukos
8 Replies

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

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

9. UNIX for Dummies Questions & Answers

How to extract one column from csv file in perl?

Hi everyone, i am new to perl programming, i have a problem in extracting single column from csv file. the column is the 20th column, please help me.. at present i use this code #!C:/perl/bin use warnings; use strict; my $file1 = $ARGV; open FILE1, "<$file1" or die "Can't... (13 Replies)
Discussion started by: kvth
13 Replies

10. Shell Programming and Scripting

correlation coefficient - Awk

Hi guys I have an input file with multiple columns and and rows. Is it possible to calculate correlation of certain value of certain No (For example x of S1 = 112) with all other values (for example start with x 112 corr a 3 of S1 = x-a 0.2 ) INPUT ******* No S1 S2 S3 S4 Sn a 3 ... (2 Replies)
Discussion started by: quincyjones
2 Replies
Login or Register to Ask a Question