Looking For GP Practice Clusters¶
Care Quality Commission (CQC) data - http://www.cqc.org.uk/content/how-get-and-re-use-cqc-information-and-data
In [2]:
import pandas as pd
In [ ]:
#!mkdir data
#!wget http://www.cqc.org.uk/sites/default/files/HSCA%20Active%20Locations%20September.xlsx -P data
In [3]:
df=pd.read_excel('data/HSCA Active Locations September.xlsx',
skiprows=7,
parse_dates=['HSCA start date','Provider HSCA start date'])
In [11]:
df[['HSCA start date','Provider HSCA start date']].dtypes
Out[11]:
HSCA start date datetime64[ns] Provider HSCA start date datetime64[ns] dtype: object
In [4]:
df.columns
Out[4]:
Index(['Location ID', 'HSCA start date', 'Care home?', 'Location Name', 'Telephone Number', 'Registered manager (note; where there is more than one manager at a location, only one is included here for ease of presentation. The full list is available if required).', 'Web Address', 'Care homes beds', 'Location Type/Sector', 'Region', 'Local Authority', 'Street Address', 'Address Line 2', 'City', 'County', 'Postal Code', 'Brand ID', 'Brand Name', 'Provider ID', 'Provider Name', 'Provider HSCA start date', 'Provider - Telephone Number', 'Provider - Web Address', 'Provider - Street Address', 'Provider - Address Line 2', 'Provider - City', 'Provider - County', 'Provider - Postal Code', 'Provider Nominated Individual Name', 'Regulated activity - Accommodation and nursing or personal care in the further education sector', 'Regulated activity - Accommodation for persons who require nursing or personal care', 'Regulated activity - Accommodation for persons who require treatment for substance misuse', 'Regulated activity - Assessment or medical treatment for persons detained under the Mental Health Act 1983', 'Regulated activity - Diagnostic and screening procedures', 'Regulated activity - Family planning', 'Regulated activity - Management of supply of blood and blood derived products', 'Regulated activity - Maternity and midwifery services', 'Regulated activity - Nursing care', 'Regulated activity - Personal care', 'Regulated activity - Services in slimming clinics', 'Regulated activity - Surgical procedures', 'Regulated activity - Termination of pregnancies', 'Regulated activity - Transport services, triage and medical advice provided remotely', 'Regulated activity - Treatment of disease, disorder or injury', 'Service type - Acute services with overnight beds', 'Service type - Acute services without overnight beds / listed acute services with or without overnight beds', 'Service type - Ambulance service', 'Service type - Blood and Transplant service', 'Service type - Care home service with nursing', 'Service type - Care home service without nursing', 'Service type - Community based services for people who misuse substances', 'Service type - Community based services for people with a learning disability', 'Service type - Community based services for people with mental health needs', 'Service type - Community health care services - Independent Midwives', 'Service type - Community health care services - Nurses Agency only', 'Service type - Community healthcare service', 'Service type - Dental service', 'Service type - Diagnostic and/or screening service', 'Service type - Diagnostic and/or screening service - single handed sessional providers', 'Service type - Doctors consultation service', 'Service type - Doctors treatment service', 'Service type - Domiciliary care service', 'Service type - Extra Care housing services', 'Service type - Hospice services', 'Service type - Hospice services at home', 'Service type - Hospital services for people with mental health needs, learning disabilities and problems with substance misuse', 'Service type - Hyperbaric Chamber', 'Service type - Long term conditions services', 'Service type - Mobile doctors service', 'Service type - Prison Healthcare Services', 'Service type - Rehabilitation services', 'Service type - Remote clinical advice service', 'Service type - Residential substance misuse treatment and/or rehabilitation service', 'Service type - Shared Lives', 'Service type - Specialist college service', 'Service type - Supported living service', 'Service type - Urgent care services', 'Service user band - Children 0-18 years', 'Service user band - Dementia', 'Service user band - Learning disabilities or autistic spectrum disorder', 'Service user band - Mental Health', 'Service user band - Older People', 'Service user band - People detained under the Mental Health Act', 'Service user band - People who misuse drugs and alcohol', 'Service user band - People with an eating disorder', 'Service user band - Physical Disability', 'Service user band - Sensory Impairment', 'Service user band - Whole Population', 'Service user band - Younger Adults'], dtype='object')
In [5]:
serviceTypes=df[ ['Location ID', 'HSCA start date','Location Name',
'County', 'Postal Code', 'Brand ID', 'Brand Name', 'Provider ID', 'Provider Name', 'Provider HSCA start date',
'Service type - Acute services with overnight beds',
'Service type - Acute services without overnight beds / listed acute services with or without overnight beds',
'Service type - Ambulance service',
'Service type - Blood and Transplant service',
'Service type - Care home service with nursing',
'Service type - Care home service without nursing',
'Service type - Community based services for people who misuse substances',
'Service type - Community based services for people with a learning disability',
'Service type - Community based services for people with mental health needs',
'Service type - Community health care services - Independent Midwives',
'Service type - Community health care services - Nurses Agency only',
'Service type - Community healthcare service',
'Service type - Dental service',
'Service type - Diagnostic and/or screening service',
'Service type - Diagnostic and/or screening service - single handed sessional providers',
'Service type - Doctors consultation service',
'Service type - Doctors treatment service',
'Service type - Domiciliary care service',
'Service type - Extra Care housing services',
'Service type - Hospice services',
'Service type - Hospice services at home',
'Service type - Hospital services for people with mental health needs, learning disabilities and problems with substance misuse',
'Service type - Hyperbaric Chamber',
'Service type - Long term conditions services',
'Service type - Mobile doctors service',
'Service type - Prison Healthcare Services',
'Service type - Rehabilitation services',
'Service type - Remote clinical advice service',
'Service type - Residential substance misuse treatment and/or rehabilitation service',
'Service type - Shared Lives', 'Service type - Specialist college service',
'Service type - Supported living service',
'Service type - Urgent care services' ] ]
In [6]:
df.groupby('Provider Name').filter(lambda x: len(x) > 10).groupby('Provider Name').size().order(ascending=False)
Out[6]:
Provider Name Voyage 1 Limited 301 Barchester Healthcare Homes Limited 185 Royal Mencap Society 174 HC-One Limited 169 Whitecross Dental Care Limited 123 IDH Limited 120 Methodist Homes 115 Leonard Cheshire Disability 109 Community Integrated Care 106 Care UK Community Partnerships Limited 102 Dimensions (UK) Limited 102 Anchor Trust 97 Turning Point 95 Housing & Care 21 94 Care Management Group Limited 88 ... GTD Primary Care Limited 11 Black Country Partnership NHS Foundation Trust 11 Total Orthodontics Limited 11 Places for People Individual Support Limited 11 Royal Free London NHS Foundation Trust 11 Phoenix Primary Care Ltd 11 People in Action 11 David Lewis Centre 11 County Durham and Darlington NHS Foundation Trust 11 Ordinary Life Project Association 11 Condover College Limited 11 Inshore Support Limited 11 Encompass (Dorset) 11 Classic Hospitals Limited 11 Maria Mallaband Limited 11 Length: 395, dtype: int64
In [7]:
df[df['Care home?']=='Y'].groupby('Provider Name').filter(lambda x: len(x) > 10).groupby('Provider Name').size().order(ascending=False)
Out[7]:
Provider Name Voyage 1 Limited 273 HC-One Limited 169 Barchester Healthcare Homes Limited 168 Royal Mencap Society 110 Care UK Community Partnerships Limited 100 Anchor Trust 92 Community Integrated Care 90 Leonard Cheshire Disability 86 Dimensions (UK) Limited 84 Methodist Homes 84 Parkcare Homes (No 2) Limited 69 Orders of St John Care Trust 69 Care Management Group Limited 65 Bupa Care Homes (CFHCare) Limited 57 Bupa Care Homes (CFC Homes) Limited 57 ... Welmede Housing Association Limited 11 Embrace (UK) Limited 11 SeeAbility 11 SheffCare Limited 11 Isle of Wight Council 11 Bondcare St Andrews Limited 11 Borough Care Ltd 11 SCC Adult Social Care 11 Partnerships in Care Limited 11 Brookdale Health Care Limited 11 Nottinghamshire County Council 11 Henshaws Society for Blind People 11 Hightown Praetorian & Churches Housing Association Limited 11 Keychange Charity 11 Mimosa Healthcare (No 4) Limited (In administration) 11 Length: 172, dtype: int64
In [114]:
df.groupby('Brand Name').filter(lambda x: len(x) > 10).groupby('Brand Name').size().order(ascending=False)
Out[114]:
Brand Name - 39681 BRAND IDH Dental 522 BRAND Four Seasons Group 366 BRAND Voyage 309 BRAND BUPA Group 287 BRAND Care UK 249 BRAND Oasis Dental Care 246 BRAND Priory Group 237 BRAND Barchester Healthcare 185 BRAND Royal Mencap Society 174 BRAND HC-One Limited 169 BRAND Bluebird Care 152 BRAND Acromas Healthcare 146 BRAND Home Instead 130 BRAND Caretech Community Services 128 ... BRAND Marie Stopes International 15 BRAND Pulse Healthcare 15 BRAND Outlook Care 15 BRAND Country Court Care 15 BRAND Sue Ryder 14 BRAND Avery 14 BRAND Bristol City Council 14 BRAND CHD Living 14 BRAND Centra 14 BRAND Redwood Care 13 BRAND Choice Healthcare 13 BRAND Alpha Dental Studio 12 BRAND Forest Healthcare 12 BRAND The Hospital Medical Group Limited 12 BRAND Woodleigh Care 12 Length: 197, dtype: int64
In [8]:
df[df['Care home?']=='Y'].groupby('Brand Name').filter(lambda x: len(x) > 10).groupby('Brand Name').size().order(ascending=False)
Out[8]:
Brand Name - 11704 BRAND Four Seasons Group 346 BRAND Voyage 279 BRAND BUPA Group 246 BRAND Priory Group 183 BRAND HC-One Limited 169 BRAND Barchester Healthcare 168 BRAND Care UK 130 BRAND Caretech Community Services 118 BRAND Royal Mencap Society 110 BRAND Caring Homes 104 BRAND Orchard Care Homes 95 BRAND Anchor Trust 92 BRAND Community Integrated Care (CIC) 90 BRAND Dimensions 87 ... BRAND Redwood Care 13 BRAND Jeesal Group 13 BRAND Agincare 12 BRAND Jewish Care 12 BRAND Making Space 12 BRAND Midland Heart Limited 12 BRAND Creative Support Limited 12 BRAND Forest Healthcare 12 BRAND SeeAbility 11 BRAND Henshaws Society for Blind People (Henshaws) 11 BRAND CHD Living 11 BRAND SCC Adult Social Care 11 BRAND Advance Housing and Support Limited 11 BRAND Retirement Villages 11 BRAND Partnerships in Care 11 Length: 130, dtype: int64
In [9]:
gp=df[df['Service type - Doctors consultation service']=='Y']
In [10]:
gp[:3]
Out[10]:
| Location ID | HSCA start date | Care home? | Location Name | Telephone Number | Registered manager (note; where there is more than one manager at a location, only one is included here for ease of presentation. The full list is available if required). | Web Address | Care homes beds | Location Type/Sector | Region | ... | Service user band - Learning disabilities or autistic spectrum disorder | Service user band - Mental Health | Service user band - Older People | Service user band - People detained under the Mental Health Act | Service user band - People who misuse drugs and alcohol | Service user band - People with an eating disorder | Service user band - Physical Disability | Service user band - Sensory Impairment | Service user band - Whole Population | Service user band - Younger Adults | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 41 | 1-1002333348 | 2013-10-09 | N | 10 Harley Street | 02074678301 | * | www.doctor4u.co.uk | 0 | Independent Healthcare Org | London | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 46 | 1-1002716537 | 2013-10-10 | N | Surrey Heart Clinic | 01932504999 | Ohri, Anil | NaN | 0 | Independent Healthcare Org | South East | ... | NaN | NaN | Y | NaN | NaN | NaN | NaN | NaN | NaN | Y |
| 50 | 1-1004459443 | 2013-10-14 | N | Park Health Care Centre | NaN | Chowdhury, Golam | NaN | 0 | Primary Medical Services | Yorkshire and The Humber | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
3 rows × 89 columns
In [15]:
gpm=gp[['Location ID', 'HSCA start date', 'Care home?', 'Location Name', 'Web Address', 'Care homes beds', 'Location Type/Sector', 'Region', 'Local Authority', 'Street Address', 'City', 'County', 'Postal Code', 'Brand ID', 'Brand Name', 'Provider ID', 'Provider Name']]
In [16]:
gpm.groupby('Brand Name').size().order(ascending=False)
Out[16]:
Brand Name - 9286 BRAND Virgin 46 BRAND SSP Health Ltd 38 BRAND The Practice Surgeries 36 BRAND Care UK 33 BRAND Malling Health 29 BRAND Hurley Clinic Partnership 22 BRAND IntraHealth Limited 20 BRAND British Pregnancy Advisory Service 20 BRAND Aesthetic and Cosmetic Surgery Limited 20 BRAND BUPA Group 17 BRAND HCA Hospitals 15 BRAND The Hospital Medical Group Limited 11 BRAND Acromas Healthcare 10 BRAND City Health Care Partnership CIC 9 BRAND Nuffield Health 6 BRAND BMI Healthcare 5 BRAND Priory Group 3 BRAND Sk:n Ltd 3 BRAND Spire Healthcare Limited 3 BRAND InHealth Limited 3 BRAND Locala 2 BRAND Peninsula Community Health 2 BRAND Alliance Medical Limited 1 BRAND St Andrew's Healthcare 1 BRAND Turning Point 1 BRAND Voyage 1 dtype: int64
In [17]:
gpm10=gpm.groupby('Provider Name').filter(lambda x: len(x) > 10).groupby('Provider Name').size().order(ascending=False)
gpm10
Out[17]:
Provider Name SSP Health Ltd 38 Malling Health UK Ltd 26 Hurley Clinic Partnership 22 Care UK Clinical Services Limited 21 IntraHealth Limited 20 Aesthetic and Cosmetic Surgery Limited 20 British Pregnancy Advisory Service 20 AT Medics Limited 19 The Practice Services Limited 19 The Practice Surgeries Limited 18 Independent Health Group Limited 17 Chilvers & McCrea Limited 16 Lincolnshire Community Health Services NHS Trust 15 Bupa Occupational Health Limited 14 East Lancashire Medical Services Limited 13 The Village Surgery 13 GTD Primary Care Limited 11 Fraterdrive Limited 11 Phoenix Primary Care Ltd 11 Care UK (Urgent Care) Limited 11 The Hospital Medical Group Limited 11 dtype: int64
Quiet a few of these are limited companies - which means we should be able to get a company number for them, and things like a list of current directors, from OpenCorporates.
In [18]:
#The OpenCorporates reconciliation API is a "fuzzy lookup" service...
import requests, json
def probeRecon(name):
''' Use OpenCorporates reconciliation API to probe a company name '''
url="https://opencorporates.com/reconcile/gb"
r=requests.get(url,params={'query':name})
return r,json.loads(r.text)
tmpx,tmpy=probeRecon("Malling Health UK Ltd")
In [19]:
#If multiple possible hits are detected from the reconciliation API, we can automatically grapb the best
def getBestReconResult(results):
''' From a set of responses from reconciliation API, return the one with the highest score.
Note: if there are multiple names with matching high score, any one of them could be returned. '''
best={}
score=0
for result in results:
if result['score']>score:
score=result['score']
best=result
return best
In [20]:
#This function will take a company name, look it up via OpenCorporates reconciliation API, and return matches
import requests, json
def ocReconcile(ix,territory='gb'):
''' Look up company against OpenCorporates reconciliation API '''
url="https://opencorporates.com/reconcile/"+territory
r=requests.get(url,params={'query':ix})
return json.loads(r.text)
def ocReconcileBest(ix,territory='gb'):
''' Look up company against OpenCorporates reconciliation API and return best match '''
jsondata=ocReconcile(ix,territory)
best=getBestReconResult(jsondata['result'])
return best
In [22]:
#Use OpenCorporates reconciliation API to get data about providers with multiple CQC locations
recon={}
for ix in gpm10.index.values:
recon[ix]=ocReconcileBest(ix)
recon
Out[22]:
{'Independent Health Group Limited': {'name': 'INDEPENDENT HEALTH GROUP LIMITED',
'uri': 'http://opencorporates.com/companies/gb/05307845',
'score': 86.0,
'id': '/companies/gb/05307845',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Chilvers & McCrea Limited': {'name': 'CHILVERS & MCCREA LIMITED',
'uri': 'http://opencorporates.com/companies/gb/04149500',
'score': 86.0,
'id': '/companies/gb/04149500',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Lincolnshire Community Health Services NHS Trust': {},
'The Practice Services Limited': {'name': 'THE PRACTICE SERVICES LIMITED',
'uri': 'http://opencorporates.com/companies/gb/05555460',
'score': 86.0,
'id': '/companies/gb/05555460',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'The Practice Surgeries Limited': {'name': 'THE PRACTICE SURGERIES LIMITED',
'uri': 'http://opencorporates.com/companies/gb/06545745',
'score': 86.0,
'id': '/companies/gb/06545745',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'GTD Primary Care Limited': {'name': 'GTD PRIMARY CARE LIMITED',
'uri': 'http://opencorporates.com/companies/gb/06504010',
'score': 86.0,
'id': '/companies/gb/06504010',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Fraterdrive Limited': {'name': 'FRATERDRIVE LIMITED',
'uri': 'http://opencorporates.com/companies/gb/01087755',
'score': 86.0,
'id': '/companies/gb/01087755',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Hurley Clinic Partnership': {},
'East Lancashire Medical Services Limited': {'name': 'EAST LANCASHIRE MEDICAL SERVICES LIMITED',
'uri': 'http://opencorporates.com/companies/gb/IP30263R',
'score': 85.0,
'id': '/companies/gb/IP30263R',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Phoenix Primary Care Ltd': {'name': 'PHOENIX PRIMARY CARE LIMITED',
'uri': 'http://opencorporates.com/companies/gb/06280242',
'score': 81.0,
'id': '/companies/gb/06280242',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Aesthetic and Cosmetic Surgery Limited': {'name': 'AESTHETIC AND COSMETIC SURGERY LIMITED',
'uri': 'http://opencorporates.com/companies/gb/08213786',
'score': 86.0,
'id': '/companies/gb/08213786',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'The Hospital Medical Group Limited': {'name': 'THE HOSPITAL MEDICAL GROUP LIMITED',
'uri': 'http://opencorporates.com/companies/gb/02811995',
'score': 86.0,
'id': '/companies/gb/02811995',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'SSP Health Ltd': {'name': 'SSP HEALTH LTD',
'uri': 'http://opencorporates.com/companies/gb/06359596',
'score': 86.0,
'id': '/companies/gb/06359596',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Malling Health UK Ltd': {'name': 'MALLING HEALTH (UK) LIMITED',
'uri': 'http://opencorporates.com/companies/gb/07034946',
'score': 81.0,
'id': '/companies/gb/07034946',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'British Pregnancy Advisory Service': {'name': 'BRITISH PREGNANCY ADVISORY SERVICE',
'uri': 'http://opencorporates.com/companies/gb/01803160',
'score': 86.0,
'id': '/companies/gb/01803160',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'The Village Surgery': {'name': 'THE VILLAGE SURGERY LIMITED',
'uri': 'http://opencorporates.com/companies/gb/02949574',
'score': 71.0,
'id': '/companies/gb/02949574',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'IntraHealth Limited': {'name': 'INTRAHEALTH LIMITED',
'uri': 'http://opencorporates.com/companies/gb/03783310',
'score': 86.0,
'id': '/companies/gb/03783310',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Bupa Occupational Health Limited': {'name': 'BUPA OCCUPATIONAL HEALTH LIMITED',
'uri': 'http://opencorporates.com/companies/gb/00631336',
'score': 86.0,
'id': '/companies/gb/00631336',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Care UK Clinical Services Limited': {'name': 'CARE UK CLINICAL SERVICES LIMITED',
'uri': 'http://opencorporates.com/companies/gb/03462881',
'score': 86.0,
'id': '/companies/gb/03462881',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Care UK (Urgent Care) Limited': {'name': 'CARE UK (URGENT CARE) LIMITED',
'uri': 'http://opencorporates.com/companies/gb/05232967',
'score': 86.0,
'id': '/companies/gb/05232967',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'AT Medics Limited': {'name': 'AT MEDICS LIMITED',
'uri': 'http://opencorporates.com/companies/gb/05057581',
'score': 86.0,
'id': '/companies/gb/05057581',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]}}
In [23]:
#Having got an OpenCorporates ID, we can look up some data about that company via OpenCorporates API
def getOCdata(cid):
''' Get company data from OpenCorproates given OpenCorporates id '''
apiurl='https://api.opencorporates.com/v0.3'
r=requests.get(apiurl+cid)
return json.loads(r.text)
In [24]:
ocd=getOCdata('/companies/gb/06504010')
ocd
Out[24]:
{'api_version': '0.3',
'results': {'company': {'controlling_entity': None,
'previous_names': [{'con_date': '2008-03-17',
'company_name': 'FORMRIVER LIMITED'}],
'dissolution_date': None,
'incorporation_date': '2008-02-14',
'created_at': '2010-10-22T09:40:24+00:00',
'retrieved_at': '2014-09-09T15:51:36+00:00',
'agent_address': None,
'registry_url': 'http://data.companieshouse.gov.uk/doc/company/06504010',
'updated_at': '2014-09-09T15:51:38+00:00',
'data': {'most_recent': [{'datum': {'opencorporates_url': 'https://opencorporates.com/data/3540817',
'description': 'THE FORUM, TAMESIDE BUSINESS PARK, DENTON, MANCHESTER, M34 3QS, United Kingdom',
'title': 'Company Address',
'data_type': 'CompanyAddress',
'id': 3540817}},
{'datum': {'opencorporates_url': 'https://opencorporates.com/data/3540816',
'description': 'register id: Z1526596',
'title': 'UK Data Protection Register entry',
'data_type': 'OfficialRegisterEntry',
'id': 3540816}}],
'total_count': 2,
'url': 'https://opencorporates.com/companies/gb/06504010/data'},
'industry_codes': [{'industry_code': {'code_system': 'UK SIC Codes 2007',
'title': 'General medical practice activities',
'uid': '86.21'}}],
'corporate_groupings': [],
'source': {'terms': 'UK Crown Copyright',
'retrieved_at': '2014-09-09T15:51:36+00:00',
'url': 'http://xmlgw.companieshouse.gov.uk/',
'publisher': 'UK Companies House'},
'branch_status': None,
'home_company': None,
'agent_name': None,
'current_status': 'Active',
'jurisdiction_code': 'gb',
'inactive': False,
'filings': [{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/200735373',
'uid': 'ce773aeJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwhjIxcjo4gQUxNzQycuzvi0/KLcksqCVCdxFscgA6AQa3xKcmKJkzijIcyYxBKgLIeRgaGJgZGRSZOac35uQWJeZmqxR35pcaqVVYSvj3u4lZVnbmJ6KpTyTq0kUhk3AwMDIxAzATEzELMAMSsQswExOxBzADEnEHMBMTcA3wA0og==',
'title': 'Annual Return',
'date': '2014-02-24',
'id': 200735373}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/198789423',
'uid': '240a3beJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0cjP1Mjn6gwUxMDYycuzvi0/KLcksqCVCdxJkdHJy7W+JTkxBIncUYDmCGJJUA5DiMDQ2NDI0PzJjXn/NyCxLzM1GKP/NLiVCurCF8f93ArK8/cxPRUKOWdWkmkMm4GBgZGIGYCYmYgZgFiViBmA2J2IOYAYk4g5gJibgCBLTQ9',
'title': 'Annual Accounts',
'date': '2013-12-17',
'id': 198789423}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/182648196',
'uid': '914ae6eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwgjI8uoAEM3U0MDQycuzvi0/KLcksqCVCdxFscgkBBrfEpyYomTOKMhzJjEEqAsh5GBobGBkaFpk5pzfm5BYl5marFHfmlxqpVVhK+Pe7iVlWduYnoqlPJOrSRSGTcDAwMjEDMBMTMQswAxKxCzATE7EHMAMScQcwExNwDbZjSc',
'title': 'Annual Return',
'date': '2013-02-15',
'id': 182648196}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/182648197',
'uid': '5046e0eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0cjgyAvYx9zUwMLIycuzvi0/KLcksqCVCdxJkdHJy7W+JTkxBIncUYDmCGJJUA5DiMDQ2MDQyPTJjXn/NyCxLzM1GKP/NLiVCurCF8f93ArK8/cxPRUKOWdWkmkMm4GBgZGIGYCYmYgZgFiViBmA2J2IOYAYk4g5gJibgBgnDQO',
'title': 'Annual Accounts',
'date': '2013-01-25',
'id': 182648197}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222849',
'uid': 'cfa40feJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwhDEyM//6gQE3NTEycuzvi0/KLcksqCVCdxFscgA0MnLtb4lOTEEidxRkOYMYklQFkOIwNDIwNjA9MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuAP2BNMw=',
'title': 'Annual Return',
'date': '2012-03-05',
'id': 149222849}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222851',
'uid': '99851aeJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0cDf1Nz30ADEzNzCycuzvi0/KLcksqCVCdxJkdHJy7W+JTkxBIncUYDmCGJJUA5DiMDQ0NDI0PLJjXn/NyCxLzM1GKP/NLiVCurCF8f93ArK8/cxPRUKOWdWkmkMm4GBgZGIGYCYmYgZgFiViBmA2J2IOYAYk4g5gJibgBsljQg',
'title': 'Annual Accounts',
'date': '2011-12-19',
'id': 149222851}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222854',
'uid': '18e887eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0dDD0vnMAt3E1MDEycuzvi0/KLcksqCVCdxJkdHJy7W+JTkxBIncUYDmCGJJUA5DiMDQ0MDMyOTJjXn/NyCxLzM1GKP/NLiVCurCF8f93ArK8/cxPRUKOWdWkmkMm4GBgZGIGYCYmYgZgFiViBmA2J2IOYAYk4g5gJibgBv2jQk',
'title': 'Annual Accounts',
'date': '2011-06-24',
'id': 149222854}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222855',
'uid': 'dda002eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDg4gIcy/vYOMoE2NLIycuzvi0/KLcksqCVCdxFscgA0MnLtb4lOTEEidxRkOYMYklQFkOIwNDQwNjA+MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuABQKNOk=',
'title': 'Annual Return',
'date': '2011-03-03',
'id': 149222855}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222857',
'uid': '7305e4eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDg4gIc0//YGMjE2NLQycuzvi0/KLcksqCVCdxlhBfA6AQa3xKcmKJkzijEcyYxBKgLIeRgaGhgbGBUZOac35uQWJeZmqxR35pcaqVVYSvj3u4lZVnbmJ6KpTyTq0kUhk3AwMDIxAzATEzELMAMSsQswExOxBzADEnEHMBMTcAAgk00Q==',
'title': 'Termination of appointment of director ',
'date': '2011-03-02',
'id': 149222857}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222859',
'uid': 'eb2d87eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwjjCGMzT1cjE0NjSycuzvi0/KLcksqCVCdxFscgA0MnLtb4lOTEEidxRkOYMYklQFkOIwNDAwNjQ4smNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuANv3NJ0=',
'title': 'Annual Return',
'date': '2010-03-18',
'id': 149222859}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222860',
'uid': '1b6fbdeJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA8eIUMdIt0BXEwNzUycuzvi0/KLcksqCVCdxJkdHJy7W+JTkxBIncUYDmCGJJUA5DiMDA0tDI0OLJjXn/NyCxLzM1GKP/NLiVCurCF8f93ArK8/cxPRUKOWdWkmkMm4GBgZGIGYCYmYgZgFiViBmA2J2IOYAYk4g5gJibgC1gTSH',
'title': 'Annual Accounts',
'date': '2009-12-18',
'id': 149222860}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222863',
'uid': 'f47825eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0cznzADNxM3EwNTCycuzvi0/KLcksqCVCdxFl93A0MnLtb4lOTEEidxRhOYMYklQFkOIwMDS0NDI4MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuANvyNJ4=',
'title': 'Particulars of a mortgage or charge',
'date': '2009-11-20',
'id': 149222863}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222865',
'uid': '3d0cadeJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwjPAEdfl3BLEwMjEycuzvi0/KLcksqCVCdxFkdHA0MnLtb4lOTEEidxRkuYMYklQFkOIwMDS0MDA/MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuAAZ2NNg=',
'title': 'Change of accounting reference date',
'date': '2009-10-07',
'id': 149222865}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222868',
'uid': 'b355a2eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwBXbzdvR293Y3Nzcycuzvi0/KLcksqCVCdxZiMLoAhrfEpyYomTOKMxzJTEEqAkh5GBgaWBmaFBk5pzfm5BYl5marFHfmlxqpVVhK+Pe7iVlWduYnoqlPJOrSRSGTcDAwMjEDMBMTMQswAxKxCzATE7EHMAMScQcwExNwDHOTSN',
'title': 'Filing dated 2009-06-10',
'date': '2009-06-10',
'id': 149222868}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222871',
'uid': 'ff0826eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwgDM2M/C2cXY3MjEycuzvi0/KLcksqCVCdxFmMz40QnLtb4lOTEEidxRkOYMYklQFkOIwMDSwNjI+MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuANgNNJs=',
'title': 'Companies Annual Return',
'date': '2009-03-23',
'id': 149222871}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222873',
'uid': 'adbef8eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0fT4BCPSO8oYxNzCycuzvi0/KLcksqCVCdxFiMLi0QnLtb4lOTEEidxRiOYMYklQFkOIwMDCwMTA/MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuACooNQ8=',
'title': 'Appointment of Director or Secretary',
'date': '2008-04-07',
'id': 149222873}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222893',
'uid': '377ef7eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0fvEFeLiChTYxNzIycuzvi0/KLcksqCVCdxDl9XX33HoJBgJy7W+JTkxBIncUZLmFGJJSAVRgYGFgbGRpZNas75uQWJeZmpxR75pcWpVlYRvj7u4VZWnrmJ6alQyju1kkhl3AwMDIxAzATEzEDMAsSsQMwGxOxAzAHEnEDMBcTcACaFNj0=',
'title': 'Filing dated 2008-03-29',
'date': '2008-03-29',
'id': 149222893}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222897',
'uid': '803fc5eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0dXP0/jSAsjYxNzAycuzvi0/KLcksqCVCdxFiMLi0QnLtb4lOTEEidxRiOYMYklQFkOIwMDCwNjI7MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuAOtyNLc=',
'title': 'Appointment of Director or Secretary',
'date': '2008-03-26',
'id': 149222897}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222895',
'uid': '0b2e1beJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDA0dXP0+jSAtDYxNzAycuzvi0/KLcksqCVCdxFiMLi0QnLtb4lOTEEidxRiOYMYklQFkOIwMDCwNjI7MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuAOoDNLU=',
'title': 'Appointment of Director or Secretary',
'date': '2008-03-26',
'id': 149222895}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222899',
'uid': 'f12a8feJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfmcDM1MDEwNDPQN3YxMzUycuzvi0/KLcksqCVCdxNmfXoBA/Xycu1viU5MQSJ3FGc5hBiSVAeQ4jAwMLA2ND4yY15/zcgsS8zNRij/zS4lQrqwhfH/dwKyvP3MT0VCjlnVpJpDJuBgYGRiBmAmJmIGYBYlYgZgNidiDmAGJOIOYCYm4ASo01KQ==',
'title': 'Certificate of Incorporation on Change of Name',
'date': '2008-03-13',
'id': 149222899}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222903',
'uid': '2292d6eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwhfP0OTiAhTYxMzAycuzvi0/KLcksqCVCdxFiMLi0QnLtb4lOTEEidxRiOYMYklQFkOIwMDCwNjQ8MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuAAZNNNo=',
'title': 'Appointment of Director or Secretary',
'date': '2008-03-11',
'id': 149222903}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222901',
'uid': '040a8ceJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwhfP0OXiAhXYxMzAycuzvi0/KLcksqCVCdxFiMLiyQnLtb4lOTEEidxRiOYMYklQFkOIwMDCwNjQ8MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuAB3cNPs=',
'title': 'Terminating appointment as Director or Secretary',
'date': '2008-03-11',
'id': 149222901}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222904',
'uid': 'c2f7b7eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwivIEvfCG8vYxNTIycuzvi0/KLcksqCVCdxFiMLi0QnLtb4lOTEEidxRiOYMYklQFkOIwMDCwMjI4smNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuACikNQw=',
'title': 'Appointment of Director or Secretary',
'date': '2008-02-28',
'id': 149222904}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222911',
'uid': '07868ceJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwivIEufCG9PYxNTQycuzvi0/KLcksqCVCdxZiMLcycu1viU5MQSJ3FGY5gpiSVASQ4jAwMLAyMj8yY15/zcgsS8zNRij/zS4lQrqwhfH/dwKyvP3MT0VCjlnVpJpDJuBgYGRiBmAmJmIGYBYlYgZgNidiDmAGJOIOYCYm4A2M00pg==',
'title': 'Filing dated 2008-02-27',
'date': '2008-02-27',
'id': 149222911}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222910',
'uid': '2b9767eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwivIEu3CG9nYxNTQycuzvi0/KLcksqCVCdxFiMLi0QnLtb4lOTEEidxRiOYMYklQFkOIwMDCwMjI/MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuAB1sNPw=',
'title': 'Appointment of Director or Secretary',
'date': '2008-02-27',
'id': 149222910}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222909',
'uid': 'adc709eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwivIEvnCG9LYxNTQycuzvi0/KLcksqCVCdxFiMLiyQnLtb4lOTEEidxRiOYMYklQFkOIwMDCwMjI/MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuABTENPA=',
'title': 'Terminating appointment as Director or Secretary',
'date': '2008-02-27',
'id': 149222909}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222907',
'uid': '5aec32eJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfgMDgwivIEv3CG8XYxNTQycuzvi0/KLcksqCVCdxFiMLiyQnLtb4lOTEEidxRiOYMYklQFkOIwMDCwMjI/MmNef83ILEvMzUYo/80uJUK6sIXx/3cCsrz9zE9FQo5Z1aSaQybgYGBkYgZgJiZiBmAWJWIGYDYnYg5gBiTiDmAmJuAB96NP8=',
'title': 'Terminating appointment as Director or Secretary',
'date': '2008-02-27',
'id': 149222907}},
{'filing': {'opencorporates_url': 'https://opencorporates.com/filings/149222912',
'uid': '0ecc3aeJxjZRd2YnXi4ohPyU9OSS1OBnHYQJzMFCdxfj8DM1MDEwNDPQMnYxMTIycuzvi0/KLcksqCVCdxNj/XcE8/Zycu1viU5MQSJ3FGM5hBiSVAeQ4jAwMLAyNDkyY15/zcgsS8zNRij/zS4lQrqwhfH/dwKyvP3MT0VCjlnVpJpDJuBgYGRiBmAmJmIGYBYlYgZgNidiDmAGJOIOYCYm4ASEU1JA==',
'title': 'Filing dated 2008-02-14',
'date': '2008-02-14',
'id': 149222912}}],
'company_number': '06504010',
'financial_summary': None,
'name': 'GTD PRIMARY CARE LIMITED',
'registered_address_in_full': 'THE FORUM TAMESIDE BUSINESS DEVELOPMENT CENTRE, WINDMILL LANE DENTON, MANCHESTER, LANCASHIRE, M34 3QS',
'opencorporates_url': 'https://opencorporates.com/companies/gb/06504010',
'officers': [{'officer': {'name': 'DAVID RONALD BECKETT',
'uid': None,
'end_date': None,
'opencorporates_url': 'https://opencorporates.com/officers/51581121',
'position': 'secretary',
'id': 51581121,
'start_date': '2008-02-26'}},
{'officer': {'name': 'CHRISTINA MARY GREENHOUGH',
'uid': None,
'end_date': None,
'opencorporates_url': 'https://opencorporates.com/officers/51581122',
'position': 'director',
'id': 51581122,
'start_date': '2008-03-03'}},
{'officer': {'name': 'RUSKIN HOWARD HARTLEY',
'uid': None,
'end_date': None,
'opencorporates_url': 'https://opencorporates.com/officers/51581123',
'position': 'director',
'id': 51581123,
'start_date': '2008-03-03'}},
{'officer': {'name': 'BRIAN LEWIS',
'uid': None,
'end_date': None,
'opencorporates_url': 'https://opencorporates.com/officers/51581124',
'position': 'director',
'id': 51581124,
'start_date': '2008-02-26'}},
{'officer': {'name': 'JAG KAPUR',
'uid': None,
'end_date': '2009-09-10',
'opencorporates_url': 'https://opencorporates.com/officers/56924784',
'position': 'director',
'id': 56924784,
'start_date': '2008-03-03'}},
{'officer': {'name': ' SWIFT INCORPORATIONS LIMITED',
'uid': None,
'end_date': '2008-02-26',
'opencorporates_url': 'https://opencorporates.com/officers/101487350',
'position': 'secretary',
'id': 101487350,
'start_date': '2008-02-14'}},
{'officer': {'name': ' GOTODOC LIMITED',
'uid': None,
'end_date': '2008-02-26',
'opencorporates_url': 'https://opencorporates.com/officers/101487351',
'position': 'director',
'id': 101487351,
'start_date': '2008-02-26'}},
{'officer': {'name': ' INSTANT COMPANIES LIMITED',
'uid': None,
'end_date': '2008-02-26',
'opencorporates_url': 'https://opencorporates.com/officers/101487352',
'position': 'director',
'id': 101487352,
'start_date': '2008-02-14'}}],
'company_type': 'Private Limited Company'}}}
In [25]:
#We can tunnel into the OpenCorporates data to get directors information
def getCurrentOfficers(ocd):
''' Get current officers from OpenCorporates company data '''
curr=[]
for o in ocd['results']['company']['officers']:
if o['officer']['end_date']==None:
curr.append(o)
return curr
#getCurrentOfficers(ocd)
In [26]:
#Let's see if we can reconcile with some HSCIC codes
#!wget http://systems.hscic.gov.uk/data/ods/datadownloads/data-files/xls/epraccur.zip -P data
#!unzip "data/epraccur.zip" -d data
In [27]:
ep=pd.read_excel('data/epraccur.xls',header=None)
ep.head(3)
Out[27]:
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | A81001 | THE DENSHAM SURGERY | Y54 | Q45 | THE HEALTH CENTRE | LAWSON STREET | STOCKTON | CLEVELAND | NaN | TS18 1HU | ... | NaN | 01642 672351 | NaN | NaN | NaN | 0 | NaN | NaN | NaN | 4 |
| 1 | A81002 | QUEENS PARK MEDICAL CENTRE | Y54 | Q45 | QUEENS PARK MEDICAL CTR | FARRER STREET | STOCKTON ON TEES | CLEVELAND | NaN | TS18 2AW | ... | NaN | 01642 679681 | NaN | NaN | NaN | 0 | NaN | NaN | NaN | 4 |
| 2 | A81003 | THE HEALTH CENTRE | Y54 | Q45 | THE HEALTH CENTRE | VICTORIA ROAD | HARTLEPOOL | CLEVELAND | NaN | TS26 8DB | ... | NaN | 01429 272945 | NaN | NaN | NaN | 0 | NaN | NaN | NaN | 4 |
3 rows × 26 columns
In [28]:
virgin=gpm[gpm['Brand Name']=='BRAND Virgin']
virgin['name']=virgin['Location Name'].str.upper()
virgin[:3]
Out[28]:
| Location ID | HSCA start date | Care home? | Location Name | Web Address | Care homes beds | Location Type/Sector | Region | Local Authority | Street Address | City | County | Postal Code | Brand ID | Brand Name | Provider ID | Provider Name | name | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 328 | 1-1051403692 | 2014-03-06 | N | Lyme Regis Medical Centre | NaN | 0 | Independent Healthcare Org | South West | Dorset | Lyme Regis Medical Centre | Lyme Regis | Dorset | DT7 3LS | BD231 | BRAND Virgin | 1-325698686 | VH Doctors Limited | LYME REGIS MEDICAL CENTRE |
| 7818 | 1-1213929971 | 2014-03-06 | N | East Park Practice | NaN | 0 | Independent Healthcare Org | Yorkshire and The Humber | Kingston upon Hull, City of | The Park Healthcare Centre | Hull | North Humberside | HU9 3JA | BD231 | BRAND Virgin | 1-325698686 | VH Doctors Limited | EAST PARK PRACTICE |
| 18328 | 1-145049431 | 2011-01-24 | N | Oldham Integrated Care Centre | www.assurablackpoolllp.co.uk | 0 | Independent Healthcare Org | North West | Oldham | New Radcliffe Street | Oldham | Lancashire | OL1 1NL | BD231 | BRAND Virgin | 1-132191719 | Virgin Care Blackpool LLP | OLDHAM INTEGRATED CARE CENTRE |
In [28]:
In [29]:
virgin_merge_test1=pd.merge(virgin,ep, left_on=['name','Postal Code'],right_on=[1,9])
virgin_merge_test1[['Location ID','Location Name','Postal Code','name',9,0,4]]
Out[29]:
| Location ID | Location Name | Postal Code | name | 9 | 0 | 4 | |
|---|---|---|---|---|---|---|---|
| 0 | 1-1051403692 | Lyme Regis Medical Centre | DT7 3LS | LYME REGIS MEDICAL CENTRE | DT7 3LS | Y03661 | LYME REGIS MEDICAL CENTRE |
| 1 | 1-1213929971 | East Park Practice | HU9 3JA | EAST PARK PRACTICE | HU9 3JA | B81645 | EAST PARK PRACTICE |
| 2 | 1-318228148 | Reading Walk-in Health Centre | RG1 7QA | READING WALK-IN HEALTH CENTRE | RG1 7QA | Y02476 | READING WALK-IN HTH CTR |
| 3 | 1-375358006 | Riverside Surgery | DN20 8AS | RIVERSIDE SURGERY | DN20 8AS | B81109 | RIVERSIDE SURGERY |
| 4 | 1-582112768 | Meadow Dale Group Practice | HX3 5SX | MEADOW DALE GROUP PRACTICE | HX3 5SX | Y03112 | 120 NURSERY LANE |
| 5 | 1-604026780 | Langbaurgh NHS Medical Centre | TS10 1SR | LANGBAURGH NHS MEDICAL CENTRE | TS10 1SR | Y02880 | COATHAM HEALTH VILLAGE |
| 6 | 1-639388631 | Hartlepool NHS Healthcare Centre | TS24 7PW | HARTLEPOOL NHS HEALTHCARE CENTRE | TS24 7PW | Y02496 | HARTLEPOOL NHS H/CARE CTR |
In [30]:
#There were 46 Virgin centres listed on the CQC site - how many have we got in the merge set?
len(virgin_merge_test1['Location Name'].unique())
Out[30]:
7
In [31]:
virgin_merge_test2=pd.merge(virgin,ep, left_on=['Postal Code'],right_on=[9])
virgin_merge_test2[['Location ID','Location Name','Street Address','Postal Code','name',1,9,0,4]]
Out[31]:
| Location ID | Location Name | Street Address | Postal Code | name | 1 | 9 | 0 | 4 | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 1-1051403692 | Lyme Regis Medical Centre | Lyme Regis Medical Centre | DT7 3LS | LYME REGIS MEDICAL CENTRE | THE LYME PRACTICE | DT7 3LS | J81088 | THE LYME PRACTICE |
| 1 | 1-1051403692 | Lyme Regis Medical Centre | Lyme Regis Medical Centre | DT7 3LS | LYME REGIS MEDICAL CENTRE | LYME MEDICAL CENTRE | DT7 3LS | Y03445 | LYME COMMUNITY CARE CNTRE |
| 2 | 1-1051403692 | Lyme Regis Medical Centre | Lyme Regis Medical Centre | DT7 3LS | LYME REGIS MEDICAL CENTRE | LYME REGIS MEDICAL CENTRE | DT7 3LS | Y03661 | LYME REGIS MEDICAL CENTRE |
| 3 | 1-1213929971 | East Park Practice | The Park Healthcare Centre | HU9 3JA | EAST PARK PRACTICE | DR AE OGUNBA AND PARTNERS | HU9 3JA | B81001 | PARK HEALTH CENTRE |
| 4 | 1-1213929971 | East Park Practice | The Park Healthcare Centre | HU9 3JA | EAST PARK PRACTICE | DR GM CHOWDHURY'S PRACTICE | HU9 3JA | B81066 | PARK HEALTH CENTRE |
| 5 | 1-1213929971 | East Park Practice | The Park Healthcare Centre | HU9 3JA | EAST PARK PRACTICE | DR RD YAGNIK'S PRACTICE | HU9 3JA | B81097 | PARK HEALTH CENTRE |
| 6 | 1-1213929971 | East Park Practice | The Park Healthcare Centre | HU9 3JA | EAST PARK PRACTICE | EAST PARK PRACTICE | HU9 3JA | B81645 | EAST PARK PRACTICE |
| 7 | 1-1213929971 | East Park Practice | The Park Healthcare Centre | HU9 3JA | EAST PARK PRACTICE | ASSURA ER LLP HULL DERMATOLOGY | HU9 3JA | Y03508 | PARK HEALTH CENTRE |
| 8 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | THE CHOWDHURY PRACTICE | OL1 1NL | P85003 | INTEGRATED CARE CENTRE |
| 9 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | OLDHAM FAMILY PRACTICE | OL1 1NL | P85007 | 1ST FLOOR INTEGRATED C C |
| 10 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | TREWINARD PRACTICE | OL1 1NL | P85601 | INTEGRATED CARE CENTRE |
| 11 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | PERKINS PRACTICE | OL1 1NL | P85608 | INTEGRATED CARE CENTRE |
| 12 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | OLDHAM PCT WALK IN CENTRE | OL1 1NL | Y00665 | INTEGRATED CARE CENTRE |
| 13 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | THE DURU PRACTICE | OL1 1NL | Y01124 | INTEGRATED CARE CENTRE |
| 14 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | PENNINE MSK PARTNERSHIP | OL1 1NL | Y01634 | OLDHAM INTERGRATED CC |
| 15 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | LINDLEY HOUSE HEALTH CENTRE | OL1 1NL | Y02875 | INTEGRATED CARE CENTRE |
| 16 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | SEXUAL HEALTH OLDHAM | OL1 1NL | Y03547 | INTEGRATED CARE CENTRE |
| 17 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | OLDHAM DERMATOLOGY SERVICE | OL1 1NL | Y03699 | DERMATOLOGY SERVICE (O/M) |
| 18 | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL | OLDHAM INTEGRATED CARE CENTRE | LEARNING ASSESSMENT AND NEUROCARE CENTRE | OL1 1NL | Y04692 | INTEGRATED CARE CENTRE |
| 19 | 1-145283366 | The Vertis Centre | Upper Ground Floor | B97 4AJ | THE VERTIS CENTRE | THE DOW SURGERY | B97 4AJ | M81020 | THE DOW SURGERY |
| 20 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | COMMUNITY HOSP WARDS (POWCH) | B61 0BB | Y01830 | STOURBRIDGE ROAD |
| 21 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | COMM HW (POWCH PALL CARE) | B61 0BB | Y01831 | STOURBRIDGE ROAD |
| 22 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | R&B ADMISSION PREVENTION TEAM | B61 0BB | Y04276 | PRINCESS OF WALES COM HSP |
| 23 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | REDDITCH & BROMSGROVE COMMUNITY NURSES | B61 0BB | Y04277 | PRINCESS OF WALES COM HSP |
| 24 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | REDDITCH & BROMSGROVE COUNTYWIDE NURSES | B61 0BB | Y04278 | PRINCESS OF WALES COM HSP |
| 25 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | RB PALL | B61 0BB | Y04337 | R&B PALLIATIVE CARE |
| 26 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | R&B OA CMHT | B61 0BB | Y04523 | R&B OA CMHT NEW HAVEN |
| 27 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | BROM CMHT | B61 0BB | Y04525 | B CMHT NEWBROOK, POWCH |
| 28 | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB | PRINCESS OF WALES COMMUNITY HOSPITAL | EIN | B61 0BB | Y04565 | EI NORTH SERVICE |
| 29 | 1-145503153 | Kidderminster Treatment Centre | Bewdley Road | DY11 6RJ | KIDDERMINSTER TREATMENT CENTRE | CAMHS KIDDERMINSTER | DY11 6RJ | M81642 | KIDDERMINSTER HOSPITAL |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 107 | 1-677085054 | Virgin Care Services | Sterling Street Medical Centre | DN31 3AE | VIRGIN CARE SERVICES | VIRGINCARE NORTH EAST LINCOLNSHIRE | DN31 3AE | Y04465 | STIRLING MEDICAL CENTRE |
| 108 | 1-679514695 | Finch Road GP Practice | Finch Road Surgery | B19 1HS | FINCH ROAD GP PRACTICE | FINCH ROAD PRIMARY CARE CENTRE | B19 1HS | M85154 | FINCH RD PRIMARY CARE CTR |
| 109 | 1-679514695 | Finch Road GP Practice | Finch Road Surgery | B19 1HS | FINCH ROAD GP PRACTICE | HOB TPCT-DIABETES CARE | B19 1HS | Y00385 | FINCH RD PRIMARY CARE CTR |
| 110 | 1-679514695 | Finch Road GP Practice | Finch Road Surgery | B19 1HS | FINCH ROAD GP PRACTICE | FINCH ROAD SURGERY | B19 1HS | Y02963 | FINCH ROAD SURGERY |
| 111 | 1-679851397 | Anchor Centre GP Practice | Simon House | CV1 5FX | ANCHOR CENTRE GP PRACTICE | SWANSWELL MEDICAL CENTRE | CV1 5FX | M86621 | SWANSWELL MEDICAL CENTRE |
| 112 | 1-679851397 | Anchor Centre GP Practice | Simon House | CV1 5FX | ANCHOR CENTRE GP PRACTICE | THE ANCHOR CENTRE | CV1 5FX | Y00060 | SIMON HOUSE |
| 113 | 1-679851773 | Broad Lane GP Practice | 684 Broad Lane | CV5 7BB | BROAD LANE GP PRACTICE | BROAD LANE GP SURGERY | CV5 7BB | M86608 | BROAD LANE SURGERY |
| 114 | 1-679851773 | Broad Lane GP Practice | 684 Broad Lane | CV5 7BB | BROAD LANE GP PRACTICE | BROAD LANE SURGERY | CV5 7BB | Y02857 | 684 BROAD LANE |
| 115 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | HILLFIELDS HEALTH CENTRE - 1 | CV1 4FS | M86005 | CITY OF COVENTRY HC |
| 116 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | PRIORY GATE PRACTICE | CV1 4FS | M86012 | CITY OF COVENTRY HLTH CTR |
| 117 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | CITY OF COVENTRY HEALTH CENTRE | CV1 4FS | M86617 | CITY OF COVENTRY HTH CTR |
| 118 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | C&WPT OOH SERVICE | CV1 4FS | M86639 | 1ST FLOOR ROOM 018 |
| 119 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | CHILD AND FAMILY SERVICES | CV1 4FS | Y00992 | 1ST FLOOR PAYBODY BLDG |
| 120 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | MERIDIAN PRACTICE | CV1 4FS | Y00996 | CITY OF COVENTRY H.C. |
| 121 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | COMMUNITY DERMATOLOGY SERVICE | CV1 4FS | Y00997 | CITY OF COVENTRY HTH CTR |
| 122 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | COMMUNITY ENT SERVICE | CV1 4FS | Y01130 | CITY OF COVENTRY HTH CTR |
| 123 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | COMMUNITY GYNAECOLOGY SERVICE | CV1 4FS | Y01168 | CITY OF COVENTRY HTH CTR |
| 124 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | INTEGRATED SEXUAL HEALTH SERVICE | CV1 4FS | Y01667 | CITY OF COVENTRY HTH CTR |
| 125 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | COMMUNITY OPHTHALMOLOGY SERV | CV1 4FS | Y02318 | CITY OF COVENTRY HTH CTR |
| 126 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | COVENTRY NHS HEALTHCARE CTR | CV1 4FS | Y02613 | CITY OF COVENTRY HTH CTR |
| 127 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | CAMHS - COVENTRY | CV1 4FS | Y03110 | 4TH FLOOR COVENTRY HC |
| 128 | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS | COVENTRY GP AND WALK IN CENTRE | COVENTRY CMHT | CV1 4FS | Y03557 | SWANSWELL POINT |
| 129 | 1-679928569 | Deer Park | Deer Park Medical Centre | OX28 5YT | DEER PARK | DEER PARK MEDICAL CENTRE | OX28 5YT | K84622 | DEER PARK MEDICAL CENTRE |
| 130 | 1-679928946 | Kingsheath GP Practice | North Oval | NN5 7LN | KINGSHEATH GP PRACTICE | KINGS HEATH HEALTH CARE CENTRE | NN5 7LN | Y00028 | NORTH OVAL |
| 131 | 1-679989614 | Lingsbrook GP Practice | Weston Flavell Centre, Billingbrook Road | NN3 8DW | LINGSBROOK GP PRACTICE | MOLLA & KESANI | NN3 8DW | K83033 | WESTON FAVELL HEALTH CTR |
| 132 | 1-679989614 | Lingsbrook GP Practice | Weston Flavell Centre, Billingbrook Road | NN3 8DW | LINGSBROOK GP PRACTICE | LINGS BROOK PRACTICE | NN3 8DW | K83074 | LINGS BROOK PRACTICE |
| 133 | 1-679989614 | Lingsbrook GP Practice | Weston Flavell Centre, Billingbrook Road | NN3 8DW | LINGSBROOK GP PRACTICE | JAMEEL | NN3 8DW | K83076 | WESTON FAVELL HEALTH CTR |
| 134 | 1-679989614 | Lingsbrook GP Practice | Weston Flavell Centre, Billingbrook Road | NN3 8DW | LINGSBROOK GP PRACTICE | DRS LAKHA & ABBAS | NN3 8DW | K83618 | WESTON FAVELL P/C CENTRE |
| 135 | 1-714016472 | Kingstanding Community Practice | Warren Farm Road | B44 0PU | KINGSTANDING COMMUNITY PRACTICE | URGENT CARE CENTRE | B44 0PU | Y01176 | WARREN FARM HEALTH CENTRE |
| 136 | 1-714016472 | Kingstanding Community Practice | Warren Farm Road | B44 0PU | KINGSTANDING COMMUNITY PRACTICE | THE KINGSTANDING COMMUNITY PRACTICE | B44 0PU | Y02988 | WARREN FARM HEALTH CENTRE |
137 rows × 9 columns
In [32]:
#There were 46 Virgin centres listed on the CQC site - how many have we got in the merge set?
len(virgin_merge_test2['Location Name'].unique())
Out[32]:
37
In [33]:
virgin[['Provider Name','Location ID','Location Name','Street Address','Postal Code']]
Out[33]:
| Provider Name | Location ID | Location Name | Street Address | Postal Code | |
|---|---|---|---|---|---|
| 328 | VH Doctors Limited | 1-1051403692 | Lyme Regis Medical Centre | Lyme Regis Medical Centre | DT7 3LS |
| 7818 | VH Doctors Limited | 1-1213929971 | East Park Practice | The Park Healthcare Centre | HU9 3JA |
| 18328 | Virgin Care Blackpool LLP | 1-145049431 | Oldham Integrated Care Centre | New Radcliffe Street | OL1 1NL |
| 18422 | Virgin Care Vertis LLP | 1-145283366 | The Vertis Centre | Upper Ground Floor | B97 4AJ |
| 18423 | Virgin Care Vertis LLP | 1-145283386 | Princess Of Wales Community Hospital | Stourbridge Road | B61 0BB |
| 18455 | Virgin Care Wyre Forest LLP | 1-145503153 | Kidderminster Treatment Centre | Bewdley Road | DY11 6RJ |
| 19121 | Peninsula Health LLP | 1-149321981 | Peninsula Health Birkenhead Medical Building | 2nd Floor Birkenhead Medical Building | CH41 8DB |
| 21936 | Virgin Care Macclesfield LLP | 1-188720285 | Spire Regency Hospital | West Street | SK11 8DW |
| 25696 | Virgin Care Cambridge LLP | 1-201621036 | Nuffield Road Medical Practice | Nuffield Road | CB4 1GL |
| 28494 | Virgin Care Vertis LLP | 1-227856719 | The Alexandra Hospital | Woodrow Drive | B98 7UB |
| 30657 | Virgin Care Coventry LLP | 1-301391094 | Summerfield GP and Urgent Care Centre | 134 Heath Street | B18 7AL |
| 31425 | Assura Reading LLP | 1-318228148 | Reading Walk-in Health Centre | 1st Floor | RG1 7QA |
| 32339 | VH Doctors Limited | 1-348027749 | VH Doctors Ltd | Lynton House | WC1H 9LT |
| 32489 | North Lancashire Doctors Limited | 1-352858943 | Queen Victoria Centre Morecambe | Thornton Road | LA4 5NN |
| 32985 | Virgin Care East Riding LLP | 1-375358006 | Riverside Surgery | Barnard Avenue | DN20 8AS |
| 32989 | Virgin Care East Riding LLP | 1-375458032 | Central Surgery | King Street | DN18 5ER |
| 32990 | Virgin Care East Riding LLP | 1-375458072 | Ironstone Centre | West Street | DN15 6HX |
| 33285 | Virgin Care Wandle LLP | 1-390977846 | Croydon Urgent Care Centre | 530 London Road | CR7 7YE |
| 34601 | Virgin Care Blackpool LLP | 1-446510115 | Moor Park Health and Leisure | Moor Park Health and Leisure Centre | FY2 0JG |
| 40947 | Virgin Care Provider Services Limited | 1-552207793 | Sexual Health Milton Keynes | 18 Crown Walk | MK9 3AH |
| 43944 | Virgin Care Leeds LLP | 1-582065001 | Virgin Care Leeds LLP | The Meadow Dale Group Practice | HX5 0EE |
| 43953 | Virgin Care Leeds LLP | 1-582112768 | Meadow Dale Group Practice | Nursery Lane | HX3 5SX |
| 43997 | Virgin Care Leeds LLP | 1-582399283 | Meadow Dale Group Practice | Ground floor | HX6 3AD |
| 44635 | Virgin Care Chelmsford LLP | 1-596529441 | North Chelmsford NHS Healthcare Centre | North Chelmsford NHS Healthcare Centre | CM2 5EF |
| 44672 | Virgin Care Services Limited | 1-600018110 | Meadow Dale Group Practice | Ground floor, Rosemount House | HX5 0EE |
| 44673 | Virgin Care Services Limited | 1-600018746 | Virgin Care Services Limited | The Meadow Dale Group Practice | HX3 5SX |
| 44708 | Virgin Care Stockton LLP | 1-604026076 | Stockton NHS Healthcare Centre | High Newton Road | TS19 8RH |
| 44709 | Virgin Care Stockton LLP | 1-604026780 | Langbaurgh NHS Medical Centre | Coatham Health Village | TS10 1SR |
| 44891 | VH Doctors Limited | 1-619613982 | VH Doctors Ltd - Purfleet Care Centre | Purfleet Care Centre | RM19 1SX |
| 45026 | Virgin Care Leicestershire LLP | 1-631450642 | Leicester City Assist Service | 1A Clyde Street | LE1 2BG |
| 45090 | Virgin Care Hartlepool LLP | 1-639388631 | Hartlepool NHS Healthcare Centre | One Life Hartlepool | TS24 7PW |
| 45264 | Virgin Care East Riding LLP | 1-659419411 | Story street medical practice and walk in centre | Wilberforce Health Centre | HU1 3AS |
| 45265 | Virgin Care East Riding LLP | 1-659430334 | Northpoint Medical Practice | Bransholme Health Centre | HU7 4DW |
| 45455 | Virgin Care Services Limited | 1-677057619 | Ironstone Medical Centre | West Street | DN15 6HX |
| 45456 | Virgin Care Services Limited | 1-677085054 | Virgin Care Services | Sterling Street Medical Centre | DN31 3AE |
| 45493 | Virgin Care Coventry LLP | 1-679514695 | Finch Road GP Practice | Finch Road Surgery | B19 1HS |
| 45498 | Virgin Care Coventry LLP | 1-679851397 | Anchor Centre GP Practice | Simon House | CV1 5FX |
| 45499 | Virgin Care Coventry LLP | 1-679851773 | Broad Lane GP Practice | 684 Broad Lane | CV5 7BB |
| 45500 | Virgin Care Coventry LLP | 1-679928232 | Coventry GP and Walk In Centre | City of Coventry Health Centre | CV1 4FS |
| 45501 | Virgin Care Coventry LLP | 1-679928569 | Deer Park | Deer Park Medical Centre | OX28 5YT |
| 45503 | Virgin Care Coventry LLP | 1-679928946 | Kingsheath GP Practice | North Oval | NN5 7LN |
| 45504 | Virgin Care Coventry LLP | 1-679989614 | Lingsbrook GP Practice | Weston Flavell Centre, Billingbrook Road | NN3 8DW |
| 45505 | Virgin Care Coventry LLP | 1-680020809 | Stone Road | Stone Road IA Unit | B15 2HH |
| 45851 | Virgin Care Vertis LLP | 1-714016472 | Kingstanding Community Practice | Warren Farm Road | B44 0PU |
| 46400 | North Lancashire Doctors Limited | 1-791314789 | Royal Lancaster Infirmary | Royal Lancaster Infirmary | LA1 4RP |
| 47138 | Virgin Care Liverpool LLP | 1-906745991 | Birkenhead Medical Building | Birkenhead Medical Centre | CH41 8DB |
In [34]:
virginco={}
for v in virgin['Provider Name'].unique().tolist():
virginco[v]=ocReconcileBest(v)
virginco
Out[34]:
{'Assura Reading LLP': {'name': 'ASSURA READING LLP',
'uri': 'http://opencorporates.com/companies/gb/OC336595',
'score': 85.0,
'id': '/companies/gb/OC336595',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Macclesfield LLP': {'name': 'VIRGIN CARE MACCLESFIELD LLP',
'uri': 'http://opencorporates.com/companies/gb/OC322654',
'score': 85.0,
'id': '/companies/gb/OC322654',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Vertis LLP': {'name': 'VIRGIN CARE VERTIS LLP',
'uri': 'http://opencorporates.com/companies/gb/OC326636',
'score': 85.0,
'id': '/companies/gb/OC326636',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Blackpool LLP': {'name': 'VIRGIN CARE BLACKPOOL LLP',
'uri': 'http://opencorporates.com/companies/gb/OC333037',
'score': 85.0,
'id': '/companies/gb/OC333037',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'VH Doctors Limited': {'name': 'VH DOCTORS LIMITED',
'uri': 'http://opencorporates.com/companies/gb/07087850',
'score': 86.0,
'id': '/companies/gb/07087850',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Leicestershire LLP': {'name': 'VIRGIN CARE LEICESTERSHIRE LLP',
'uri': 'http://opencorporates.com/companies/gb/OC337208',
'score': 85.0,
'id': '/companies/gb/OC337208',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Cambridge LLP': {'name': 'VIRGIN CARE CAMBRIDGE LLP',
'uri': 'http://opencorporates.com/companies/gb/OC337660',
'score': 85.0,
'id': '/companies/gb/OC337660',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Liverpool LLP': {'name': 'VIRGIN CARE LIVERPOOL LLP',
'uri': 'http://opencorporates.com/companies/gb/OC320467',
'score': 85.0,
'id': '/companies/gb/OC320467',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Stockton LLP': {'name': 'VIRGIN CARE STOCKTON LLP',
'uri': 'http://opencorporates.com/companies/gb/OC338425',
'score': 85.0,
'id': '/companies/gb/OC338425',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Coventry LLP': {'name': 'VIRGIN CARE COVENTRY LLP',
'uri': 'http://opencorporates.com/companies/gb/OC338351',
'score': 85.0,
'id': '/companies/gb/OC338351',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care East Riding LLP': {'name': 'VIRGIN CARE EAST RIDING LLP',
'uri': 'http://opencorporates.com/companies/gb/OC322010',
'score': 85.0,
'id': '/companies/gb/OC322010',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'North Lancashire Doctors Limited': {'name': 'NORTH LANCASHIRE DOCTORS LIMITED',
'uri': 'http://opencorporates.com/companies/gb/07178590',
'score': 86.0,
'id': '/companies/gb/07178590',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Peninsula Health LLP': {'name': 'PENINSULA HEALTH LLP',
'uri': 'http://opencorporates.com/companies/gb/OC327017',
'score': 85.0,
'id': '/companies/gb/OC327017',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Leeds LLP': {'name': 'VIRGIN CARE LEEDS LLP',
'uri': 'http://opencorporates.com/companies/gb/OC337207',
'score': 85.0,
'id': '/companies/gb/OC337207',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Hartlepool LLP': {'name': 'VIRGIN CARE HARTLEPOOL LLP',
'uri': 'http://opencorporates.com/companies/gb/OC338356',
'score': 85.0,
'id': '/companies/gb/OC338356',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Wandle LLP': {'name': 'VIRGIN CARE WANDLE LLP',
'uri': 'http://opencorporates.com/companies/gb/OC338798',
'score': 85.0,
'id': '/companies/gb/OC338798',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Chelmsford LLP': {'name': 'VIRGIN CARE CHELMSFORD LLP',
'uri': 'http://opencorporates.com/companies/gb/OC337209',
'score': 85.0,
'id': '/companies/gb/OC337209',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Wyre Forest LLP': {'name': 'VIRGIN CARE WYRE FOREST LLP',
'uri': 'http://opencorporates.com/companies/gb/OC333038',
'score': 85.0,
'id': '/companies/gb/OC333038',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Provider Services Limited': {'name': 'VIRGIN CARE PROVIDER SERVICES LIMITED',
'uri': 'http://opencorporates.com/companies/gb/07645492',
'score': 86.0,
'id': '/companies/gb/07645492',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]},
'Virgin Care Services Limited': {'name': 'VIRGIN CARE SERVICES LIMITED',
'uri': 'http://opencorporates.com/companies/gb/07557877',
'score': 86.0,
'id': '/companies/gb/07557877',
'match': False,
'type': [{'name': 'Organization', 'id': '/organization/organization'}]}}
In [5]:
#!pip3 install --upgrade fuzzywuzzy #git+git://github.com/seatgeek/fuzzywuzzy.git#egg=fuzzywuzzy
Downloading/unpacking fuzzywuzzy from https://pypi.python.org/packages/source/f/fuzzywuzzy/fuzzywuzzy-0.3.2.tar.gz#md5=a3a5b9caa9d1f7d800d33a2c02dba897
Downloading fuzzywuzzy-0.3.2.tar.gz
Running setup.py (path:/tmp/pip_build_root/fuzzywuzzy/setup.py) egg_info for package fuzzywuzzy
Installing collected packages: fuzzywuzzy
Found existing installation: fuzzywuzzy 0.3.1
Uninstalling fuzzywuzzy:
Successfully uninstalled fuzzywuzzy
Running setup.py install for fuzzywuzzy
Successfully installed fuzzywuzzy
Cleaning up...
In [36]:
from fuzzywuzzy import fuzz
In [37]:
fuzz.ratio("this is a test", "this is a test!")
Out[37]:
97
CQC Scraper¶
Simple function to scrape headline report about a location from CQC website
In [64]:
import requests
from bs4 import BeautifulSoup
def getCQCheadlineReport(location):
''' Simple scraper to extract headline report about a CQC location inspection '''
urlstub='http://www.cqc.org.uk/location/'
response = requests.get(urlstub+location)
soup=BeautifulSoup(response.content)
results={}
inspectionStatus=soup.find('div',{'class':"header-wrapper"}).find('h2')
if inspectionStatus.text.startswith('CQC have not inspected'):
return {'status':inspectionStatus.text}
#Get the list of results
overview=soup.find('div',{'class':'overview-inspections'})
h3=overview.find('h3')
ul=overview.find('ul')
results= {'status':'inspected'}#{'Overall':h3.find('strong').text}
for result in ul.findAll('li'):
if result.find('span').text!='':
results[result.find('a').text]=result.find('span').text
else:
results[result.find('a').text]=[attr for attr in result.find('span').attrs['class'] if attr not in ['sprite', 'icon', 'check']]
return results
#getCQCheadlineReport('1-1001973807')
#getCQCheadlineReport('1-106897628')
In [164]:
getCQCheadlineReport('1-390977846')
Out[164]:
{'Caring for people safely and protecting them from harm': ['not-met-all'],
'Staffing': ['not-met-all'],
'status': 'inspected',
'Treating people with respect and involving them in their care': ['not-met-all'],
'Quality and suitability of management': ['not-met-all'],
"Providing care, treatment and support that meets people's needs": ['not-met-all']}
In [165]:
getCQCheadlineReport('1-106897628')
Out[165]:
{'Responsive': 'No action required',
'status': 'inspected',
'Effective': 'No action required',
'Safe': 'No action required',
'Well-led': 'No action required',
'Caring': 'No action required'}
IW Map¶
In [38]:
iwch=df[ (df['County']=='Isle of Wight') & (df['Care home?']=='Y')]
In [39]:
iwch.groupby('Brand Name').size().order(ascending=False)
Out[39]:
Brand Name - 75 BRAND Somerset Care 4 BRAND Barchester Healthcare 2 BRAND Regard Partnership 1 BRAND Leonard Cheshire Disability 1 dtype: int64
In [40]:
iwch.groupby('Provider Name').size().order(ascending=False)
Out[40]:
Provider Name Isle of Wight Council 6 Ryde House LLP 5 Somerset Care Limited 4 Island Health Care Limited 4 Scio Health Care Limited 3 Isle of Wight Care Limited 2 Colville Care Limited 2 Mr Sanjay Prakashsingh Ramdany & Mrs Sandhya Kumari Ramdany 2 Southern Housing Group Limited 2 St Vincent Care Homes Limited 2 Buckland Care Limited 2 Make All Limited 2 Barchester Healthcare Homes Limited 2 Kensington Health Care Limited 1 Hampshire & Isle of Wight & Channel Islands Association for Deaf People Limited 1 Island Care Limited 1 In Safe Hands Home Care Limited 1 Hillview Care Limited 1 Harrison Care Enterprises Limited 1 Ward House Limited 1 G Elliott and Mrs Brenda Mary Furse 1 H & W Coastal Limited 1 Going Places Nursery Ltd 1 Georgia Rose Residential Care Limited 1 Leonard Cheshire Disability 1 First For Care Limited 1 Fairview Care Home Limited 1 Downside House Limited 1 Dee Zee Ltd 1 C James and D Burn 1 Brighstone Care Limited 1 Kevin Bell 1 McCourt Care Limited 1 London Residential Health Care Limited 1 Mrs Eveline Anne Basile 1 The Regard Partnership Limited 1 The Moorings Care Limited 1 The Croft (RCH) Limited 1 SSAFA Forces Help Enterprises Limited 1 Newport Residential Care Limited 1 Ms Julie Coombs 1 Mrs Trina Marie Clack 1 Mrs M Fuller 1 Mrs Lynda Lawlor 1 Mrs J Bacon 1 Mrs Christine Rosemary Willett 1 M & S Care Limited 1 Mrs Carole Brooke 1 Mrs Barbara Winifred Anne Bailey 1 Mr Keith John Betteley & Mrs Jennifer Ann Betteley 1 Mr David John Knowles 1 Mr Anthony Delannoy and Mrs Hannah Delannoy 1 Mr & Mrs R Cowen 1 Mr & Mrs A Cable 1 Merrydale Residential Home 1 Mentfade Limited 1 Venetian Health Care Limited 1 Autumn House Care Limited 1 Length: 58, dtype: int64
In [41]:
iwdentists=df[ (df['County']=='Isle of Wight') & (df['Service type - Dental service']=='Y')]
iwdentists
Out[41]:
| Location ID | HSCA start date | Care home? | Location Name | Telephone Number | Registered manager (note; where there is more than one manager at a location, only one is included here for ease of presentation. The full list is available if required). | Web Address | Care homes beds | Location Type/Sector | Region | ... | Service user band - Learning disabilities or autistic spectrum disorder | Service user band - Mental Health | Service user band - Older People | Service user band - People detained under the Mental Health Act | Service user band - People who misuse drugs and alcohol | Service user band - People with an eating disorder | Service user band - Physical Disability | Service user band - Sensory Impairment | Service user band - Whole Population | Service user band - Younger Adults | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 21300 | 1-188089121 | 2011-06-01 | N | The Orthodontic Practice - Dover Street | 01983566405 | * | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 22611 | 1-189906279 | 2011-08-30 | N | Tower House Dental Clinic | 01983617791 | Kazmierczak, Marcin | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | Y | NaN | NaN | NaN | Y | NaN | Y | Y |
| 22693 | 1-190048087 | 2011-11-23 | N | Island Dental Care | 01983852189 | Hamilton, Brian | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 23058 | 1-194207931 | 2011-05-24 | N | Fradgley & Willetts Dental Surgeons Ventnor | 01983853721 | * | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 24631 | 1-198554319 | 2011-08-31 | N | James Spence Dental Surgery | 01983615108 | * | www.iwdentist.co.uk | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 25359 | 1-200124920 | 2011-06-01 | N | St James Clinic | 01983822220 | Gautier, Rebecca | www.stjamesclinic.com | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 26217 | 1-206140891 | 2011-05-06 | N | Newport Dental Centre | NaN | Millroy, Jacqueline | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 26260 | 1-206141586 | 2011-05-06 | N | Whitecross Dental Care - Freshwater | NaN | Millroy, Jacqueline | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 26261 | 1-206141601 | 2011-05-06 | N | Whitecross Dental Care - Ryde | NaN | Millroy, Jacqueline | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 27059 | 1-212979089 | 2011-04-06 | N | Apex Dental Care - Shanklin | 01983864411 | Chaing, Benjamin Ka Ping | www.apex-dental.co.uk | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 28049 | 1-221172022 | 2011-06-28 | N | M R Richards Dental Surgery | 01983863339 | Richards, Martin | NaN | 0 | Primary Dental Care | South East | ... | Y | Y | Y | NaN | Y | Y | Y | Y | NaN | Y |
| 28052 | 1-221191352 | 2011-04-01 | N | Amery House Dental Practice | 01983291863 | * | www.ameryhouse.com | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 28058 | 1-221206112 | 2011-04-01 | N | Island Dental Services | 01983291423 | * | www.islanddentalservices.webden.co.uk | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 30944 | 1-307379859 | 2011-09-22 | N | Apex Dental Care- Ryde | NaN | Chaing, Benjamin Ka Ping | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 33232 | 1-388678724 | 2012-04-05 | N | Yarmouth Dental Practice | 01983761237 | Duggan, Tracy Jane | NaN | 0 | Primary Dental Care | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 47751 | R1F01 | 2012-04-01 | N | St Mary's Hospital | 01983524081 | * | www.iow.nhs.uk | 0 | NHS Healthcare Organisation | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
| 47757 | R1FX5 | 2012-04-01 | N | Community Healthcare Services, St Mary's Hospital | 01983535476 | * | www.iow.nhs.uk | 0 | NHS Healthcare Organisation | South East | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | Y | NaN |
17 rows × 89 columns
In [42]:
iwdentists.groupby('Brand Name').size().order(ascending=False)
Out[42]:
Brand Name - 12 BRAND IDH Dental 3 BRAND Oasis Dental Care 2 dtype: int64
In [43]:
iwdentists.groupby('Provider Name').size().order(ascending=False)
Out[43]:
Provider Name Whitecross Dental Care Limited 3 Isle of Wight NHS Trust 2 Apex Dental Care Limited 2 Wight Dental Care Limited 1 Wight Aesthetic Limited 1 Mrs. Mary Elizabeth Lammiman 1 Mr. Mark Cronshaw 1 Mr. James Spence 1 Mr. Alfred Keith Tierney 1 Moons Hill Administrative Services Limited 1 Dr. Timothy Murray Fradgley 1 Dr John Philip Dine 1 B. D. Hamilton 1 dtype: int64
In [44]:
df.groupby('Brand Name').size().order(ascending=False)
Out[44]:
Brand Name - 39681 BRAND IDH Dental 522 BRAND Four Seasons Group 366 BRAND Voyage 309 BRAND BUPA Group 287 BRAND Care UK 249 BRAND Oasis Dental Care 246 BRAND Priory Group 237 BRAND Barchester Healthcare 185 BRAND Royal Mencap Society 174 BRAND HC-One Limited 169 BRAND Bluebird Care 152 BRAND Acromas Healthcare 146 BRAND Home Instead 130 BRAND Caretech Community Services 128 ... BRAND Richmond Fellowship 8 BRAND Majesticare 8 BRAND Matta Care Group 8 BRAND St Andrew's Healthcare 8 BRAND Viridian Housing 7 BRAND Marie Curie Cancer Care 6 BRAND The Royal British Legion 6 BRAND Aster Healthcare Ltd 6 BRAND UBU 5 BRAND C&C Alpha Group 5 BRAND Ablegrange Group 5 BRAND Caremark Limited 3 BRAND Oxfordshire County Council 3 BRAND Circle Healthcare Ltd 3 P.R.A.C Limited 3 Length: 218, dtype: int64
In [45]:
serviceTypes.groupby('Brand Name').size().order(ascending=False)
Out[45]:
Brand Name - 39681 BRAND IDH Dental 522 BRAND Four Seasons Group 366 BRAND Voyage 309 BRAND BUPA Group 287 BRAND Care UK 249 BRAND Oasis Dental Care 246 BRAND Priory Group 237 BRAND Barchester Healthcare 185 BRAND Royal Mencap Society 174 BRAND HC-One Limited 169 BRAND Bluebird Care 152 BRAND Acromas Healthcare 146 BRAND Home Instead 130 BRAND Caretech Community Services 128 ... BRAND Richmond Fellowship 8 BRAND Majesticare 8 BRAND Matta Care Group 8 BRAND St Andrew's Healthcare 8 BRAND Viridian Housing 7 BRAND Marie Curie Cancer Care 6 BRAND The Royal British Legion 6 BRAND Aster Healthcare Ltd 6 BRAND UBU 5 BRAND C&C Alpha Group 5 BRAND Ablegrange Group 5 BRAND Caremark Limited 3 BRAND Oxfordshire County Council 3 BRAND Circle Healthcare Ltd 3 P.R.A.C Limited 3 Length: 218, dtype: int64
In [45]:
In [46]:
serviceTypesLong=pd.melt(serviceTypes,id_vars=['Location ID', 'HSCA start date','Location Name',
'County', 'Postal Code', 'Brand ID', 'Brand Name', 'Provider ID', 'Provider Name', 'Provider HSCA start date'], value_vars=[
'Service type - Acute services with overnight beds',
'Service type - Acute services without overnight beds / listed acute services with or without overnight beds',
'Service type - Ambulance service',
'Service type - Blood and Transplant service',
'Service type - Care home service with nursing',
'Service type - Care home service without nursing',
'Service type - Community based services for people who misuse substances',
'Service type - Community based services for people with a learning disability',
'Service type - Community based services for people with mental health needs',
'Service type - Community health care services - Independent Midwives',
'Service type - Community health care services - Nurses Agency only',
'Service type - Community healthcare service',
'Service type - Dental service',
'Service type - Diagnostic and/or screening service',
'Service type - Diagnostic and/or screening service - single handed sessional providers',
'Service type - Doctors consultation service',
'Service type - Doctors treatment service',
'Service type - Domiciliary care service',
'Service type - Extra Care housing services',
'Service type - Hospice services',
'Service type - Hospice services at home',
'Service type - Hospital services for people with mental health needs, learning disabilities and problems with substance misuse',
'Service type - Hyperbaric Chamber',
'Service type - Long term conditions services',
'Service type - Mobile doctors service',
'Service type - Prison Healthcare Services',
'Service type - Rehabilitation services',
'Service type - Remote clinical advice service',
'Service type - Residential substance misuse treatment and/or rehabilitation service',
'Service type - Shared Lives', 'Service type - Specialist college service',
'Service type - Supported living service',
'Service type - Urgent care services' ])
serviceTypesLong=serviceTypesLong[serviceTypesLong['value']=='Y']
In [47]:
serviceTypesLong[:3]
Out[47]:
| Location ID | HSCA start date | Location Name | County | Postal Code | Brand ID | Brand Name | Provider ID | Provider Name | Provider HSCA start date | variable | value | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 802 | 1-108386437 | 2011-01-24 | The New Victoria Hospital | Surrey | KT2 7EG | - | - | 1-101727633 | The New Victoria Hospital Limited | 2010-10-01 | Service type - Acute services with overnight beds | Y |
| 812 | 1-108411638 | 2010-11-24 | First Trust Hospital | Lancashire | PR3 5LD | - | - | 1-101634722 | Anaster Limited | 2010-10-01 | Service type - Acute services with overnight beds | Y |
| 844 | 1-108552349 | 2011-01-21 | Burrswood Hospital | Kent | TN3 9PY | - | - | 1-101635537 | The Dorothy Kerin Trust | 2010-10-01 | Service type - Acute services with overnight beds | Y |
In [48]:
serviceTypesLong[serviceTypesLong['Brand Name']!='-'].groupby(['Brand Name','variable']).filter(lambda x: len(x) >= 75).groupby(['Brand Name','variable']).size().order(ascending=False)
Out[48]:
Brand Name variable BRAND IDH Dental Service type - Dental service 522 BRAND Four Seasons Group Service type - Care home service with nursing 272 BRAND Voyage Service type - Care home service without nursing 263 BRAND Oasis Dental Care Service type - Dental service 246 BRAND BUPA Group Service type - Care home service with nursing 230 BRAND Four Seasons Group Service type - Care home service without nursing 168 BRAND Bluebird Care Service type - Domiciliary care service 152 BRAND Priory Group Service type - Care home service without nursing 148 BRAND Barchester Healthcare Service type - Care home service with nursing 144 BRAND HC-One Limited Service type - Care home service with nursing 135 BRAND Acromas Healthcare Service type - Domiciliary care service 132 BRAND Home Instead Service type - Domiciliary care service 130 BRAND Caretech Community Services Service type - Care home service without nursing 114 BRAND Royal Mencap Society Service type - Care home service without nursing 110 BRAND Housing & Care 21 Service type - Domiciliary care service 95 BRAND Voyage Service type - Domiciliary care service 88 BRAND Dimensions Service type - Care home service without nursing 87 BRAND Care UK Service type - Care home service with nursing 81 BRAND Anchor Trust Service type - Care home service without nursing 81 dtype: int64
In [56]:
#Create accession number for each group member
sl=serviceTypesLong
sl['loc_accNum']=sl.sort('HSCA start date').groupby(['Brand Name']).cumcount()
In [59]:
sl[:3]
Out[59]:
| Location ID | HSCA start date | Location Name | County | Postal Code | Brand ID | Brand Name | Provider ID | Provider Name | Provider HSCA start date | variable | value | loc_accNum | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 802 | 1-108386437 | 2011-01-24 | The New Victoria Hospital | Surrey | KT2 7EG | - | - | 1-101727633 | The New Victoria Hospital Limited | 2010-10-01 | Service type - Acute services with overnight beds | Y | 12394 |
| 812 | 1-108411638 | 2010-11-24 | First Trust Hospital | Lancashire | PR3 5LD | - | - | 1-101634722 | Anaster Limited | 2010-10-01 | Service type - Acute services with overnight beds | Y | 3144 |
| 844 | 1-108552349 | 2011-01-21 | Burrswood Hospital | Kent | TN3 9PY | - | - | 1-101635537 | The Dorothy Kerin Trust | 2010-10-01 | Service type - Acute services with overnight beds | Y | 11898 |
In [58]:
from ggplot import *
In [62]:
ggplot(sl[(sl['Brand Name']=='BRAND IDH Dental') | (sl['Brand Name']=='BRAND Oasis Dental Care')],
aes(x='HSCA start date',y='loc_accNum',color='Brand Name'))+geom_line()
Out[62]:
<ggplot: (-9223363283570719705)>
In [63]:
sl[sl['Brand Name']=='BRAND IDH Dental'][:5]
Out[63]:
| Location ID | HSCA start date | Location Name | County | Postal Code | Brand ID | Brand Name | Provider ID | Provider Name | Provider HSCA start date | variable | value | loc_accNum | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 597474 | 1-1025253788 | 2013-12-04 | Lyppard Dental Centre | Worcestershire | WR4 0DZ | BD230 | BRAND IDH Dental | 1-677418445 | Lyppard Dental Centre Practice Partnership | 2013-12-04 | Service type - Dental service | Y | 422 |
| 597569 | 1-1043259741 | 2013-12-18 | Falmouth Dental Health Centre | Cornwall | TR11 2DH | BD230 | BRAND IDH Dental | 1-973502741 | The Old Brewery Yard Dental Practice Partnership | 2013-12-18 | Service type - Dental service | Y | 423 |
| 598645 | 1-1102135511 | 2014-01-08 | Lingwell Croft Dental Practice | West Yorkshire | LS10 3NB | BD230 | BRAND IDH Dental | 1-1070355565 | Shelldrake Drive Dental Practice Partnership | 2014-01-08 | Service type - Dental service | Y | 438 |
| 598940 | 1-1111751240 | 2014-02-02 | Kenton Park Dental Practice | Tyne And Wear | NE3 4RU | BD230 | BRAND IDH Dental | 1-1098009961 | Kenton Park Dental Practice Partnership | 2014-02-02 | Service type - Dental service | Y | 464 |
| 599186 | 1-1116031048 | 2014-01-07 | Perkins and Harrison Dental | North Yorkshire | YO12 7HT | BD230 | BRAND IDH Dental | 1-1102521969 | North Marine Road Dental Practice Partnership | 2014-01-07 | Service type - Dental service | Y | 437 |
In [65]:
getCQCheadlineReport('1-1111751240')
Out[65]:
{'status': 'CQC have not inspected this service yet'}
In [66]:
serviceTypesLong['variable'].unique()
Out[66]:
array(['Service type - Acute services with overnight beds',
'Service type - Acute services without overnight beds / listed acute services with or without overnight beds',
'Service type - Ambulance service',
'Service type - Blood and Transplant service',
'Service type - Care home service with nursing',
'Service type - Care home service without nursing',
'Service type - Community based services for people who misuse substances',
'Service type - Community based services for people with a learning disability',
'Service type - Community based services for people with mental health needs',
'Service type - Community health care services - Independent Midwives',
'Service type - Community health care services - Nurses Agency only',
'Service type - Community healthcare service',
'Service type - Dental service',
'Service type - Diagnostic and/or screening service',
'Service type - Diagnostic and/or screening service - single handed sessional providers',
'Service type - Doctors consultation service',
'Service type - Doctors treatment service',
'Service type - Domiciliary care service',
'Service type - Extra Care housing services',
'Service type - Hospice services',
'Service type - Hospice services at home',
'Service type - Hospital services for people with mental health needs, learning disabilities and problems with substance misuse',
'Service type - Hyperbaric Chamber',
'Service type - Long term conditions services',
'Service type - Mobile doctors service',
'Service type - Prison Healthcare Services',
'Service type - Rehabilitation services',
'Service type - Remote clinical advice service',
'Service type - Residential substance misuse treatment and/or rehabilitation service',
'Service type - Shared Lives',
'Service type - Specialist college service',
'Service type - Supported living service',
'Service type - Urgent care services'], dtype=object)
In [76]:
#gpcats=['Service type - Doctors treatment service','Service type - Doctors consultation service']
gpgroup1=sl[sl['variable']=='Service type - Doctors consultation service'].groupby('Provider Name').filter(lambda x: len(x) > 10)
gpgroup2=sl[sl['variable']=='Service type - Doctors treatment service'].groupby('Provider Name').filter(lambda x: len(x) > 10)
#gpgroup1=sl[(sl['variable'].isin(gpcats)) & (sl['Brand Name']!='-')]
gpgroup1[:3]
Out[76]:
| Location ID | HSCA start date | Location Name | County | Postal Code | Brand ID | Brand Name | Provider ID | Provider Name | Provider HSCA start date | variable | value | loc_accNum | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 746783 | 1-1020208748 | 2013-11-12 | Park Community Practice | NaN | HX1 5UA | BD329 | BRAND Locala | 1-256729774 | Locala Community Partnerships C.I.C | 2011-10-03 | Service type - Doctors consultation service | Y | 28 |
| 746784 | 1-1020224614 | 2013-11-12 | Calder Community Practice | Lancashire | OL14 5QN | BD329 | BRAND Locala | 1-256729774 | Locala Community Partnerships C.I.C | 2011-10-03 | Service type - Doctors consultation service | Y | 29 |
| 746866 | 1-1039065531 | 2013-10-29 | BPAS Taunton | Somerset | TA1 5DA | BD125 | BRAND British Pregnancy Advisory Service | 1-116992830 | British Pregnancy Advisory Service | 2011-04-12 | Service type - Doctors consultation service | Y | 71 |
In [77]:
gpgroup['BrandName']=gpgroup['Brand Name'].str.replace('BRAND ','')
ggplot(gpgroup[(gpgroup['BrandName']!='-')],
aes(x='HSCA start date',y='loc_accNum',color='BrandName'))+geom_line()
Out[77]:
<ggplot: (-9223363283573175331)>
In [80]:
kx=['Location ID', 'HSCA start date','Location Name', 'County', 'Postal Code', 'Brand ID', 'Brand Name', 'Provider ID', 'Provider Name', 'Provider HSCA start date']
gpboth=serviceTypes[(serviceTypes['Service type - Doctors treatment service']=='Y')
& (serviceTypes['Service type - Doctors consultation service']=='Y')]
gpboth=gpboth[kx]
In [87]:
gpbothgp=gpboth.groupby('Provider Name').filter(lambda x: len(x) > 10)
gpbothgp[:2]
Out[87]:
| Location ID | HSCA start date | Location Name | County | Postal Code | Brand ID | Brand Name | Provider ID | Provider Name | Provider HSCA start date | |
|---|---|---|---|---|---|---|---|---|---|---|
| 155 | 1-1024710742 | 2013-11-14 | Lawn Medical Practice - IHG | Wiltshire | SN3 1JL | - | - | 1-158662155 | Independent Health Group Limited | 2011-03-30 |
| 501 | 1-1065850512 | 2013-12-03 | Kingkerswell Health Centre - IHG | Devon | TQ12 5DJ | - | - | 1-158662155 | Independent Health Group Limited | 2011-03-30 |
In [88]:
gpbothgp['BrandName']=gpbothgp['Brand Name'].str.replace('BRAND ','')
In [89]:
gpbothgp['loc_accNum']=gpbothgp.sort('HSCA start date').groupby(['Brand Name']).cumcount()
In [90]:
ggplot(gpbothgp[(gpbothgp['BrandName']!='-')],
aes(x='HSCA start date',y='loc_accNum',color='BrandName'))+geom_line()
Out[90]:
<ggplot: (-9223363283573325536)>
In [ ]: