有一个HR列表:
Department Start End Salary per month
0 Sales 01.01.2020 30.04.2020 1000
1 People 01.05.2020 30.07.2022 3000
2 Marketing 01.02.2020 30.12.2099 3200
3 Sales 01.03.2020 01.08.2023 1200
4 Engineer 01.04.2020 30.12.2099 3500
使用Python我想知道如何按月(从第一个员工开始)和部门分组工资总额
我曾尝试按日期范围对工资进行分组,但不知道如何与部门分组并在每列中显示每个部门
month_starts = pd.date_range(data.start.min(),data.end.max(),freq = 'MS').to_numpy()
contained = np.logical_and(
np.greater_equal.outer(month_starts, data['start'].to_numpy()),
np.less.outer(month_starts,data['end'].to_numpy())
)
masked = np.where(contained, np.broadcast_to(data[['salary_per_month']].transpose(), contained.shape),np.nan)
df = pd.DataFrame(masked, index = month_starts).agg('sum',axis=1).to_frame().reset_index()
df.columns = ['month', 'total_cost']
1条答案
按热度按时间iq3niunx1#
您可以
repeat
您的行并递增句点,然后pivot_table
:输出: