Untitled

filenm = "summary_statistics_broken_down_{0}_new_queues.xlsx".format(e)
with pd.ExcelWriter(filenm) as writer:
    for queuecat in df['Queue Category'].unique():
        print(queuecat)
        print('-----')

        dfv = df.loc[df['Queue Category'] == queuecat]
        dfv = dfv.groupby(['MM-YY'], as_index=False).apply(fn)
        dfv = dfv.groupby(['MM-YY'],as_index=False).agg({'Offer':'sum','Answer':'sum','Abandon':'sum','weighted_avg_ASA':'sum'})
        # then convert to minutes
        dfv['weighted_average_ASA_minutes'] = dfv['weighted_avg_ASA'] / (ms_in_an_hour) * 60

        # Abandon %- calculated as # of abandons / # of offer
        # Answer % - calculated as # of answers / # of offer
        dfv['Abandon %'] = 100 * (dfv['Abandon'] / dfv['Offer'])
        dfv['Answer %'] = 100 * (dfv['Answer'] / dfv['Offer'])
        months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", 
                  "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

        dfv['Month'] = dfv['MM-YY'].apply(lambda x: x[:3])
        dfv['Year'] =  dfv['MM-YY'].apply(lambda x: x[4:])

        dfv['Month'] = pd.Categorical(dfv['Month'], categories=months, ordered=True)

        output_dfs = []

        for i in ['Offer','Answer','Abandon','weighted_average_ASA_minutes','Abandon %','Answer %']:
            output = dfv.pivot(index='Year', columns=['Month'], values=i).reset_index()
            output = output[['Year','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']]
            output['Value'] = i
            output_dfs.append(output)

        ov_output = pd.concat(output_dfs)

        file_name = 'summary_statistics_{0}_new_queues.xlsx'.format(queuecat)
        ov_output.to_excel(writer,sheet_name=file_name,index=False)