Appendix D — Exercise 5 solutions

D.1 Question 1

Load in the OBR’s household disposable income data (sheet 1.13). Split the period data into separate year and quarter variables, ensure that all variable names follow Tidyverse’s style guide. Name this object disposable_income.

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.

disposable_income <-
  read_xlsx("data/Detailed_forecast_tables_Economy_March_2024.xlsx",
                               sheet = "1.13",range = "B4:I51", 
                               col_names = FALSE) %>% 
  setNames(c("period", "labour_income", "emp_comp", "mixed_income",
             "emp_social_cont", "nonlabour_inc", "net_tax_benefits",
             "disposable_income")) %>% 
  mutate(year = as.numeric(str_sub(period, start = 1L, end = 4L)),
         quarter = as.numeric(str_sub(period, start = -1L, end = -1L))) %>% 
  select(-period)

str(disposable_income)

tibble [48 × 9] (S3: tbl_df/tbl/data.frame)
 $ labour_income    : num [1:48] 196 197 197 198 199 ...
 $ emp_comp         : num [1:48] 208 210 210 211 212 ...
 $ mixed_income     : num [1:48] 25.4 26.2 25.8 25.6 25.8 ...
 $ emp_social_cont  : num [1:48] 38.2 38.6 38.9 38.6 38.6 ...
 $ nonlabour_inc    : num [1:48] 67 68.1 67.5 66.5 66.4 ...
 $ net_tax_benefits : num [1:48] 16.7 18.2 17.5 19 18.5 ...
 $ disposable_income: num [1:48] 279 284 282 284 284 ...
 $ year             : num [1:48] 2012 2012 2012 2012 2013 ...
 $ quarter          : num [1:48] 1 2 3 4 1 2 3 4 1 2 ...

D.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))