prep ocsi data - wales.r

library(tidyverse)
library(readxl)

# Read in Wales OSCI data
ocsi_wal = read_excel("data/OCSI/Wales_COVID_19_Dataset_MSOA.xlsx",
                   col_names = FALSE)

# Change cell values to get a row of consistent variable names
ocsi_wal[3,1] <- ocsi_wal[8,1]
ocsi_wal[3,2] <- ocsi_wal[8,2]
ocsi_wal[3,3] <- ocsi_wal[8,3]

# Replace NA values with empty strings so string concatenation doesn't fail in later step
ocsi_wal[6,1] <- ""                
ocsi_wal[6,2] <- ""
ocsi_wal[6,3] <- ""

# Remove metadata
ocsi_wal <- ocsi_wal %>% slice(c(-1, -2, -4, -5, -7, -8))

# Combine first two rows to create new col names
column_names <- str_c(ocsi_wal[1,], ocsi_wal[2,], sep = " ")

# Rename columns
names(ocsi_wal) <- column_names

# Remove first two rows used to create new column names and
# COVID-19 column
ocsi_wal <- ocsi_wal %>% 
  slice(-1:-2) %>% 
  select(-`COVID-19 vulnerability index Score`)

# Rename Aged columns to make variables clear
ocsi_wal <- ocsi_wal %>%
  rename_at(vars(starts_with("Aged")), ~ str_c("Proportion of Population ", .))

# Remove whitespace and convert MSOA Code to upper case
ocsi_wal <- ocsi_wal %>% 
  rename(Code = `MSOA Code `,
         `MSOA Name` = `MSOA Name `,
         `La Name` =  `LA Name `) %>% 
  mutate(Code = str_to_upper(Code))

# Convert All columns to numeric except Code
ocsiw_code <- ocsi_wal %>% 
  select(Code)

ocsiw_numeric <- ocsi_wal %>% 
  select(-Code) %>% 
  mutate_if(is.character, as.numeric)

ocsi_wal <- bind_cols(ocsiw_code,
                      ocsiw_numeric)

rm(ocsiw_code, ocsiw_numeric, column_names)