python - 如何使用 Python 将每日数据转换为每周、每月和每季度

Question

我想使用 python 将每日数据转换为每周、每月和每季度的数据

输入

|Employee Name  |Subjects   |Billable   |Hours      |Date      
|Deepak     |Java           |Yes        |8      |01-03-2021
|Deepak     |Python         |No         |9      |02-03-2021
|Deepak     |SQL            |No         |6      |03-03-2021
|Deepak     |React          |Yes        |5      |03-03-2021
|Deepak     |Python         |No         |8      |04-03-2021
|Deepak     |Java           |Yes        |9      |05-03-2021
|Deepak     |.Net           |No         |9      |06-03-2021
|Deepak     |SQL            |No         |9      |07-03-2021             
|Ravi       |SQL            |No         |5      |01-03-2021
|Ravi       |Python         |Yes        |4      |01-03-2021
|Ravi       |Java           |Yes        |2      |02-03-2021
|Ravi       |.Net           |No         |8      |03-03-2021
|Ravi       |React          |Yes        |7      |04-03-2021
|Ravi       |.Net           |No         |5      |05-03-2021
|Ravi       |React          |Yes        |9      |06-03-2021
|Ravi       |React          |Yes        |9      |07-03-2021

应在输出文件中添加列：Utilization、Billable Utilization、Non-Billable Utilization

利用率列的计算：利用率 = df[小时]/9*100

-> 如果计费列值为“是”” - 创建列作为计费利用率

列中的计费利用率计算：计费利用率 = df[小时]/9 * 100

-> 如果计费列值为“NO”” - 将列创建为不可计费利用率

列中非计费利用率的计算：非计费利用率 = df[Hours]/9*100

代码

df = df.pivot_table(index=['Date', 'Employee Name'], columns='Billable').reset_index()
df.columns = ['Date', 'Employee Name', 'Non-Billable', 'Billable']

# compute utilization
available=9
df['Non-Billable'] = df['Non-Billable'].div(available).mul(100).round(2)
df['Billable'] = df['Billable'].div(available).mul(100).round(2)
df['Utilization'] = df[['Billable', 'Non-Billable']].fillna(0).sum(axis=1)

#Modify the columns order
df = df[['Date', 'Employee Name', 'Utilization', 'Billable', 'Non-Billable']]

我的输出

预期产出

标签： pythonpandasdataframepandas.excelwriter