Geek Answers Handbook

How to sort a Dataframe using ocurrences in a column in Python (pandas)

I am trying to create a dataframe from my data (estimates between chemicals and proteins) using pandas in python.

I want the proteins that have the most cases to be displayed in my framework first, so I sorted my data earlier. But when I do a dataframe, it does not get the expected result.

Here is an example of my data:

chemicals   prots   scores
CID000000006    10116.ENSRNOP00000003921    196
CID000000051    10116.ENSRNOP00000003921    246
CID000000085    10116.ENSRNOP00000003921    196
CID000000119    10116.ENSRNOP00000003921    247
CID000000134    10116.ENSRNOP00000008952    159
CID000000135    10116.ENSRNOP00000008952    157
CID000000174    10116.ENSRNOP00000008952    439
CID000000175    10116.ENSRNOP00000001021    858
CID000000177    10116.ENSRNOP00000004027    760

As you can see, "10116.ENSRNOP00000003921" is the protein with the largest amount in my data.

So, I would like to get something like:

             10116.ENSRNOP00000003921     10116.ENSRNOP00000008952  
CID000000006   196                 
CID000000051   246 
CID000000085   196 
CID000000119   247 
CID000000134                                  159   
CID000000135                                  157   
CID000000174                                  439

And here is my code:

import pandas as pd

df_rat= pd.read_csv("dt_matrix_rat.csv",sep="\t", header=True)
df_rat.columns = ['chemicals','proteins','scores']
df_rat1 = df_rat.pivot(index='chemicals', columns='proteins', values='scores')

df_rat1.to_csv("rat_matrix.csv", sep='\t', index=True  )
+4
source share
2 answers

, sort_values notnull sum cols. Lasy subset:

df1 = df.pivot(index='chemicals', columns='proteins', values='scores')

cols = df1.notnull().sum(axis=0).sort_values(ascending=False).index
print cols
Index([u'10116.ENSRNOP00000003921', u'10116.ENSRNOP00000008952',
       u'10116.ENSRNOP00000004027', u'10116.ENSRNOP00000001021'],
      dtype='object', name=u'proteins')

print df1[cols]
proteins      10116.ENSRNOP00000003921  10116.ENSRNOP00000008952  \
chemicals                                                          
CID000000006                     196.0                       NaN   
CID000000051                     246.0                       NaN   
CID000000085                     196.0                       NaN   
CID000000119                     247.0                       NaN   
CID000000134                       NaN                     159.0   
CID000000135                       NaN                     157.0   
CID000000174                       NaN                     439.0   
CID000000175                       NaN                       NaN   
CID000000177                       NaN                       NaN   

proteins      10116.ENSRNOP00000004027  10116.ENSRNOP00000001021  
chemicals                                                         
CID000000006                       NaN                       NaN  
CID000000051                       NaN                       NaN  
CID000000085                       NaN                       NaN  
CID000000119                       NaN                       NaN  
CID000000134                       NaN                       NaN  
CID000000135                       NaN                       NaN  
CID000000174                       NaN                       NaN  
CID000000175                       NaN                     858.0  
CID000000177                     760.0                       NaN

reindex_axis:

print df1.reindex_axis(cols, axis=1)
proteins      10116.ENSRNOP00000003921  10116.ENSRNOP00000008952  \
chemicals                                                          
CID000000006                     196.0                       NaN   
CID000000051                     246.0                       NaN   
CID000000085                     196.0                       NaN   
CID000000119                     247.0                       NaN   
CID000000134                       NaN                     159.0   
CID000000135                       NaN                     157.0   
CID000000174                       NaN                     439.0   
CID000000175                       NaN                       NaN   
CID000000177                       NaN                       NaN   

proteins      10116.ENSRNOP00000004027  10116.ENSRNOP00000001021  
chemicals                                                         
CID000000006                       NaN                       NaN  
CID000000051                       NaN                       NaN  
CID000000085                       NaN                       NaN  
CID000000119                       NaN                       NaN  
CID000000134                       NaN                       NaN  
CID000000135                       NaN                       NaN  
CID000000174                       NaN                       NaN  
CID000000175                       NaN                     858.0  
CID000000177                     760.0                       NaN
0

@jezrael ( ):

In [136]: df
Out[136]:
      chemicals                     prots  scores
0  CID000000006  10116.ENSRNOP00000003921     196
1  CID000000051  10116.ENSRNOP00000003921     246
2  CID000000085  10116.ENSRNOP00000003921     196
3  CID000000119  10116.ENSRNOP00000003921     247
4  CID000000134  10116.ENSRNOP00000008952     159
5  CID000000135  10116.ENSRNOP00000008952     157
6  CID000000174  10116.ENSRNOP00000008952     439
7  CID000000175  10116.ENSRNOP00000001021     858
8  CID000000177  10116.ENSRNOP00000004027     760


In [169]: df.groupby('prots').sum().sort('scores', ascending=False)
Out[169]:
                          scores
prots
10116.ENSRNOP00000003921     885
10116.ENSRNOP00000001021     858
10116.ENSRNOP00000004027     760
10116.ENSRNOP00000008952     755

( pandas) .sort() .sort_values():

In [170]: cols = df.groupby('prots').sum().sort_values(by='scores', ascending=False).index

In [171]: cols
Out[171]:
Index(['10116.ENSRNOP00000003921', '10116.ENSRNOP00000001021',
       '10116.ENSRNOP00000004027', '10116.ENSRNOP00000008952'],
      dtype='object', name='prots')

:

In [175]: df_rat1 = df.pivot(index='chemicals', columns='prots', values='scores').fillna('')

In [176]: df_rat1 = df_rat1[cols]

In [177]: df_rat1
Out[177]:
prots        10116.ENSRNOP00000003921 10116.ENSRNOP00000001021 10116.ENSRNOP00000004027 10116.ENSRNOP00000008952
chemicals
CID000000006                      196
CID000000051                      246
CID000000085                      196
CID000000119                      247
CID000000134                                                                                                 159
CID000000135                                                                                                 157
CID000000174                                                                                                 439
CID000000175                                               858
CID000000177                                                                        760
0

More articles:


All Articles