Appendix C — Exercise 5 solutions

C.1 Question 1

Load in the OBR’s quarterly labour market data (sheet 1.6), keeping the following variables:

  • Period
  • Employment rate (%)
  • Average earning growth (%)
  • Average earning index
  • Productivity per hour index
  • Real wage product
  • Real consumption wage

Split the period data into separate year and quarter variables. Ensure that all variable names follow Tidyverse’s style guide.

Solution

Using the same approach as the housing market sheet, load the range of cells containing the data required, not including the variable names. Add variable names manually after selecting the variables required, then split the time variable into years and quarters.

# List files in data folder (to copy and paste file name!)
list.files(path = "data")
[1] "Detailed_forecast_tables_Economy_March_2024.xlsx"
[2] "generalfs21_EUL.sav"                             
[3] "interviewfs21_EUL.sav"                           
labour_market <- 
  read_xlsx("data/Detailed_forecast_tables_Economy_March_2024.xlsx",
            # Specify the sheet and range of cells to keep
            sheet = "1.6",range = c("B4:V88"), 
            # Remove column names (too messy)
            col_names = FALSE) %>% 
  # Select the variables needed by their position
  select(1, 3, 15, 16, 18, 20, 21) %>% 
  # Add variable names manually
  setNames(c("period", "employment_rate", "earning_growth", "earning_index", 
             "productivity_hour", "real_product_wage",
             "real_consumption_wage")) %>% 
  # Split the period variable into years and quarter
  mutate(year = as.numeric(str_sub(period, start = 1L, end = 4L)),
         quarter = as.numeric(str_sub(period, start = -1L, end = -1L))) %>% 
  # Remove the perioud variable
  select(-period)

C.2 Question 2

Load the OBR’s national living wage data (sheet 1.14).

Solution

Using the same approach as earlier, load the correct sheet in, selecting cells with data included. Variable names cannot begin with numbers, so rename them either manually, or by adding a prefix. The rename_with function allows us to rename variables by applying a function to them, in this case paste0 which combines elements in the function separated by commas:

living_wage <- 
  read_xlsx("data/Detailed_forecast_tables_Economy_March_2024.xlsx",
            # Specify the sheet and range of cells to keep
            sheet = "1.14", range = c("C4:K5")) %>% 
  # Rename variables by pasting the prefix "year_" to the original name
  rename_with(~paste0("year_", .x))