python - 根据另一列的值创建具有最大索引数的新列

Question

我有一个包含两列的 DataFrame:“商品名称”及其“整体销售额”。我需要制作另一列，其中将包含从 1、2、3 中计算出的最大销售额的索引...其中 1 是最大数字，2 是第二大数字，依此类推。

希望你能帮助我。

我的数据框:

lst = [['Keyboard1', 1860], ['Keyboard2', 1650], ['Keyboard3', 900], ['Keyboard4', 1230], ['Keyboard5', 1150], ['Keyboard6', 1345],
                   ['Mouse1', 3100], ['Mouse2', 2900], ['Mouse3', 3050], ['Mouse4', 2750], ['Mouse5', 4100], ['Mouse6', 3910]]

df = pd.DataFrame(lst, columns = ['Goods', 'Sales'])

       Goods    Sales
0   Keyboard1   1860
1   Keyboard2   1650
2   Keyboard3   900
3   Keyboard4   1230
4   Keyboard5   1150
5   Keyboard6   1345
6   Mouse1  3100
7   Mouse2  2900
8   Mouse3  3050
9   Mouse4  2750
10  Mouse5  4100
11  Mouse6  3910

我正在尝试使用这段代码:

import pandas as pd
import numpy as np

df = df.sort_values('Sales', ascending = False)
df['Largest'] = np.arange(len(df))+1

但是我得到了所有商品的最大值索引，我需要分别得到每种商品的最大值索引。我的结果:

        Goods  Sales  Largest
10     Mouse5    4100        1
11     Mouse6    3910        2
6      Mouse1    3100        3
8      Mouse3    3050        4
7      Mouse2    2900        5
9      Mouse4    2750        6
1   Keyboard2    1860        7
0   Keyboard1    1650        8
5   Keyboard6    1345        9
3   Keyboard4    1230       10
4   Keyboard5    1150       11
2   Keyboard3     900       12

这是我需要的输出:

        Goods  Sales  Largest
10     Mouse5    4100        1
11     Mouse6    3910        2
6      Mouse1    3100        3
8      Mouse3    3050        4
7      Mouse2    2900        5
9      Mouse4    2750        6
1   Keyboard2    1860        1
0   Keyboard1    1650        2
5   Keyboard6    1345        3
3   Keyboard4    1230        4
4   Keyboard5    1150        5
2   Keyboard3     900        6

Answer 1

只是做:

# remove any number of groups at the end
df['goods_group'] = df['Goods'].str.replace('\d+$', '')

# sort by the new column and sales
df = df.sort_values(['goods_group', 'Sales'], ascending=False)

# create largest column
df['largest'] = df.groupby('goods_group').cumcount() + 1

# drop the new column
res = df.drop('goods_group', 1)
print(res)

输出

        Goods  Sales  largest
10     Mouse5   4100        1
11     Mouse6   3910        2
6      Mouse1   3100        3
8      Mouse3   3050        4
7      Mouse2   2900        5
9      Mouse4   2750        6
0   Keyboard1   1860        1
1   Keyboard2   1650        2
5   Keyboard6   1345        3
3   Keyboard4   1230        4
4   Keyboard5   1150        5
2   Keyboard3    900        6