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