data-raw/data_mesas_englandwales.R

In djmorris1989/iomodeltobalc: Input-Output Modelling of Tobacco and Alcohol Policy

### Read in the MESAS population, consumption, and price data

library(readxl)
library(dplyr)

#####################################################
############ YEAR AND PRODUCT IDENTIFIERS ###########

year <- c(rep(1994,9),rep(1995,9),rep(1996,9),rep(1997,9),rep(1998,9),
          rep(1999,9),rep(2000,9),rep(2001,9),rep(2002,9),rep(2003,9),
          rep(2004,9),rep(2005,9),rep(2006,9),rep(2007,9),rep(2008,9),
          rep(2009,9),rep(2010,9),rep(2011,9),rep(2012,9),rep(2013,9),
          rep(2014,9),rep(2015,9))


product <- rep(c("Total","Spirits","RTDs","Fortified Wines","Wine","Other","Cider","Perry","Beer"),22)


#####################################################
######## TOTAL CONSUMED IN 000s OF LITRES ###########

# consumption data - units per person. (on trade)

litres.data <- read_excel(path = paste0("data-raw/","MESAS alcohol retail sales dataset",".xls"),
                          sheet = "England & Wales data",
                          range = "D4:Y12",
                          col_names = FALSE)

litres.data <- as.matrix(litres.data)
litres.data <- round(as.numeric(litres.data),2)

litres.ontrade <- as.vector(matrix(litres.data,ncol=1))

# consumption data - units per person. (off trade)

litres.data <- read_excel(path = paste0("data-raw/","MESAS alcohol retail sales dataset",".xls"),
                          sheet = "England & Wales data",
                          range = "AB4:AW12",
                          col_names = FALSE)

litres.data <- as.matrix(litres.data)
litres.data <- round(as.numeric(litres.data),2)

litres.offtrade <- as.vector(matrix(litres.data,ncol=1))

#####################################################
############ UNITS CONSUMED PER PERSON ##############

# consumption data - units per person. (on trade)

units.data <- read_excel(path = paste0("data-raw/","MESAS alcohol retail sales dataset",".xls"),
                         sheet = "England & Wales data",
                         range = "D30:Y38",
                         col_names = FALSE)

units.data <- as.matrix(units.data)
units.data <- round(as.numeric(units.data),2)

units.pp.ontrade <- as.vector(matrix(units.data,ncol=1))

# consumption data - units per person. (off trade)

units.data <- read_excel(path = paste0("data-raw/","MESAS alcohol retail sales dataset",".xls"),
                         sheet = "England & Wales data",
                         range = "AB30:AW38",
                         col_names = FALSE)

units.data <- as.matrix(units.data)
units.data <- round(as.numeric(units.data),2)

units.pp.offtrade <- as.vector(matrix(units.data,ncol=1))

#####################################################
############ PRICES PER UNIT ########################

# price data - average price per unit sold (on trade)

prices.data <- read_excel(path = paste0("data-raw/","MESAS alcohol retail sales dataset",".xls"),
                          sheet = "England & Wales data",
                          range = "D43:Y51",
                          col_names = FALSE)

prices.data <- as.matrix(prices.data)
prices.data <- round(as.numeric(prices.data),2)


price.ontrade <- as.vector(matrix(prices.data,ncol=1))

# price data - average price per unit sold (off trade)

prices.data <- read_excel(path = paste0("data-raw/","MESAS alcohol retail sales dataset",".xls"),
                          sheet = "England & Wales data",
                          range = "AB43:AW51",
                          col_names = FALSE)

prices.data <- as.matrix(prices.data)
prices.data <- round(as.numeric(prices.data),2)

price.offtrade <- as.vector(matrix(prices.data,ncol=1))


#####################################################
############ POPULATION DATA ########################

population <- read_excel(path = paste0("data-raw/","MESAS alcohol retail sales dataset",".xls"),
                         sheet = "Population data",
                         range = "A4:C21",
                         col_names = FALSE)

population <- data.frame(population) %>%
  rename(year = ...1,
         population = ...3) %>%
  select(year,population) %>%
  filter(year >= 2000)

#####################################################
######### COMBINE DATA INTO ONE FRAME ###############

data <- data.frame(year,product,
                   litres.ontrade,litres.offtrade,
                   units.pp.ontrade,units.pp.offtrade,
                   price.ontrade,price.offtrade)

data <- data %>%
  filter(year >= 2000) %>%
  filter(product != "Other")



data_mesas_englandwales <- merge(data,population) %>%
  mutate(consumption.ontrade = (price.ontrade*units.pp.ontrade*population)/1000000 ) %>%
  mutate(consumption.offtrade = (price.offtrade*units.pp.offtrade*population)/1000000 )


usethis::use_data(data_mesas_englandwales,overwrite=TRUE)