Outcome 3 focuses on analysing collected data to extract insights.

(a) Prepare and pre-process data for analysis 

There were several aspects of data cleaning undertaken during the investigation. Firstly, data formats were standardised to ensure these would not cause an issue during the data analysis stage. The submission date column was altered to specify year, for example. In the ONS datasets, I also set the quartile columns to 0 dp to provide consistency.

Secondly, missing values were investigated and resolved or deleted. Some columns in the ONS datasets were missing mean and median figures which needed be amended. Columns DiffMeanHourlyPercent and DiffMedianHourlyPercent were formatted to 1dp.

I appended the SIC data and median hourly rate into the UK government datasets. This was then converted to text and split by every 2 characters to give me the required classifications of each business. The postcode file was also joined into the data sets. I then used Power Query to edit the postcode and split it by digits to obtain the area part of the postcodes.

(b) Calculate dataset summary metrics 

KPIs for the gender pay gap investigation included the following that I hoped to investigate:

• Average Gender Pay Gap
• Median Gender Pay Gap
• Pay Gap by Job Category
• Pay Gap by Age Group
• Changes in the Gender Pay Gap over time (2018-2022)
• Representation of the Gender Pay Gap in Pay Quartiles

I ended up choosing to summarise my data in the following sections of the report:

• Overview
• Sector
• Employer
• Age
• Full vs Part Time
• Industry

I aggregated my data in the following ways to provide analysis:

• Calculating the mean gender pay gap across years by employer size in the UK, and Scotland
• Calculating the median gender pay gap across years by employer size in the UK, and Scotland
• Calculating the mean gender pay gap per pay quartile
• Calculating the mean gender pay gap for full-time and part-time employees, comparing the UK and Scotland
• Calculating the mean and median gender pay gap for full-time and part-time employees, comparing the individual regions of the UK
• Calculating the median gender pay gap for full-time and part-time employees, comparing sectors
• Calculating the mean and median gender pay gap for individual employers
• Calculating the mean and median gender pay gap for age brackets and their sector
• Calculating the mean gender pay gap across years by region, industry and industry type

(c) Interpret analysis to identify insights 

The focus of my investigation was set out on the following sections:

• Overview
  • This section of the report will provide insight into the overall gender pay gap to understand the magnitude of disparities across the UK and Scotland. It would also seek to understand how the gender pay gap has changed.
• Sector
  • This report section will provide insight into the public, private and non-profit sectors. It would seek to identify trends between sectors and whether any change has occurred. It would also compare this to full and part-time employee data.
• Employer
  • Within this section, an analysis of the gender pay gap data for individual employers to understand disparities within specific organisations will be represented. This section could be filtered by location and sector type.
• Age
  • This part of the report will look at the gender pay gap data by age group to understand variations in pay disparities across different generations. It will seek to identify if there are age-related patterns in the gender pay gap, such as larger gaps among older or younger employees.
• Full vs Part Time
  • This report section will compare the gender pay gaps between full-time and part-time employees. It would investigate what disparities occur between full and part-time employees. This would include whether part-time workers experience higher or lower gender pay gaps than full-time employees.
• Industry
  • This report area will compare the gender pay gap data across different industries to identify disparities within specific sectors. It will allow comparison of industries to understand variations in pay equity practices. This will highlight certain industries exhibit consistently wider or narrower gender pay gaps and investigate the underlying factors

(d) Create graphs to visualise insights

I created an interactive report using Power BI to visualise the insights of my findings. I created a multi-page design to dedicate space to the various aspects of my analysis. I decided to use several types of charts, including pie charts, bar charts, stacked bar charts, column charts and stacked column charts.