prep ocsi data - scotland.r

library(tidyverse)
library(readxl)
library(janitor)

# Read in OSCI data
ocsi_sco = read_excel("data/OCSI/Scotland_COVID_19_Dataset_IMZ.xlsx",
                      col_names = FALSE)

ocsi_sco = ocsi_sco %>% 
  janitor::remove_empty("cols")

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

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

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

# drop Excel "LINK" columns
# ocsi_sco = ocsi_sco[,-c(20, 35, 48, 71, 106)]

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

# Rename columns
names(ocsi_sco) <- column_names

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

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

# Remove whitespace and convert MSOA Code to upper case
ocsi_sco <- ocsi_sco %>% 
  rename(Code = `Intermediate Zone Code `) %>% 
  mutate(Code = str_to_upper(Code)) %>% 
  select(-`Intermediate Zone Name `)

# Convert all columns to numeric except Code
ocsi_sco_code <- ocsi_sco %>% 
  select(Code)

ocsi_sco_numeric <- ocsi_sco %>% 
  select(-Code) %>% 
  mutate_if(is.character, as.numeric)

ocsi_sco <- bind_cols(ocsi_sco_code,
                   ocsi_sco_numeric)

rm(ocsi_sco_code, ocsi_sco_numeric, column_names)