python - Pandas - 通过优先级从列中减去

Question

简单说明我正在尝试做的事情:给定一组工资数据，其中包含列regular、over_time、double_time，lunch_break，我想按照指定的顺序从其他时间列中减去lunch_break列，直到lunch_break分钟用完。例如，lunch_break 分钟数应首先来自 regular，然后是 over_time，然后是 double_time。因此给出以下数据集:

import pandas as pd

payroll = [
    {'regular': 120, 'over_time':  60, 'double_time':   0, 'lunch_break': 30},
    {'regular':  15, 'over_time':  60, 'double_time':  30, 'lunch_break': 45},
    {'regular':  15, 'over_time':  15, 'double_time': 120, 'lunch_break': 45},
    {'regular':   0, 'over_time': 120, 'double_time': 120, 'lunch_break': 30}
]

payroll_df = pd.DataFrame(payroll)

我需要以下结果:

result = [
    {'regular': 90, 'over_time': 60, 'double_time':   0}, # 30 from reg
    {'regular':  0, 'over_time': 30, 'double_time':  30}, # 15 from reg, 30 from ovr
    {'regular':  0, 'over_time':  0, 'double_time': 105}, # 15 from reg, 15 from ovr, 15 from dbl
    {'regular':  0, 'over_time': 90, 'double_time': 120}, # 0 from reg, 30 from ovr
]

result_df = pd.DataFrame(result)

有没有好的方法使用 pandas 来做到这一点？

Answer 1

矢量化版本

df = payroll_df.copy()
df['regular'] = df.regular - df['lunch_break']
df.loc[df.regular < 0, 'over_time'] +=  df[df.regular < 0].regular
df.loc[df.over_time < 0, 'double_time'] += df[df.over_time < 0].over_time
df[df < 0] = 0
print(df.drop(columns='lunch_break'))

   regular  over_time  double_time
0       90         60            0
1        0         30           30
2        0          0          105
3        0         90          120