pandas 按日期创建记录的列排名顺序

cbwuti44  于 2023-04-04  发布在  其他
关注(0)|答案(1)|浏览(118)

假设我有下面的数据框,其中包含员工,他们的合同类型(值可以是员工,承包商和代理)。此外,一个人可以有多个合同,正如您在下面的数据框示例中看到的那样:

ID      Name    Contract    Date
10000   John    Employee    2021-01-01
10000   John    Employee    2021-01-01
10000   John    Employee    2020-03-06
10000   John    Contractor  2021-01-03
10000   John    Agency      2021-01-01
10000   John    Contractor  2021-02-01
10001   Carmen  Employee    1988-06-03
10001   Carmen  Employee    2021-02-03
10001   Carmen  Contractor  2021-02-03
10002   Peter   Contractor  2021-02-03
10003   Fred    Employee    2020-01-05
10003   Fred    Employee    1988-06-03

我需要找到一种方法,根据每个唯一的ID和每个唯一的合同类型,它创建了一个名为“Order”的列,该列将对每个ID具有的每个合同类型进行排名,从最早的合同上的1开始。如果日期相同,则排名顺序无关紧要。这将导致以下数据框:

ID      Name    Contract    Date        Order
10000   John    Employee    2021-01-01  1
10000   John    Employee    2021-01-01  2
10000   John    Employee    2020-03-06  3
10000   John    Contractor  2021-01-03  2
10000   John    Agency      2021-01-01  1
10000   John    Contractor  2021-02-01  1
10001   Carmen  Employee    1988-06-03  1
10001   Carmen  Employee    2021-02-03  2
10001   Carmen  Contractor  2021-02-03  1
10002   Peter   Contractor  2021-02-03  1
10003   Fred    Employee    2020-01-05  2
10003   Fred    Employee    1988-06-03  1
2g32fytz

2g32fytz1#

下面是一个方法:首先使用df.sort_values()df进行排序,最后使用df.groupby().cumcount()

df['Data'] = pd.to_datetime(df['Date'])
df = df.sort_values(['ID', 'Contract', 'Date'])
df['Order'] = df.groupby(['ID', 'Contract']).cumcount() + 1
print(df)
ID    Name    Contract        Date       Data  Order
4   10000    John      Agency  2021-01-01 2021-01-01      1
3   10000    John  Contractor  2021-01-03 2021-01-03      1
5   10000    John  Contractor  2021-02-01 2021-02-01      2
2   10000    John    Employee  2020-03-06 2020-03-06      1
0   10000    John    Employee  2021-01-01 2021-01-01      2
1   10000    John    Employee  2021-01-01 2021-01-01      3
8   10001  Carmen  Contractor  2021-02-03 2021-02-03      1
6   10001  Carmen    Employee  1988-06-03 1988-06-03      1
7   10001  Carmen    Employee  2021-02-03 2021-02-03      2
9   10002   Peter  Contractor  2021-02-03 2021-02-03      1
11  10003    Fred    Employee  1988-06-03 1988-06-03      1
10  10003    Fred    Employee  2020-01-05 2020-01-05      2

相关问题