以一种不寻常的方式在熊猫中排序两列

Question

我有一个类似的数据框架：

col_1 | serial_number | remaining time
B     |            17 | 02:45
A     |            02 | 02:00
C     |            25 | 03:30
A     |            03 | 03:00
B     |            12 | 03:45
C     |            07 | 01:30
C     |            89 | 02:30
B     |            45 | 01:45
A     |            01 | 01:00

我想对剩余时间进行排序(顶部最低(，但应该将COL_1分组。这是以上数据框的外观。

col_1 | serial_number | remaining time
A     |            01 | 01:00
A     |            02 | 02:00
A     |            03 | 03:00
C     |            07 | 01:30
C     |            89 | 02:30
C     |            25 | 03:30
B     |            45 | 01:45
B     |            17 | 02:45
B     |            12 | 03:45

我目前无法做到。首先，我需要按剩余时间进行排序，然后将COL_1组在一起。

Answer 1

您可以使用groupby transform

df['remaining_time_group_min'] = df['remaining time'].groupby(df.col_1).transform(min)

创建一个新列，该列对于每个元素的任何成员的最小时间。

之后，正如Jdehesa在评论中所建议的那样，您可以使用

df.sort_values(['remaining_time_group_min', 'remaining time']) 

Answer 2

创建col_1列的有序订购的订购的订单：

cats = df.sort_values(['remaining time'])['col_1'].unique()
print (cats)
['A' 'C' 'B']
df['col_1'] = pd.Categorical(df['col_1'], categories=cats, ordered=True)
df = df.sort_values([ 'col_1', 'remaining time'])
print (df)
  col_1 serial_number remaining time
8     A            01          01:00
1     A            02          02:00
3     A            03          03:00
5     C            07          01:30
6     C            89          02:30
2     C            25          03:30
7     B            45          01:45
0     B            17          02:45
4     B            12          03:45

详细信息：

print (df['col_1'])
8    A
1    A
3    A
5    C
6    C
2    C
7    B
0    B
4    B
Name: col_1, dtype: category
Categories (3, object): [A < C < B]

Answer 3

创建您的DataFrame：

import pandas as pd
df = pd.DataFrame({'col_1':['B','A','C','A','B','C','C','B','A'], 'serial_number':[17,2,25,3,12,7,89,45,1],'remaining time':['02:45','02:00','03:30','03:00','03:45','01:30','02:30','01:45','01:00']})
df['remaining time'] = pd.to_datetime(df['remaining time'])
df['remaining time'] = [time.time() for time in df['remaining time']]

添加一个新列，每组最短时间：

df['min time'] =  df.groupby('col_1')['remaining time'].transform(min)

然后按这个新的最小时间进行排序，然后按实际时间剩下的时间：

>>> df.sort_values(by=['min time','remaining time'])
  col_1 remaining time  serial_number  min time
8     A       01:00:00              1  01:00:00
1     A       02:00:00              2  01:00:00
3     A       03:00:00              3  01:00:00
5     C       01:30:00              7  01:30:00
6     C       02:30:00             89  01:30:00
2     C       03:30:00             25  01:30:00
7     B       01:45:00             45  01:45:00
0     B       02:45:00             17  01:45:00
4     B       03:45:00             12  01:45:00