Assignment 2: NHS Utilisation & Capacity Planning

Brief

You are part of a team of data analysts contracted by the National Health Service (NHS). The team has been provided with internal and external data and a number of high-level business questions concerning the utilisation of services, missed appointments and the potential value of using external data sources such as X (formerly Twitter). Your role is to refine the business questions to actionable analytic questions based on your review of the available data. You will then plan and perform the exploratory data analysis and present the insights to stakeholders.

Problem statement

The UK population is growing. NHS infrastructure will need investment to grow relative to demand and the organisation will need to make better use of its existing resources.

Objective

Explore available data to identify insights and recommendations which will inform strategic decisions that shape capacity planning and optimise service delivery.

Tools and data

Tools: Python

Libraries: Pandas (data operations), numpy (numeric operations), datetime (date manipulation), seaborn and matplotlib (visualisations), re, WordCloud and STOPWORDS (WordCloud/Social Media visualisation)

Data: Three csv files containing over 1.5 million records describing approximately 24 million monthly NHS appointments from a 30 month period. Additionally approximately a csv contiaing tweet data from approximately 1000 tweets extracted from X (formerly Twitter).

Method

• Data wrangling to group and aggregate data for visualisations.
• Functions created to keep visualisations consistent based on chart type.
• Research identified libraries used to wrangle social media data and visualise it with increased impact.

Insights

• 95% of appointments in General Practice
• Higher demand for services during autumn
• Demand trending towards capacity
• Increased use of remote technologies for cosultation (telephone appointments etc)

Recommendations

• Calculate new capacity limit and staff to meet increased demand
• Ensure coverage during seasonal peaks
• Address unattadence with increased remote appointments, reducing time between booking and appointment and with social media campaigns to promote awareness