如何为Pandas DataFrame按组给指定位次记录分配自定义标签？

步骤1：转换时间列类型 #

首先需要将Date_Time_1和Date_Time_2转为datetime类型，否则无法正确比较时间大小：

import pandas as pd

# 转换时间列格式
df['Date_Time_1'] = pd.to_datetime(df['Date_Time_1'], format='%d-%m-%Y %H:%M')
df['Date_Time_2'] = pd.to_datetime(df['Date_Time_2'], format='%d-%m-%Y %H:%M')

步骤2：按ID+Group生成时间排名 #

对每个ID下的每个Group，按Date_Time_2从小到大排序，生成从1开始的排名：

# 分组后生成时间排名，method='first'避免相同时间的排名冲突
df['rank'] = df.groupby(['ID', 'Group'])['Date_Time_2'].rank(method='first', ascending=True).astype(int)

步骤3：设置基础标签 #

根据Group和排名匹配对应的标签：

# 初始化新列为空值
df['New_Column'] = pd.NA

# Group A：时间最小的记录（排名1）标"First"
df.loc[(df['Group'] == 'A') & (df['rank'] == 1), 'New_Column'] = 'First'

# Group B：时间第二小的记录（排名2）标"Second"
df.loc[(df['Group'] == 'B') & (df['rank'] == 2), 'New_Column'] = 'Second'

# Group C：时间第三小的记录（排名3）标"Third"
df.loc[(df['Group'] == 'C') & (df['rank'] == 3), 'New_Column'] = 'Third'

步骤4：处理Group C的特殊标签 #

对每个ID下的Group C，按Date_Time_1分组，给不同时间起点的记录分配Third_A、Third_B等递进标签：

# 给Group C内的不同Date_Time_1分组生成序号
c_groups = df[df['Group'] == 'C'].groupby(['ID', 'Date_Time_1'])
df.loc[df['Group'] == 'C', 'dt1_rank'] = c_groups.ngroup()

# 给未标基础标签的Group C记录分配特殊标签
mask = (df['Group'] == 'C') & (df['New_Column'].isna())
df.loc[mask, 'New_Column'] = 'Third_' + df.loc[mask, 'dt1_rank'].apply(lambda x: chr(ord('A') + x))

# 清理临时列
df.drop(['rank', 'dt1_rank'], axis=1, inplace=True)

完整代码 #

import pandas as pd

# 转换时间列
df['Date_Time_1'] = pd.to_datetime(df['Date_Time_1'], format='%d-%m-%Y %H:%M')
df['Date_Time_2'] = pd.to_datetime(df['Date_Time_2'], format='%d-%m-%Y %H:%M')

# 生成组内时间排名
df['rank'] = df.groupby(['ID', 'Group'])['Date_Time_2'].rank(method='first', ascending=True).astype(int)

# 设置基础标签
df['New_Column'] = pd.NA
df.loc[(df['Group'] == 'A') & (df['rank'] == 1), 'New_Column'] = 'First'
df.loc[(df['Group'] == 'B') & (df['rank'] == 2), 'New_Column'] = 'Second'
df.loc[(df['Group'] == 'C') & (df['rank'] == 3), 'New_Column'] = 'Third'

# 处理Group C的特殊标签
c_groups = df[df['Group'] == 'C'].groupby(['ID', 'Date_Time_1'])
df.loc[df['Group'] == 'C', 'dt1_rank'] = c_groups.ngroup()

mask = (df['Group'] == 'C') & (df['New_Column'].isna())
df.loc[mask, 'New_Column'] = 'Third_' + df.loc[mask, 'dt1_rank'].apply(lambda x: chr(ord('A') + x))

# 清理临时列
df.drop(['rank', 'dt1_rank'], axis=1, inplace=True)

代码说明 #

• 用groupby+rank实现每个ID分组内的时间排序，避免了循环的低效问题
• 布尔索引直接匹配规则赋值，逻辑清晰易维护
• Group C的特殊标签通过ngroup()给不同时间起点分组编号，再转换为字母后缀，自动生成递进标签

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