3.Data Quality Checks.Rmd

---
title: "ScotPHO Profile indicator Data Quality Checks"
subtitle: "Report looks for errors in files intended for ScotPHO profiles tool"
output: html_document
runtime: shiny

---

```{r setup, include=FALSE}
#global options for markdown report
knitr::opts_chunk$set(echo = TRUE)
```

```{r, include=FALSE}
library (reactable)

#Read in geography code names dictionary
code_dictionary <- readRDS(paste0(data_folder,"Lookups/Geography/codedictionary.rds")) 

#Read in indicator file being checked
data_indicator <- readRDS(paste0(data_folder, "Data to be checked/", filename, "_shiny.rds"))
data_indicator$year <- as.numeric(data_indicator$year)

#check if all expected columns for shiny tool are present & count number of columns present
columns_expected <-all(c("code","ind_id","year","numerator","rate","lowci","upci","def_period","trend_axis") %in% colnames(data_indicator))
column_count <- ncol(data_indicator)

#Add geography code names - useful when exploring outfiles in shiny
data_indicator <- left_join(x=data_indicator, y=code_dictionary, by="code") %>% 
mutate(code_name=paste0(code,": ",as.character(areaname)),#add field with geography code & name to ease identification
       geo_type=case_when(
          substr(data_indicator$code,1,3) == "S00" ~ "Scotland (S00)",
                substr(data_indicator$code,1,3) == "S08" ~ "NHS Board (S08)",
                substr(data_indicator$code,1,3) == "S12" ~ "Council (S12)",
                substr(data_indicator$code,1,3) == "S37" ~ "HSCP (S37)",
                substr(data_indicator$code,1,3) == "S11" ~ "ADP (S11)",
                substr(data_indicator$code,1,3) == "S99" ~ "HSCP Locality (S99)",
                substr(data_indicator$code,1,3) == "S02" ~ "IZ (S02)",TRUE ~ "other")) 

#Logical test of which geographies are present in data set
# values will return TRUE/FALSE according to whether each geography type is present
board <- any(substr(data_indicator$code,1,3) == "S08") 
ca <- any(substr(data_indicator$code,1,3) == "S12")
hscp <- any(substr(data_indicator$code,1,3) == "S37")
adp <- any(substr(data_indicator$code,1,3) == "S11")
locality <- any(substr(data_indicator$code,1,3) == "S99")
iz <- any(substr(data_indicator$code,1,3) == "S02")

#Recode missing CI to zeros rather than NA to allow report to run
data_indicator$lowci[is.na(data_indicator$lowci)] <- 0 # Converting NA's to 0s
data_indicator$upci[is.na(data_indicator$upci)] <- 0 # Converting NA's to 0s

#Read in old indicator data file from network /Shiny data folder - if one exists (new indicators won't have a historic file)
old_indicator_filepath <- paste0(data_folder, "Shiny Data/", filename, "_shiny.rds") #create value which is file path to old file

# statement that checks if there an old indicator data file of the same name exists in Shiny data folder 
if(file.exists(old_indicator_filepath)) {
  skip_historic_file_checks <-"n" # if historic file found then instruct this report not to skip over thse checks
  old_data_indicator <- readRDS(paste0(data_folder, "Shiny Data/", filename, "_shiny.rds"))
  old_data_indicator$year <- as.numeric(old_data_indicator$year)#read in file if one exists
} else {
  skip_historic_file_checks <-"y" #if no historic file found then instruct this report to skip over some of the checks
  old_indicator_filepath <-"No historic file available for comparison. Check filename spelling or 'shiny data' network folder if you were expecting a comparison" #if no historic file exists set value to no file available
}

```

Indicator:  **`r filename`** <br>
Date:  **`r format(Sys.time(), '%d %B, %Y %H:%M')`** <br>

------------------------------------------------------------------------------------

**New** indicator file:  /`r (paste0(data_folder, "Data to be checked/", filename, "_shiny.rds"))`<br>
**Old** indicator file (for comparisons):  /`r (paste0(old_indicator_filepath)) `<br>

**Geographies present in indicator output file** <br>
NHS board: `r board` <br>
Council area: `r ca` <br>
Health and Social Care Partnership: `r hscp` <br>
Alcohol and Drugs Partnership: `r adp`<br>
HSC locality: `r locality`<br>
Intermediate zone: `r iz` <br>

**Are expected columns present output file:**

```{r, out.width = "400px", echo=FALSE}

# paste warning if one or more column is missing from shiny data file
if (columns_expected == FALSE) {
    paste0("Shiny input file is missing required colummns")
} else {
    paste0("All expected columns present")
}
  
# paste warning if the column count isnt as expected (either too many or too few or missing expected)
if (column_count != 9 | columns_expected == FALSE) {
    paste0("Check columns in shiny input file - wrong column count or missing column")
} else {
    paste0("No extra columns")
}
  
```

------------------------------------------------------------------------------------


###**Data check 1:**
<br>
####Which geographical levels and years are present & how many unique geographies appear?

```{r include=FALSE}
#Aggegrate file to detect which geographies and years are present.
geo_checks <- data_indicator %>%
  mutate(geo_type=substr(code,1,3)) %>%
  group_by(geo_type, trend_axis) %>%
  summarise(count=n()) %>%
  spread(geo_type, count)

# ft_geo_check - summary table of years and geogrpahy types
# Conditional formatting on counts of geography types (except HSCP locality)
# Not sure how many localities there are supposed to be?
# Not sure how to handle indicators with ADP or only Council area - need to think about this...

ft_geo_check <- flextable(geo_checks) %>%
  color(~ S08!=14, ~S08, color = "red") # Applies conditional formatting - should be 14 NHS board

if(ca == TRUE) {ft_geo_check <- ft_geo_check %>%  color(~ S12!=32, ~S12, color = "red")}    #should be 32 councils
if(adp == TRUE) {ft_geo_check <- ft_geo_check %>%  color(~ S11!=30, ~S11, color = "red")}   #should be 30 ADP
if(hscp == TRUE) {ft_geo_check <- ft_geo_check %>%  color(~ S37!=31, ~S37, color = "red")}  #should be 31 hscp
if(iz == TRUE) {ft_geo_check <- ft_geo_check %>%  color(~ S02!=1279, ~S02, color = "red")}  #should be 1279 2011 IZ

```  

Are the expected geographies appearing for all expected years?<br>
Conditional formatting should highlight where geographies present are not equal to expected (e.g. 14 NHS boards, 32 councils or 31 HSCP)<br>
If geographies are missing consider:<br>
Has any suppression been applied to the dataset?<br>
Are there legitimate reasons why be no data for some areas?

```{r, out.width = "400px", echo=FALSE}

#print flextable
ft_geo_check %>%
  autofit()

```
------------------------------------------------------------------------------------  

###**Data check 2:**
<br>
####How many new rows added in latest update?

**Note:** some indicators generate data for geographies that are not available in live tool
(e.g. alcohol mortality we generate IZ data but this is not routinely published to avoid secondary disclosure)

```{r, include=FALSE}

if(skip_historic_file_checks=="n"){
new_row <- nrow(data_indicator)
old_row <- nrow(old_data_indicator)
} else {
new_row <- nrow(data_indicator)
old_row <- nrow(data_indicator)}   

```

1.Rows in new shiny file: **`r new_row`** <br>
2.Rows added compared to last file: **`r paste0(c(new_row-old_row))`**

------------------------------------------------------------------------------------  


###**Data check 3:(optional)**
<br>
####How does new data compare to figures prepared last time (ie those in Shiny data folder)?

Table below will show geographies where the values within latest indicator file differ by more than 5% from previous figures.
If present in dataset rows will be grouped by geography type - these can be expanded to explore individual areas where differences exist.
If the indicator includes small geographies (e.g. IZ)/rare event then possible that a difference of more than 5% is not an issue but may be worth a closer inspection.
Use your judgement to decide if any differences are acceptable.

There is also a table at the end of this checking report where you can see exactly comparisons of historic and latest indicator data for all geographies.

```{r, include=FALSE}

if (skip_historic_file_checks=="n"){

  #Find the last year in historic data file which can be used as filter in latest indicator data
  old_data_max_year <-max(data_indicator$year)
  
  # join historic and latest indicator data files
  matched_indicator_data <- left_join(x = data_indicator, y = old_data_indicator, by=c("code", "year")) %>%
  filter (year <= old_data_max_year ) %>% # ensure comparisons run on data for years that should be in both dataset
  mutate_if(is.numeric, round, 2) %>%
  #calculate difference between old and new values
  mutate(rate_match = round(rate.x-rate.y,2),
        numerator_match = ifelse((numerator.x==""|numerator.y==""),0, round(numerator.x-numerator.y,2)),
        lowci_match =ifelse((lowci.x ==""|lowci.y ==""),0,round(lowci.x-lowci.y,2)),
        upci_match =ifelse((upci.x ==""|upci.y ==""),0,round(upci.x-upci.y,2)),
        difference_flag = case_when(rate_match !=0 | numerator_match !=0 |lowci_match !=0 |upci_match!=0 ~ TRUE, TRUE~ FALSE),
        year=as.factor(year))
   
  # calculate percentage differences between old and new figures
  matched_summary <-  matched_indicator_data %>%
  filter(difference_flag==TRUE) %>%
  mutate(p_diff_n = round(numerator_match/numerator.x*100,1), #percent difference
         p_diff_rate = round(rate_match/rate.x*100,1),
         p_diff_lci = round(lowci_match/lowci.x*100,1),
         p_diff_uci = round(upci_match/upci.x*100,1)) %>%
  filter_at(vars(starts_with('p_diff')),  any_vars(abs(.) >5)) %>% #filters any percentage differences greater than +/- 5 %
  group_by(geo_type) %>%
  mutate(diff_larger_than_5_percent=n_distinct(code_name)) %>%
  arrange(geo_type,code_name, year) %>%
  ungroup() %>%
  select(geo_type,code_name,year,p_diff_n,p_diff_rate,p_diff_lci,p_diff_uci,rate.x,rate.y,numerator.x,numerator.y,lowci.x,lowci.y,upci.x,upci.y,diff_larger_than_5_percent)

} else { # if no historic file then skip this step

  # do nothing if historic checks are to be skipped 
}

```
```{r, out.width = "400px", echo=FALSE}

if (skip_historic_file_checks=="n"){
  
# insert reactable with expandable rows grouped by geography type.
data <- unique(matched_summary[, c("geo_type","diff_larger_than_5_percent")])

reactable(data, details = function(index) {
  differences_summary_data <- matched_summary[matched_summary$geo_type == data$geo_type[index], ]
  htmltools::div(style = "padding: 1rem",
                 reactable(differences_summary_data, outlined = TRUE)
  )
})

} else{ #if no historic file
  paste0("No historic data file found in network folder")
}
  
```

------------------------------------------------------------------------------------

###**Data check 4:**
<br>
####Rates & Confidence intervals
Do any rates fall outwith the confidence limits - they shouldn't!?

```{r, echo=FALSE}
# Check no rates/percentages sit outside of CI range  
 confidence_check <- data_indicator %>% 
  mutate(ci_error = ifelse(rate<lowci | rate>upci, TRUE, FALSE)) %>%
  summarise(ci_error_total = sum(ci_error, na.rm = TRUE))

```

```{r, echo=FALSE}
ifelse(confidence_check$ci_error_total == 0, "No rates outwith confidence limits :-)",
"Uh-oh looks like there are some rates outwith confidence limits :(")

```

------------------------------------------------------------------------------------

###**Data check 5:**
<br>
####Scotland & NHS boards
Does Scotland rate seem like a sensible figure, how wide are the confidence intervals? <br>
Does Scotland rate look about average of all NHS boards?<br>
Are there any outliers or strange looking confidence intervals (expect island boards to have wide CI)?

```{r, echo=FALSE}
##Produce ggplot showing scotland and NHS board values (+CI) used as a sense check of figures and to see values are in middle of CI
#Filter Scotland value for use in chart
scot_value <-data_indicator %>% 
  filter((code=="S00000001") & year==max(year)) %>% 
  select(rate)

#Selecting Health boards and Scotland for latest year in dataset
scot_check <- ggplot(data = data_indicator %>% 
                       filter((substr(code, 1, 3)=="S08" | code=="S00000001") 
                                        & year== max(year)), aes(areaname, rate)) +
  geom_point(stat = "identity") +
  geom_hline(yintercept= scot_value$rate[1], linetype="dashed", color = "red")+
  geom_errorbar(aes(ymax=upci, ymin=lowci), width=0.5)+
  theme(axis.text.x = element_text(angle = 90, hjust = 1)) +
  labs(x = "", y =  "Measure")

```

```{r, echo=FALSE}
scot_check
```

------------------------------------------------------------------------------------

###**Data check 6:**
<br>
#### Are geographies that ought to be the same matching?
Most council areas are the same as HSC Partnerships.
If these geographies are available, use the chart to look at whether the rates for council are and HSCP match. Also check if HSC locality rates seem consistent with partnership data (i.e partnership rate should be roughly in the middle)

```{r, include=FALSE}

#Selecting Council area, HSCP and its localities for latest year in dataset
# filter based on areanames as these change less frequently than codes (hscp locality codes derived by scotpho so can change anytime boundaries updated)
#city of edinburgh council = S1200036
#Edinburgh partnership = S37000012
#Edinburgh localities = S99000043,44,45,46

#Filter City of Edinburgh value central line in chart below
ed_value <- data_indicator %>% 
  filter((areaname=="City of Edinburgh") & year==max(year)) %>% 
  select(rate)

#Generate chart showing rates for all edinburgh geographies for latest year
edinburgh_chart <- ggplot(data = data_indicator %>% 
         filter((areaname =="City of Edinburgh" | areaname=="Edinburgh"
                |areaname=="Edinburgh North East"|areaname=="Edinburgh North West"|areaname=="Edinburgh South East"|areaname=="Edinburgh South West") & year== max(year)), aes(areaname, rate)) +
  geom_point(stat = "identity") +
  geom_hline(yintercept= ed_value$rate, linetype="dashed", color = "red")+
  geom_errorbar(aes(ymax=upci, ymin=lowci), width=0.5) +
  theme(axis.text.x = element_text(angle = 90, hjust = 1))+
  labs(x = "", y =  "Measure")

# Additional check of numerators (where present) to test if all numerators agree

if(all(data_indicator$numerator != "", na.rm = TRUE)){    #if numerator column is not blank
edinburgh_data <- data_indicator %>%        # create data frame filtered for edinburgh geographies
  filter(areaname =="City of Edinburgh" | areaname=="Edinburgh" | areaname=="Edinburgh North East"| 
           areaname=="Edinburgh North West"| areaname=="Edinburgh South East" | areaname=="Edinburgh South West") %>%
  mutate(geotype=substr(code,1,3)) %>%
  group_by(year, geotype) %>%
  summarise(numerator=sum(numerator)) %>%
  spread(geotype, numerator) %>%
  mutate(ca_partnership=ifelse((hscp==TRUE && ca==TRUE),(round(S12-S37,2)),0),  #create fields that test for differences - use rounded figures
         partnership_localities=ifelse(locality==TRUE,(round(S99-S37,2)),0), #if dataset includes localities check sum
         localities_ca=ifelse(locality==TRUE,(round(S99-S12,2)),0),
         check_tot=round(sum(ca_partnership,partnership_localities,localities_ca),2))
}

if(all(data_indicator$numerator == "", na.rm = TRUE)){
  ed_flex <- "No numerator to cross reference totals"
} else if (sum(edinburgh_data$check_tot, na.rm = T) !=0){
  ed_flex <- flextable(edinburgh_data) %>%
    fontsize(part = "header", size = 16) %>%
    color(~ check_tot!=0, ~check_tot, color = "red") %>%
    autofit()
} else if (sum(edinburgh_data$check_tot, na.rm = T) ==0){
  ed_flex <- "Numerators for Edinburgh council, HSCP and localities (if present) all agree :-)"
}


```

```{r, echo=FALSE}
if(ca==TRUE & hscp==TRUE){ #only include chart when either ca or hscp data available
edinburgh_chart}

if(ca==FALSE | hscp==FALSE){ #only include chart when either ca or hscp data available
"No ca or hscp rates to check"
}

```

If dataset contains numerators check if for agreement between geography types. <br>

```{r, echo=FALSE}
if(ca==TRUE & hscp==TRUE){ #only include if ca and hscp data available
ed_flex}

if(ca==FALSE | hscp==FALSE){ #only include chart when either ca or hscp data available
"No ca or hscp numerators to check"
}

```

------------------------------------------------------------------------------------ 

###**Data check 7:**
<br>
####Are numerators likely to generate a robust indicator?

The table below shows area types where more than 40% of the rows contain numerators <5.
Use this table to assess if a particular geography type is likely to be robust and useful.
There is no definite point at which an indicator is no longer robust but if a large proportion of your data is small numbers then consider whether the data is worth publishing. <br>

```{r include=FALSE}
ifelse(all(data_indicator$numerator == "" | is.na(data_indicator$numerator)),
       "No numerator available",        #if no numerator data available print statement
small_count_data <- data_indicator  %>%# if numerator is available generate flextable data
  mutate(geo_type=substr(code,1,3)) %>%
  group_by(geo_type, year) %>%
  summarise(count=n(),
            u5=sum(numerator<5)) %>%
  mutate(percent_U5=u5/count*100,
         year =as.factor(year)) %>%
   filter(percent_U5>40) %>%
   ungroup())

```

```{r echo=FALSE}
#if numerator column is available (i.e. not all blank) produce flextable summarising counts of small numbers
if(all(data_indicator$numerator != "" & !is.na(data_indicator$numerator))) {
  flextable(small_count_data)
}

```

```{r, echo=FALSE}
##if numerator column is not available (i.e. all blank) skip check and notify user
if(all(data_indicator$numerator == "" | is.na(data_indicator$numerator))){
  "No numerators available in dataset"
}

```

------------------------------------------------------------------------------------  

###**Data check 8:**
<br>
Shiny app to view data trends for one or more geography - add geographies to filter box to view trends

```{r, echo=FALSE}

shinyApp(

  ui = fluidPage(
     selectInput("code_selected", "Area:", choices = unique(data_indicator$code_name),multiple = TRUE, selected="S00000001: Scotland"),
     radioButtons("var_plot_trend", label =NULL,
                                       choices = c("Rate/Percentage"="rate",
                                                   "Numerator"="numerator")),
     radioButtons("ci_trend", label = "95% confidence intervals", choices = c("on","off"),selected = "off"),
    plotlyOutput("trend_plot")
    ),
  
  
  server = function(input, output) {
    
      auto_invalidate <- reactiveTimer(10000) 
  observe({ 
    auto_invalidate() 
    cat(".") 
  })

      # Creating plot for ui side
  output$trend_plot <- renderPlotly({

    trend_data <- data_indicator %>% subset(code_name %in% input$code_selected)

    trend_palette <- c("#000000", "#a6cee3", "#1f78b4", "#b2df8a", "#33a02c", "#fb9a99",
                         "#e31a1c", "#fdbf6f", "#ff7f00", "#cab2d6", "#6a3d9a", "#b15928")
    
    #set colours based on areaname - this required to keep lines and CI consistent colours
    trend_scale <- c(setNames(trend_palette, unique(trend_data$areaname)))
    trend_col <- trend_scale
    
        #Text for tooltip
    tooltip_trend <- c(paste0(trend_data$code,"-",trend_data$areaname, "<br>",
                              "Rate: ", round(trend_data$rate,2), "<br>",
                              "Numerator: ", trend_data$numerator))  ##can't round numerator without issues with app when no numerator

    trend_plot <- plot_ly(data=trend_data, x=~trend_axis,  y = ~get(input$var_plot_trend),
                          color = ~areaname,colors = trend_col,text=tooltip_trend, height = 600) %>%
        add_trace(type = 'scatter', mode = 'lines+markers', symbol = ~trend_data$code) %>%
        #Layout
        layout(annotations = list(), #It needs this because of a buggy behaviour of Plotly
               yaxis = list(title = input$var_plot_trend, rangemode="tozero", fixedrange=TRUE,
                            size = 4, titlefont =list(size=14), tickfont =list(size=14)),
               xaxis = list(title = FALSE, tickfont =list(size=14), tickangle = 270, fixedrange=TRUE),
               showlegend = TRUE,
               legend = list(orientation = 'h', x = 0, y = 1.18)) %>%  #legend on top
        config(displayModeBar = FALSE, displaylogo = F) # taking out plotly logo button
    
        #Adding confidence intervals depending on user input
       if (input$ci_trend == "on" & input$var_plot_trend != "Numerator") {
         trend_plot %>% 
           add_ribbons(data = trend_data, ymin = ~lowci, ymax = ~upci, showlegend = F,
                   opacity = 0.2) 
      } else if (input$ci_trend == "off") {
        trend_plot
      }})
         
    },# server close
  options = list(height = 1000)
)

```

------------------------------------------------------------------------------------  

###**Data check 9:**
<br>
Shiny app looking at differences between historic and latest indicator data output files - contains all geographies

```{r, echo=FALSE}

# shinyApp(
# 
#   ui = fluidPage(
#       selectizeInput("geo_type_selected", "Area type:", choices = unique(data_indicator$geo_type),multiple = TRUE, selected="Scotland (S00)"),
#       reactableOutput("comparisons_table"), 
#     ),
#   
#   
#   server = function(input, output) {
# 
#   output$comparisons_table <- renderReactable({
# 
#     matched_indicator_data <- left_join(x = data_indicator, y = old_data_indicator, by=c("code", "year")) %>%
#         filter(geo_type %in% input$geo_type_selected) %>% #
#         mutate_if(is.numeric, round,2) %>%
#         mutate(rate_match = round((rate.x-rate.y)/rate.x,2)) %>% #all indicators have rate -
#       # not all indicators have numerator,lci,uci so need to allow for null comparison
#         mutate(numerator_match = ifelse((numerator.x ==""|numerator.y ==""),0,round((numerator.x-numerator.y)/numerator.x,2)),
#             lowci_match =ifelse((lowci.x ==""|lowci.y ==""),0,round((lowci.x-lowci.y)/lowci.x,2)),
#             upci_match =ifelse((upci.x ==""|upci.y ==""),0,round((upci.x-upci.y)/upci.x,2)),
#             year=as.factor(year)) %>%
#   select(geo_type,code_name,year,numerator_match,rate_match,lowci_match,upci_match, numerator.x,numerator.y,rate.x,rate.y,lowci.x,lowci.y,upci.x,upci.y)
#     
#   ds_color=NULL   # set default column color to null - this gets reset if differences detected between old and new indicator data 
#   
#   matched_indicator_data %>%
#   reactable(filterable = TRUE,
#             columns = list(
#             code_name= colDef(),
#             year = colDef(width = 60),
#             numerator.x =colDef(name="N new",width = 65),
#             numerator.y = colDef(name="N old",width = 65),        
#             numerator_match = colDef(name="N",width = 30,style = function(value) {
#                             if (value != 0) {ds_color <- "lightCoral"} 
#               list(background = ds_color)}), # 
#             rate.x =colDef(name="R new",width = 60),
#             rate.y = colDef(name="R old",width = 60),
#             rate_match = colDef(name="R",width = 30,style = function(value) {
#                             if (value != 0) {ds_color <- "lightCoral"}
#               list(background = ds_color)}),
#             lowci.x =colDef(width = 60),
#             lowci.y = colDef(width = 60),
#             lowci_match = colDef(name="LC",width = 30,style = function(value) {
#                             if (value != 0) {ds_color <- "lightCoral"}
#               list(background = ds_color)}),
#             upci.x =colDef(width = 60),
#             upci.y = colDef(width = 60),
#             upci_match = colDef(name="UC",width = 30,style = function(value) {
#                             if (value != 0) {ds_color <- "lightCoral"} 
#               list(background = ds_color)})),
#             defaultPageSize = 20)})
# 
#     },# server close
#   options = list(height = 1000)
# )

```


###End of checks

<br>
**Don't forget to update technical document once checks are complete**
<br>
<br>