In [1]:

#spreadsheets downloaded from:
#http://www.hscic.gov.uk/searchcatalogue?productid=18419&q=title%3a%22Prescribing+for+Diabetes%22&sort=Relevance&size=10&page=1#top
!ls *.xlsx

Child Poverty - South East_LA and ward data.xlsx
blah.xlsx
pres-diab-eng-200506-201314-apx1.xlsx
pres-diab-eng-200506-201415-apx1.xlsx
pres-diab-eng-200506-201415-apx2.xlsx
~$pres-diab-eng-200506-201415-apx1.xlsx

In [2]:

import pandas as pd

In [3]:

xl=pd.ExcelFile('pres-diab-eng-200506-201415-apx2.xlsx')
xl.sheet_names

Out[3]:

[u'title sheet',
 u'notes',
 u'BNF 6.1 Diabetes drugs',
 u'BNF 6.1.1 Insulins',
 u'BNF 6.1.2 Antidiabetic Drugs ']

In [4]:

tname='BNF 6.1 Diabetes drugs'
#dfx=xl.parse(tname,skiprows=4,thousands=',',index_col=0,na_values=['*','..'])

colnames=["Period Name","Prescriber Name","Prescriber Code","Prescription items","Net Ingredient Cost",
          "Cost per item","Prescription Items per cent","Net Ingredient Cost per cent"]

dbd=xl.parse(tname,skiprows=2,thousands=',',index_col=0,na_values=['*','..'])
dbd.reset_index(inplace=True)
dbd.columns=colnames

In [5]:

xl2=pd.ExcelFile('pres-diab-eng-200506-201415-apx1.xlsx')
xl2.sheet_names

Out[5]:

[u'Title sheet',
 u'Contents',
 u'table 1',
 u'figures 1a & 1b',
 u'table 2',
 u'table 3',
 u'figure 2',
 u'figure 3',
 u'figure 4',
 u'figure 5',
 u'figure 6',
 u'figure 7',
 u'figure 8',
 u'table 4',
 u'figure 9',
 u'figure 10',
 u'table 5',
 u'figure 11',
 u'figure 12',
 u'table 6',
 u'table 7',
 u'figure 13',
 u'figure 14',
 u'figure 15',
 u'figure 16',
 u'table 8',
 u'figure 17',
 u'figure 18',
 u'table 9',
 u'table 10',
 u'table 11',
 u'figure 19',
 u'figure 20',
 u'figure 21',
 u'table 12',
 u'figures 22',
 u'figures 23',
 u'figures 24',
 u'table 13',
 u'figures 25 & 27',
 u'figures 26 & 28']

In [6]:

cpc=xl2.parse('figure 3',skiprows=1,thousands=',',index_col=0,na_values=['*','..'])
cpc.reset_index(inplace=True)
cpc.columns=['CCG Code', 'CCG Name','NIC per patient']
iwcpc=cpc[cpc['CCG Name']=='NHS ISLE OF WIGHT CCG'].iloc[0].to_dict()
iwcpc

Out[6]:

{'CCG Code': u'10L',
 'CCG Name': u'NHS ISLE OF WIGHT CCG',
 'NIC per patient': 321.53064469347396}

In [16]:

dbd.head()

Out[16]:

	Period Name	Prescriber Name	Prescriber Code	Prescription items	Net Ingredient Cost	Cost per item	Prescription Items per cent	Net Ingredient Cost per cent
0	Financial 2014/2015	AIREDALE, WHARFEDALE AND CRAVEN CCG	02N00	125884	2420864.00	19.230911	0.040953	0.086801
1	Financial 2014/2015	ASHFORD CCG	09C00	97025	2054284.24	21.172731	0.043856	0.107553
2	Financial 2014/2015	AYLESBURY VALE CCG	10Y00	163806	2831291.46	17.284419	0.049094	0.113418
3	Financial 2014/2015	BARKING & DAGENHAM CCG	07L00	194815	3443295.96	17.674696	0.059243	0.132257
4	Financial 2014/2015	BARNET CCG	07M00	264974	5910006.26	22.304099	0.050393	0.114850

In [8]:

tname='BNF 6.1.1 Insulins'
dbi=xl.parse(tname,skiprows=2,thousands=',',index_col=0,na_values=['*','..'])
dbi.reset_index(inplace=True)
dbi.columns=colnames
dbi.head()

Out[8]:

	Period Name	Prescriber Name	Prescriber Code	Prescription items	Net Ingredient Cost	Cost per item	Prescription Items per cent	Net Ingredient Cost per cent
0	Financial 2014/2015	AIREDALE, WHARFEDALE AND CRAVEN CCG	02N00	18775	933208.36	49.704839	0.006108	0.033460
1	Financial 2014/2015	ASHFORD CCG	09C00	16032	841567.61	52.492990	0.007247	0.044061
2	Financial 2014/2015	AYLESBURY VALE CCG	10Y00	25298	1274002.06	50.359794	0.007582	0.051035
3	Financial 2014/2015	BARKING & DAGENHAM CCG	07L00	25997	1170402.57	45.020678	0.007906	0.044955
4	Financial 2014/2015	BARNET CCG	07M00	31452	1723613.21	54.801387	0.005982	0.033495

In [9]:

tname='BNF 6.1.2 Antidiabetic Drugs '
dba=xl.parse(tname,skiprows=2,thousands=',',index_col=0,na_values=['*','..'])
dba.reset_index(inplace=True)
dba.columns=colnames
dba.head()

Out[9]:

	Period Name	Prescriber Name	Prescriber Code	Prescription items	Net Ingredient Cost	Cost per item	Prescription Items per cent	Net Ingredient Cost per cent
0	Financial 2014/2015	AIREDALE, WHARFEDALE AND CRAVEN CCG	02N00	83286	850226.72	10.208519	0.027095	0.030485
1	Financial 2014/2015	ASHFORD CCG	09C00	65356	836331.24	12.796549	0.029542	0.043787
2	Financial 2014/2015	AYLESBURY VALE CCG	10Y00	111071	876914.75	7.895083	0.033289	0.035128
3	Financial 2014/2015	BARKING & DAGENHAM CCG	07L00	142809	1746764.05	12.231470	0.043428	0.067093
4	Financial 2014/2015	BARNET CCG	07M00	193465	3023510.13	15.628202	0.036794	0.058757

In [10]:

dba[dba['Prescriber Name']=='ISLE OF WIGHT CCG']

Out[10]:

	Period Name	Prescriber Name	Prescriber Code	Prescription items	Net Ingredient Cost	Cost per item	Prescription Items per cent	Net Ingredient Cost per cent
86	Financial 2014/2015	ISLE OF WIGHT CCG	10L00	93660	825682.54	8.815744	0.028662	0.033364

In [11]:

def itemgetter(df, key):
    row=df[df['Prescriber Name']==key].iloc[0]
    return row.to_dict()

In [12]:

d=itemgetter(dbd, "ISLE OF WIGHT CCG")
d

Out[12]:

{'Cost per item': 17.996963993273063,
 'Net Ingredient Cost': 2450628.5899999999,
 'Net Ingredient Cost per cent': 0.099023908644984171,
 'Period Name': u'Financial 2014/2015',
 'Prescriber Code': u'10L00',
 'Prescriber Name': u'ISLE OF WIGHT CCG',
 'Prescription Items per cent': 0.041670300642242065,
 'Prescription items': 136169.0}

In [13]:

cprc=xl2.parse('figures 1a & 1b',skiprows=2,thousands=',',index_col=0,na_values=['*','..'])
cprc.reset_index(inplace=True)
cprc.head()

Out[13]:

	PCT Code	PCT Name	2008/09	Unnamed: 3	PCT Code.1	PCT Name.1	2009/10	Unnamed: 7	PCT Code.2	PCT Name.2	...	PCT Name.3	2011/12	Unnamed: 15	CCG Code	CCG Name	2012/13	Unnamed: 19	CCG Code.1	CCG Name.1	2013/14
0	5M6	RICHMOND & TWICKENHAM	0.030416	NaN	5M6	RICHMOND & TWICKENHAM	0.031806	NaN	5M6	RICHMOND & TWICKENHAM	...	RICHMOND & TWICKENHAM	0.034367	NaN	08P	NHS RICHMOND CCG	0.035265	NaN	08P	NHS RICHMOND CCG	0.035186
1	5LA	KENSINGTON AND CHELSEA PCT	0.032870	NaN	5LA	KENSINGTON AND CHELSEA PCT	0.033799	NaN	5LA	KENSINGTON AND CHELSEA PCT	...	CAMDEN PRIMARY CARE TRUST	0.035803	NaN	07R	NHS CAMDEN CCG	0.038125	NaN	07R	NHS CAMDEN CCG	0.038234
2	5LC	WESTMINSTER PCT	0.034521	NaN	5K7	CAMDEN PRIMARY CARE TRUST	0.034997	NaN	5K7	CAMDEN PRIMARY CARE TRUST	...	KENSINGTON AND CHELSEA PCT	0.039285	NaN	09A	NHS CENTRAL LONDON (WESTMINSTER) CCG	0.041282	NaN	09A	NHS CENTRAL LONDON (WESTMINSTER) CCG	0.039484
3	5LQ	BRIGHTON AND HOVE CITY TEACHING PCT	0.035000	NaN	5LC	WESTMINSTER PCT	0.035712	NaN	5LQ	BRIGHTON AND HOVE CITY TEACHING PCT	...	BRIGHTON AND HOVE CITY TEACHING PCT	0.039881	NaN	09D	NHS BRIGHTON AND HOVE CCG	0.041648	NaN	09D	NHS BRIGHTON AND HOVE CCG	0.041408
4	5LG	WANDSWORTH PCT	0.036340	NaN	5LQ	BRIGHTON AND HOVE CITY TEACHING PCT	0.036621	NaN	5LC	WESTMINSTER PCT	...	WANDSWORTH PCT	0.042280	NaN	08X	NHS WANDSWORTH CCG	0.042448	NaN	08X	NHS WANDSWORTH CCG	0.041957

5 rows × 23 columns

In [14]:

iwprev=cprc[cprc['CCG Code.1']==iwcpc['CCG Code']][['CCG Code.1','CCG Name.1','2013/14']].iloc[0].to_dict()
iwprev

Out[14]:

{u'2013/14': 0.06432428330136418,
 u'CCG Code.1': u'10L',
 u'CCG Name.1': u'NHS ISLE OF WIGHT CCG'}

In [20]:

#IW is part of Q70 and Eng
#Figure 2: Prevalence of diabetes 2013/14 and proportion of items and Net Ingredient Cost accounted for by Drugs for Diabetes in 2014/15, by area team in England
nprc=xl2.parse('figure 2',skiprows=2,thousands=',',index_col=0,na_values=['*','..'])
nprc.reset_index(inplace=True)
nprc.columns=['AT code','AT Name','Diabetes Prevalence per cent','Items','NIC']
nprc.head()

Out[20]:

	AT code	AT Name	Diabetes Prevalence per cent	Items	NIC
0	Q69	Thames Valley	0.051867	0.047211	0.106439
1	Q68	Surrey & Sussex	0.054150	0.039920	0.098065
2	Q65	Bristol, North Som, Som & S Glos	0.055774	0.040145	0.099432
3	Q70	Wessex	0.057028	0.040200	0.098493
4	Q58	Hertfordshire & South Midlands	0.057667	0.045314	0.100960

In [32]:

#Really should look up the AT Code based on the CCG code?
region=nprc[nprc['AT code']=='Q70'].iloc[0].to_dict()
region

Out[32]:

{'AT Name': u'Wessex',
 'AT code': u'Q70',
 'Diabetes Prevalence per cent': 0.057027688727637339,
 'Items': 0.040199634874282228,
 'NIC': 0.098492587498059864}

In [22]:

country=nprc[nprc['AT code']=='Eng'].iloc[0].to_dict()
country

Out[22]:

{'AT Name': u'England',
 'AT code': u'Eng',
 'Diabetes Prevalence per cent': 0.062,
 'Items': 0.044515646682664771,
 'NIC': 0.099787215533502865}

In [36]:

#Thousands separator
def c(amount):
    return '{:,}'.format(amount)

d=itemgetter(dbd, "ISLE OF WIGHT CCG")
d

txt="Figures recently published by the HSCIC show that for the reporting period {period}, ".format(period=d["Period Name"])
txt=txt+"the total Net Ingredient Costs (NIC) for prescribed diabetes drugs was £{tpc}, ".format(tpc="{0:,.2f}".format(d['Net Ingredient Cost']))
txt=txt+"representing {npc} of overall Net Ingredient Costs. ".format(npc="{0:.2f}%".format(100*d['Net Ingredient Cost per cent']))
txt=txt+"The {ccg} prescribed {items} diabetes drugs items, ".format(ccg=d['Prescriber Name'], items=c(int(d['Prescription items'])))
txt=txt+"representing {ipc} of all prescribed items. ".format(ipc="{0:.2f}%".format(100*d['Prescription Items per cent']))
txt=txt+"The average net ingredient cost (NIC) was £{apc} per item. ".format(apc="{0:.2f}".format(d['Cost per item']))

txt=txt+"This compares to {rpci} of items ({rpct} of total NIC) in the {r} ({rc}) region and {npci} of items ({npct} of total NIC) in {c}. ".format(c=country['AT Name'],
                                                                                                                                  rc=region['AT code'],
                                                                                                                                  r=region['AT Name'],
                                                                                                                                 rpci= "{0:.2f}%".format(100*region['Items']),
                                                                                                                                 rpct= "{0:.2f}%".format(100*region['NIC']),
                                                                                                                                 npci="{0:.2f}%".format(100*country['Items']),
                                                                                                                                 npct="{0:.2f}%".format(100*country['NIC']))

d=itemgetter(dbi, "ISLE OF WIGHT CCG")
d
txt=txt+'\n\n'+"Of the total diabetes drugs prescribed, Insulins accounted for {ti} items at a total NIC of £{tnic} (£{cpi} per item (on average), {opc} of overall prescriptions, {cpc} of total NIC) ".format(ti="{:,.0f}".format(d['Prescription items']),
        tnic="{0:,.2f}".format(d['Net Ingredient Cost']),
        cpi="{0:.2f}".format(d['Cost per item']),
        opc="{0:.2f}%".format(100*d['Prescription Items per cent']),
        cpc="{0:.2f}%".format(100*d['Net Ingredient Cost per cent'])
       )

d=itemgetter(dba, "ISLE OF WIGHT CCG")
d
txt=txt+"and Antidiabetic Drugs accounted for {ti} items at a total NIC of £{tnic} (£{cpi} per item (on average), {opc} of overall prescriptions, {cpc} of total NIC).".format(ti="{:,.0f}".format(d['Prescription items']),
        tnic="{:,.2f}".format(d['Net Ingredient Cost']),
        cpi="{0:.2f}".format(d['Cost per item']),
        opc="{0:.2f}%".format(100*d['Prescription Items per cent']),
        cpc="{0:.2f}%".format(100*d['Net Ingredient Cost per cent'])
       )

txt = txt+'\n\n'+"For the {n}, the NIC in 2014/15 per patient on the QOF diabetes register in 2013/14 was £{cc}. ".format(n=iwcpc['CCG Name'],
                                                                                                   cc="{0:,.2f}".format(iwcpc['NIC per patient']))
p='2013/14'
txt= txt+"The QOF prevalence of diabetes, aged 17+, for the {ccg} in {p} was {prev}%. ".format(ccg=iwprev['CCG Name.1'],p=p, prev="{0:.2f}".format(100*iwprev[p]))
txt=txt+"This compares to a prevalence rate of {rp} in {r} and {cp} across {c}.".format(c=country['AT Name'],
                                                                                      r=region['AT Name'],
                                                                                      cp="{0:.2f}%".format(100*region['Diabetes Prevalence per cent']),
                                                                                      rp="{0:.2f}%".format(100*country['Diabetes Prevalence per cent']))
print(txt)

Figures recently published by the HSCIC show that for the reporting period Financial 2014/2015, the total Net Ingredient Costs (NIC) for prescribed diabetes drugs was £2,450,628.59, representing 9.90% of overall Net Ingredient Costs. The ISLE OF WIGHT CCG prescribed 136,169 diabetes drugs items, representing 4.17% of all prescribed items. The average net ingredient cost (NIC) was £18.00 per item. This compares to 4.02% of items (9.85% of total NIC) in the Wessex (Q70) region and 4.45% of items (9.98% of total NIC) in England. 

Of the total diabetes drugs prescribed, Insulins accounted for 21,170 items at a total NIC of £1,013,676.82 (£47.88 per item (on average), 0.65% of overall prescriptions, 4.10% of total NIC) and Antidiabetic Drugs accounted for 93,660 items at a total NIC of £825,682.54 (£8.82 per item (on average), 2.87% of overall prescriptions, 3.34% of total NIC).

For the NHS ISLE OF WIGHT CCG, the NIC in 2014/15 per patient on the QOF diabetes register in 2013/14 was £321.53. The QOF prevalence of diabetes, aged 17+, for the NHS ISLE OF WIGHT CCG in 2013/14 was 6.43%. This compares to a prevalence rate of 6.20% in Wessex and 5.70% across England.

In [ ]: