#!/usr/bin/env python # coding: utf-8 # 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 get_ipython().system('ls *.xlsx') # In[2]: import pandas as pd # In[3]: xl=pd.ExcelFile('pres-diab-eng-200506-201415-apx2.xlsx') xl.sheet_names # 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 # 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 # In[16]: dbd.head() # 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() # 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() # In[10]: dba[dba['Prescriber Name']=='ISLE OF WIGHT CCG'] # 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 # In[13]: cprc=xl2.parse('figures 1a & 1b',skiprows=2,thousands=',',index_col=0,na_values=['*','..']) cprc.reset_index(inplace=True) cprc.head() # In[14]: iwprev=cprc[cprc['CCG Code.1']==iwcpc['CCG Code']][['CCG Code.1','CCG Name.1','2013/14']].iloc[0].to_dict() iwprev # 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() # 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 # In[22]: country=nprc[nprc['AT code']=='Eng'].iloc[0].to_dict() country # 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) # In[ ]: