python - 按仅与其中一组对应的值列对双分组 df 进行排序

Question

我有一个 pd.DataFrame，它是 .groupby(['Product', 'Salesperson']).sum() 的结果。我现在想按产品销售额(而不是按产品和销售人员的销售额)的顺序对产品列进行排序。然后在每个产品组内按每个销售人员的销售额进行排序。

这是我的起始df:

这是我想要的答案a1，其中包含一些说明以阐明订购过程:

下面是我的示例 df 和我想要的答案 a1 以及简单的断言测试。

import pandas as pd
from pandas.util.testing import assert_frame_equal
import numpy as np

s1 = {'Product': {0: 'Soap',
                  1: 'Soap',
                  2: 'Pencil',
                  3: 'Paper',
                  4: 'Paper',
                  5: 'Bags',
                  6: 'Bags'},
      'Salesperson': {0: 'Jack',
                      1: 'Jill',
                      2: 'Jill',
                      3: 'Jack',
                      4: 'Barry',
                      5: 'Barry',
                      6: 'Jack'},
      'Sales': {0: 40, 1: 20, 2: 500, 3: 50, 4: 10, 5: 450, 6: 100}}

a1 = {'Product': {0: 'Bags',
                  1: 'Bags',
                  2: 'Pencil',
                  3: 'Paper',
                  4: 'Paper',
                  5: 'Soap',
                  6: 'Soap'},
      'Salesperson': {0: 'Barry',
                      1: 'Jack',
                      2: 'Jill',
                      3: 'Jack',
                      4: 'Barry',
                      5: 'Jack',
                      6: 'Jill'},
      'Sales': {0: 450, 1: 100, 2: 500, 3: 50, 4: 10, 5: 40, 6: 20}}

df = pd.DataFrame(s1).set_index(['Product', 'Salesperson']) # sample
a1 = pd.DataFrame(a1).set_index(['Product', 'Salesperson']) # desired answer

print(df)
print(a1)

def my_sort(df):
    raise NotImplementedError

my_answer = my_sort(df)

assert_frame_equal(my_answer, a1)

Answer 1

您可以按'Product'groupby并创建虚拟列'sum'和'max' ，您可以通过排序来使用:

g = df.groupby('Product')['Sales']
df['sum'] = g.transform('sum')
df['max'] = g.transform('max')
df.sort_values(['sum', 'max', 'Sales'], ascending=False)\
.drop(['sum', 'max'], axis=1)

输出:

                     Sales
Product Salesperson       
Bags    Barry          450
        Jack           100
Pencil  Jill           500
Paper   Jack            50
        Barry           10
Soap    Jack            40
        Jill            20