Pandas按小时规则拆分DataFrame行并添加标记列需求

需求说明 #

• 行拆分规则：
  1. 若hours值处于**[10,12]区间**：将原行hours改为10，插入一行复制行，填入剩余小时数（原hours-10）
  2. 若hours值大于12：将原行hours改为10，插入两行复制行，分别填入2小时和剩余小时数（原hours-12）
• 标记列规则：
  • 规则1中插入的剩余小时行，标记为ATU1
  • 规则2中插入的剩余小时行，标记为BHY3
  • 其他行（未拆分的原行、拆分出的非剩余小时行）标记为空字符串

原始代码（未实现标记列） #

import pandas as pd
df = pd.DataFrame()
df['number'] = (651,651,651,4267,4267,4267,4267,4267,4267,4267,8806,8806,8806,6841,6841,6841,6841)
df['name']=('Alex','Alex','Alex','Ankit','Ankit','Ankit','Ankit','Ankit','Ankit','Ankit','Abhishek','Abhishek','Abhishek','Blake','Blake','Blake','Blake')
df['hours']=(8.25,7.5,7.5,7.5,14,12,15,11,6.5,14,15,15,13.5,8,8,8,8)
df['loc']=('Nar','SCC','RSL','UNIT-C','UNIT-C','UNIT-C','UNIT-C','UNIT-C','UNIT-C','UNIT-C','UNI','UNI','UNI','UNKING','UNKING','UNKING','UNKING')
s =  df[df['hours'] < 10]
s1 = df[df['hours'] > 12]
s2 = df[df['hours'].between(10, 12)]

df = pd.concat([
s,
s1.assign(hours=10), 
s1.assign(hours=2), 
s1.assign(hours=s1['hours'] - 12),
s2.assign(hours=10),
s2.assign(hours=s2['hours'] - 10)]).sort_index(kind='stable', ignore_index=True)

print(df)

优化后代码（含标记列实现） #

import pandas as pd

# 构造原始数据集
df = pd.DataFrame({
    'number': (651,651,651,4267,4267,4267,4267,4267,4267,4267,8806,8806,8806,6841,6841,6841,6841),
    'name': ('Alex','Alex','Alex','Ankit','Ankit','Ankit','Ankit','Ankit','Ankit','Ankit','Abhishek','Abhishek','Abhishek','Blake','Blake','Blake','Blake'),
    'hours': (8.25,7.5,7.5,7.5,14,12,15,11,6.5,14,15,15,13.5,8,8,8,8),
    'loc': ('Nar','SCC','RSL','UNIT-C','UNIT-C','UNIT-C','UNIT-C','UNIT-C','UNIT-C','UNIT-C','UNI','UNI','UNI','UNKING','UNKING','UNKING','UNKING')
})

# 拆分数据组：小于10的行无需拆分，直接加空标记
s = df[df['hours'] < 10].assign(mark='')
# 大于12的行组
s1 = df[df['hours'] > 12]
# [10,12]区间的行组
s2 = df[df['hours'].between(10, 12)]

# 处理大于12的行：拆分出三行
s1_part1 = s1.assign(hours=10, mark='')       # 原行改10，标记空
s1_part2 = s1.assign(hours=2, mark='')        # 插入2小时行，标记空
s1_part3 = s1.assign(hours=s1['hours']-12, mark='BHY3')  # 剩余小时行，标记BHY3

# 处理[10,12]区间的行：拆分出两行
s2_part1 = s2.assign(hours=10, mark='')       # 原行改10，标记空
s2_part2 = s2.assign(hours=s2['hours']-10, mark='ATU1')  # 剩余小时行，标记ATU1

# 合并所有行，保持原始顺序并重置索引
result_df = pd.concat(
    [s, s1_part1, s1_part2, s1_part3, s2_part1, s2_part2],
    sort=False
).sort_index(kind='stable', ignore_index=True)

# 打印结果
print(result_df)

输出示例（部分） #

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