prep ocsi data - england.r

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

# Read in OSCI data
ocsi = read_excel("data/OCSI/data-download-20200401.xlsx",
                   col_names = FALSE)

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

# Change cell values to get a row of consistent variable names
ocsi[3,1] <- ocsi[7,1]
ocsi[3,2] <- ocsi[7,2]
ocsi[3,3] <- ocsi[7,3]
ocsi[3,4] <- ocsi[7,4]
ocsi[3,5] <- ocsi[7,5]

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

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

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

# the last two columns should say 'Rate' and 'Count' but are blank
ocsi[2, c( ncol(ocsi) - 1, ncol(ocsi) )] = list("Count", "Rate")

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

# Rename columns
names(ocsi) <- column_names

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

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

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

# Convert all columns to numeric except Code
ocsi_code <- ocsi %>% 
  select(`MSOA Code`)

ocsi_numeric <- ocsi %>% 
  select(-`MSOA Code`) %>% 
  mutate_if(is.character, as.numeric)

ocsi <- bind_cols(ocsi_code,
                   ocsi_numeric)

rm(ocsi_code, ocsi_numeric, column_names)