Untitled

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