Untitled

mail@pastecode.io avatar
unknown
plain_text
8 months ago
1.2 kB
2
Indexable
Never
dfv = df.loc[~df['Queue Name'].isin(queues_to_remove)].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)

file_nm = "summary_statistics_overall_{0}_new_queues.xlsx".format(e)
with pd.ExcelWriter(file_nm) as writer:
    for i in ['22','23']:
        dfz = dfv.loc[dfv['Year'] == i].sort_values(by='Month').transpose().reset_index()
        dfz.columns = dfz.iloc[-3]
        dfz.to_excel(writer,sheet_name=i,index=False)