Pandas DataFrame添加列标记调整行与插入行的技术需求

原始数据定义 #

以下是初始的Pandas DataFrame：

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')
print(df)

现有工时调整逻辑 #

已实现当个人累计工时达到38时，拆分触发阈值的行并转入剩余工时的逻辑：

s = df.groupby('number')['hours'].cumsum()
m = s.gt(38)
idx = m.groupby(df['number']).idxmax()
delta = s.groupby(df['number']).shift().rsub(38).fillna(s)
out = df.loc[df.index.repeat((df.index.isin(idx)&m)+1)]
out.loc[out.index.duplicated(keep='last'), 'hours'] = delta
out.loc[out.index.duplicated(), 'hours'] -= delta
print(out)

新增标记列的实现 #

现在需要添加一列adjusted，用x标记被调整的原始行和插入的新行，修改后的完整代码如下：

import pandas as pd
# 原始DataFrame定义
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.groupby('number')['hours'].cumsum()
m = s.gt(38)
idx = m.groupby(df['number']).idxmax()
delta = s.groupby(df['number']).shift().rsub(38).fillna(s)
out = df.loc[df.index.repeat((df.index.isin(idx)&m)+1)]
out.loc[out.index.duplicated(keep='last'), 'hours'] = delta
out.loc[out.index.duplicated(), 'hours'] -= delta

# 添加标记列
out['adjusted'] = ''
# 标记被调整的原始行（触发阈值的行）
adjusted_original_rows = out.index.isin(idx)
# 标记插入的新行（重复索引中的后出现项）
inserted_new_rows = out.index.duplicated(keep='first')
# 为两类行设置标记'x'
out.loc[adjusted_original_rows | inserted_new_rows, 'adjusted'] = 'x'

print(out)

逻辑说明 #

• adjusted_original_rows：筛选出触发工时阈值、被拆分的原始行，对应idx中的索引
• inserted_new_rows：筛选出通过repeat插入的新行，即out中重复出现的索引项（保留第一个原始行，后续重复行即为插入的新行）
• 对上述两类行的adjusted列赋值为x，其余行留空

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