Untitled

df['Avg Handle Minutes'] = df['Avg Handle'] / (ms_in_an_hour) * 60
def fn(group):
    group['W Avg Handle Minutes'] = group['Avg Handle Minutes'] * group['Answer'] / group['Answer'].sum()
    return group
dfr = df.groupby(['file','Interval Start HB'], as_index=False).apply(fn)
dfi = dfr.groupby(['Interval Start HB','file'], as_index=False).agg({'Day of the Week':'first','Weekday/Weekend':'first',
                                                          'Time of day': 'first','W Avg Handle Minutes':'sum','Offer':'sum','Answer':'sum','Abandon':'sum','Flow-Out':'sum'
                                                             })
dfi['Supply_Total_call_effort'] = (dfi['W Avg Handle Minutes'] * dfi['Answer']) / 60
dfi['Demand_Total_calls_offered'] = (dfi['W Avg Handle Minutes'] * dfi['Offer']) / 60
dfi['Imbalance'] = dfi['Demand_Total_calls_offered'] - dfi['Supply_Total_call_effort']
dfi['file'] = dfi['file'].apply(lambda x: x.strip())
dfi['Time of dayc'] = dfi['Time of day'].astype('str')
dfi['Abandon %'] = (dfi['Abandon'] / dfi['Offer'])
dfi['Answer %'] = (dfi['Answer'] / dfi['Offer'])
dfi = dfi.reset_index()
dfi['Date'] = dfi['Interval Start HB'].dt.date
dfi.loc[dfi['Imbalance'] < 0, 'Imbalance'] = 0


output_file_nm = "{0}_hod_outputs_new_queues.xlsx".format(e)
with pd.ExcelWriter(output_file_nm) as writer:
    for i in dfi['file'].unique():
        print(i)
        dfw = dfi.loc[dfi['file'] == i].groupby(['Time of day'], as_index=False).agg({'Demand_Total_calls_offered':'mean', 'Imbalance':'mean',
                                                                                      'Supply_Total_call_effort':'mean','Abandon %':'mean'}).transpose().reset_index()

        dfw.to_excel(writer,sheet_name=i,index=False)