How to keep x highest person correlation valued pairs of genes for each gene in a csv file in python?

user_9692220 0
Viewed 43

I have a csv file with thousands of rows of the following form:

geneA geneB value
a     b     5
a     c     3
a     d     7
b     c     8
b     d     1

I want to extract for each gene the two best matches with other genes in terms of highest values. For the former example the result for genes a and b would be:

a d 7
a b 5
b c 8
b a 5

Based on answer I got partly the result I wanted as it doesn't test both 1st and 2nd columns but only the first. So the result is not proper i.e. it does not take into consideration the row a b 5 when it checks for best values of b.

Any ideas what can I do?

pandas dataframe csv python
2 Answers

You can swap geneA, geneB and concatenate to the dataframe, then sort by value and use groupby().head():

total_df = pd.concat([df, df.rename(columns={'geneA':'geneB','geneB':'geneA'})])

(total_df.sort_values(['geneA','value'], ascending=[True,False])
   .groupby('geneA').head(2)
)

Output:

  geneA geneB  value
2     a     d      7
0     a     b      5
3     b     c      8
0     b     a      5
3     c     b      8
1     c     a      3
2     d     a      7
4     d     b      1
user_42384080

Below will give you Top 2 values for each gene based on value column.

In [1093]: A = df.groupby('geneA')['value'].nlargest(2).reset_index()
In [1096]: A.rename(columns={'geneA': 'gene'}, inplace=True)

In [1097]: B = df.groupby('geneB')['value'].nlargest(2).reset_index()    
In [1098]: B.rename(columns={'geneB': 'gene'}, inplace=True) 

In [1102]: d = A.append(B)

In [1111]: d.groupby('gene')['value'].nlargest(2).reset_index().drop('level_1', 1)
Out[1111]: 
  gene  value
0    a      7
1    a      5
2    b      8
3    b      5
4    c      8
5    c      3
6    d      7
7    d      1
user_58208140
Related