An R API to the Oxford COVID-19 Database

The primary use case for the oxcovid19 package is for facilitating a simplified, R-based workflow for 1) connecting to the OxCOVID19 PostgreSQL server; 2) accessing table/s available from the PostgreSQL server; and, 3) querying the PostgreSQL server with specific parameters to customise table/s output for intended use. This workflow is illustrated in the workflow diagram below:



The following code demonstrates how this workflow can be implemented in R:

library(oxcovid19)

## Step 1: Create a connection to OxCOVID19 PostgreSQL server
con <- connect_oxcovid19()

## Step 2: Access epidemiology table from OxCOVID19 PostgreSQL server
epi_tab <- get_table(con = con, tbl_name = "epidemiology")

## Step 3: Query the epidemiology table to show data for Great Britain
gbr_epi_tab <- dplyr::filter(.data = epi_tab, countrycode == "GBR")

Step 1 and Step 2 above are facilitated by the connect_oxcovid19 and the get_table functions provided by the oxcovid19 package. These functions are basically low-level wrappers to functions in the DBI and RPostgres packages applied specifically to work with the OxCOVID19 PostgreSQL. These functions facilitate convenient access to the server for general R users without having to learn to use the DBI and RPostgres packages.

Step 3, on the other hand, is facilitated by the dplyr package functions which were designed to work with different types of tables including those from various database server connections such as PostgreSQL.

The output of the workflow shown above is:

#> # Source:   lazy query [?? x 15]
#> # Database: postgres [covid19@covid19db.org:5432/covid19]
#>    source date       country countrycode adm_area_1 adm_area_2 adm_area_3 tested
#>    <chr>  <date>     <chr>   <chr>       <chr>      <chr>      <chr>       <int>
#>  1 GBR_P… 2020-07-22 United… GBR         England    Worcester… Bromsgrove     NA
#>  2 GBR_P… 2020-07-01 United… GBR         England    Worcester… Wyre Fore…     NA
#>  3 GBR_P… 2020-07-23 United… GBR         England    Leicester  Leicester      NA
#>  4 GBR_P… 2020-07-01 United… GBR         England    West Suss… Horsham        NA
#>  5 GBR_P… 2020-07-01 United… GBR         England    Blackburn… Blackburn…     NA
#>  6 GBR_P… 2020-06-30 United… GBR         England    Dudley     Dudley         NA
#>  7 GBR_P… 2020-07-01 United… GBR         England    Leicester… Harborough     NA
#>  8 GBR_P… 2020-07-01 United… GBR         England    Leicester… Charnwood      NA
#>  9 GBR_P… 2020-07-01 United… GBR         England    Blackpool  Blackpool      NA
#> 10 GBR_P… 2020-06-30 United… GBR         England    West Berk… West Berk…     NA
#> # … with more rows, and 7 more variables: confirmed <int>, recovered <int>,
#> #   dead <int>, hospitalised <int>, hospitalised_icu <int>, quarantined <int>,
#> #   gid <chr>

The oxcovid19 package functions are also designed to allow pipe operations using the magrittr package. The workflow above can be done using piped operations as follows:

## Load magrittr to use pipe operator %>%
library(magrittr)

connect_oxcovid19() %>%
  get_table(tbl_name = "epidemiology") %>%
  dplyr::filter(countrycode == "GBR")
#> # Source:   lazy query [?? x 15]
#> # Database: postgres [covid19@covid19db.org:5432/covid19]
#>    source date       country countrycode adm_area_1 adm_area_2 adm_area_3 tested
#>    <chr>  <date>     <chr>   <chr>       <chr>      <chr>      <chr>       <int>
#>  1 GBR_P… 2020-07-22 United… GBR         England    Worcester… Bromsgrove     NA
#>  2 GBR_P… 2020-07-01 United… GBR         England    Worcester… Wyre Fore…     NA
#>  3 GBR_P… 2020-07-23 United… GBR         England    Leicester  Leicester      NA
#>  4 GBR_P… 2020-07-01 United… GBR         England    West Suss… Horsham        NA
#>  5 GBR_P… 2020-07-01 United… GBR         England    Blackburn… Blackburn…     NA
#>  6 GBR_P… 2020-06-30 United… GBR         England    Dudley     Dudley         NA
#>  7 GBR_P… 2020-07-01 United… GBR         England    Leicester… Harborough     NA
#>  8 GBR_P… 2020-07-01 United… GBR         England    Leicester… Charnwood      NA
#>  9 GBR_P… 2020-07-01 United… GBR         England    Blackpool  Blackpool      NA
#> 10 GBR_P… 2020-06-30 United… GBR         England    West Berk… West Berk…     NA
#> # … with more rows, and 7 more variables: confirmed <int>, recovered <int>,
#> #   dead <int>, hospitalised <int>, hospitalised_icu <int>, quarantined <int>,
#> #   gid <chr>

The workflow using the piped workflow outputs the same result as the earlier workflow but with a much streamlined use of code.