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
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Display the source blob
Display the rendered blob
Raw
nhs_complaints.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#NHS Complaints\n",
"\n",
"Via a tweet from @HSCICOpenData today - *Data on Written Complaints in the NHS - CSV datasets on http://data.gov.uk at http://bit.ly/1IfyLE2* I grabbed a spreadsheet from http://data.gov.uk/dataset/data_on_written_complaints_in_the_nhs and had a quick play..."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Some set up stuff...\n",
"\n",
"Get some metadata into play about GP and dental practices so we can make sense of the practice codes that appear in the complaints dataset..."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### GP Practices Administrative Data"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {
"activity": false,
"collapsed": false
},
"outputs": [],
"source": [
"#Downloads: http://systems.hscic.gov.uk/data/ods/datadownloads/index"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import pandas as pd"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[u'epraccur']"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#epracurr is administrative info about GP practices - practice codes, address, etc etc\n",
"\n",
"#http://systems.hscic.gov.uk/data/ods/datadownloads/gppractice/index_html\n",
"#http://systems.hscic.gov.uk/data/ods/datadownloads/data-files/xls/epraccur.zip\n",
"xl=pd.ExcelFile('epraccur/epraccur.xls')\n",
"xl.sheet_names"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import requests, zipfile, StringIO\n",
"def zipgrabber(url, f):\n",
" r = requests.get(url)\n",
" z = zipfile.ZipFile(StringIO.StringIO(r.content))\n",
" return z.open(f)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[u'epraccur']"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"url='http://systems.hscic.gov.uk/data/ods/datadownloads/data-files/xls/epraccur.zip'\n",
"xl=pd.ExcelFile(zipgrabber(url,'epraccur.xls'))\n",
"xl.sheet_names"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"cols=['Organisation Code','Name','National Grouping','High Level Health Geography',\n",
" 'Address Line 1','Address Line 2','Address Line 3','Address Line 4','Address Line 5','Postcode',\n",
" 'Open Date','Close Date','Status Code','Organisation Sub-Type code',\n",
" 'Parent Organisation Code','Join Parent Date','Left Parent Date','Contact Telephone Number',\n",
" 'Available for future use','Available for future use','Available for future use',\n",
" 'Amended Record Indicator','Available for future use',\n",
" 'Provider/Purchaser','Available for future use','Prescribing Setting']"
]
},
{
"cell_type": "code",
"execution_count": 7,
"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>Organisation Code</th>\n",
" <th>Name</th>\n",
" <th>National Grouping</th>\n",
" <th>High Level Health Geography</th>\n",
" <th>Address Line 1</th>\n",
" <th>Address Line 2</th>\n",
" <th>Address Line 3</th>\n",
" <th>Address Line 4</th>\n",
" <th>Address Line 5</th>\n",
" <th>Postcode</th>\n",
" <th>...</th>\n",
" <th>Left Parent Date</th>\n",
" <th>Contact Telephone Number</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Amended Record Indicator</th>\n",
" <th>Available for future use</th>\n",
" <th>Provider/Purchaser</th>\n",
" <th>Available for future use</th>\n",
" <th>Prescribing Setting</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> A81043</td>\n",
" <td> THE MANOR HOUSE SURGERY</td>\n",
" <td> Y54</td>\n",
" <td> Q74</td>\n",
" <td> THE MANOR HOUSE SURGERY</td>\n",
" <td> BRAIDWOOD ROAD, NORMANBY</td>\n",
" <td> MIDDLESBROUGH</td>\n",
" <td> CLEVELAND</td>\n",
" <td> NaN</td>\n",
" <td> TS6 0HA</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01642 374788</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 1</td>\n",
" <td>NaN</td>\n",
" <td> 00M</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> A81044</td>\n",
" <td> MCKENZIE HOUSE SURGERY</td>\n",
" <td> Y54</td>\n",
" <td> Q74</td>\n",
" <td> MCKENZIE HOUSE</td>\n",
" <td> 17 KENDAL ROAD</td>\n",
" <td> HARTLEPOOL</td>\n",
" <td> CLEVELAND</td>\n",
" <td> NaN</td>\n",
" <td> TS25 1QU</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01429 230000</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 1</td>\n",
" <td>NaN</td>\n",
" <td> 00K</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> A81057</td>\n",
" <td> KINGSWAY MEDICAL CENTRE</td>\n",
" <td> Y54</td>\n",
" <td> Q74</td>\n",
" <td> KINGSWAY MEDICAL CENTRE</td>\n",
" <td> KINGSWAY</td>\n",
" <td> BILLINGHAM</td>\n",
" <td> CLEVELAND</td>\n",
" <td> NaN</td>\n",
" <td> TS23 2LS</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01642 554967</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 1</td>\n",
" <td>NaN</td>\n",
" <td> 00K</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td> A81623</td>\n",
" <td> NORTH SHORE MEDICAL PRACTICE</td>\n",
" <td> Y54</td>\n",
" <td> Q74</td>\n",
" <td> ENDURANCE HOUSE</td>\n",
" <td> CLARENCE STREET</td>\n",
" <td> STOCKTON ON TEES</td>\n",
" <td> CLEVELAND</td>\n",
" <td> NaN</td>\n",
" <td> TS18 2EP</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01642 602222</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 1</td>\n",
" <td>NaN</td>\n",
" <td> 00K</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td> A82034</td>\n",
" <td> STONELEIGH SURGERY</td>\n",
" <td> Y54</td>\n",
" <td> Q74</td>\n",
" <td> STONELEIGH SURGERY</td>\n",
" <td> POLICE SQUARE</td>\n",
" <td> MILNTHORPE</td>\n",
" <td> CUMBRIA</td>\n",
" <td> NaN</td>\n",
" <td> LA7 7PW</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01539 563307</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 1</td>\n",
" <td>NaN</td>\n",
" <td> 01H</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 26 columns</p>\n",
"</div>"
],
"text/plain": [
" Organisation Code Name National Grouping \\\n",
"0 A81043 THE MANOR HOUSE SURGERY Y54 \n",
"1 A81044 MCKENZIE HOUSE SURGERY Y54 \n",
"2 A81057 KINGSWAY MEDICAL CENTRE Y54 \n",
"3 A81623 NORTH SHORE MEDICAL PRACTICE Y54 \n",
"4 A82034 STONELEIGH SURGERY Y54 \n",
"\n",
" High Level Health Geography Address Line 1 \\\n",
"0 Q74 THE MANOR HOUSE SURGERY \n",
"1 Q74 MCKENZIE HOUSE \n",
"2 Q74 KINGSWAY MEDICAL CENTRE \n",
"3 Q74 ENDURANCE HOUSE \n",
"4 Q74 STONELEIGH SURGERY \n",
"\n",
" Address Line 2 Address Line 3 Address Line 4 Address Line 5 \\\n",
"0 BRAIDWOOD ROAD, NORMANBY MIDDLESBROUGH CLEVELAND NaN \n",
"1 17 KENDAL ROAD HARTLEPOOL CLEVELAND NaN \n",
"2 KINGSWAY BILLINGHAM CLEVELAND NaN \n",
"3 CLARENCE STREET STOCKTON ON TEES CLEVELAND NaN \n",
"4 POLICE SQUARE MILNTHORPE CUMBRIA NaN \n",
"\n",
" Postcode ... Left Parent Date Contact Telephone Number \\\n",
"0 TS6 0HA ... NaN 01642 374788 \n",
"1 TS25 1QU ... NaN 01429 230000 \n",
"2 TS23 2LS ... NaN 01642 554967 \n",
"3 TS18 2EP ... NaN 01642 602222 \n",
"4 LA7 7PW ... NaN 01539 563307 \n",
"\n",
" Available for future use Available for future use \\\n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 NaN NaN \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" Available for future use Amended Record Indicator \\\n",
"0 NaN 1 \n",
"1 NaN 1 \n",
"2 NaN 1 \n",
"3 NaN 1 \n",
"4 NaN 1 \n",
"\n",
" Available for future use Provider/Purchaser Available for future use \\\n",
"0 NaN 00M NaN \n",
"1 NaN 00K NaN \n",
"2 NaN 00K NaN \n",
"3 NaN 00K NaN \n",
"4 NaN 01H NaN \n",
"\n",
" Prescribing Setting \n",
"0 4 \n",
"1 4 \n",
"2 4 \n",
"3 4 \n",
"4 4 \n",
"\n",
"[5 rows x 26 columns]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"gp=xl.parse('epraccur',header=None)\n",
"gp.columns=cols\n",
"gp.head()"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"###Dental Practices Administrative Data"
]
},
{
"cell_type": "code",
"execution_count": 9,
"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>Organisation Code</th>\n",
" <th>Name</th>\n",
" <th>National Grouping</th>\n",
" <th>High Level Health Geography</th>\n",
" <th>Address Line 1</th>\n",
" <th>Address Line 2</th>\n",
" <th>Address Line 3</th>\n",
" <th>Address Line 4</th>\n",
" <th>Address Line 5</th>\n",
" <th>Postcode</th>\n",
" <th>...</th>\n",
" <th>Contact Telephone Number</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Amended Record Indicator</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> V00003</td>\n",
" <td> CRABTREE ROAD DENTAL PRACTICE</td>\n",
" <td> Y57</td>\n",
" <td> Q81</td>\n",
" <td> CRABTREE ROAD DENTAL PRACTICE</td>\n",
" <td> 25 CRABTREE ROAD</td>\n",
" <td> CRAWLEY</td>\n",
" <td> WEST SUSSEX</td>\n",
" <td> NaN</td>\n",
" <td> RH11 7HL</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> V00004</td>\n",
" <td> DENTAL SURGERY</td>\n",
" <td> Y57</td>\n",
" <td> Q81</td>\n",
" <td> DENTAL SURGERY</td>\n",
" <td> WOODCROFT</td>\n",
" <td> BEPTON ROAD</td>\n",
" <td> MIDHURST</td>\n",
" <td> WEST SUSSEX</td>\n",
" <td> GU29 9HH</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> V00005</td>\n",
" <td> DENTAL SURGERY</td>\n",
" <td> Y57</td>\n",
" <td> Q81</td>\n",
" <td> DENTAL SURGERY</td>\n",
" <td> 31 WICK STREET</td>\n",
" <td> WICK</td>\n",
" <td> LITTLEHAMPTON</td>\n",
" <td> WEST SUSSEX</td>\n",
" <td> BN17 7JJ</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td> V00006</td>\n",
" <td> DENTAL SURGERY</td>\n",
" <td> Y57</td>\n",
" <td> Q81</td>\n",
" <td> DENTAL SURGERY</td>\n",
" <td> 43 BUSTICLE LANE</td>\n",
" <td> SOMPTING</td>\n",
" <td> LANCING</td>\n",
" <td> WEST SUSSEX</td>\n",
" <td> BN15 0DJ</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td> V00007</td>\n",
" <td> CRAWLEY DENTAL CLINIC</td>\n",
" <td> Y57</td>\n",
" <td> Q81</td>\n",
" <td> CRAWLEY DENTAL CLINIC</td>\n",
" <td> 158 BUCKSWOOD DRIVE</td>\n",
" <td> CRAWLEY</td>\n",
" <td> WEST SUSSEX</td>\n",
" <td> NaN</td>\n",
" <td> RH11 8JF</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 27 columns</p>\n",
"</div>"
],
"text/plain": [
" Organisation Code Name National Grouping \\\n",
"0 V00003 CRABTREE ROAD DENTAL PRACTICE Y57 \n",
"1 V00004 DENTAL SURGERY Y57 \n",
"2 V00005 DENTAL SURGERY Y57 \n",
"3 V00006 DENTAL SURGERY Y57 \n",
"4 V00007 CRAWLEY DENTAL CLINIC Y57 \n",
"\n",
" High Level Health Geography Address Line 1 \\\n",
"0 Q81 CRABTREE ROAD DENTAL PRACTICE \n",
"1 Q81 DENTAL SURGERY \n",
"2 Q81 DENTAL SURGERY \n",
"3 Q81 DENTAL SURGERY \n",
"4 Q81 CRAWLEY DENTAL CLINIC \n",
"\n",
" Address Line 2 Address Line 3 Address Line 4 Address Line 5 Postcode \\\n",
"0 25 CRABTREE ROAD CRAWLEY WEST SUSSEX NaN RH11 7HL \n",
"1 WOODCROFT BEPTON ROAD MIDHURST WEST SUSSEX GU29 9HH \n",
"2 31 WICK STREET WICK LITTLEHAMPTON WEST SUSSEX BN17 7JJ \n",
"3 43 BUSTICLE LANE SOMPTING LANCING WEST SUSSEX BN15 0DJ \n",
"4 158 BUCKSWOOD DRIVE CRAWLEY WEST SUSSEX NaN RH11 8JF \n",
"\n",
" ... Contact Telephone Number \\\n",
"0 ... NaN \n",
"1 ... NaN \n",
"2 ... NaN \n",
"3 ... NaN \n",
"4 ... NaN \n",
"\n",
" Available for future use Available for future use \\\n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 NaN NaN \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" Available for future use Amended Record Indicator \\\n",
"0 NaN 0 \n",
"1 NaN 0 \n",
"2 NaN 0 \n",
"3 NaN 0 \n",
"4 NaN 0 \n",
"\n",
" Available for future use Available for future use \\\n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 NaN NaN \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" Available for future use Available for future use \\\n",
"0 NaN NaN \n",
"1 NaN NaN \n",
"2 NaN NaN \n",
"3 NaN NaN \n",
"4 NaN NaN \n",
"\n",
" Available for future use \n",
"0 NaN \n",
"1 NaN \n",
"2 NaN \n",
"3 NaN \n",
"4 NaN \n",
"\n",
"[5 rows x 27 columns]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#egdpprac is administrative info about dental practices - practice codes, address, etc etc\n",
"\n",
"##http://systems.hscic.gov.uk/data/ods/datadownloads/misc/index_html\n",
"#http://systems.hscic.gov.uk/data/ods/datadownloads/data-files/egdpprac.zip\n",
"#dentists=pd.read_csv('egdpprac/egdpprac.csv',header=None)\n",
"url='http://systems.hscic.gov.uk/data/ods/datadownloads/data-files/egdpprac.zip'\n",
"dentists=pd.read_csv(zipgrabber(url,'egdpprac.csv'))\n",
"\n",
"cols2=['Organisation Code','Name','National Grouping','High Level Health Geography',\n",
" 'Address Line 1','Address Line 2','Address Line 3','Address Line 4','Address Line 5','Postcode',\n",
" 'Open Date','Close Date','Status Code','Organisation Sub-Type code',\n",
" 'Parent Organisation Code','Join Parent Date','Left Parent Date','Contact Telephone Number',\n",
" 'Available for future use','Available for future use','Available for future use',\n",
" 'Amended Record Indicator','Available for future use','Available for future use',\n",
" 'Available for future use','Available for future use','Available for future use']\n",
"\n",
"dentists.columns=cols2\n",
"dentists.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Complaints Data\n",
"\n",
"via: http://data.gov.uk/dataset/data_on_written_complaints_in_the_nhs"
]
},
{
"cell_type": "code",
"execution_count": 10,
"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>Year</th>\n",
" <th>Area_Team_Code</th>\n",
" <th>Area_Team_Name</th>\n",
" <th>Org_Code</th>\n",
" <th>Org_Name</th>\n",
" <th>Hospital acute services: Inpatient total</th>\n",
" <th>Hospital acute services: Inpatient total upheld</th>\n",
" <th>Hospital acute services: Outpatient total</th>\n",
" <th>Hospital acute services: Outpatient total upheld</th>\n",
" <th>Hospital acute services: A&amp;E total</th>\n",
" <th>...</th>\n",
" <th>Policy and commercial decisions of trusts total</th>\n",
" <th>Policy and commercial decisions of trusts total upheld</th>\n",
" <th>Code of openness - complaints total</th>\n",
" <th>Code of openness - complaints total upheld</th>\n",
" <th>Hotel services (including food) total</th>\n",
" <th>Hotel services (including food) total upheld</th>\n",
" <th>Other total.2</th>\n",
" <th>Other total upheld.2</th>\n",
" <th>TOTAL.2</th>\n",
" <th>TOTAL upheld.2</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> 2014-15</td>\n",
" <td> Q45</td>\n",
" <td> Durham, Darlington and Tees</td>\n",
" <td> 00C</td>\n",
" <td> NHS Darlington CCG</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td>...</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 4</td>\n",
" <td> 0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> 2014-15</td>\n",
" <td> Q45</td>\n",
" <td> Durham, Darlington and Tees</td>\n",
" <td> 00D</td>\n",
" <td> NHS Durham Dales, Easington and Sedgefield CCG</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td>...</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 3</td>\n",
" <td> 2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 2014-15</td>\n",
" <td> Q49</td>\n",
" <td> Cumbria, Northumberland, Tyne and Wear</td>\n",
" <td> 00F</td>\n",
" <td> NHS Gateshead CCG</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td>...</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 4</td>\n",
" <td> 2</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows × 107 columns</p>\n",
"</div>"
],
"text/plain": [
" Year Area_Team_Code Area_Team_Name Org_Code \\\n",
"0 2014-15 Q45 Durham, Darlington and Tees 00C \n",
"1 2014-15 Q45 Durham, Darlington and Tees 00D \n",
"2 2014-15 Q49 Cumbria, Northumberland, Tyne and Wear 00F \n",
"\n",
" Org_Name \\\n",
"0 NHS Darlington CCG \n",
"1 NHS Durham Dales, Easington and Sedgefield CCG \n",
"2 NHS Gateshead CCG \n",
"\n",
" Hospital acute services: Inpatient total \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Hospital acute services: Inpatient total upheld \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Hospital acute services: Outpatient total \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Hospital acute services: Outpatient total upheld \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Hospital acute services: A&E total ... \\\n",
"0 0 ... \n",
"1 0 ... \n",
"2 0 ... \n",
"\n",
" Policy and commercial decisions of trusts total \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Policy and commercial decisions of trusts total upheld \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Code of openness - complaints total \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Code of openness - complaints total upheld \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Hotel services (including food) total \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Hotel services (including food) total upheld Other total.2 \\\n",
"0 0 0 \n",
"1 0 0 \n",
"2 0 0 \n",
"\n",
" Other total upheld.2 TOTAL.2 TOTAL upheld.2 \n",
"0 0 4 0 \n",
"1 0 3 2 \n",
"2 0 4 2 \n",
"\n",
"[3 rows x 107 columns]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"url='http://www.hscic.gov.uk/catalogue/PUB18021/data-writ-comp-nhs-2014-2015-csv.zip'\n",
"#complaints_a=pd.read_csv('data-writ-comp-nhs-2014-2015-csv/Data on Written Complaints in the NHS 2014-15 KO41a csv.csv')\n",
"complaints_a=pd.read_csv(zipgrabber(url,'Data on Written Complaints in the NHS 2014-15 KO41a csv.csv'))\n",
"complaints_a.dropna(how='all',axis=1,inplace=True)\n",
"complaints_a.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 11,
"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>Name</th>\n",
" <th>typ</th>\n",
" <th>meta</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0 </th>\n",
" <td> Year</td>\n",
" <td> (date time yyyy/yy)</td>\n",
" <td> Year of collection</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1 </th>\n",
" <td> Return</td>\n",
" <td> (varchar)</td>\n",
" <td> Organisation type (Area Team, GP practice, Den...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2 </th>\n",
" <td> Area_Team_Code</td>\n",
" <td> (varchar)</td>\n",
" <td> NHS England Area Team Code</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3 </th>\n",
" <td> Area_Team_Name</td>\n",
" <td> (varchar)</td>\n",
" <td> NHS England Area Team Name</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4 </th>\n",
" <td> Practice_Code</td>\n",
" <td> (varchar)</td>\n",
" <td> Dental/GP Practice Code</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5 </th>\n",
" <td> Medical Total number of written complaints rec...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6 </th>\n",
" <td> Dental Total number of written complaints rece...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7 </th>\n",
" <td> Practice Admin Total number of written complai...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8 </th>\n",
" <td> Other Total number of written complaints received</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9 </th>\n",
" <td> Total number of written complaints received</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td> Communications/attitude Total number of writte...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td> Premises Total number of written complaints re...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td> Practice/surgery management Total number of wr...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td> Practice administration Total number of writte...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td> Clinical Total number of written complaints re...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td> Other Total number of written complaints received</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td> Total number of written complaints received</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td> Medical Total number of written \\ncomplaints u...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td> Dental Total number of written \\ncomplaints up...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td> Practice Admin Total number of written \\ncompl...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td> Other Total number of written \\ncomplaints upheld</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td> Total number of written \\ncomplaints upheld</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by area</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td> Communications/attitude Total number of writte...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td> Premises Total number of written \\ncomplaints ...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td> Practice/surgery management Total number of wr...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td> Practice administration Total number of writte...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td> Clinical Total number of written \\ncomplaints ...</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>27</th>\n",
" <td> Other Total number of written \\ncomplaints upheld</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td> Total number of written \\ncomplaints upheld</td>\n",
" <td> (int)</td>\n",
" <td> Complaint by subject</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Name typ \\\n",
"0 Year (date time yyyy/yy) \n",
"1 Return (varchar) \n",
"2 Area_Team_Code (varchar) \n",
"3 Area_Team_Name (varchar) \n",
"4 Practice_Code (varchar) \n",
"5 Medical Total number of written complaints rec... (int) \n",
"6 Dental Total number of written complaints rece... (int) \n",
"7 Practice Admin Total number of written complai... (int) \n",
"8 Other Total number of written complaints received (int) \n",
"9 Total number of written complaints received (int) \n",
"10 Communications/attitude Total number of writte... (int) \n",
"11 Premises Total number of written complaints re... (int) \n",
"12 Practice/surgery management Total number of wr... (int) \n",
"13 Practice administration Total number of writte... (int) \n",
"14 Clinical Total number of written complaints re... (int) \n",
"15 Other Total number of written complaints received (int) \n",
"16 Total number of written complaints received (int) \n",
"17 Medical Total number of written \\ncomplaints u... (int) \n",
"18 Dental Total number of written \\ncomplaints up... (int) \n",
"19 Practice Admin Total number of written \\ncompl... (int) \n",
"20 Other Total number of written \\ncomplaints upheld (int) \n",
"21 Total number of written \\ncomplaints upheld (int) \n",
"22 Communications/attitude Total number of writte... (int) \n",
"23 Premises Total number of written \\ncomplaints ... (int) \n",
"24 Practice/surgery management Total number of wr... (int) \n",
"25 Practice administration Total number of writte... (int) \n",
"26 Clinical Total number of written \\ncomplaints ... (int) \n",
"27 Other Total number of written \\ncomplaints upheld (int) \n",
"28 Total number of written \\ncomplaints upheld (int) \n",
"\n",
" meta \n",
"0 Year of collection \n",
"1 Organisation type (Area Team, GP practice, Den... \n",
"2 NHS England Area Team Code \n",
"3 NHS England Area Team Name \n",
"4 Dental/GP Practice Code \n",
"5 Complaint by area \n",
"6 Complaint by area \n",
"7 Complaint by area \n",
"8 Complaint by area \n",
"9 Complaint by area \n",
"10 Complaint by subject \n",
"11 Complaint by subject \n",
"12 Complaint by subject \n",
"13 Complaint by subject \n",
"14 Complaint by subject \n",
"15 Complaint by subject \n",
"16 Complaint by subject \n",
"17 Complaint by area \n",
"18 Complaint by area \n",
"19 Complaint by area \n",
"20 Complaint by area \n",
"21 Complaint by area \n",
"22 Complaint by subject \n",
"23 Complaint by subject \n",
"24 Complaint by subject \n",
"25 Complaint by subject \n",
"26 Complaint by subject \n",
"27 Complaint by subject \n",
"28 Complaint by subject "
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#http://data.gov.uk/dataset/data_on_written_complaints_in_the_nhs\n",
"url='http://www.hscic.gov.uk/catalogue/PUB18021/data-writ-comp-nhs-2014-2015-csv.zip'\n",
"#xl=pd.ExcelFile('data-writ-comp-nhs-2014-2015-csv/NHS Written Complaints 2014_15 KO41b meta data.xls')\n",
"xl=pd.ExcelFile(zipgrabber(url,'NHS Written Complaints 2014_15 KO41b meta data.xls'))\n",
"xl.sheet_names\n",
"complaints_meta=xl.parse('KO41b Metadata',header=None)\n",
"complaints_meta.columns=['Name','typ','meta']\n",
"complaints_meta"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"cols=['::'.join(x) for x in zip(complaints_meta['Name'],complaints_meta['meta'])]"
]
},
{
"cell_type": "code",
"execution_count": 23,
"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>Year::Year of collection</th>\n",
" <th>Return::Organisation type (Area Team, GP practice, Dental practice) making return</th>\n",
" <th>Area_Team_Code::NHS England Area Team Code</th>\n",
" <th>Area_Team_Name::NHS England Area Team Name</th>\n",
" <th>Practice_Code::Dental/GP Practice Code</th>\n",
" <th>Medical Total number of written complaints received::Complaint by area</th>\n",
" <th>Dental Total number of written complaints received::Complaint by area</th>\n",
" <th>Practice Admin Total number of written complaints received::Complaint by area</th>\n",
" <th>Other Total number of written complaints received::Complaint by area</th>\n",
" <th>Total number of written complaints received::Complaint by area</th>\n",
" <th>...</th>\n",
" <th>Practice Admin Total number of written \n",
"complaints upheld::Complaint by area</th>\n",
" <th>Other Total number of written \n",
"complaints upheld::Complaint by area</th>\n",
" <th>Total number of written \n",
"complaints upheld::Complaint by area</th>\n",
" <th>Communications/attitude Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Premises Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Practice/surgery management Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Practice administration Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Clinical Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Other Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> 2014-15</td>\n",
" <td> NHS England AT</td>\n",
" <td> Q44</td>\n",
" <td> Cheshire, Warrington and Wirral</td>\n",
" <td> Q44</td>\n",
" <td> 124</td>\n",
" <td> 30</td>\n",
" <td> 13</td>\n",
" <td> 0</td>\n",
" <td> 167</td>\n",
" <td>...</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 35</td>\n",
" <td> 7</td>\n",
" <td> 0</td>\n",
" <td> 7</td>\n",
" <td> 6</td>\n",
" <td> 13</td>\n",
" <td> 2</td>\n",
" <td> 35</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> 2014-15</td>\n",
" <td> NHS England AT</td>\n",
" <td> Q45</td>\n",
" <td> Durham, Darlington and Tees</td>\n",
" <td> Q45</td>\n",
" <td> 63</td>\n",
" <td> 16</td>\n",
" <td> 4</td>\n",
" <td> 0</td>\n",
" <td> 83</td>\n",
" <td>...</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 31</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 4</td>\n",
" <td> 3</td>\n",
" <td> 19</td>\n",
" <td> 2</td>\n",
" <td> 31</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 2014-15</td>\n",
" <td> NHS England AT</td>\n",
" <td> Q46</td>\n",
" <td> Greater Manchester</td>\n",
" <td> Q46</td>\n",
" <td> 592</td>\n",
" <td> 94</td>\n",
" <td> 65</td>\n",
" <td> 15</td>\n",
" <td> 766</td>\n",
" <td>...</td>\n",
" <td> 10</td>\n",
" <td> 3</td>\n",
" <td> 83</td>\n",
" <td> 11</td>\n",
" <td> 0</td>\n",
" <td> 31</td>\n",
" <td> 5</td>\n",
" <td> 33</td>\n",
" <td> 3</td>\n",
" <td> 83</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3 rows × 29 columns</p>\n",
"</div>"
],
"text/plain": [
" Year::Year of collection \\\n",
"0 2014-15 \n",
"1 2014-15 \n",
"2 2014-15 \n",
"\n",
" Return::Organisation type (Area Team, GP practice, Dental practice) making return \\\n",
"0 NHS England AT \n",
"1 NHS England AT \n",
"2 NHS England AT \n",
"\n",
" Area_Team_Code::NHS England Area Team Code \\\n",
"0 Q44 \n",
"1 Q45 \n",
"2 Q46 \n",
"\n",
" Area_Team_Name::NHS England Area Team Name \\\n",
"0 Cheshire, Warrington and Wirral \n",
"1 Durham, Darlington and Tees \n",
"2 Greater Manchester \n",
"\n",
" Practice_Code::Dental/GP Practice Code \\\n",
"0 Q44 \n",
"1 Q45 \n",
"2 Q46 \n",
"\n",
" Medical Total number of written complaints received::Complaint by area \\\n",
"0 124 \n",
"1 63 \n",
"2 592 \n",
"\n",
" Dental Total number of written complaints received::Complaint by area \\\n",
"0 30 \n",
"1 16 \n",
"2 94 \n",
"\n",
" Practice Admin Total number of written complaints received::Complaint by area \\\n",
"0 13 \n",
"1 4 \n",
"2 65 \n",
"\n",
" Other Total number of written complaints received::Complaint by area \\\n",
"0 0 \n",
"1 0 \n",
"2 15 \n",
"\n",
" Total number of written complaints received::Complaint by area \\\n",
"0 167 \n",
"1 83 \n",
"2 766 \n",
"\n",
" ... \\\n",
"0 ... \n",
"1 ... \n",
"2 ... \n",
"\n",
" Practice Admin Total number of written \\ncomplaints upheld::Complaint by area \\\n",
"0 3 \n",
"1 3 \n",
"2 10 \n",
"\n",
" Other Total number of written \\ncomplaints upheld::Complaint by area \\\n",
"0 0 \n",
"1 0 \n",
"2 3 \n",
"\n",
" Total number of written \\ncomplaints upheld::Complaint by area \\\n",
"0 35 \n",
"1 31 \n",
"2 83 \n",
"\n",
" Communications/attitude Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 7 \n",
"1 3 \n",
"2 11 \n",
"\n",
" Premises Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"\n",
" Practice/surgery management Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 7 \n",
"1 4 \n",
"2 31 \n",
"\n",
" Practice administration Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 6 \n",
"1 3 \n",
"2 5 \n",
"\n",
" Clinical Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 13 \n",
"1 19 \n",
"2 33 \n",
"\n",
" Other Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 2 \n",
"1 2 \n",
"2 3 \n",
"\n",
" Total number of written \\ncomplaints upheld::Complaint by subject \n",
"0 35 \n",
"1 31 \n",
"2 83 \n",
"\n",
"[3 rows x 29 columns]"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"url='http://www.hscic.gov.uk/catalogue/PUB18021/data-writ-comp-nhs-2014-2015-csv.zip'\n",
"\n",
"complaints_b=pd.read_csv(zipgrabber(url,'Data on Written Complaints in the NHS 2014-15 KO41b csv.csv'))\n",
"realcols = [c for c in complaints_b.columns if not c.startswith('Unnamed')]\n",
"complaints_b=complaints_b[realcols]\n",
"#complaints_b.dropna(how='all',axis=1,inplace=True)\n",
"complaints_b.columns=cols\n",
"complaints_b.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"#wight: 10L (and R1F?)"
]
},
{
"cell_type": "code",
"execution_count": 15,
"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>Organisation Code</th>\n",
" <th>Parent Organisation Code</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td> A81043</td>\n",
" <td> 00M</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> A81044</td>\n",
" <td> 00K</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> A81057</td>\n",
" <td> 00K</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Organisation Code Parent Organisation Code\n",
"0 A81043 00M\n",
"1 A81044 00K\n",
"2 A81057 00K"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#join complaints parent codes\n",
"parentGP=gp[['Organisation Code','Parent Organisation Code']]\n",
"parentDentist=dentists[['Organisation Code','Parent Organisation Code']]\n",
"codeparent=pd.concat([parentGP,parentDentist])\n",
"codeparent.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 16,
"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>Organisation Code</th>\n",
" <th>Parent Organisation Code</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>3163 </th>\n",
" <td> J84606</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3426 </th>\n",
" <td> J84007</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3719 </th>\n",
" <td> J84016</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3757 </th>\n",
" <td> J84004</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3869 </th>\n",
" <td> J84017</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4174 </th>\n",
" <td> J84008</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4175 </th>\n",
" <td> J84011</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4176 </th>\n",
" <td> J84013</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4177 </th>\n",
" <td> J84602</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5065 </th>\n",
" <td> J84010</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5066 </th>\n",
" <td> J84015</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6009 </th>\n",
" <td> Y00438</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6268 </th>\n",
" <td> Y01856</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6571 </th>\n",
" <td> Y02694</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6598 </th>\n",
" <td> Y01857</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6840 </th>\n",
" <td> J84005</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6841 </th>\n",
" <td> J84012</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6897 </th>\n",
" <td> Y00355</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7370 </th>\n",
" <td> Y03833</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8337 </th>\n",
" <td> J84014</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8853 </th>\n",
" <td> J84019</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9702 </th>\n",
" <td> J84003</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9703 </th>\n",
" <td> J84020</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10009</th>\n",
" <td> Y00588</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10347</th>\n",
" <td> Y01858</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10613</th>\n",
" <td> Y02758</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12414</th>\n",
" <td> J84018</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Organisation Code Parent Organisation Code\n",
"3163 J84606 10L\n",
"3426 J84007 10L\n",
"3719 J84016 10L\n",
"3757 J84004 10L\n",
"3869 J84017 10L\n",
"4174 J84008 10L\n",
"4175 J84011 10L\n",
"4176 J84013 10L\n",
"4177 J84602 10L\n",
"5065 J84010 10L\n",
"5066 J84015 10L\n",
"6009 Y00438 10L\n",
"6268 Y01856 10L\n",
"6571 Y02694 10L\n",
"6598 Y01857 10L\n",
"6840 J84005 10L\n",
"6841 J84012 10L\n",
"6897 Y00355 10L\n",
"7370 Y03833 10L\n",
"8337 J84014 10L\n",
"8853 J84019 10L\n",
"9702 J84003 10L\n",
"9703 J84020 10L\n",
"10009 Y00588 10L\n",
"10347 Y01858 10L\n",
"10613 Y02758 10L\n",
"12414 J84018 10L"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"iwcodes=codeparent[(codeparent['Parent Organisation Code']=='10L')]\n",
"iwcodes"
]
},
{
"cell_type": "code",
"execution_count": 17,
"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>Year::Year of collection</th>\n",
" <th>Return::Organisation type (Area Team, GP practice, Dental practice) making return</th>\n",
" <th>Area_Team_Code::NHS England Area Team Code</th>\n",
" <th>Area_Team_Name::NHS England Area Team Name</th>\n",
" <th>Practice_Code::Dental/GP Practice Code</th>\n",
" <th>Medical Total number of written complaints received::Complaint by area</th>\n",
" <th>Dental Total number of written complaints received::Complaint by area</th>\n",
" <th>Practice Admin Total number of written complaints received::Complaint by area</th>\n",
" <th>Other Total number of written complaints received::Complaint by area</th>\n",
" <th>Total number of written complaints received::Complaint by area</th>\n",
" <th>...</th>\n",
" <th>Total number of written \n",
"complaints upheld::Complaint by area</th>\n",
" <th>Communications/attitude Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Premises Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Practice/surgery management Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Practice administration Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Clinical Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Other Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Total number of written \n",
"complaints upheld::Complaint by subject</th>\n",
" <th>Organisation Code</th>\n",
" <th>Parent Organisation Code</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84003</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td>...</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> J84003</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84004</td>\n",
" <td> 4</td>\n",
" <td> 0</td>\n",
" <td> 6</td>\n",
" <td> 1</td>\n",
" <td> 11</td>\n",
" <td>...</td>\n",
" <td> 11</td>\n",
" <td> 4</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 3</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 11</td>\n",
" <td> J84004</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84005</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 1</td>\n",
" <td> 6</td>\n",
" <td>...</td>\n",
" <td> 5</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 4</td>\n",
" <td> J84005</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84007</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td>...</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 3</td>\n",
" <td> 4</td>\n",
" <td> 0</td>\n",
" <td> 10</td>\n",
" <td> J84007</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84008</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 4</td>\n",
" <td>...</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> J84008</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84010</td>\n",
" <td> 8</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 8</td>\n",
" <td>...</td>\n",
" <td> 2</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> J84010</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84011</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 3</td>\n",
" <td> 4</td>\n",
" <td> 9</td>\n",
" <td>...</td>\n",
" <td> 9</td>\n",
" <td> 3</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 3</td>\n",
" <td> 1</td>\n",
" <td> 9</td>\n",
" <td> J84011</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84012</td>\n",
" <td> 7</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 8</td>\n",
" <td>...</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> J84012</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84013</td>\n",
" <td> 6</td>\n",
" <td> 0</td>\n",
" <td> 3</td>\n",
" <td> 1</td>\n",
" <td> 10</td>\n",
" <td>...</td>\n",
" <td> 5</td>\n",
" <td> 4</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 5</td>\n",
" <td> J84013</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9 </th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84014</td>\n",
" <td> 9</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 11</td>\n",
" <td>...</td>\n",
" <td> 11</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 9</td>\n",
" <td> 11</td>\n",
" <td> J84014</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84015</td>\n",
" <td> 8</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 10</td>\n",
" <td>...</td>\n",
" <td> 4</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 4</td>\n",
" <td> J84015</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84016</td>\n",
" <td> 7</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 9</td>\n",
" <td>...</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> J84016</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84017</td>\n",
" <td> 5</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 7</td>\n",
" <td>...</td>\n",
" <td> 7</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 2</td>\n",
" <td> 4</td>\n",
" <td> 0</td>\n",
" <td> 7</td>\n",
" <td> J84017</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84018</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 6</td>\n",
" <td>...</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 3</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 3</td>\n",
" <td> J84018</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84019</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td>...</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> J84019</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84020</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td>...</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> J84020</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td> 2014-15</td>\n",
" <td> GP</td>\n",
" <td> Q70</td>\n",
" <td> Wessex</td>\n",
" <td> J84602</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 1</td>\n",
" <td> 3</td>\n",
" <td>...</td>\n",
" <td> 2</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 1</td>\n",
" <td> 0</td>\n",
" <td> 0</td>\n",
" <td> 2</td>\n",
" <td> J84602</td>\n",
" <td> 10L</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>17 rows × 31 columns</p>\n",
"</div>"
],
"text/plain": [
" Year::Year of collection \\\n",
"0 2014-15 \n",
"1 2014-15 \n",
"2 2014-15 \n",
"3 2014-15 \n",
"4 2014-15 \n",
"5 2014-15 \n",
"6 2014-15 \n",
"7 2014-15 \n",
"8 2014-15 \n",
"9 2014-15 \n",
"10 2014-15 \n",
"11 2014-15 \n",
"12 2014-15 \n",
"13 2014-15 \n",
"14 2014-15 \n",
"15 2014-15 \n",
"16 2014-15 \n",
"\n",
" Return::Organisation type (Area Team, GP practice, Dental practice) making return \\\n",
"0 GP \n",
"1 GP \n",
"2 GP \n",
"3 GP \n",
"4 GP \n",
"5 GP \n",
"6 GP \n",
"7 GP \n",
"8 GP \n",
"9 GP \n",
"10 GP \n",
"11 GP \n",
"12 GP \n",
"13 GP \n",
"14 GP \n",
"15 GP \n",
"16 GP \n",
"\n",
" Area_Team_Code::NHS England Area Team Code \\\n",
"0 Q70 \n",
"1 Q70 \n",
"2 Q70 \n",
"3 Q70 \n",
"4 Q70 \n",
"5 Q70 \n",
"6 Q70 \n",
"7 Q70 \n",
"8 Q70 \n",
"9 Q70 \n",
"10 Q70 \n",
"11 Q70 \n",
"12 Q70 \n",
"13 Q70 \n",
"14 Q70 \n",
"15 Q70 \n",
"16 Q70 \n",
"\n",
" Area_Team_Name::NHS England Area Team Name \\\n",
"0 Wessex \n",
"1 Wessex \n",
"2 Wessex \n",
"3 Wessex \n",
"4 Wessex \n",
"5 Wessex \n",
"6 Wessex \n",
"7 Wessex \n",
"8 Wessex \n",
"9 Wessex \n",
"10 Wessex \n",
"11 Wessex \n",
"12 Wessex \n",
"13 Wessex \n",
"14 Wessex \n",
"15 Wessex \n",
"16 Wessex \n",
"\n",
" Practice_Code::Dental/GP Practice Code \\\n",
"0 J84003 \n",
"1 J84004 \n",
"2 J84005 \n",
"3 J84007 \n",
"4 J84008 \n",
"5 J84010 \n",
"6 J84011 \n",
"7 J84012 \n",
"8 J84013 \n",
"9 J84014 \n",
"10 J84015 \n",
"11 J84016 \n",
"12 J84017 \n",
"13 J84018 \n",
"14 J84019 \n",
"15 J84020 \n",
"16 J84602 \n",
"\n",
" Medical Total number of written complaints received::Complaint by area \\\n",
"0 0 \n",
"1 4 \n",
"2 3 \n",
"3 0 \n",
"4 3 \n",
"5 8 \n",
"6 2 \n",
"7 7 \n",
"8 6 \n",
"9 9 \n",
"10 8 \n",
"11 7 \n",
"12 5 \n",
"13 3 \n",
"14 0 \n",
"15 0 \n",
"16 1 \n",
"\n",
" Dental Total number of written complaints received::Complaint by area \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"3 0 \n",
"4 0 \n",
"5 0 \n",
"6 0 \n",
"7 0 \n",
"8 0 \n",
"9 0 \n",
"10 0 \n",
"11 0 \n",
"12 0 \n",
"13 0 \n",
"14 0 \n",
"15 0 \n",
"16 0 \n",
"\n",
" Practice Admin Total number of written complaints received::Complaint by area \\\n",
"0 1 \n",
"1 6 \n",
"2 2 \n",
"3 0 \n",
"4 0 \n",
"5 0 \n",
"6 3 \n",
"7 1 \n",
"8 3 \n",
"9 2 \n",
"10 2 \n",
"11 0 \n",
"12 2 \n",
"13 3 \n",
"14 0 \n",
"15 2 \n",
"16 1 \n",
"\n",
" Other Total number of written complaints received::Complaint by area \\\n",
"0 0 \n",
"1 1 \n",
"2 1 \n",
"3 0 \n",
"4 1 \n",
"5 0 \n",
"6 4 \n",
"7 0 \n",
"8 1 \n",
"9 0 \n",
"10 0 \n",
"11 2 \n",
"12 0 \n",
"13 0 \n",
"14 0 \n",
"15 0 \n",
"16 1 \n",
"\n",
" Total number of written complaints received::Complaint by area \\\n",
"0 1 \n",
"1 11 \n",
"2 6 \n",
"3 0 \n",
"4 4 \n",
"5 8 \n",
"6 9 \n",
"7 8 \n",
"8 10 \n",
"9 11 \n",
"10 10 \n",
"11 9 \n",
"12 7 \n",
"13 6 \n",
"14 0 \n",
"15 2 \n",
"16 3 \n",
"\n",
" ... \\\n",
"0 ... \n",
"1 ... \n",
"2 ... \n",
"3 ... \n",
"4 ... \n",
"5 ... \n",
"6 ... \n",
"7 ... \n",
"8 ... \n",
"9 ... \n",
"10 ... \n",
"11 ... \n",
"12 ... \n",
"13 ... \n",
"14 ... \n",
"15 ... \n",
"16 ... \n",
"\n",
" Total number of written \\ncomplaints upheld::Complaint by area \\\n",
"0 0 \n",
"1 11 \n",
"2 5 \n",
"3 0 \n",
"4 0 \n",
"5 2 \n",
"6 9 \n",
"7 1 \n",
"8 5 \n",
"9 11 \n",
"10 4 \n",
"11 0 \n",
"12 7 \n",
"13 3 \n",
"14 0 \n",
"15 1 \n",
"16 2 \n",
"\n",
" Communications/attitude Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 0 \n",
"1 4 \n",
"2 2 \n",
"3 2 \n",
"4 0 \n",
"5 1 \n",
"6 3 \n",
"7 0 \n",
"8 4 \n",
"9 0 \n",
"10 0 \n",
"11 0 \n",
"12 0 \n",
"13 0 \n",
"14 0 \n",
"15 0 \n",
"16 1 \n",
"\n",
" Premises Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 0 \n",
"1 1 \n",
"2 0 \n",
"3 0 \n",
"4 0 \n",
"5 0 \n",
"6 1 \n",
"7 0 \n",
"8 0 \n",
"9 0 \n",
"10 0 \n",
"11 0 \n",
"12 0 \n",
"13 0 \n",
"14 0 \n",
"15 0 \n",
"16 0 \n",
"\n",
" Practice/surgery management Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"3 1 \n",
"4 0 \n",
"5 0 \n",
"6 0 \n",
"7 0 \n",
"8 0 \n",
"9 0 \n",
"10 0 \n",
"11 0 \n",
"12 1 \n",
"13 0 \n",
"14 0 \n",
"15 0 \n",
"16 0 \n",
"\n",
" Practice administration Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 0 \n",
"1 3 \n",
"2 1 \n",
"3 3 \n",
"4 0 \n",
"5 0 \n",
"6 1 \n",
"7 1 \n",
"8 1 \n",
"9 2 \n",
"10 2 \n",
"11 0 \n",
"12 2 \n",
"13 3 \n",
"14 0 \n",
"15 1 \n",
"16 1 \n",
"\n",
" Clinical Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 0 \n",
"1 3 \n",
"2 1 \n",
"3 4 \n",
"4 0 \n",
"5 1 \n",
"6 3 \n",
"7 0 \n",
"8 0 \n",
"9 0 \n",
"10 2 \n",
"11 0 \n",
"12 4 \n",
"13 0 \n",
"14 0 \n",
"15 0 \n",
"16 0 \n",
"\n",
" Other Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 0 \n",
"1 0 \n",
"2 0 \n",
"3 0 \n",
"4 0 \n",
"5 0 \n",
"6 1 \n",
"7 0 \n",
"8 0 \n",
"9 9 \n",
"10 0 \n",
"11 0 \n",
"12 0 \n",
"13 0 \n",
"14 0 \n",
"15 0 \n",
"16 0 \n",
"\n",
" Total number of written \\ncomplaints upheld::Complaint by subject \\\n",
"0 0 \n",
"1 11 \n",
"2 4 \n",
"3 10 \n",
"4 0 \n",
"5 2 \n",
"6 9 \n",
"7 1 \n",
"8 5 \n",
"9 11 \n",
"10 4 \n",
"11 0 \n",
"12 7 \n",
"13 3 \n",
"14 0 \n",
"15 1 \n",
"16 2 \n",
"\n",
" Organisation Code Parent Organisation Code \n",
"0 J84003 10L \n",
"1 J84004 10L \n",
"2 J84005 10L \n",
"3 J84007 10L \n",
"4 J84008 10L \n",
"5 J84010 10L \n",
"6 J84011 10L \n",
"7 J84012 10L \n",
"8 J84013 10L \n",
"9 J84014 10L \n",
"10 J84015 10L \n",
"11 J84016 10L \n",
"12 J84017 10L \n",
"13 J84018 10L \n",
"14 J84019 10L \n",
"15 J84020 10L \n",
"16 J84602 10L \n",
"\n",
"[17 rows x 31 columns]"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"complaints_iw=pd.merge(complaints_b,iwcodes, left_on='Practice_Code::Dental/GP Practice Code', right_on='Organisation Code')\n",
"complaints_iw"
]
},
{
"cell_type": "code",
"execution_count": 18,
"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>Organisation Code</th>\n",
" <th>Name</th>\n",
" <th>National Grouping</th>\n",
" <th>High Level Health Geography</th>\n",
" <th>Address Line 1</th>\n",
" <th>Address Line 2</th>\n",
" <th>Address Line 3</th>\n",
" <th>Address Line 4</th>\n",
" <th>Address Line 5</th>\n",
" <th>Postcode</th>\n",
" <th>...</th>\n",
" <th>Left Parent Date</th>\n",
" <th>Contact Telephone Number</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Available for future use</th>\n",
" <th>Amended Record Indicator</th>\n",
" <th>Available for future use</th>\n",
" <th>Provider/Purchaser</th>\n",
" <th>Available for future use</th>\n",
" <th>Prescribing Setting</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>3426 </th>\n",
" <td> J84007</td>\n",
" <td> ST.HELENS MEDICAL CENTRE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> ST.HELENS MEDICAL CENTRE</td>\n",
" <td> UPPER GREEN ROAD</td>\n",
" <td> ST.HELENS</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO33 1UG</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 871828</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3719 </th>\n",
" <td> J84016</td>\n",
" <td> SOUTH WIGHT MEDICAL PRACTICE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> THE SURGERY</td>\n",
" <td> NEW ROAD</td>\n",
" <td> BRIGHSTONE</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO30 4BB</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 740219</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3757 </th>\n",
" <td> J84004</td>\n",
" <td> EAST COWES MEDICAL CENTRE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> EAST COWES MEDICAL CENTRE</td>\n",
" <td> CHURCH PATH</td>\n",
" <td> EAST COWES</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO32 6RP</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 284333</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3869 </th>\n",
" <td> J84017</td>\n",
" <td> MEDINA HEALTHCARE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> MEDINA HEALTHCARE</td>\n",
" <td> 16 WEST STREET</td>\n",
" <td> NEWPORT</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO30 1PR</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 522198</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4174 </th>\n",
" <td> J84008</td>\n",
" <td> ARGYLL HOUSE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> ARGYLL HOUSE</td>\n",
" <td> WEST STREET</td>\n",
" <td> RYDE</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO33 2QG</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 562955</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4175 </th>\n",
" <td> J84011</td>\n",
" <td> CARISBROOKE HEALTH CENTRE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> CARISBROOKE HEALTH CENTRE</td>\n",
" <td> 22 CARISBROOKE HIGH ST.</td>\n",
" <td> CARISBROOKE, NEWPORT</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO30 1NR</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 522150</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4176 </th>\n",
" <td> J84013</td>\n",
" <td> SANDOWN HEALTH CENTRE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> SANDOWN HEALTH CENTRE</td>\n",
" <td> BROADWAY</td>\n",
" <td> SANDOWN</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO36 9GA</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 409292</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4177 </th>\n",
" <td> J84602</td>\n",
" <td> GARFIELD ROAD SURGERY</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> 18 GARFIELD ROAD</td>\n",
" <td> RYDE</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> NaN</td>\n",
" <td> PO33 2PT</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 565103</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5065 </th>\n",
" <td> J84010</td>\n",
" <td> SHANKLIN MEDICAL CENTRE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> SHANKLIN MEDICAL CENTRE</td>\n",
" <td> CARTER ROAD</td>\n",
" <td> SHANKLIN</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO37 7HR</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 862245</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5066 </th>\n",
" <td> J84015</td>\n",
" <td> COWES HEALTH CENTRE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> COWES MEDICAL CENTRE</td>\n",
" <td> 200 NEWPORT ROAD</td>\n",
" <td> COWES</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO31 7ER</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 295251</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6840 </th>\n",
" <td> J84005</td>\n",
" <td> ESPLANADE SURGERY</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> THE ESPLANADE SURGERY</td>\n",
" <td> 19 THE ESPLANADE</td>\n",
" <td> RYDE</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO33 2EH</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 618388</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6841 </th>\n",
" <td> J84012</td>\n",
" <td> TOWER HOUSE SURGERY</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> TOWER HOUSE SURGERY</td>\n",
" <td> RINK ROAD</td>\n",
" <td> RYDE</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO33 1LP</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 817200</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8337 </th>\n",
" <td> J84014</td>\n",
" <td> THE DOWER HOUSE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> THE DOWER HOUSE</td>\n",
" <td> 27 PYLE STREET</td>\n",
" <td> NEWPORT</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO30 1JW</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 523525</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8853 </th>\n",
" <td> J84019</td>\n",
" <td> BROOKSIDE HEALTH CENTRE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> BROOKSIDE HEALTH CENTRE</td>\n",
" <td> QUEENS ROAD</td>\n",
" <td> FRESHWATER</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO40 9DT</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 758998</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9702 </th>\n",
" <td> J84003</td>\n",
" <td> VENTNOR MEDICAL CENTRE</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> VENTNOR MEDICAL CENTRE</td>\n",
" <td> 3 ALBERT STREET</td>\n",
" <td> VENTNOR</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO38 1EZ</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 857288</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9703 </th>\n",
" <td> J84020</td>\n",
" <td> BEECH GROVE SURGERY</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> BEECH GROVE SURGERY</td>\n",
" <td> THE MALL</td>\n",
" <td> BRADING</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO36 0DE</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 407558</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12414</th>\n",
" <td> J84018</td>\n",
" <td> GROVE HOUSE SURGERY</td>\n",
" <td> Y57</td>\n",
" <td> Q70</td>\n",
" <td> GROVE HOUSE SURGERY</td>\n",
" <td> 102 ALBERT STREET</td>\n",
" <td> VENTNOR</td>\n",
" <td> ISLE OF WIGHT</td>\n",
" <td> NaN</td>\n",
" <td> PO38 1EU</td>\n",
" <td>...</td>\n",
" <td>NaN</td>\n",
" <td> 01983 852427</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td> 0</td>\n",
" <td>NaN</td>\n",
" <td> 10L</td>\n",
" <td>NaN</td>\n",
" <td> 4</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>17 rows × 26 columns</p>\n",
"</div>"
],
"text/plain": [
" Organisation Code Name National Grouping \\\n",
"3426 J84007 ST.HELENS MEDICAL CENTRE Y57 \n",
"3719 J84016 SOUTH WIGHT MEDICAL PRACTICE Y57 \n",
"3757 J84004 EAST COWES MEDICAL CENTRE Y57 \n",
"3869 J84017 MEDINA HEALTHCARE Y57 \n",
"4174 J84008 ARGYLL HOUSE Y57 \n",
"4175 J84011 CARISBROOKE HEALTH CENTRE Y57 \n",
"4176 J84013 SANDOWN HEALTH CENTRE Y57 \n",
"4177 J84602 GARFIELD ROAD SURGERY Y57 \n",
"5065 J84010 SHANKLIN MEDICAL CENTRE Y57 \n",
"5066 J84015 COWES HEALTH CENTRE Y57 \n",
"6840 J84005 ESPLANADE SURGERY Y57 \n",
"6841 J84012 TOWER HOUSE SURGERY Y57 \n",
"8337 J84014 THE DOWER HOUSE Y57 \n",
"8853 J84019 BROOKSIDE HEALTH CENTRE Y57 \n",
"9702 J84003 VENTNOR MEDICAL CENTRE Y57 \n",
"9703 J84020 BEECH GROVE SURGERY Y57 \n",
"12414 J84018 GROVE HOUSE SURGERY Y57 \n",
"\n",
" High Level Health Geography Address Line 1 \\\n",
"3426 Q70 ST.HELENS MEDICAL CENTRE \n",
"3719 Q70 THE SURGERY \n",
"3757 Q70 EAST COWES MEDICAL CENTRE \n",
"3869 Q70 MEDINA HEALTHCARE \n",
"4174 Q70 ARGYLL HOUSE \n",
"4175 Q70 CARISBROOKE HEALTH CENTRE \n",
"4176 Q70 SANDOWN HEALTH CENTRE \n",
"4177 Q70 18 GARFIELD ROAD \n",
"5065 Q70 SHANKLIN MEDICAL CENTRE \n",
"5066 Q70 COWES MEDICAL CENTRE \n",
"6840 Q70 THE ESPLANADE SURGERY \n",
"6841 Q70 TOWER HOUSE SURGERY \n",
"8337 Q70 THE DOWER HOUSE \n",
"8853 Q70 BROOKSIDE HEALTH CENTRE \n",
"9702 Q70 VENTNOR MEDICAL CENTRE \n",
"9703 Q70 BEECH GROVE SURGERY \n",
"12414 Q70 GROVE HOUSE SURGERY \n",
"\n",
" Address Line 2 Address Line 3 Address Line 4 \\\n",
"3426 UPPER GREEN ROAD ST.HELENS ISLE OF WIGHT \n",
"3719 NEW ROAD BRIGHSTONE ISLE OF WIGHT \n",
"3757 CHURCH PATH EAST COWES ISLE OF WIGHT \n",
"3869 16 WEST STREET NEWPORT ISLE OF WIGHT \n",
"4174 WEST STREET RYDE ISLE OF WIGHT \n",
"4175 22 CARISBROOKE HIGH ST. CARISBROOKE, NEWPORT ISLE OF WIGHT \n",
"4176 BROADWAY SANDOWN ISLE OF WIGHT \n",
"4177 RYDE ISLE OF WIGHT NaN \n",
"5065 CARTER ROAD SHANKLIN ISLE OF WIGHT \n",
"5066 200 NEWPORT ROAD COWES ISLE OF WIGHT \n",
"6840 19 THE ESPLANADE RYDE ISLE OF WIGHT \n",
"6841 RINK ROAD RYDE ISLE OF WIGHT \n",
"8337 27 PYLE STREET NEWPORT ISLE OF WIGHT \n",
"8853 QUEENS ROAD FRESHWATER ISLE OF WIGHT \n",
"9702 3 ALBERT STREET VENTNOR ISLE OF WIGHT \n",
"9703 THE MALL BRADING ISLE OF WIGHT \n",
"12414 102 ALBERT STREET VENTNOR ISLE OF WIGHT \n",
"\n",
" Address Line 5 Postcode ... Left Parent Date \\\n",
"3426 NaN PO33 1UG ... NaN \n",
"3719 NaN PO30 4BB ... NaN \n",
"3757 NaN PO32 6RP ... NaN \n",
"3869 NaN PO30 1PR ... NaN \n",
"4174 NaN PO33 2QG ... NaN \n",
"4175 NaN PO30 1NR ... NaN \n",
"4176 NaN PO36 9GA ... NaN \n",
"4177 NaN PO33 2PT ... NaN \n",
"5065 NaN PO37 7HR ... NaN \n",
"5066 NaN PO31 7ER ... NaN \n",
"6840 NaN PO33 2EH ... NaN \n",
"6841 NaN PO33 1LP ... NaN \n",
"8337 NaN PO30 1JW ... NaN \n",
"8853 NaN PO40 9DT ... NaN \n",
"9702 NaN PO38 1EZ ... NaN \n",
"9703 NaN PO36 0DE ... NaN \n",
"12414 NaN PO38 1EU ... NaN \n",
"\n",
" Contact Telephone Number Available for future use \\\n",
"3426 01983 871828 NaN \n",
"3719 01983 740219 NaN \n",
"3757 01983 284333 NaN \n",
"3869 01983 522198 NaN \n",
"4174 01983 562955 NaN \n",
"4175 01983 522150 NaN \n",
"4176 01983 409292 NaN \n",
"4177 01983 565103 NaN \n",
"5065 01983 862245 NaN \n",
"5066 01983 295251 NaN \n",
"6840 01983 618388 NaN \n",
"6841 01983 817200 NaN \n",
"8337 01983 523525 NaN \n",
"8853 01983 758998 NaN \n",
"9702 01983 857288 NaN \n",
"9703 01983 407558 NaN \n",
"12414 01983 852427 NaN \n",
"\n",
" Available for future use Available for future use \\\n",
"3426 NaN NaN \n",
"3719 NaN NaN \n",
"3757 NaN NaN \n",
"3869 NaN NaN \n",
"4174 NaN NaN \n",
"4175 NaN NaN \n",
"4176 NaN NaN \n",
"4177 NaN NaN \n",
"5065 NaN NaN \n",
"5066 NaN NaN \n",
"6840 NaN NaN \n",
"6841 NaN NaN \n",
"8337 NaN NaN \n",
"8853 NaN NaN \n",
"9702 NaN NaN \n",
"9703 NaN NaN \n",
"12414 NaN NaN \n",
"\n",
" Amended Record Indicator Available for future use Provider/Purchaser \\\n",
"3426 0 NaN 10L \n",
"3719 0 NaN 10L \n",
"3757 0 NaN 10L \n",
"3869 0 NaN 10L \n",
"4174 0 NaN 10L \n",
"4175 0 NaN 10L \n",
"4176 0 NaN 10L \n",
"4177 0 NaN 10L \n",
"5065 0 NaN 10L \n",
"5066 0 NaN 10L \n",
"6840 0 NaN 10L \n",
"6841 0 NaN 10L \n",
"8337 0 NaN 10L \n",
"8853 0 NaN 10L \n",
"9702 0 NaN 10L \n",
"9703 0 NaN 10L \n",
"12414 0 NaN 10L \n",
"\n",
" Available for future use Prescribing Setting \n",
"3426 NaN 4 \n",
"3719 NaN 4 \n",
"3757 NaN 4 \n",
"3869 NaN 4 \n",
"4174 NaN 4 \n",
"4175 NaN 4 \n",
"4176 NaN 4 \n",
"4177 NaN 4 \n",
"5065 NaN 4 \n",
"5066 NaN 4 \n",
"6840 NaN 4 \n",
"6841 NaN 4 \n",
"8337 NaN 4 \n",
"8853 NaN 4 \n",
"9702 NaN 4 \n",
"9703 NaN 4 \n",
"12414 NaN 4 \n",
"\n",
"[17 rows x 26 columns]"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"gp[gp['Organisation Code'].isin(complaints_iw['Practice_Code::Dental/GP Practice Code'].tolist())]"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"---------------\n",
"Complaint report for: VENTNOR MEDICAL CENTRE (J84003)\n",
"\n",
"Address: VENTNOR MEDICAL CENTRE, 3 ALBERT STREET, VENTNOR, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (1 received, of which 0 upheld):\n",
" - Practice Admin: 1 received, of which 0 upheld.\n",
"\n",
"Complaints by subject (3 received, of which 0 upheld):\n",
" - Clinical: 1 received, of which 0 upheld.\n",
" - Communications/attitude: 1 received, of which 0 upheld.\n",
" - Practice administration: 1 received, of which 0 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: EAST COWES MEDICAL CENTRE (J84004)\n",
"\n",
"Address: EAST COWES MEDICAL CENTRE, CHURCH PATH, EAST COWES, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (11 received, of which 11 upheld):\n",
" - Practice Admin: 6 received, of which 6 upheld.\n",
" - Medical: 4 received, of which 4 upheld.\n",
" - Other: 1 received, of which 1 upheld.\n",
"\n",
"Complaints by subject (11 received, of which 11 upheld):\n",
" - Premises: 1 received, of which 1 upheld.\n",
" - Clinical: 3 received, of which 3 upheld.\n",
" - Communications/attitude: 4 received, of which 4 upheld.\n",
" - Practice administration: 3 received, of which 3 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: ESPLANADE SURGERY (J84005)\n",
"\n",
"Address: THE ESPLANADE SURGERY, 19 THE ESPLANADE, RYDE, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (6 received, of which 5 upheld):\n",
" - Practice Admin: 2 received, of which 2 upheld.\n",
" - Medical: 3 received, of which 2 upheld.\n",
" - Other: 1 received, of which 1 upheld.\n",
"\n",
"Complaints by subject (6 received, of which 4 upheld):\n",
" - Other: 2 received, of which 0 upheld.\n",
" - Clinical: 1 received, of which 1 upheld.\n",
" - Communications/attitude: 2 received, of which 2 upheld.\n",
" - Practice administration: 1 received, of which 1 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: ST.HELENS MEDICAL CENTRE (J84007)\n",
"\n",
"Address: ST.HELENS MEDICAL CENTRE, UPPER GREEN ROAD, ST.HELENS, ISLE OF WIGHT.\n",
"\n",
"Complaints by subject (12 received, of which 10 upheld):\n",
" - Communications/attitude: 2 received, of which 2 upheld.\n",
" - Practice administration: 3 received, of which 3 upheld.\n",
" - Practice/surgery management: 1 received, of which 1 upheld.\n",
" - Other: 1 received, of which 0 upheld.\n",
" - Clinical: 5 received, of which 4 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: ARGYLL HOUSE (J84008)\n",
"\n",
"Address: ARGYLL HOUSE, WEST STREET, RYDE, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (4 received, of which 0 upheld):\n",
" - Medical: 3 received, of which 0 upheld.\n",
" - Other: 1 received, of which 0 upheld.\n",
"\n",
"Complaints by subject (4 received, of which 0 upheld):\n",
" - Communications/attitude: 1 received, of which 0 upheld.\n",
" - Clinical: 3 received, of which 0 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: SHANKLIN MEDICAL CENTRE (J84010)\n",
"\n",
"Address: SHANKLIN MEDICAL CENTRE, CARTER ROAD, SHANKLIN, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (8 received, of which 2 upheld):\n",
" - Medical: 8 received, of which 2 upheld.\n",
"\n",
"Complaints by subject (8 received, of which 2 upheld):\n",
" - Communications/attitude: 7 received, of which 1 upheld.\n",
" - Clinical: 1 received, of which 1 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: CARISBROOKE HEALTH CENTRE (J84011)\n",
"\n",
"Address: CARISBROOKE HEALTH CENTRE, 22 CARISBROOKE HIGH ST., CARISBROOKE, NEWPORT, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (9 received, of which 9 upheld):\n",
" - Practice Admin: 3 received, of which 3 upheld.\n",
" - Medical: 2 received, of which 2 upheld.\n",
" - Other: 4 received, of which 4 upheld.\n",
"\n",
"Complaints by subject (9 received, of which 9 upheld):\n",
" - Communications/attitude: 3 received, of which 3 upheld.\n",
" - Premises: 1 received, of which 1 upheld.\n",
" - Practice administration: 1 received, of which 1 upheld.\n",
" - Clinical: 3 received, of which 3 upheld.\n",
" - Other: 1 received, of which 1 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: TOWER HOUSE SURGERY (J84012)\n",
"\n",
"Address: TOWER HOUSE SURGERY, RINK ROAD, RYDE, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (8 received, of which 1 upheld):\n",
" - Practice Admin: 1 received, of which 1 upheld.\n",
" - Medical: 7 received, of which 0 upheld.\n",
"\n",
"Complaints by subject (8 received, of which 1 upheld):\n",
" - Clinical: 5 received, of which 0 upheld.\n",
" - Communications/attitude: 2 received, of which 0 upheld.\n",
" - Practice administration: 1 received, of which 1 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: SANDOWN HEALTH CENTRE (J84013)\n",
"\n",
"Address: SANDOWN HEALTH CENTRE, BROADWAY, SANDOWN, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (10 received, of which 5 upheld):\n",
" - Practice Admin: 3 received, of which 1 upheld.\n",
" - Medical: 6 received, of which 4 upheld.\n",
" - Other: 1 received, of which 0 upheld.\n",
"\n",
"Complaints by subject (10 received, of which 5 upheld):\n",
" - Clinical: 3 received, of which 0 upheld.\n",
" - Communications/attitude: 6 received, of which 4 upheld.\n",
" - Practice administration: 1 received, of which 1 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: THE DOWER HOUSE (J84014)\n",
"\n",
"Address: THE DOWER HOUSE, 27 PYLE STREET, NEWPORT, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (11 received, of which 11 upheld):\n",
" - Practice Admin: 2 received, of which 2 upheld.\n",
" - Medical: 9 received, of which 9 upheld.\n",
"\n",
"Complaints by subject (11 received, of which 11 upheld):\n",
" - Other: 0 received, of which 9 upheld.\n",
" - Clinical: 9 received, of which 0 upheld.\n",
" - Practice administration: 2 received, of which 2 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: COWES HEALTH CENTRE (J84015)\n",
"\n",
"Address: COWES MEDICAL CENTRE, 200 NEWPORT ROAD, COWES, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (10 received, of which 4 upheld):\n",
" - Practice Admin: 2 received, of which 2 upheld.\n",
" - Medical: 8 received, of which 2 upheld.\n",
"\n",
"Complaints by subject (10 received, of which 4 upheld):\n",
" - Clinical: 8 received, of which 2 upheld.\n",
" - Practice administration: 2 received, of which 2 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: SOUTH WIGHT MEDICAL PRACTICE (J84016)\n",
"\n",
"Address: THE SURGERY, NEW ROAD, BRIGHSTONE, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (9 received, of which 0 upheld):\n",
" - Medical: 7 received, of which 0 upheld.\n",
" - Other: 2 received, of which 0 upheld.\n",
"\n",
"Complaints by subject (9 received, of which 0 upheld):\n",
" - Communications/attitude: 2 received, of which 0 upheld.\n",
" - Clinical: 7 received, of which 0 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: MEDINA HEALTHCARE (J84017)\n",
"\n",
"Address: MEDINA HEALTHCARE, 16 WEST STREET, NEWPORT, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (7 received, of which 7 upheld):\n",
" - Practice Admin: 2 received, of which 2 upheld.\n",
" - Medical: 5 received, of which 5 upheld.\n",
"\n",
"Complaints by subject (7 received, of which 7 upheld):\n",
" - Clinical: 4 received, of which 4 upheld.\n",
" - Practice administration: 2 received, of which 2 upheld.\n",
" - Practice/surgery management: 1 received, of which 1 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: GROVE HOUSE SURGERY (J84018)\n",
"\n",
"Address: GROVE HOUSE SURGERY, 102 ALBERT STREET, VENTNOR, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (6 received, of which 3 upheld):\n",
" - Practice Admin: 3 received, of which 3 upheld.\n",
" - Medical: 3 received, of which 0 upheld.\n",
"\n",
"Complaints by subject (6 received, of which 3 upheld):\n",
" - Clinical: 3 received, of which 0 upheld.\n",
" - Practice administration: 3 received, of which 3 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: BROOKSIDE HEALTH CENTRE (J84019)\n",
"\n",
"Address: BROOKSIDE HEALTH CENTRE, QUEENS ROAD, FRESHWATER, ISLE OF WIGHT.\n",
"\n",
"Complaints by subject (10 received, of which 0 upheld):\n",
" - Clinical: 3 received, of which 0 upheld.\n",
" - Communications/attitude: 4 received, of which 0 upheld.\n",
" - Practice administration: 3 received, of which 0 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: BEECH GROVE SURGERY (J84020)\n",
"\n",
"Address: BEECH GROVE SURGERY, THE MALL, BRADING, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (2 received, of which 1 upheld):\n",
" - Practice Admin: 2 received, of which 1 upheld.\n",
"\n",
"Complaints by subject (2 received, of which 1 upheld):\n",
" - Practice administration: 2 received, of which 1 upheld.\n",
"\n",
"---------------\n",
"Complaint report for: GARFIELD ROAD SURGERY (J84602)\n",
"\n",
"Address: 18 GARFIELD ROAD, RYDE, ISLE OF WIGHT.\n",
"\n",
"Complaints by area (3 received, of which 2 upheld):\n",
" - Practice Admin: 1 received, of which 1 upheld.\n",
" - Medical: 1 received, of which 0 upheld.\n",
" - Other: 1 received, of which 1 upheld.\n",
"\n",
"Complaints by subject (3 received, of which 2 upheld):\n",
" - Clinical: 1 received, of which 0 upheld.\n",
" - Communications/attitude: 1 received, of which 1 upheld.\n",
" - Practice administration: 1 received, of which 1 upheld.\n"
]
}
],
"source": [
"def epracurrDetails(code):\n",
" return gp[gp['Organisation Code']==code].to_dict(orient='records')[0]\n",
"\n",
"def getAddress(d):\n",
" c=[x for x in d.keys() if 'address line' in x.lower()]\n",
" return [d[x] for x in c if ((d[x] is not None) and not pd.isnull(d[x]))] \n",
" \n",
" \n",
"def receivedUpheld(d,k):\n",
" rxd=d[k]['received'] if 'received' in d[k] else 0\n",
" upheld=d[k]['upheld'] if 'upheld' in d[k] else 0\n",
" return rxd,upheld\n",
"\n",
"def complaintReport(item):\n",
" details=epracurrDetails(item['Practice_Code::Dental/GP Practice Code'])\n",
" txt='\\n---------------\\n'\n",
" txt=txt+'Complaint report for: {name} ({code})\\n'.format(name=details['Name'],\n",
" code=item['Practice_Code::Dental/GP Practice Code'])\n",
" txt=txt+'\\nAddress: {addr}.'.format(addr=', '.join(getAddress(details)))\n",
" txt=txt+'\\n'\n",
" complaints_received={'Complaint by subject':{},'Complaint by area':{}}\n",
" complaints_upheld={'Complaint by subject':{},'Complaint by area':{}}\n",
" \n",
" complaints_by_area={}\n",
" complaints_by_subject={}\n",
" \n",
" for col in item.index.values:\n",
" if 'complaint' in col.lower() and item[col]>0:\n",
" \n",
" k=col.replace('\\n','').split('Total number of written complaints ')\n",
"\n",
" if k[0]=='': k[0]='Total'\n",
" k[0]=k[0].strip()\n",
" k[1]=k[1]\n",
"\n",
" if 'received' in k[1]:\n",
" complaints_received[k[1].split('::')[1]][k[0]]=int(item[col]) \n",
" elif 'upheld' in k[1]:\n",
" complaints_upheld[k[1].split('::')[1]][k[0]]=int(item[col])\n",
" \n",
" if 'area' in k[1]:\n",
" if k[0] not in complaints_by_area: complaints_by_area[k[0]]={}\n",
" complaints_by_area[k[0]][k[1].split('::')[0]]=int(item[col])\n",
" elif 'subject' in k[1]:\n",
" if k[0] not in complaints_by_subject: complaints_by_subject[k[0]]={}\n",
" complaints_by_subject[k[0]][k[1].split('::')[0]]=int(item[col])\n",
"\n",
" txt=txt+'\\n'\n",
"\n",
" if complaints_by_area!={}:\n",
" rxd,upheld=receivedUpheld(complaints_by_area,\"Total\")\n",
" txt=txt+'Complaints by area ({} received, of which {} upheld):'.format(rxd,upheld)\n",
" for complaint in complaints_by_area:\n",
" if complaint=='Total': continue\n",
" rxd,upheld=receivedUpheld(complaints_by_area,complaint)\n",
" txt=txt+'\\n - {}: {} received, of which {} upheld.'.format(complaint,rxd,upheld)\n",
" txt=txt+'\\n\\n'\n",
"\n",
" if complaints_by_subject!={}:\n",
" rxd,upheld=receivedUpheld(complaints_by_subject,\"Total\")\n",
" txt=txt+'Complaints by subject ({} received, of which {} upheld):'.format(rxd,upheld)\n",
" for complaint in complaints_by_subject:\n",
" if complaint=='Total': continue\n",
" rxd,upheld=receivedUpheld(complaints_by_subject,complaint)\n",
" txt=txt+'\\n - {}: {} received, of which {} upheld.'.format(complaint,rxd,upheld)\n",
" \n",
" #print(txt)\n",
" #print('\\n---------------\\n')\n",
" \n",
" return txt\n",
"\n",
"reports=complaints_iw.apply(lambda x: complaintReport(x), axis=1 )\n",
"for report in reports:\n",
" print(report)"
]
}
],
"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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment