Untitled

df.loc[df['Queue Category'] == 'Service and support functions', 'Queue Category'] = 'CS / Support'

df['BU_Lvl_2'] = df['file']

df.loc[~df['file'].str.contains('Smartleasing'), 'BU_Lvl_2'] = df.loc[df['file'] != 'Smartleasing']['file'] + ' ' + df.loc[df['file'] != 'Smartleasing']['Queue Category']

df['Avg Handle Hours'] = df['Avg Handle'] / ms_in_an_hour

def weighted_handle(group):
    group['W Avg Handle Hours'] = group['Avg Handle Hours'] * group['Answer'] / group['Answer'].sum()
    return group

dfq = df.groupby(['MM-YY','BU_Lvl_2'], as_index=False).apply(weighted_handle)
dfq = dfq.groupby(['MM-YY','BU_Lvl_2'],as_index=False).agg({'Offer':'sum','Answer':'sum','Abandon':'sum','Flow-Out':'sum','W Avg Handle Hours':'sum','Size':'first','file':'first'})
# then convert to minutes

# Abandon %- calculated as # of abandons / # of offer
# Answer % - calculated as # of answers / # of offer
dfq['Abandon %'] = 100 * (dfq['Abandon'] / dfq['Offer'])
dfq['Answer %'] = 100 * (dfq['Answer'] / dfq['Offer'])

dfq['Supply_Total_call_effort'] = dfq['W Avg Handle Hours'] * dfq['Answer']
dfq['Demand_Total_calls_offered'] = dfq['W Avg Handle Hours'] * dfq['Offer']
dfq['Monthly_gap_supply_demand'] = dfq['Demand_Total_calls_offered'] - dfq['Supply_Total_call_effort']

### yearly variant

dfqy = df.groupby(['Year','BU_Lvl_2'], as_index=False).apply(weighted_handle)
dfqy = dfqy.groupby(['Year','BU_Lvl_2'],as_index=False).agg({'Offer':'sum','Answer':'sum','Abandon':'sum','Flow-Out':'sum','W Avg Handle Hours':'sum','Size':'first','file':'first'})
# then convert to minutes

# Abandon %- calculated as # of abandons / # of offer
# Answer % - calculated as # of answers / # of offer
dfqy['Abandon %'] = 100 * (dfqy['Abandon'] / dfqy['Offer'])
dfqy['Answer %'] = 100 * (dfqy['Answer'] / dfqy['Offer'])

dfqy['Supply_Total_call_effort'] = dfqy['W Avg Handle Hours'] * dfqy['Answer']
dfqy['Demand_Total_calls_offered'] = dfqy['W Avg Handle Hours'] * dfqy['Offer']
dfqy['Monthly_gap_supply_demand'] = dfqy['Demand_Total_calls_offered'] - dfqy['Supply_Total_call_effort']

for x in ['22','23']:
    dfyr = dfq.loc[dfq['MM-YY'].str.contains(x)]
    print(dfyr['BU_Lvl_2'].unique())
    
    filelist = sorted(dfyr['BU_Lvl_2'].unique(),reverse=True)
    filelist += [filelist.pop(0)]
    
    files = filelist
    months = ["Jan-", "Feb-", "Mar-", "Apr-", "May-", "Jun-", "Jul-", "Aug-", "Sep-", "Oct-", "Nov-", "Dec-"]
    
    months = [s + x for s in months]

    dfyr['MM-YY'] = pd.Categorical(dfyr['MM-YY'], categories=months, ordered=True)

    dfyr['BU_Lvl_2'] = pd.Categorical(dfyr['BU_Lvl_2'], categories=files, ordered=True)
    
    dfyr = dfyr[['MM-YY','Abandon %','Monthly_gap_supply_demand','Size','BU_Lvl_2']]
    
    dfyr.loc[dfyr['BU_Lvl_2'] == 'Accesspay','BU_Lvl_2'] = dfyr.loc[dfyr['BU_Lvl_2'] == 'Accesspay'].apply(lambda x: x.replace('AccessPay','Accesspay'))
    dfyr = dfyr.sort_values(by=['BU_Lvl_2','MM-YY'])

    
    name = "monthly_supply_demand_gap_lvl_2_rec_{0}_new_queues.xlsx".format(x)
    dfyr.to_excel(name,index=False)