1.1 Methods

We have used two data science techniques - web-scraping and text analysis (natural langauge processing or NLP) - to try and create a repository of DPH annual reports which we can then analyse.

There are 4 steps

1. Discovery - We need to be able to find DPH reports on the web.
   
2. Web-sraping - if we can find reports or pages holding reports we can extract PDFs for further processing.
   
3. Cleaning and processing - creating a ‘tidy’ data frame of reports, cleaning texts, adding metadata e.g. report year, local authority name
   
4. Analysis - applying text mining and natural language processing tools to identify key themes, search for Health Intelligence products and so on.

To make this as automated as possible, all elements of the analytical pipeline have been conducted in R, and we have written functions and scripts to facilitate the process.

1.2 Workflow

The proposed workflow is set out below:

dsph <- myScrapers::get_dsph_england()

dsph %>% write_csv("data/dsph.csv")

head(dsph, 20) %>%
  knitr::kable("html", caption = "DsPH in England") %>%
  kableExtra::kable_styling(full_width = T )

2.1 Try one search and retrieval

york <- u[152]

safe_google <- safely(googlesearchR)

## pull 5 results
york_test <-purrr::map(york, ~(safe_google(.x, n= 3)))

york_test1 <- york_test %>% map(., "result") 

york_test2 <- york_test1 %>% flatten() %>% map(., 1) %>%.[1:2]

york_test1

## [[1]]
## [[1]][[1]]
## [1] "https://www.york.gov.uk/info/20125/health_and_wellbeing/957/director_of_public_healths_annual_report"
## 
## [[1]][[2]]
## [1] "https://www.local.gov.uk/directors-public-health-annual-reports"
## 
## [[1]][[3]]
## [1] "http://www.nypartnerships.org.uk/dphreport2017"

The results are the same as a direct Google search

google screen shot

We can see that the search hasn’t given us a link to a downloadable report.

In some cases, the report is further linked to from another page. We can try and read the links on this page.

get_links <- function(url){
  
  page <- read_html(url) %>%
    html_nodes("a")  %>%
    html_attr("href") 
}

york1 <- unlist(york_test1[[1]][1], as.character)

york1

## [1] "https://www.york.gov.uk/info/20125/health_and_wellbeing/957/director_of_public_healths_annual_report"

safe_get_links <- safely(get_links)

york_links <- safe_get_links(york1)$result

york_links[1:20]

##  [1] "https://www.york.gov.uk/info/20125/health_and_wellbeing/957/director_of_public_healths_annual_report#content"                                  
##  [2] "https://www.york.gov.uk/info/20125/health_and_wellbeing/957/director_of_public_healths_annual_report#main-navigation"                          
##  [3] "/cookies"                                                                                                                                      
##  [4] "/cookies#managingcookies"                                                                                                                      
##  [5] "https://www.york.gov.uk"                                                                                                                       
##  [6] "https://www.york.gov.uk/info/20125/health_and_wellbeing/957/director_of_public_healths_annual_report#sidr-search"                              
##  [7] "https://www.york.gov.uk/info/20125/health_and_wellbeing/957/director_of_public_healths_annual_report#sidr-menu"                                
##  [8] "https://www.york.gov.uk/info/20003/your_council"                                                                                               
##  [9] "https://www.york.gov.uk/myaccount"                                                                                                             
## [10] "https://www.york.gov.uk/homepage/47/news_centre"                                                                                               
## [11] "https://www.york.gov.uk/homepage/14/jobs_training_and_volunteering"                                                                            
## [12] "https://www.york.gov.uk/homepage/16/contact_us"                                                                                                
## [13] "https://www.york.gov.uk/accessibility/settings"                                                                                                
## [14] "https://www.york.gov.uk/downloads/file/14761/director_of_public_healths_annual_report_2016-17"                                                 
## [15] "https://www.york.gov.uk/info/20139/health_and_wellbeing_partnerships/973/health_and_wellbeing_board"                                           
## [16] "http://www.healthyork.org/"                                                                                                                    
## [17] "https://www.york.gov.uk/info/20145/healthcare_services/991/healthcare_services"                                                                
## [18] "https://www.york.gov.uk/homepage/44/health_and_wellbeing"                                                                                      
## [19] "https://www.york.gov.uk/page_comments/L2luZm8vMjAxMjUvaGVhbHRoX2FuZF93ZWxsYmVpbmcvOTU3L2RpcmVjdG9yX29mX3B1YmxpY19oZWFsdGhzX2FubnVhbF9yZXBvcnQ="
## [20] "https://www.york.gov.uk/forms/20125/health_and_wellbeing"

We can see that there are a few links to the DPH report. The ‘downloads’ one looks promising.

We have written a get_docs to identify PDF or docx files on web pages - we can see if there is a downloadable document at https://www.york.gov.uk/downloads/file/14761/director_of_public_healths_annual_report_2016-17.

get_docs <- function(url){
  
  page <- read_html(url) %>%
    html_nodes("a")  %>%
    html_attr("href") %>%
    .[grepl("pdf$|docx$", .)]
  
}


safe_get_docs <- safely(get_docs)

york1 <- unlist(york_test1[[1]][1], as.character)

york1

## [1] "https://www.york.gov.uk/info/20125/health_and_wellbeing/957/director_of_public_healths_annual_report"

pdf_test <- map(york_links[14], ~(safe_get_docs(.x)) ) %>% map(., "result")

pdf_test

## [[1]]
## [1] "http://www.york.gov.uk/download/downloads/id/14761/director_of_public_healths_annual_report_2016-17.pdf"

This reveals a link to the 2016-17 report. The next step is to download the report.

4.1 Reading into R

We can now read the file with readtext and analyse it further.

pdf_files <- list.files(pattern = ".pdf")

dph_report <- readtext(pdf_files[1])

dph_report

## readtext object consisting of 1 document and 0 docvars.
## # data.frame [1 × 2]
##   doc_id                                               text               
##   <chr>                                                <chr>              
## 1 director_of_public_healths_annual_report_2016-17.pdf "\"Director o\"..."

4.2 Text analysis

We can rapidly analyse the report

dph_report_corpus <- corpus(dph_report)

summary(dph_report_corpus)

## Corpus consisting of 1 document:
## 
##                                                  Text Types Tokens
##  director_of_public_healths_annual_report_2016-17.pdf  1333   6756
##  Sentences
##        153
## 
## Source: /Users/julianflowers/Documents/R_projects/my_scrapers/dph_reports/* on x86_64 by julianflowers
## Created: Sun Sep  2 16:06:39 2018
## Notes:

This shows that the report has 153 sentences and 6765 words.

4.3 Using a dictionary to count occurrences of key words

Going back to our original hypothesis we are interested in whether we can find references to PHE products like the public health dashboard, the PHOF, Fingertips and so on. We can create a dictionary of terms and search across all documents to count ocurrences and the estimate the proprtion of documents.

For example, for the York report we can search for: phe, fingertips, profile, localhealth, public_health_dashboard*, phof, outcomes_framework

dict <- dictionary(list(
  phe = "phe*", 
  fingertips = "fingertips*", 
  profile = "profile*", 
  local_health = "local*health*",
  phd = "public_health_dashboard*", 
  phof = c("phof", "outcomes_framework")))

dtm <- dfm(dph_report_corpus, remove = stopwords("en"), remove_punct = TRUE, ngrams = 1:3)

lookup <- dfm_lookup(dtm, dictionary = dict)


lookup %>% data.frame() %>%
  knitr::kable() %>%
  kableExtra::kable_styling(full_width = T )

There are 6 mentions of Fingertips and 4 of local health

kwic(dph_report_corpus, phrase("local health"))

##                                                                   
##  [director_of_public_healths_annual_report_2016-17.pdf, 4668:4669]
##  [director_of_public_healths_annual_report_2016-17.pdf, 6449:6450]
##                                                  
##            to and the quality of | local health |
##  outcomes• Public Health England | Local Health |
##                                     
##  services and effort. Shopping      
##  Profiles. on cardiovascular disease

This shows that one of the references to local health is to the PHE tool.

4.4 Word cloud

textplot_wordcloud(dtm)

5.1 Try for all

We’ll try and download all the reports - it seems to work better in batches.

reports1 <-purrr::map(search_la[1:10], ~(safe_google(.x, n= 2 )))
reports2 <- purrr::map(search_la[11:20], ~(safe_google(.x, n= 2 )))
reports3 <- purrr::map(search_la[21:30], ~(safe_google(.x, n= 2 )))
reports4 <- purrr::map(search_la[31:40], ~(safe_google(.x, n= 2 )))
reports5 <- purrr::map(search_la[41:50], ~(safe_google(.x, n= 2 )))
reports6 <- purrr::map(search_la[51:60], ~(safe_google(.x, n= 2 )))
reports7 <- purrr::map(search_la[61:70], ~(safe_google(.x, n= 2 )))
reports8 <- purrr::map(search_la[71:80], ~(safe_google(.x, n= 2 )))
reports9 <- purrr::map(search_la[81:90], ~(safe_google(.x, n= 2 )))
reports10 <- purrr::map(search_la[91:100], ~(safe_google(.x, n= 2 )))
reports11<- purrr::map(search_la[101:110], ~(safe_google(.x, n= 2 )))
reports12<- purrr::map(search_la[111:120], ~(safe_google(.x, n= 2 )))
reports13<- purrr::map(search_la[121:130], ~(safe_google(.x, n= 2 )))
reports14 <- purrr::map(search_la[131:140], ~(safe_google(.x, n= 2 )))
reports15 <- purrr::map(search_la[141:152], ~(safe_google(.x, n= 2 )))


# reports1 <- reports1 %>% map(., "result")
# 
# names(reports1) <- las[1:50]
# names(report2) <- las[51:100]
# names(report3) <- las[101:152]


## combine all into a single list

reports_all <-c(reports1, reports2, reports3, reports4, reports5,
                reports6, reports7, reports8, reports9, reports10, reports11, reports12, reports13, reports14, reports15)

reports_all <- map(reports_all, "result")
names(reports_all) <- las

head(reports_all)

## $`Derby `
## $`Derby `[[1]]
## [1] "https://www.derby.gov.uk/media/derbycitycouncil/contentassets/documents/adultsocialcare/jointstrategicneedsassessment/derbycitycouncil-director-public-health-annual-report-1718.pdf"
## 
## $`Derby `[[2]]
## [1] "https://www.derbyshire.gov.uk/site-elements/documents/pdf/social-health/health-and-wellbeing/about-public-health/director-of-public-health-annual-report-2017.pdf"
## 
## 
## $Derbyshire
## $Derbyshire[[1]]
## [1] "https://www.derbyshire.gov.uk/site-elements/documents/pdf/social-health/health-and-wellbeing/about-public-health/director-of-public-health-annual-report-2016.pdf"
## 
## $Derbyshire[[2]]
## [1] "https://www.derbyshire.gov.uk/site-elements/documents/pdf/social-health/health-and-wellbeing/about-public-health/director-of-public-health-annual-report-2017.pdf"
## 
## $Derbyshire[[3]]
## [1] "http://www.google.co.uk/aclk?sa=l&ai=DChcSEwiGk7bjzJzdAhWqvO0KHcl7DDQYABAAGgJkZw&sig=AOD64_2DcvviI6t1glCWaSadgZelKUGFaA&ved=0ahUKEwjY-rHjzJzdAhUBBsAKHb7LA4EQ0QwIHw&adurl="
## 
## $Derbyshire[[4]]
## [1] "http://www.google.co.uk/aclk?sa=l&ai=DChcSEwiGk7bjzJzdAhWqvO0KHcl7DDQYABABGgJkZw&sig=AOD64_2uZws57Jvj7vRNb7LDCRa0mm_EFA&ved=0ahUKEwjY-rHjzJzdAhUBBsAKHb7LA4EQpigIJCgA&adurl="
## 
## $Derbyshire[[5]]
## [1] "http://www.google.co.uk/aclk?sa=l&ai=DChcSEwiGk7bjzJzdAhWqvO0KHcl7DDQYABACGgJkZw&sig=AOD64_2lpOcxxA6DdnXKggtVzTnYXQ4YJQ&ved=0ahUKEwjY-rHjzJzdAhUBBsAKHb7LA4EQpigIJSgB&adurl="
## 
## $Derbyshire[[6]]
## [1] "http://www.google.co.uk/aclk?sa=l&ai=DChcSEwiGk7bjzJzdAhWqvO0KHcl7DDQYABADGgJkZw&sig=AOD64_2cupvq-z7FXsBDxg7eosNMPk1QFQ&ved=0ahUKEwjY-rHjzJzdAhUBBsAKHb7LA4EQpigIJigC&adurl="
## 
## $Derbyshire[[7]]
## [1] "http://www.google.co.uk/aclk?sa=l&ai=DChcSEwiGk7bjzJzdAhWqvO0KHcl7DDQYABAEGgJkZw&sig=AOD64_2tjizyrdDxU0jLJ63BPM-Oc9VWZg&ved=0ahUKEwjY-rHjzJzdAhUBBsAKHb7LA4EQpigIJygD&adurl="
## 
## 
## $`Leicester `
## $`Leicester `[[1]]
## [1] "https://www.leicester.gov.uk/media/177395/director-of-public-health-annual-report-2013-2014.pdf"
## 
## $`Leicester `[[2]]
## [1] "https://www.leicester.gov.uk/media/184506/director-of-public-health-2018.pdf"
## 
## 
## $Leicestershire
## $Leicestershire[[1]]
## [1] "http://www.lsr-online.org/uploads/dph-annual-report-2017.pdf"
## 
## $Leicestershire[[2]]
## [1] "http://www.lsr-online.org/uploads/director-of-public-health-annual-report-2016.pdf"
## 
## 
## $Lincolnshire
## $Lincolnshire[[1]]
## [1] "http://www.boston.gov.uk/index.aspx"
## 
## $Lincolnshire[[2]]
## [1] "https://www.lincolnshire.gov.uk/health-and-wellbeing/information-for-professionals/health-data-policies-and-publications/annual-reports/129825.article"
## 
## 
## $Northamptonshire
## $Northamptonshire[[1]]
## [1] "https://www3.northamptonshire.gov.uk/councilservices/health/health-and-wellbeing-board/Documents/NCC%2520DPH%2520Report%2520Aug%25202017.pdf"
## 
## $Northamptonshire[[2]]
## [1] "https://www3.northamptonshire.gov.uk/councilservices/health/health-and-wellbeing-board/Pages/jsna-and-reports.aspx"

link1 <- reports_all %>%
  map(., 1)

link2 <-  reports_all %>%
  map(., 2)

links <- data.frame(cbind(link1, link2)) 

library(rlist)

name <- rlist::list.names(links)
test <- rlist::list.rbind(links)

links_table <- data.frame(cbind(la = name, test = links)) %>%
  gather(report, links, 2:3) %>%
  mutate(ispdf = str_detect(links, ".pdf$"))

pdfs_table <- filter(links_table, ispdf == TRUE) %>% arrange(la)


n_dph <- length(unique(pdfs_table$name))

We know have a set of links to at least 1 report for 0 LAs.

5.2 Try to download pdfs

We can try to read pdfs directly or download and then read them

safe_readtext <- safely(readtext)

pdfs_links <- pdfs_table %>% pull(links)

docs <- map(pdfs_links, ~(safe_readtext(.x)))
docs1 <- map(docs, "result")
docs1 <- map_df(docs1, data.frame)

5.3 Corpus

corpus_dph <- corpus(docs1)

setDT(summary(corpus_dph)) %>%
  ggplot(aes(Sentences))+
  geom_density(fill = "goldenrod") +
  govstyle::theme_gov()

dict <- dictionary(list(
  phe = "phe*", 
  fingertips = "fingertips*", 
  profile = "profile*", 
  local_health = "local*health*",
  phd = "public_health_dashboard*", 
  phof = c("phof", "outcomes_framework"), 
  mental = "mental_health", 
  cancer = "cancer",
  smoking = c("tobacco", "smok*"), 
  alcohol = c("alcohol", "drink*"), 
  sugar = "sugar*"))

dtm1 <- dfm(corpus_dph, remove = stopwords("en"), remove_punct = TRUE, ngrams = 1:3)

lookup1 <- dfm_lookup(dtm1, dictionary = dict)

topfeatures(dtm1, 30)

##        health        people        of_the        public        in_the 
##         22069          6983          6434          6369          5343 
## public_health      children         local      services    health_and 
##          5331          4581          4386          4172          4116 
##          care        report       england        mental           can 
##          3965          3588          3489          3438          3421 
##    population       support     wellbeing         years        social 
##          3281          3077          3068          2971          2895 
##          year          work          life          also         young 
##          2770          2670          2536          2509          2459 
##       healthy mental_health        to_the       smoking             1 
##          2450          2394          2361          2334          2325

lookup1 %>% data.frame() %>%
  knitr::kable() %>%
  kableExtra::kable_styling(full_width = T )