Untitled
unknown
plain_text
10 months ago
5.6 kB
3
Indexable
Never
def fn(group): group['W Avg Handle Minutes'] = group['Avg Handle Minutes'] * group['Answer'] / group['Answer'].sum() return group df['crude_answer_%'] = df['Offer'] / df['Answer'] df.loc[(df['crude_answer_%'] != df['Answer %'])] # seems to be solely NaNs issue #& [~df['Answer %'].isna()]] # roll up to 1 hour view rather than 30 minute view df['Interval Start'] = pd.to_datetime(df['Interval Start']) df['Interval Start HB'] = df['Interval Start'].dt.floor('h') # add weekday/weekend flag df['Day of the Week'] = df['Interval Start'].dt.day_name() df['Weekday/Weekend'] = 'Weekday' df.loc[df['Day of the Week'].isin(['Saturday','Sunday']), 'Weekday/Weekend'] = 'Weekend' df.loc[df['Weekday/Weekend'] == 'Weekend']['Day of the Week'].unique() # pull out floor hour df['Time of day'] = df['Interval Start HB'].dt.time # kill the implicit calculation columns as these will need to be redone once merged df.drop(['Answer %','Abandon %'],axis=1,inplace=True) df.fillna(0,inplace=True) # shouldn't be filling 0 for answer rates though, otherwise it causes problems # answer rate not true len(df.loc[df['Offer'] == 0.0]) / len(df) #70% of recorded 30 minute intervals, there are no calls # due to overnight periods df = df.loc[df['Offer'] != 0.0] df = df[['file','Interval Start','Interval Start HB','Day of the Week','Weekday/Weekend','Time of day','Queue Id','Queue Name','Offer','Answer','Abandon','Flow-Out','Hold','Transfer', 'Service Level %', 'ASA', 'Avg Wait','Avg Handle','Avg Talk','Avg Hold','Avg ACW','crude_answer_%']] # Offered interactions are either answered, abandoned, or flow-out interactions # https://help.mypurecloud.com/faqs/offered-metric-not-always-equal-answered-plus-abandoned-metrics/ # Converting to 60 minute intervals should fix a fair bit of this df['offer_sum_check'] = df['Answer'] + df['Abandon'] + df['Flow-Out'] df['Avg Handle Minutes'] = df['Avg Handle'] / (ms_in_an_hour) * 60 df.groupby(['file','Interval Start HB'], as_index=False).apply(fn) # this actually completely works - see Excel worked example- quick_test_of_logic # whatever the groupby chooses, it will weight the average on the relative sums of the column, with the group passed above # based on the groupby # e.g., Answer was the weight, an group passes each group of MM-YY dfr = df.groupby(['file','Interval Start HB'], as_index=False).apply(fn) dfl = dfr.groupby(['file','Interval Start HB'],as_index=False).agg({'Offer':'sum','Answer':'sum','Abandon':'sum','W Avg Handle Minutes':'sum'}) 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 # don't need Weekday/Weekend as no Weekends dfp = dfi.groupby(['file','Time of day'], as_index=False).agg({'Supply_Total_call_effort':'mean', 'Demand_Total_calls_offered':'mean'}) dfi.loc[dfi['file'] == i].groupby(['Time of day'], as_index=False).agg({'Supply_Total_call_effort':'mean', 'Demand_Total_calls_offered':'mean'}).transpose().reset_index() dfi['Imbalance'] = dfi['Demand_Total_calls_offered'] - dfi['Supply_Total_call_effort'] print(dfi['Demand_Total_calls_offered'].sum() / dfi['W Avg Handle Minutes'].sum()) dfi['file'] = dfi['file'].apply(lambda x: x.strip()) dfi['Time of dayc'] = dfi['Time of day'].astype('str') # Abandon %- calculated as # of abandons / # of offer # Answer % - calculated as # of answers / # of offer dfi['Abandon %'] = (dfi['Abandon'] / dfi['Offer']) dfi['Answer %'] = (dfi['Answer'] / dfi['Offer']) print(dfi.loc[dfi['Imbalance'] < 0]) dfi = dfi.reset_index() print(dfi[dfi.index.duplicated()]) dfi['Date'] = dfi['Interval Start HB'].dt.date dfi.loc[dfi['Imbalance'] < 0, 'Imbalance'] = 0 # dfi.to_excel('20231106_hod_output_info.xlsx') #dfi.loc[dfi['file'] == 'Smartsalary'].groupby(['Time of day'], as_index=False).agg({'Offer':'mean','Answer':'mean','Demand_Total_calls_offered':'mean', 'Imbalance':'mean', # 'Supply_Total_call_effort':'mean','Abandon %':'mean','W Avg Handle Minutes':'mean'}).transpose().reset_index().to_excel('20231106_hod_output_smartsalary_info_v3.xlsx') 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) dfc = dfi.groupby(['Time of day','file'], as_index=False).agg({'Demand_Total_calls_offered':'mean', 'Imbalance':'mean', 'Supply_Total_call_effort':'mean','Abandon dfc['Offer'].sum() * 21
Leave a Comment