Convert correlation dataframe to dictionary {key = (sample_x,sample_y), value = correlation}

Question

So I'm trying to do something very similar to these 2 posts but there a few differences. One, I don't want a csv file so no csv module and I want to get it done in Python not R.

Convert Adjacency Matrix into Edgelist (csv file) for Cytoscape

Convert adjacency matrix to a csv file

Input:

AF001         AF002     AF003   AF004  AF005
AF001  1.000000  0.000000e+00  0.000000  0.0000      0
AF002  0.374449  1.000000e+00  0.000000  0.0000      0
AF003  0.000347  1.173926e-05  1.000000  0.0000      0
AF004  0.001030  1.494282e-07  0.174526  1.0000      0
AF005  0.001183  1.216664e-06  0.238497  0.7557      1

Output:

{('AF002', 'AF003'): 1.17392596672424e-05, ('AF004', 'AF005'): 0.75570008659397792, ('AF001', 'AF002'): 0.374449352805868, ('AF001', 'AF003'): 0.00034743953114899502, ('AF002', 'AF005'): 1.2166642639889999e-06, ('AF002', 'AF004'): 1.49428208843456e-07, ('AF003', 'AF004'): 0.17452569907144502, ('AF001', 'AF004'): 0.00103026903356954, ('AF003', 'AF005'): 0.238497202355299, ('AF001', 'AF005'): 0.0011830950375467401}

I have a nonredundant correlation matrix DF_sCorr which has been processed from a redundant matrix using np.tril (code courteously provided by @jezrael).

I want to collapse it into a dictionary where the key is a sorted tuple of the samples {i.e. key=tuple(sorted([row_sample,col_sample])} and the value to be their value. I wrote an example function below sif_format which generates a dictionary analogous to a sif format (3 column table in the format sample_x interaction_value sample_y) but it is taking a very long time.

I thought the best way to organize this type of table would be a dictionary. I feel like there is a much more effecient way to do this. Possibly w/ only processing boolean values? The real dataset I'm using is ~7000x7000

I'm not sure if there is a function w/in numpy,pandas,scipy, or networkx that can do this type of processing effeciently.

import pandas as pd
import numpy as np

A_sCorr = np.array([[0.999999999999999, 0.0, 0.0, 0.0, 0.0], [0.374449352805868, 1.0, 0.0, 0.0, 0.0], [0.00034743953114899502, 1.17392596672424e-05, 1.0, 0.0, 0.0], [0.00103026903356954, 1.49428208843456e-07, 0.17452569907144502, 1.0, 0.0], [0.0011830950375467401, 1.2166642639889999e-06, 0.238497202355299, 0.75570008659397792, 1.0]])
sampleLabels = ['AF001', 'AF002', 'AF003', 'AF004', 'AF005']

DF_sCorr = pd.DataFrame(A_sCorr,columns=sampleLabels, index=sampleLabels)

#AF001         AF002     AF003   AF004  AF005
#AF001  1.000000  0.000000e+00  0.000000  0.0000      0
#AF002  0.374449  1.000000e+00  0.000000  0.0000      0
#AF003  0.000347  1.173926e-05  1.000000  0.0000      0
#AF004  0.001030  1.494282e-07  0.174526  1.0000      0
#AF005  0.001183  1.216664e-06  0.238497  0.7557      1

def sif_format(DF_var):
    D_interaction_corr = {}
    n,m = DF_var.shape 
    for i in range(n):
        row_sample = DF_var.index[i]
        for j in range(m):
            col_sample = DF_var.columns[j]
            if row_sample != col_sample:
                D_interaction_corr[tuple(sorted([row_sample,col_sample]))] = DF_var.iloc[i,j]
            if j==i:
                break
    return(D_interaction_corr)

D_interaction_corr = sif_format(DF_sCorr)  
{('AF002', 'AF003'): 1.17392596672424e-05, ('AF004', 'AF005'): 0.75570008659397792, ('AF001', 'AF002'): 0.374449352805868, ('AF001', 'AF003'): 0.00034743953114899502, ('AF002', 'AF005'): 1.2166642639889999e-06, ('AF002', 'AF004'): 1.49428208843456e-07, ('AF003', 'AF004'): 0.17452569907144502, ('AF001', 'AF004'): 0.00103026903356954, ('AF003', 'AF005'): 0.238497202355299, ('AF001', 'AF005'): 0.0011830950375467401}

DataFrame.to_dict() won't work for this

DF_sCorr.to_dict()
{'AF002': {'AF002': 1.0, 'AF003': 1.17392596672424e-05, 'AF001': 0.0, 'AF004': 1.49428208843456e-07, 'AF005': 1.2166642639889999e-06}, 'AF003': {'AF002': 0.0, 'AF003': 1.0, 'AF001': 0.0, 'AF004': 0.17452569907144502, 'AF005': 0.238497202355299}, 'AF001': {'AF002': 0.374449352805868, 'AF003': 0.00034743953114899502, 'AF001': 0.999999999999999, 'AF004': 0.00103026903356954, 'AF005': 0.0011830950375467401}, 'AF004': {'AF002': 0.0, 'AF003': 0.0, 'AF001': 0.0, 'AF004': 1.0, 'AF005': 0.75570008659397792}, 'AF005': {'AF002': 0.0, 'AF003': 0.0, 'AF001': 0.0, 'AF004': 0.0, 'AF005': 1.0}}

maxymoo · Accepted Answer · 2016-02-25T02:40:08.583

In pandas this is unstack; if you call to_dict on the resulting you will get what you want:

In [43]: df.unstack()
Out[43]:
AF001  AF001    1.000000e+00
       AF002    3.744490e-01
       AF003    3.470000e-04
       AF004    1.030000e-03
       AF005    1.183000e-03
AF002  AF001    0.000000e+00
       AF002    1.000000e+00
       AF003    1.173926e-05
       AF004    1.494282e-07
       AF005    1.216664e-06
AF003  AF001    0.000000e+00
       AF002    0.000000e+00
       AF003    1.000000e+00
       AF004    1.745260e-01
       AF005    2.384970e-01
AF004  AF001    0.000000e+00
       AF002    0.000000e+00
       AF003    0.000000e+00
       AF004    1.000000e+00
       AF005    7.557000e-01
AF005  AF001    0.000000e+00
       AF002    0.000000e+00
       AF003    0.000000e+00
       AF004    0.000000e+00
       AF005    1.000000e+00
dtype: float64

In [45]: df.unstack().to_dict()
Out[45]:
{('AF001', 'AF001'): 1.0,
 ('AF001', 'AF002'): 0.37444899999999998,
 ('AF001', 'AF003'): 0.00034700000000000003,
 ('AF001', 'AF004'): 0.0010300000000000001,
 ('AF001', 'AF005'): 0.001183,
 ('AF002', 'AF001'): 0.0,
 ('AF002', 'AF002'): 1.0,
 ('AF002', 'AF003'): 1.1739260000000002e-05,
 ('AF002', 'AF004'): 1.4942820000000002e-07,
 ('AF002', 'AF005'): 1.216664e-06,
 ('AF003', 'AF001'): 0.0,
 ('AF003', 'AF002'): 0.0,
 ('AF003', 'AF003'): 1.0,
 ('AF003', 'AF004'): 0.17452599999999999,
 ('AF003', 'AF005'): 0.23849699999999999,
 ('AF004', 'AF001'): 0.0,
 ('AF004', 'AF002'): 0.0,
 ('AF004', 'AF003'): 0.0,
 ('AF004', 'AF004'): 1.0,
 ('AF004', 'AF005'): 0.75570000000000004,
 ('AF005', 'AF001'): 0.0,
 ('AF005', 'AF002'): 0.0,
 ('AF005', 'AF003'): 0.0,
 ('AF005', 'AF004'): 0.0,
 ('AF005', 'AF005'): 1.0}

If you want to do additional manipulations on your data, for example removing diagnoals or nulls, you'll need to call reset_index on the unstacked dataframe, and then use whatever transformations you need,

u = df.unstack().reset_index()
u[(u['level_0']!=u['level_1']) & (u[0] != 0)]

   level_0 level_1             0
1    AF001   AF002  3.744490e-01
2    AF001   AF003  3.470000e-04
3    AF001   AF004  1.030000e-03
4    AF001   AF005  1.183000e-03
7    AF002   AF003  1.173926e-05
8    AF002   AF004  1.494282e-07
9    AF002   AF005  1.216664e-06
13   AF003   AF004  1.745260e-01
14   AF003   AF005  2.384970e-01
19   AF004   AF005  7.557000e-01

hey @maxymoo this is an awesome method. It almost creates the output but still contains the self-pairs (e.g. "AF001","AF001" = 1.0) and also contains the empty values too. For example, ('AF001', 'AF002'): 0.37444 so there shouldn't be ('AF002', 'AF001'): 0.0 — O.rka, Feb 25 '16 at 02:15
Just pop them in the end combined with a map or simply a for loop, in both cases on a list that you can easily with a list constructor. — John Smith, Feb 25 '16 at 02:54

Convert correlation dataframe to dictionary {key = (sample_x,sample_y), value = correlation}

Input:

Output:

DataFrame.to_dict() won't work for this

1 Answers1