4
\$\begingroup\$

This code basically calculates a few metrics based on numerators and denominators and then it bins the data into quintiles. This is a common operation that I perform for various data sets. My issue is that I always have to rename the metrics and columns.

I have a feeling that I could create functions to make this more general perhaps. The common operations I am doing are:

  1. % of total numerator
  2. delta from a target
  3. attainment (actual divided by the target)
  4. impact (the weighted delta from the target, how much a person is increasing or decreasing the total score)
  5. The quintiles (I have them labeled so the end-user understands what they are. These files are used in Excel for pivoting how the top and bottom employees perform)

I would like to have it more general: input a numerator and denominator. Instead of global variables, perhaps they could be input into a function?

csat_target =  0.75
fcr_target = 0.75
nps_target = 0.25
aht_target = 5.25

csat_weight = 0.30
fcr_weight = 0.30
nps_weight = 0.40
aht_weight = 0.00

df['fcr'] = df['final_hits'] / df['total_hits']
df['csat'] = df['csat_score_weight'] / df['survey_count']
df['nps'] = (df['promoters'] - df['detractors']) / df['survey_count']
df['aht'] =  (df['total_time'] / df['total_calls']) 
df['goal_attainment'] = ((df['nps'] / nps_target) * nps_weight) + ((df['csat'] / csat_target) * csat_weight) + ((df['fcr'] / fcr_target) * fcr_weight)

df['fcr_volume'] = df['total_hits'] / df['total_hits'].sum()
df['fcr_delta'] = df['fcr'] - fcr_target
df['fcr_impact'] = (df['fcr_delta'] * df['fcr_volume'])


df['survey_volume'] = df['survey_count'] / df['survey_count'].sum()
df['nps_delta'] = nps_target - df['nps']
df['nps_impact'] = df['nps_delta'] * df['survey_volume']
df['overall_nps_impact'] = np.where(df['survey_count'] > 0, df['nps_impact'], 0)


df['csat_delta'] = df['csat'] - csat_target
df['csat_impact'] = -(df['csat_delta'] * df['survey_volume'])
df['overall_csat_impact'] = np.where(df['survey_count'] > 0, df['csat_impact'], 0)

df.replace([np.inf, -np.inf], np.nan, inplace = True)

df['call_volume'] = df['total_calls'] / df['total_calls'].sum()
df['aht_delta'] = df['aht'] - aht_target
df['aht_impact'] = (df['aht_delta'] * df['call_volume'] )

df['fcr_weight'] = np.where(df['total_hits'] > 0, fcr_weight, 0)
df['nps_weight'] = np.where(df['survey_count'] > 0, nps_weight, 0)
df['csat_weight'] = np.where(df['survey_count'] > 0, csat_weight, 0)


df['fcr_dfile'] = pd.qcut(df['fcr_impact'], 5, labels = ["D1", "D2", "D3", "D4", "D5"])
df['nps_dfile'] =  pd.qcut(df['overall_nps_impact'] , 5, labels = ["D1", "D2", "D3", "D4", "D5"])
df['csat_dfile'] = pd.qcut(df['overall_csat_impact'] , 5, labels = ["D1", "D2", "D3", "D4", "D5"])
df['aht_impact_dfile'] = pd.qcut(df['aht_impact'], 5, labels = ["D1", "D2", "D3", "D4", "D5"])
df['aht_dfile'] = pd.qcut(df['aht'], 5, labels = ["D1", "D2", "D3", "D4", "D5"])
\$\endgroup\$
1
\$\begingroup\$

Yes, functions would be a splendid idea. However a lot of the calculations aren't repeated, so it's hard to see which parts could be factored out usefully.

Apart from that I'd suggest putting the *_target and *_weight variables into a dictionary or another data frame so that they can be referred to by name, making it more easy to use them from functions:

parameters = pandas.DataFrame({
    'target': [0.75, 0.75, 0.25, 5.25],
    'weight': [0.30, 0.30, 0.40, 0.00]
}, index = ['csat', 'fcr', 'nps', 'aht']).transpose()

With the data frame above it's easy to select just the parameters for "csat" for example, using parameters.csat.target or .weight.

That also makes certain queries more generic:

df['goal_attainment'] = sum(df[name] / parameters[name].target * parameters[name].weight
                            for name in parameters if name not in {"aht"})

A couple of frequently used queries can also be reused, e.g. df['survey_count'] should likely be cached, similarly for the comparisons:

survey_count = df['survey_count']
positive_survey_count = df['survey_count'] > 0

(Maybe take a closer look to the Pandas API, e.g. how the data could be structured with nested names and multi indexes.)

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.