如何在Pandas中提取每位教师前3条记录并添加出现顺序列

需要对给定的Pandas DataFrame按created列的时间顺序提取每位Teacher的前3条记录，并新增appearance_order列标记该教师的出现顺序。尝试过groupby方法，但不知道如何保留前3条实例。

数据示例代码 #

import pandas as pd
from datetime import datetime

data = pd.DataFrame(
                     {'id': [1, 2, 3, 4, 5, 6, 7, 8, 9,],
                     'Section': ['A', 'A', 'A', 'B', 'B', 'B',
                                 'C', 'C', 'C'],
                     'Teacher': ['Kakashi', 'Kakashi', 'Iruka',
                                 'Kakashi', 'Kakashi', 'Kakashi',
                                 'Iruka', 'Iruka', 'Guy'],
                     'created': [datetime(2022,7,11), datetime(2022, 7, 12), datetime(2022, 7, 13), 
                                 datetime(2022, 7, 14), datetime(2022, 7, 15), datetime(2022, 7, 16), 
                                 datetime(2022, 7, 17), datetime(2022, 7, 18), datetime(2022, 7, 19), ]})

期望输出 #

id  Section Teacher created               appearance_order
1   A       Kakashi 2022-07-11            1
2   A       Kakashi 2022-07-12            2
4   B       Kakashi 2022-07-14            3
3   A       Iruka   2022-07-13            1
7   C       Iruka   2022-07-17            2
8   C       Iruka   2022-07-18            3
9   C       Guy     2022-07-19            1

解决步骤 #

1. 按时间排序：先确保数据按created列的时间顺序排列，这是后续分组统计的基础

   data_sorted = data.sort_values(by='created')
   

2. 分组生成出现序号：按Teacher分组，用cumcount()生成每位教师的出现顺序（该方法从0开始计数，需加1转为从1开始）

   data_sorted['appearance_order'] = data_sorted.groupby('Teacher').cumcount() + 1
   

3. 过滤前3条记录：筛选出appearance_order≤3的行，得到每位教师的前3条时间顺序记录

   result = data_sorted[data_sorted['appearance_order'] <= 3]
   

4. 可选：重置索引：如果需要规整索引，可执行以下代码（不影响数据内容）

   result = result.reset_index(drop=True)
   

完整运行代码 #

import pandas as pd
from datetime import datetime

data = pd.DataFrame(
                     {'id': [1, 2, 3, 4, 5, 6, 7, 8, 9,],
                     'Section': ['A', 'A', 'A', 'B', 'B', 'B',
                                 'C', 'C', 'C'],
                     'Teacher': ['Kakashi', 'Kakashi', 'Iruka',
                                 'Kakashi', 'Kakashi', 'Kakashi',
                                 'Iruka', 'Iruka', 'Guy'],
                     'created': [datetime(2022,7,11), datetime(2022, 7, 12), datetime(2022, 7, 13), 
                                 datetime(2022, 7, 14), datetime(2022, 7, 15), datetime(2022, 7, 16), 
                                 datetime(2022, 7, 17), datetime(2022, 7, 18), datetime(2022, 7, 19), ]})

# 按时间排序
data_sorted = data.sort_values(by='created')
# 添加出现序号
data_sorted['appearance_order'] = data_sorted.groupby('Teacher').cumcount() + 1
# 筛选前3条记录
result = data_sorted[data_sorted['appearance_order'] <= 3]

print(result)

最终输出结果 #

id Section    Teacher     created  appearance_order
0   1       A    Kakashi  2022-07-11                 1
1   2       A    Kakashi  2022-07-12                 2
2   3       A      Iruka  2022-07-13                 1
3   4       B    Kakashi  2022-07-14                 3
6   7       C      Iruka  2022-07-17                 2
7   8       C      Iruka  2022-07-18                 3
8   9       C        Guy  2022-07-19                 1

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