Covid_Wales_Shashidhar
Shashidhar
02/11/2021
#importing all the packages required for data processiong
library("jsonlite")
library("tidyverse")## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.1 ──## ✓ ggplot2 3.3.5 ✓ purrr 0.3.4
## ✓ tibble 3.1.3 ✓ dplyr 1.0.7
## ✓ tidyr 1.1.3 ✓ stringr 1.4.0
## ✓ readr 2.0.0 ✓ forcats 0.5.1## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## x dplyr::filter() masks stats::filter()
## x purrr::flatten() masks jsonlite::flatten()
## x dplyr::lag() masks stats::lag()library("RPostgreSQL")## Loading required package: DBIlibrary("devtools")## Loading required package: usethislibrary("remotes")##
## Attaching package: 'remotes'## The following objects are masked from 'package:devtools':
##
## dev_package_deps, install_bioc, install_bitbucket, install_cran,
## install_deps, install_dev, install_git, install_github,
## install_gitlab, install_local, install_svn, install_url,
## install_version, update_packages## The following object is masked from 'package:usethis':
##
## git_credentialslibrary("DBI")
#importing data from api link in to R studio
json_file <- "http://open.statswales.gov.wales/en-gb/dataset/hlth0091"
json_data <- jsonlite::fromJSON(json_file)
#converting list data into table form for further processing
df<-json_data[2]
statewales<-df[[1]]
head(statewales,5)## Data Date_Code Date_ItemName_ENG Date_SortOrder Date_ItemNotes_ENG
## 1 28.00000000 20200401 01 April 2020 10
## 2 28.00000000 20200401 01 April 2020 10
## 3 98.00000000 20200401 01 April 2020 10
## 4 98.00000000 20200401 01 April 2020 10
## 5 768.00000000 20200401 01 April 2020 10
## LocalHealthBoard_Code LocalHealthBoard_ItemName_ENG
## 1 7A1 Betsi Cadwaladr University Local Health Board
## 2 7A1 Betsi Cadwaladr University Local Health Board
## 3 7A1 Betsi Cadwaladr University Local Health Board
## 4 7A1 Betsi Cadwaladr University Local Health Board
## 5 7A1 Betsi Cadwaladr University Local Health Board
## LocalHealthBoard_SortOrder LocalHealthBoard_Hierarchy
## 1 2 W92000004
## 2 2 W92000004
## 3 2 W92000004
## 4 2 W92000004
## 5 2 W92000004
## LocalHealthBoard_ItemNotes_ENG LocalHealthBoard_AltCode1 Hospitaltype_Code
## 1 W11000023 NHS
## 2 W11000023 TotAcute
## 3 W11000023 NHS
## 4 W11000023 TotAcute
## 5 W11000023 NHS
## Hospitaltype_ItemName_ENG Hospitaltype_SortOrder Hospitaltype_Hierarchy
## 1 All NHS hospitals 1 AllHosp
## 2 All Acute hospitals 2 NHS
## 3 All NHS hospitals 1 AllHosp
## 4 All Acute hospitals 2 NHS
## 5 All NHS hospitals 1 AllHosp
## Hospitaltype_ItemNotes_ENG
## 1 Includes data from acute hospitals only until 19 April 2020. Field hospitals data were added from 20 April 2020, community hospitals data were added from 23 April 2020, and mental health hospitals data were added from 10 July 2020.
## 2 Prior to 10 July 2020, data may include small numbers of mental health unit beds.
## 3 Includes data from acute hospitals only until 19 April 2020. Field hospitals data were added from 20 April 2020, community hospitals data were added from 23 April 2020, and mental health hospitals data were added from 10 July 2020.
## 4 Prior to 10 July 2020, data may include small numbers of mental health unit beds.
## 5 Includes data from acute hospitals only until 19 April 2020. Field hospitals data were added from 20 April 2020, community hospitals data were added from 23 April 2020, and mental health hospitals data were added from 10 July 2020.
## Indicator_Code Indicator_ItemName_ENG
## 1 CO_Admissions_C19 COVID-19 admissions (suspected and confirmed)
## 2 CO_Admissions_C19 COVID-19 admissions (suspected and confirmed)
## 3 CO_Admissions_NonC19 Non-COVID-19 admissions
## 4 CO_Admissions_NonC19 Non-COVID-19 admissions
## 5 CO_Hosps_All All hospitalisations
## Indicator_SortOrder Indicator_Hierarchy
## 1 102 Misc_Admissions_All
## 2 102 Misc_Admissions_All
## 3 103 Misc_Admissions_All
## 4 103 Misc_Admissions_All
## 5 104
## Indicator_ItemNotes_ENG RowKey
## 1 Patients admitted as suspected or confirmed with COVID-19 0000000000000000
## 2 Patients admitted as suspected or confirmed with COVID-19 0000000000000001
## 3 0000000000000002
## 4 0000000000000003
## 5 0000000000000004
## PartitionKey
## 1
## 2
## 3
## 4
## 5#reorgnising data so that meaningfull analysis can be done, filtering with indicator co_hosps_c19 as it
#contains total number of covid patients which includes both suspect and confirmed
coviddata<-statewales%>%group_by(LocalHealthBoard_Code,Date_Code,Indicator_Code)%>%
summarise(numofpatients=sum(as.numeric(Data)),LocalHealthBoard_Code,Indicator_Code,Date_ItemName_ENG)%>%
filter(row_number()==1&Indicator_Code=='CO_Hosps_C19')%>%ungroup()## `summarise()` has grouped output by 'LocalHealthBoard_Code', 'Date_Code', 'Indicator_Code'. You can override using the `.groups` argument.head(coviddata,5)## # A tibble: 5 × 5
## LocalHealthBoard_Code Date_Code Indicator_Code numofpatients Date_ItemName_ENG
## <chr> <chr> <chr> <dbl> <chr>
## 1 7A1 20200401 CO_Hosps_C19 214 01 April 2020
## 2 7A1 20200402 CO_Hosps_C19 170 02 April 2020
## 3 7A1 20200403 CO_Hosps_C19 198 03 April 2020
## 4 7A1 20200404 CO_Hosps_C19 206 04 April 2020
## 5 7A1 20200405 CO_Hosps_C19 240 05 April 2020created database named covidwales in postgressql database
code for connecting R studio with postgresql database
con <- dbConnect(RPostgres::Postgres(), host = “localhost”, port = “5433”,dbname=“covidwales”, user = “postgres”, password = .rs.askForPassword(prompt = “please provide passowrd for postgresql”))
importing data from R studio into postgresql
dbWriteTable(con, “statewales”,statewales,overwrite = T )
Time Series Plots
