Skip to content

Instantly share code, notes, and snippets.

@psychemedia

psychemedia/diabetes_textualisation.ipynb

Last active September 8, 2015 13:10
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save psychemedia/75bba697b9b67bc848d2 to your computer and use it in GitHub Desktop.
Save psychemedia/75bba697b9b67bc848d2 to your computer and use it in GitHub Desktop.
Download ZIP
Sketching a handcrafted data2text report for diabetes prescribing.
Display the source blob
Display the rendered blob
Raw
diabetes_textualisation.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Child Poverty - South East_LA and ward data.xlsx\r\n",
"blah.xlsx\r\n",
"pres-diab-eng-200506-201314-apx1.xlsx\r\n",
"pres-diab-eng-200506-201415-apx1.xlsx\r\n",
"pres-diab-eng-200506-201415-apx2.xlsx\r\n",
"~$pres-diab-eng-200506-201415-apx1.xlsx\r\n"
]
}
],
"source": [
"#spreadsheets downloaded from:\n",
"#http://www.hscic.gov.uk/searchcatalogue?productid=18419&q=title%3a%22Prescribing+for+Diabetes%22&sort=Relevance&size=10&page=1#top\n",
"!ls *.xlsx"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"activity": false,
"collapsed": true
},
"outputs": [],
"source": [
"import pandas as pd"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[u'title sheet',\n",
" u'notes',\n",
" u'BNF 6.1 Diabetes drugs',\n",
" u'BNF 6.1.1 Insulins',\n",
" u'BNF 6.1.2 Antidiabetic Drugs ']"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"xl=pd.ExcelFile('pres-diab-eng-200506-201415-apx2.xlsx')\n",
"xl.sheet_names"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [],
"source": [
"tname='BNF 6.1 Diabetes drugs'\n",
"#dfx=xl.parse(tname,skiprows=4,thousands=',',index_col=0,na_values=['*','..'])\n",
"\n",
"colnames=[\"Period Name\",\"Prescriber Name\",\"Prescriber Code\",\"Prescription items\",\"Net Ingredient Cost\",\n",
" \"Cost per item\",\"Prescription Items per cent\",\"Net Ingredient Cost per cent\"]\n",
"\n",
"dbd=xl.parse(tname,skiprows=2,thousands=',',index_col=0,na_values=['*','..'])\n",
"dbd.reset_index(inplace=True)\n",
"dbd.columns=colnames\n"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[u'Title sheet',\n",
" u'Contents',\n",
" u'table 1',\n",
" u'figures 1a & 1b',\n",
" u'table 2',\n",
" u'table 3',\n",
" u'figure 2',\n",
" u'figure 3',\n",
" u'figure 4',\n",
" u'figure 5',\n",
" u'figure 6',\n",
" u'figure 7',\n",
" u'figure 8',\n",
" u'table 4',\n",
" u'figure 9',\n",
" u'figure 10',\n",
" u'table 5',\n",
" u'figure 11',\n",
" u'figure 12',\n",
" u'table 6',\n",
" u'table 7',\n",
" u'figure 13',\n",
" u'figure 14',\n",
" u'figure 15',\n",
" u'figure 16',\n",
" u'table 8',\n",
" u'figure 17',\n",
" u'figure 18',\n",
" u'table 9',\n",
" u'table 10',\n",
" u'table 11',\n",
" u'figure 19',\n",
" u'figure 20',\n",
" u'figure 21',\n",
" u'table 12',\n",
" u'figures 22',\n",
" u'figures 23',\n",
" u'figures 24',\n",
" u'table 13',\n",
" u'figures 25 & 27',\n",
" u'figures 26 & 28']"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"xl2=pd.ExcelFile('pres-diab-eng-200506-201415-apx1.xlsx')\n",
"xl2.sheet_names"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"{'CCG Code': u'10L',\n",
" 'CCG Name': u'NHS ISLE OF WIGHT CCG',\n",
" 'NIC per patient': 321.53064469347396}"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cpc=xl2.parse('figure 3',skiprows=1,thousands=',',index_col=0,na_values=['*','..'])\n",
"cpc.reset_index(inplace=True)\n",
"cpc.columns=['CCG Code', 'CCG Name','NIC per patient']\n",
"iwcpc=cpc[cpc['CCG Name']=='NHS ISLE OF WIGHT CCG'].iloc[0].to_dict()\n",
"iwcpc"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Period Name</th>\n",
" <th>Prescriber Name</th>\n",
" <th>Prescriber Code</th>\n",
" <th>Prescription items</th>\n",
" <th>Net Ingredient Cost</th>\n",
" <th>Cost per item</th>\n",
" <th>Prescription Items per cent</th>\n",
" <th>Net Ingredient Cost per cent</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> AIREDALE, WHARFEDALE AND CRAVEN CCG</td>\n",
" <td> 02N00</td>\n",
" <td> 125884</td>\n",
" <td> 2420864.00</td>\n",
" <td> 19.230911</td>\n",
" <td> 0.040953</td>\n",
" <td> 0.086801</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> ASHFORD CCG</td>\n",
" <td> 09C00</td>\n",
" <td> 97025</td>\n",
" <td> 2054284.24</td>\n",
" <td> 21.172731</td>\n",
" <td> 0.043856</td>\n",
" <td> 0.107553</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> AYLESBURY VALE CCG</td>\n",
" <td> 10Y00</td>\n",
" <td> 163806</td>\n",
" <td> 2831291.46</td>\n",
" <td> 17.284419</td>\n",
" <td> 0.049094</td>\n",
" <td> 0.113418</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> BARKING &amp; DAGENHAM CCG</td>\n",
" <td> 07L00</td>\n",
" <td> 194815</td>\n",
" <td> 3443295.96</td>\n",
" <td> 17.674696</td>\n",
" <td> 0.059243</td>\n",
" <td> 0.132257</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> BARNET CCG</td>\n",
" <td> 07M00</td>\n",
" <td> 264974</td>\n",
" <td> 5910006.26</td>\n",
" <td> 22.304099</td>\n",
" <td> 0.050393</td>\n",
" <td> 0.114850</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Period Name Prescriber Name Prescriber Code \\\n",
"0 Financial 2014/2015 AIREDALE, WHARFEDALE AND CRAVEN CCG 02N00 \n",
"1 Financial 2014/2015 ASHFORD CCG 09C00 \n",
"2 Financial 2014/2015 AYLESBURY VALE CCG 10Y00 \n",
"3 Financial 2014/2015 BARKING & DAGENHAM CCG 07L00 \n",
"4 Financial 2014/2015 BARNET CCG 07M00 \n",
"\n",
" Prescription items Net Ingredient Cost Cost per item \\\n",
"0 125884 2420864.00 19.230911 \n",
"1 97025 2054284.24 21.172731 \n",
"2 163806 2831291.46 17.284419 \n",
"3 194815 3443295.96 17.674696 \n",
"4 264974 5910006.26 22.304099 \n",
"\n",
" Prescription Items per cent Net Ingredient Cost per cent \n",
"0 0.040953 0.086801 \n",
"1 0.043856 0.107553 \n",
"2 0.049094 0.113418 \n",
"3 0.059243 0.132257 \n",
"4 0.050393 0.114850 "
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dbd.head()"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Period Name</th>\n",
" <th>Prescriber Name</th>\n",
" <th>Prescriber Code</th>\n",
" <th>Prescription items</th>\n",
" <th>Net Ingredient Cost</th>\n",
" <th>Cost per item</th>\n",
" <th>Prescription Items per cent</th>\n",
" <th>Net Ingredient Cost per cent</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> AIREDALE, WHARFEDALE AND CRAVEN CCG</td>\n",
" <td> 02N00</td>\n",
" <td> 18775</td>\n",
" <td> 933208.36</td>\n",
" <td> 49.704839</td>\n",
" <td> 0.006108</td>\n",
" <td> 0.033460</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> ASHFORD CCG</td>\n",
" <td> 09C00</td>\n",
" <td> 16032</td>\n",
" <td> 841567.61</td>\n",
" <td> 52.492990</td>\n",
" <td> 0.007247</td>\n",
" <td> 0.044061</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> AYLESBURY VALE CCG</td>\n",
" <td> 10Y00</td>\n",
" <td> 25298</td>\n",
" <td> 1274002.06</td>\n",
" <td> 50.359794</td>\n",
" <td> 0.007582</td>\n",
" <td> 0.051035</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> BARKING &amp; DAGENHAM CCG</td>\n",
" <td> 07L00</td>\n",
" <td> 25997</td>\n",
" <td> 1170402.57</td>\n",
" <td> 45.020678</td>\n",
" <td> 0.007906</td>\n",
" <td> 0.044955</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> BARNET CCG</td>\n",
" <td> 07M00</td>\n",
" <td> 31452</td>\n",
" <td> 1723613.21</td>\n",
" <td> 54.801387</td>\n",
" <td> 0.005982</td>\n",
" <td> 0.033495</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Period Name Prescriber Name Prescriber Code \\\n",
"0 Financial 2014/2015 AIREDALE, WHARFEDALE AND CRAVEN CCG 02N00 \n",
"1 Financial 2014/2015 ASHFORD CCG 09C00 \n",
"2 Financial 2014/2015 AYLESBURY VALE CCG 10Y00 \n",
"3 Financial 2014/2015 BARKING & DAGENHAM CCG 07L00 \n",
"4 Financial 2014/2015 BARNET CCG 07M00 \n",
"\n",
" Prescription items Net Ingredient Cost Cost per item \\\n",
"0 18775 933208.36 49.704839 \n",
"1 16032 841567.61 52.492990 \n",
"2 25298 1274002.06 50.359794 \n",
"3 25997 1170402.57 45.020678 \n",
"4 31452 1723613.21 54.801387 \n",
"\n",
" Prescription Items per cent Net Ingredient Cost per cent \n",
"0 0.006108 0.033460 \n",
"1 0.007247 0.044061 \n",
"2 0.007582 0.051035 \n",
"3 0.007906 0.044955 \n",
"4 0.005982 0.033495 "
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"tname='BNF 6.1.1 Insulins'\n",
"dbi=xl.parse(tname,skiprows=2,thousands=',',index_col=0,na_values=['*','..'])\n",
"dbi.reset_index(inplace=True)\n",
"dbi.columns=colnames\n",
"dbi.head()"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Period Name</th>\n",
" <th>Prescriber Name</th>\n",
" <th>Prescriber Code</th>\n",
" <th>Prescription items</th>\n",
" <th>Net Ingredient Cost</th>\n",
" <th>Cost per item</th>\n",
" <th>Prescription Items per cent</th>\n",
" <th>Net Ingredient Cost per cent</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> AIREDALE, WHARFEDALE AND CRAVEN CCG</td>\n",
" <td> 02N00</td>\n",
" <td> 83286</td>\n",
" <td> 850226.72</td>\n",
" <td> 10.208519</td>\n",
" <td> 0.027095</td>\n",
" <td> 0.030485</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> ASHFORD CCG</td>\n",
" <td> 09C00</td>\n",
" <td> 65356</td>\n",
" <td> 836331.24</td>\n",
" <td> 12.796549</td>\n",
" <td> 0.029542</td>\n",
" <td> 0.043787</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> AYLESBURY VALE CCG</td>\n",
" <td> 10Y00</td>\n",
" <td> 111071</td>\n",
" <td> 876914.75</td>\n",
" <td> 7.895083</td>\n",
" <td> 0.033289</td>\n",
" <td> 0.035128</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> BARKING &amp; DAGENHAM CCG</td>\n",
" <td> 07L00</td>\n",
" <td> 142809</td>\n",
" <td> 1746764.05</td>\n",
" <td> 12.231470</td>\n",
" <td> 0.043428</td>\n",
" <td> 0.067093</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> BARNET CCG</td>\n",
" <td> 07M00</td>\n",
" <td> 193465</td>\n",
" <td> 3023510.13</td>\n",
" <td> 15.628202</td>\n",
" <td> 0.036794</td>\n",
" <td> 0.058757</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Period Name Prescriber Name Prescriber Code \\\n",
"0 Financial 2014/2015 AIREDALE, WHARFEDALE AND CRAVEN CCG 02N00 \n",
"1 Financial 2014/2015 ASHFORD CCG 09C00 \n",
"2 Financial 2014/2015 AYLESBURY VALE CCG 10Y00 \n",
"3 Financial 2014/2015 BARKING & DAGENHAM CCG 07L00 \n",
"4 Financial 2014/2015 BARNET CCG 07M00 \n",
"\n",
" Prescription items Net Ingredient Cost Cost per item \\\n",
"0 83286 850226.72 10.208519 \n",
"1 65356 836331.24 12.796549 \n",
"2 111071 876914.75 7.895083 \n",
"3 142809 1746764.05 12.231470 \n",
"4 193465 3023510.13 15.628202 \n",
"\n",
" Prescription Items per cent Net Ingredient Cost per cent \n",
"0 0.027095 0.030485 \n",
"1 0.029542 0.043787 \n",
"2 0.033289 0.035128 \n",
"3 0.043428 0.067093 \n",
"4 0.036794 0.058757 "
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"tname='BNF 6.1.2 Antidiabetic Drugs '\n",
"dba=xl.parse(tname,skiprows=2,thousands=',',index_col=0,na_values=['*','..'])\n",
"dba.reset_index(inplace=True)\n",
"dba.columns=colnames\n",
"dba.head()"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Period Name</th>\n",
" <th>Prescriber Name</th>\n",
" <th>Prescriber Code</th>\n",
" <th>Prescription items</th>\n",
" <th>Net Ingredient Cost</th>\n",
" <th>Cost per item</th>\n",
" <th>Prescription Items per cent</th>\n",
" <th>Net Ingredient Cost per cent</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>86</th>\n",
" <td> Financial 2014/2015</td>\n",
" <td> ISLE OF WIGHT CCG</td>\n",
" <td> 10L00</td>\n",
" <td> 93660</td>\n",
" <td> 825682.54</td>\n",
" <td> 8.815744</td>\n",
" <td> 0.028662</td>\n",
" <td> 0.033364</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Period Name Prescriber Name Prescriber Code \\\n",
"86 Financial 2014/2015 ISLE OF WIGHT CCG 10L00 \n",
"\n",
" Prescription items Net Ingredient Cost Cost per item \\\n",
"86 93660 825682.54 8.815744 \n",
"\n",
" Prescription Items per cent Net Ingredient Cost per cent \n",
"86 0.028662 0.033364 "
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dba[dba['Prescriber Name']=='ISLE OF WIGHT CCG']"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"activity": false,
"collapsed": true
},
"outputs": [],
"source": [
"def itemgetter(df, key):\n",
" row=df[df['Prescriber Name']==key].iloc[0]\n",
" return row.to_dict()"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"{'Cost per item': 17.996963993273063,\n",
" 'Net Ingredient Cost': 2450628.5899999999,\n",
" 'Net Ingredient Cost per cent': 0.099023908644984171,\n",
" 'Period Name': u'Financial 2014/2015',\n",
" 'Prescriber Code': u'10L00',\n",
" 'Prescriber Name': u'ISLE OF WIGHT CCG',\n",
" 'Prescription Items per cent': 0.041670300642242065,\n",
" 'Prescription items': 136169.0}"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"d=itemgetter(dbd, \"ISLE OF WIGHT CCG\")\n",
"d"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>PCT Code</th>\n",
" <th>PCT Name</th>\n",
" <th>2008/09</th>\n",
" <th>Unnamed: 3</th>\n",
" <th>PCT Code.1</th>\n",
" <th>PCT Name.1</th>\n",
" <th>2009/10</th>\n",
" <th>Unnamed: 7</th>\n",
" <th>PCT Code.2</th>\n",
" <th>PCT Name.2</th>\n",
" <th>...</th>\n",
" <th>PCT Name.3</th>\n",
" <th>2011/12</th>\n",
" <th>Unnamed: 15</th>\n",
" <th>CCG Code</th>\n",
" <th>CCG Name</th>\n",
" <th>2012/13</th>\n",
" <th>Unnamed: 19</th>\n",
" <th>CCG Code.1</th>\n",
" <th>CCG Name.1</th>\n",
" <th>2013/14</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> 5M6</td>\n",
" <td> RICHMOND &amp; TWICKENHAM</td>\n",
" <td> 0.030416</td>\n",
" <td>NaN</td>\n",
" <td> 5M6</td>\n",
" <td> RICHMOND &amp; TWICKENHAM</td>\n",
" <td> 0.031806</td>\n",
" <td>NaN</td>\n",
" <td> 5M6</td>\n",
" <td> RICHMOND &amp; TWICKENHAM</td>\n",
" <td>...</td>\n",
" <td> RICHMOND &amp; TWICKENHAM</td>\n",
" <td> 0.034367</td>\n",
" <td>NaN</td>\n",
" <td> 08P</td>\n",
" <td> NHS RICHMOND CCG</td>\n",
" <td> 0.035265</td>\n",
" <td>NaN</td>\n",
" <td> 08P</td>\n",
" <td> NHS RICHMOND CCG</td>\n",
" <td> 0.035186</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> 5LA</td>\n",
" <td> KENSINGTON AND CHELSEA PCT</td>\n",
" <td> 0.032870</td>\n",
" <td>NaN</td>\n",
" <td> 5LA</td>\n",
" <td> KENSINGTON AND CHELSEA PCT</td>\n",
" <td> 0.033799</td>\n",
" <td>NaN</td>\n",
" <td> 5LA</td>\n",
" <td> KENSINGTON AND CHELSEA PCT</td>\n",
" <td>...</td>\n",
" <td> CAMDEN PRIMARY CARE TRUST</td>\n",
" <td> 0.035803</td>\n",
" <td>NaN</td>\n",
" <td> 07R</td>\n",
" <td> NHS CAMDEN CCG</td>\n",
" <td> 0.038125</td>\n",
" <td>NaN</td>\n",
" <td> 07R</td>\n",
" <td> NHS CAMDEN CCG</td>\n",
" <td> 0.038234</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 5LC</td>\n",
" <td> WESTMINSTER PCT</td>\n",
" <td> 0.034521</td>\n",
" <td>NaN</td>\n",
" <td> 5K7</td>\n",
" <td> CAMDEN PRIMARY CARE TRUST</td>\n",
" <td> 0.034997</td>\n",
" <td>NaN</td>\n",
" <td> 5K7</td>\n",
" <td> CAMDEN PRIMARY CARE TRUST</td>\n",
" <td>...</td>\n",
" <td> KENSINGTON AND CHELSEA PCT</td>\n",
" <td> 0.039285</td>\n",
" <td>NaN</td>\n",
" <td> 09A</td>\n",
" <td> NHS CENTRAL LONDON (WESTMINSTER) CCG</td>\n",
" <td> 0.041282</td>\n",
" <td>NaN</td>\n",
" <td> 09A</td>\n",
" <td> NHS CENTRAL LONDON (WESTMINSTER) CCG</td>\n",
" <td> 0.039484</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td> 5LQ</td>\n",
" <td> BRIGHTON AND HOVE CITY TEACHING PCT</td>\n",
" <td> 0.035000</td>\n",
" <td>NaN</td>\n",
" <td> 5LC</td>\n",
" <td> WESTMINSTER PCT</td>\n",
" <td> 0.035712</td>\n",
" <td>NaN</td>\n",
" <td> 5LQ</td>\n",
" <td> BRIGHTON AND HOVE CITY TEACHING PCT</td>\n",
" <td>...</td>\n",
" <td> BRIGHTON AND HOVE CITY TEACHING PCT</td>\n",
" <td> 0.039881</td>\n",
" <td>NaN</td>\n",
" <td> 09D</td>\n",
" <td> NHS BRIGHTON AND HOVE CCG</td>\n",
" <td> 0.041648</td>\n",
" <td>NaN</td>\n",
" <td> 09D</td>\n",
" <td> NHS BRIGHTON AND HOVE CCG</td>\n",
" <td> 0.041408</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td> 5LG</td>\n",
" <td> WANDSWORTH PCT</td>\n",
" <td> 0.036340</td>\n",
" <td>NaN</td>\n",
" <td> 5LQ</td>\n",
" <td> BRIGHTON AND HOVE CITY TEACHING PCT</td>\n",
" <td> 0.036621</td>\n",
" <td>NaN</td>\n",
" <td> 5LC</td>\n",
" <td> WESTMINSTER PCT</td>\n",
" <td>...</td>\n",
" <td> WANDSWORTH PCT</td>\n",
" <td> 0.042280</td>\n",
" <td>NaN</td>\n",
" <td> 08X</td>\n",
" <td> NHS WANDSWORTH CCG</td>\n",
" <td> 0.042448</td>\n",
" <td>NaN</td>\n",
" <td> 08X</td>\n",
" <td> NHS WANDSWORTH CCG</td>\n",
" <td> 0.041957</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 23 columns</p>\n",
"</div>"
],
"text/plain": [
" PCT Code PCT Name 2008/09 Unnamed: 3 \\\n",
"0 5M6 RICHMOND & TWICKENHAM 0.030416 NaN \n",
"1 5LA KENSINGTON AND CHELSEA PCT 0.032870 NaN \n",
"2 5LC WESTMINSTER PCT 0.034521 NaN \n",
"3 5LQ BRIGHTON AND HOVE CITY TEACHING PCT 0.035000 NaN \n",
"4 5LG WANDSWORTH PCT 0.036340 NaN \n",
"\n",
" PCT Code.1 PCT Name.1 2009/10 Unnamed: 7 \\\n",
"0 5M6 RICHMOND & TWICKENHAM 0.031806 NaN \n",
"1 5LA KENSINGTON AND CHELSEA PCT 0.033799 NaN \n",
"2 5K7 CAMDEN PRIMARY CARE TRUST 0.034997 NaN \n",
"3 5LC WESTMINSTER PCT 0.035712 NaN \n",
"4 5LQ BRIGHTON AND HOVE CITY TEACHING PCT 0.036621 NaN \n",
"\n",
" PCT Code.2 PCT Name.2 ... \\\n",
"0 5M6 RICHMOND & TWICKENHAM ... \n",
"1 5LA KENSINGTON AND CHELSEA PCT ... \n",
"2 5K7 CAMDEN PRIMARY CARE TRUST ... \n",
"3 5LQ BRIGHTON AND HOVE CITY TEACHING PCT ... \n",
"4 5LC WESTMINSTER PCT ... \n",
"\n",
" PCT Name.3 2011/12 Unnamed: 15 CCG Code \\\n",
"0 RICHMOND & TWICKENHAM 0.034367 NaN 08P \n",
"1 CAMDEN PRIMARY CARE TRUST 0.035803 NaN 07R \n",
"2 KENSINGTON AND CHELSEA PCT 0.039285 NaN 09A \n",
"3 BRIGHTON AND HOVE CITY TEACHING PCT 0.039881 NaN 09D \n",
"4 WANDSWORTH PCT 0.042280 NaN 08X \n",
"\n",
" CCG Name 2012/13 Unnamed: 19 CCG Code.1 \\\n",
"0 NHS RICHMOND CCG 0.035265 NaN 08P \n",
"1 NHS CAMDEN CCG 0.038125 NaN 07R \n",
"2 NHS CENTRAL LONDON (WESTMINSTER) CCG 0.041282 NaN 09A \n",
"3 NHS BRIGHTON AND HOVE CCG 0.041648 NaN 09D \n",
"4 NHS WANDSWORTH CCG 0.042448 NaN 08X \n",
"\n",
" CCG Name.1 2013/14 \n",
"0 NHS RICHMOND CCG 0.035186 \n",
"1 NHS CAMDEN CCG 0.038234 \n",
"2 NHS CENTRAL LONDON (WESTMINSTER) CCG 0.039484 \n",
"3 NHS BRIGHTON AND HOVE CCG 0.041408 \n",
"4 NHS WANDSWORTH CCG 0.041957 \n",
"\n",
"[5 rows x 23 columns]"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cprc=xl2.parse('figures 1a & 1b',skiprows=2,thousands=',',index_col=0,na_values=['*','..'])\n",
"cprc.reset_index(inplace=True)\n",
"cprc.head()"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"{u'2013/14': 0.06432428330136418,\n",
" u'CCG Code.1': u'10L',\n",
" u'CCG Name.1': u'NHS ISLE OF WIGHT CCG'}"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"iwprev=cprc[cprc['CCG Code.1']==iwcpc['CCG Code']][['CCG Code.1','CCG Name.1','2013/14']].iloc[0].to_dict()\n",
"iwprev"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>AT code</th>\n",
" <th>AT Name</th>\n",
" <th>Diabetes Prevalence per cent</th>\n",
" <th>Items</th>\n",
" <th>NIC</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> Q69</td>\n",
" <td> Thames Valley</td>\n",
" <td> 0.051867</td>\n",
" <td> 0.047211</td>\n",
" <td> 0.106439</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> Q68</td>\n",
" <td> Surrey &amp; Sussex</td>\n",
" <td> 0.054150</td>\n",
" <td> 0.039920</td>\n",
" <td> 0.098065</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> Q65</td>\n",
" <td> Bristol, North Som, Som &amp; S Glos</td>\n",
" <td> 0.055774</td>\n",
" <td> 0.040145</td>\n",
" <td> 0.099432</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> 0.057028</td>\n",
" <td> 0.040200</td>\n",
" <td> 0.098493</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td> Q58</td>\n",
" <td> Hertfordshire &amp; South Midlands</td>\n",
" <td> 0.057667</td>\n",
" <td> 0.045314</td>\n",
" <td> 0.100960</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" AT code AT Name Diabetes Prevalence per cent \\\n",
"0 Q69 Thames Valley 0.051867 \n",
"1 Q68 Surrey & Sussex 0.054150 \n",
"2 Q65 Bristol, North Som, Som & S Glos 0.055774 \n",
"3 Q70 Wessex 0.057028 \n",
"4 Q58 Hertfordshire & South Midlands 0.057667 \n",
"\n",
" Items NIC \n",
"0 0.047211 0.106439 \n",
"1 0.039920 0.098065 \n",
"2 0.040145 0.099432 \n",
"3 0.040200 0.098493 \n",
"4 0.045314 0.100960 "
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#IW is part of Q70 and Eng\n",
"#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\n",
"nprc=xl2.parse('figure 2',skiprows=2,thousands=',',index_col=0,na_values=['*','..'])\n",
"nprc.reset_index(inplace=True)\n",
"nprc.columns=['AT code','AT Name','Diabetes Prevalence per cent','Items','NIC']\n",
"nprc.head()"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"{'AT Name': u'Wessex',\n",
" 'AT code': u'Q70',\n",
" 'Diabetes Prevalence per cent': 0.057027688727637339,\n",
" 'Items': 0.040199634874282228,\n",
" 'NIC': 0.098492587498059864}"
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#Really should look up the AT Code based on the CCG code?\n",
"region=nprc[nprc['AT code']=='Q70'].iloc[0].to_dict()\n",
"region"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"{'AT Name': u'England',\n",
" 'AT code': u'Eng',\n",
" 'Diabetes Prevalence per cent': 0.062,\n",
" 'Items': 0.044515646682664771,\n",
" 'NIC': 0.099787215533502865}"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"country=nprc[nprc['AT code']=='Eng'].iloc[0].to_dict()\n",
"country"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"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. \n",
"\n",
"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).\n",
"\n",
"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.\n"
]
}
],
"source": [
"#Thousands separator\n",
"def c(amount):\n",
" return '{:,}'.format(amount)\n",
"\n",
"d=itemgetter(dbd, \"ISLE OF WIGHT CCG\")\n",
"d\n",
"\n",
"txt=\"Figures recently published by the HSCIC show that for the reporting period {period}, \".format(period=d[\"Period Name\"])\n",
"txt=txt+\"the total Net Ingredient Costs (NIC) for prescribed diabetes drugs was £{tpc}, \".format(tpc=\"{0:,.2f}\".format(d['Net Ingredient Cost']))\n",
"txt=txt+\"representing {npc} of overall Net Ingredient Costs. \".format(npc=\"{0:.2f}%\".format(100*d['Net Ingredient Cost per cent']))\n",
"txt=txt+\"The {ccg} prescribed {items} diabetes drugs items, \".format(ccg=d['Prescriber Name'], items=c(int(d['Prescription items'])))\n",
"txt=txt+\"representing {ipc} of all prescribed items. \".format(ipc=\"{0:.2f}%\".format(100*d['Prescription Items per cent']))\n",
"txt=txt+\"The average net ingredient cost (NIC) was £{apc} per item. \".format(apc=\"{0:.2f}\".format(d['Cost per item']))\n",
"\n",
"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'],\n",
" rc=region['AT code'],\n",
" r=region['AT Name'],\n",
" rpci= \"{0:.2f}%\".format(100*region['Items']),\n",
" rpct= \"{0:.2f}%\".format(100*region['NIC']),\n",
" npci=\"{0:.2f}%\".format(100*country['Items']),\n",
" npct=\"{0:.2f}%\".format(100*country['NIC']))\n",
"\n",
"d=itemgetter(dbi, \"ISLE OF WIGHT CCG\")\n",
"d\n",
"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']),\n",
" tnic=\"{0:,.2f}\".format(d['Net Ingredient Cost']),\n",
" cpi=\"{0:.2f}\".format(d['Cost per item']),\n",
" opc=\"{0:.2f}%\".format(100*d['Prescription Items per cent']),\n",
" cpc=\"{0:.2f}%\".format(100*d['Net Ingredient Cost per cent'])\n",
" )\n",
"\n",
"d=itemgetter(dba, \"ISLE OF WIGHT CCG\")\n",
"d\n",
"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']),\n",
" tnic=\"{:,.2f}\".format(d['Net Ingredient Cost']),\n",
" cpi=\"{0:.2f}\".format(d['Cost per item']),\n",
" opc=\"{0:.2f}%\".format(100*d['Prescription Items per cent']),\n",
" cpc=\"{0:.2f}%\".format(100*d['Net Ingredient Cost per cent'])\n",
" )\n",
"\n",
"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'],\n",
" cc=\"{0:,.2f}\".format(iwcpc['NIC per patient']))\n",
"p='2013/14'\n",
"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]))\n",
"txt=txt+\"This compares to a prevalence rate of {rp} in {r} and {cp} across {c}.\".format(c=country['AT Name'],\n",
" r=region['AT Name'],\n",
" cp=\"{0:.2f}%\".format(100*region['Diabetes Prevalence per cent']),\n",
" rp=\"{0:.2f}%\".format(100*country['Diabetes Prevalence per cent']))\n",
"print(txt)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"activity": false,
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.10"
}
},
"nbformat": 4,
"nbformat_minor": 0
}
Display the source blob
Display the rendered blob
Raw
nhs_complaints.ipynb
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment