按含重复项的列分组行块并排序的Pandas实现方案

输入数据 #

原始数据df2如下：

import pandas as pd

df2 = pd.DataFrame([['01p','seq01,Inj1','0.0825','446','Name','1'],
                  ['01p','seq01,Inj2','0.0560','446','Name','1'],
                  ['01p','seq01,Inj1','0.6789','445','IDZA','3'],
                  ['01p','seq01,Inj2','0.2323','445','IDZA','3'],
                  ['01p','seq01,Inj1','0.4678','359','IDA','2'],
                  ['01p','seq01,Inj2','0.0214','359','IDA','2'],
                  ['02p','seq02,Inj1','0.9999','335','BOC','5'],
                  ['02p','seq02,Inj2','0.1111','335','BOC','5'],
                  ['02p','seq02,Inj1','0.1234','446','FOO','4'],
                  ['02p','seq02,Inj2','0.5812','446','FOO','4']],
                  columns =['Acq. Name', 'Sample Name', 'A','M', 'C', 'Use_Index'])

目标结果 #

需要得到的目标数据框df如下：

df = pd.DataFrame([['01p','seq01,Inj1','0.0825','446','Name','1'],
                  ['01p','seq01,Inj2','0.0560','446','Name','1'],
                  ['01p','seq01,Inj1','0.4678','359','IDA','2'],
                  ['01p','seq01,Inj2','0.0214','359','IDA','2'],
                  ['01p','seq01,Inj1','0.6789','445','IDZA','3'],
                  ['01p','seq01,Inj2','0.2323','445','IDZA','3'],
                  ['02p','seq02,Inj1','0.1234','446','FOO','4'],
                  ['02p','seq02,Inj2','0.5812','446','FOO','4'],
                  ['02p','seq02,Inj1','0.9999','335','BOC','5'],
                  ['02p','seq02,Inj2','0.1111','335','BOC','5']],
                  columns =['Acq. Name', 'Sample Name', 'A','M', 'C', 'Use_Index'])

解决方案 #

通过按Use_Index分组，组内先按M升序、再按C降序排序，最后合并分组结果的方式实现需求，代码如下：

df3 = pd.DataFrame(columns=df2.columns)  # 创建空数据框

for group, data in df2.groupby('Use_Index'):
    sorted_data = data.sort_values(by=['M', 'C'], ascending=[True, False])
    sorted_data.reset_index(drop=True, inplace=True)
    sorted_data['Use_Index'] = group
    df3 = pd.concat([df3, sorted_data])

df4 = df3
print(df4)

内容的提问来源于stack exchange，提问作者Gina_G