I am very, very new to Python and am playing around with how I would calculate an NPS score.

The calculation is:

(count of scores 9-10/total count of scores 0-10) - (count of scores0-6/total count of scores 0-10) for each council.

Data Frame I am using:

enter image description here

The NPS would need to be calculated for each council separately.This is my first post on here, hopefully it makes sense. If someone could point me in the right direction it would be much appreciated.

Cheers, Ben.

2

Best Answer


Assuming data is in data.csv:

import pandas as pdfrom collections import defaultdictdf = pd.read_csv('data.csv')high_nps = defaultdict(lambda: 0)low_nps = defaultdict(lambda: 0)high_nps.update(dict(df[df['NPS'] >= 9].groupby('CouncilName').count().reset_index()[['CouncilName', 'NPS']].values))low_nps.update(dict(df[df['NPS'] <= 6].groupby('CouncilName').count().reset_index()[['CouncilName', 'NPS']].values))total_nps = dict(df.groupby('CouncilName').count().reset_index()[['CouncilName', 'NPS']].values)nps_score = {council: (high_nps[council] - low_nps[council]) / float(total_nps[council]) for council in total_nps}print(nps_score)

Prints:

{'Council A': 0.0, 'Council B': -1.0, 'Council C': -1.0}
def npsForField(df,column,fid):nps={}# first make sure our column has numeric values:subject = pd.DataFrame(columns=[column],data=pd.to_numeric(df[df['field_id']==fid][column]))# calculate all NPS components:nps['total'] = subject[column].count()nps['detractors'] = subject[subject[column]<7][column].count()nps['passives'] = subject[(subject[column]>6) & (subject[column]<9)][column].count()nps['promoters'] = subject[subject>8][column].count()nps['nps'] = (nps['promoters'] - nps['detractors']) / nps['total']return nps

Then suppose you want to calculate the NPS for a column of df called answer, but only where df[df['field_id']==fid]. Call it like this:

npsForField(df, column='answer', fid='abc123')

Sample result:

{'total': 979,'detractors': 313,'passives': 291,'promoters': 375,'nps': 0.06332992849846783}