Untitled
unknown
plain_text
a year ago
1.8 kB
3
Indexable
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)
Editor is loading...