获取数据框中列名的排名

Question

我将客户ID与他们最常购买的表演类型进行对比：

Genre            Jazz     Dance     Music  Theatre
Customer                                        
100000000001           0      3         1        2
100000000002           0      1         6        2
100000000003           0      3        13        4
100000000004           0      5         4        1
100000000005           1     10        16       14

我想要的结果是根据排名添加列名：

Genre            Jazz     Dance     Music  Theatre          Rank1          Rank2          Rank3          Rank4
Customer                                         
100000000001           0      3         1        2          Dance        Theatre          Music           Jazz
100000000002           0      1         6        2          Music        Theatre          Dance           Jazz
100000000003           0      3        13        4          Music        Theatre          Dance           Jazz
100000000004           0      5         4        1          Dance          Music        Theatre           Jazz
100000000005           1     10        16       14          Music        Theatre          Dance           Jazz

我已经查找了一些线程，但是我能找到的最接近的东西是idxmax。然而，这只给了我Rank1

谁能帮我得到我需要的结果

非常感谢

丹尼斯

Answer 1

让我们试试stack、cumcount和sort_values

s = df.stack().sort_values(ascending=False).groupby(level=0).cumcount() + 1
s1 = (s.reset_index(1)
    .set_index(0, append=True)
    .unstack(1)
    .add_prefix("Rank")
    
    )
s1.columns = s1.columns.get_level_values(1)

然后加入你的客户类型索引

df.join(s1)

---

                 Jazz  Dance  Music  Theatre  Rank1    Rank2    Rank3 Rank4
Customer_Genre                                                            
100000000001       0      3      1        2  Dance  Theatre    Music  Jazz
100000000002       0      1      6        2  Music  Theatre    Dance  Jazz
100000000003       0      3     13        4  Music  Theatre    Dance  Jazz
100000000004       0      5      4        1  Dance    Music  Theatre  Jazz
100000000005       1     10     16       14  Music  Theatre    Dance  Jazz

Answer 2

使用：

i = np.argsort(df.to_numpy() * -1, axis=1)
r = pd.DataFrame(df.columns[i], index=df.index, columns=range(1, i.shape[1] + 1)) 
df = df.join(r.add_prefix('Rank'))

详细信息：

使用^{}沿axis=1获取将按降序对体裁进行排序的索引i

print(i)
array([[1, 3, 2, 0],
       [2, 3, 1, 0],
       [2, 3, 1, 0],
       [1, 2, 3, 0],
       [2, 3, 1, 0]])

从沿着索引i（即df.columns[i]）获取的数据帧df列创建一个新的数据帧r，然后使用^{}将数据帧r与df连接起来：

print(df)
              Jazz  Dance  Music  Theatre  Rank1    Rank2    Rank3 Rank4
Customer                                                                
100000000001     0      3      1        2  Dance  Theatre    Music  Jazz
100000000002     0      1      6        2  Music  Theatre    Dance  Jazz
100000000003     0      3     13        4  Music  Theatre    Dance  Jazz
100000000004     0      5      4        1  Dance    Music  Theatre  Jazz
100000000005     1     10     16       14  Music  Theatre    Dance  Jazz

Answer 3

试试这个：

dfp = (df.rank(ascending=False, axis=1).stack()
         .astype(int).rename('rank').reset_index(level=1))
df.assign(**dfp.set_index('rank', append=True)['Genre'].unstack().add_prefix('Rank'))

输出：

Genre         Jazz  Dance  Music  Theatre  Rank1    Rank2    Rank3 Rank4
Customer                                                                
100000000001     0      3      1        2  Dance  Theatre    Music  Jazz
100000000002     0      1      6        2  Music  Theatre    Dance  Jazz
100000000003     0      3     13        4  Music  Theatre    Dance  Jazz
100000000004     0      5      4        1  Dance    Music  Theatre  Jazz
100000000005     1     10     16       14  Music  Theatre    Dance  Jazz

使用rank并重塑数据帧，然后使用assign连接回原始数据帧