如何使用索引对数据框中的值进行排名？ [英] How to rank values in a dataframe with indexes?

问题描述

我有以下主题文档概率矩阵的数据框，第一行是文本文件的名称。

I have the following dataframe of topic document probablity matrix with the first row being names of text files.

                       1                      2            ...                               80                      81
0                778.txt                856.txt           ...                          831.txt                 850.txt
1   0.002735042735042732  0.0054700854700846634           ...              0.01641025640567632  4.2490294446698094e-09
2  2.146512500161246e-28  8.006312700113502e-16           ...            4.580074538571013e-12     0.02017093592191074

其中第0列的值为（0.0，1.0）表示分别对主题1和主题2的索引。对每列进行排序（降序）后

where column 0 with values (0.0, 1.0) represents index for topic 1 and 2 respectively.After sorting each column(decsending)

def rank_topics_by_probability(self):
    df = df.astype(float)
    df2 = pd.DataFrame(-np.sort(-df, axis=0), columns=df.columns, index=df.index)
    return df2

我得到以下输出

     0             1         2             3         4       ...             77            78            79            80            81
1  1.0  2.735043e-03  0.004329  6.837607e-04  0.010396      ...       0.005399  1.367521e-02  1.641026e-02  1.641023e-02  2.017094e-02
2  0.0  9.941665e-23  0.001141  1.915713e-20  0.000202      ...       0.000071  6.475626e-10  1.816478e-12  2.494897e-08  1.366020e-10

我想显示每个文档的主题文档等级矩阵，例如

I want to display topic-document rank matrix for each document such as

     id      topic-rank
    778        1, 0
    856        1, 0
    835        0, 1
    786        0, 1
        ...
    831        0, 1
    850        1, 0

对于ID为1的文档我将1分配为0，因为主题2的概率大于主题1的依此类推。
该怎么做？
编辑过的问题的样本数据只是数据框的head（）值。

For document with id 1 I assigned 1, 0 because probability of topic 2 is greater than topic 1 and so on. What is the way to do that? Sample data for the edited question these are only the head() values of the dataframe.

      id                                               text
0  15623  Y:\n1. Ran preliminary experiments to set para...
1  15625  Scrum Minutes- Hersheys\nPresent: Eyob, Masres...
2  15627  Present: Eyob, Masresha,  Zelalem\nhersheys:\n...
3  15628  **********************************************...
4  15629  Scrum Minutes- Hersheys\nPresent: Eyob, Masres...

推荐答案

将 argsort降序使用用于具有DataFrame构造函数的位置：

Use argsort with descending ordering for positions with DataFrame constructor:

#create index by first column and transpose
df2 = df.set_index(0).T

arr = df2.columns.values[(-df2.values).argsort()]
df2 = pd.DataFrame({'id': df2.index, 
                    'score1': arr[:, 0].astype(int),
                    'score2': arr[:, 1].astype(int)})
print (df2)
   id  score1  score2
0   1       1       0
1   2       1       0
2   3       0       1
3   4       0       1
4  77       1       0
5  78       1       0
6  79       0       1
7  80       1       0
8  81       0       1

编辑：

df2 = df.set_index(0).T

arr = df2.columns.values[(-df2.values).argsort()]

score = (pd.Series(arr[:, 0].astype(int).astype(str)) + ', ' + 
         pd.Series(arr[:, 1].astype(int).astype(str)))
df2 = pd.DataFrame({'id': df2.index, 
                    'score': score})
print (df2)
   id score
0   1  1, 0
1   2  1, 0
2   3  0, 1
3   4  0, 1
4  77  1, 0
5  78  1, 0
6  79  0, 1
7  80  1, 0
8  81  0, 1

EDIT1：

df2 = df.T.set_index(0).astype(float)
print (df2)
                    1             2
0                                  
778.txt  2.735043e-03  2.146513e-28
856.txt  5.470085e-03  8.006313e-16
831.txt  1.641026e-02  4.580075e-12
850.txt  4.249029e-09  2.017094e-02


arr = (-df2.values).argsort()

score = (pd.Series(arr[:, 0].astype(str)) + ', ' + 
         pd.Series(arr[:, 1].astype(str)))
df2 = pd.DataFrame({'id': df2.index.str.replace('\.txt',''), 
                    'score': score})
print (df2)
    id score
0  778  0, 1
1  856  0, 1
2  831  0, 1
3  850  1, 0

这篇关于如何使用索引对数据框中的值进行排名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！