In this vignette, we show how to perform the OxCOVID19 Database access examples shown here using R and the oxcovid19 package.
The task here is to list out all the unique epidemiology table sources sorted alphabetically.
library(oxcovid19)
library(magrittr)
library(dplyr)
connect_oxcovid19() %>% ## Connect to PostgreSQL server
get_table(tbl_name = "epidemiology") %>% ## Retrieve epidemiology table
arrange(source) %>% ## Sort the table by source
select(source) %>% ## Select the source column
distinct() %>% ## Get only unique sources
pull(source)
#> [1] "NGA_SO" "POL_ROG" "CHE_OPGOV" "FRA_SPFCG" "GBR_NIDH"
#> [6] "ESP_MS" "LBN_GOV" "TUR_MHOE" "USA_CTP" "BEL_SCI"
#> [11] "IRQ_GOV" "CHN_ICL" "NLD_CW" "WRD_ECDC" "CAN_GOV"
#> [16] "IDN_GTPPC" "SWE_SIR" "ESP_MSVP" "GBR_PHS" "THA_STAT"
#> [21] "GBR_PHTW" "GBR_PHE" "LAT_DSRP" "SAU_GOV" "AUS_C1A"
#> [26] "WRD_WHOJHU" "IND_COVIND" "IRL_HSPC" "ZAF_DSFSI" "WRD_WHO"
#> [31] "BRA_MSHM" "PAK_GOV" "MYS_MHYS" "POL_WIKI" "PRT_MSDS"
#> [36] "RUS_GOV" "ITA_PCDM" "NGA_CDC" "KOR_DS4C" "USA_NYT"
#> [41] "DEU_JPGG" "FRA_SPF" "JPN_C1JACD" "SWE_GM" "GBR_PHW"
#> [46] "EU_ZH" "UAE_GOV" "BEL_LE" "BEL_WY" "ITA_PC"In this example, the task is to retrieve the epidemiology table and then get only the data from source GBR_PHTW and then sort resulting dataset by decreasing date.
connect_oxcovid19() %>% ## Connect to PostgreSQL server
get_table(tbl_name = "epidemiology") %>% ## Retrieve epidemiology table
filter(source == "GBR_PHTW") %>% ## Select specific source
arrange(desc(date)) ## Sort by date
#> # Source: lazy query [?? x 15]
#> # Database: postgres [covid19@covid19db.org:5432/covid19]
#> # Ordered by: desc(date)
#> 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-08-01 United… GBR Scotland NHS Weste… <NA> NA
#> 2 GBR_P… 2020-08-01 United… GBR Scotland NHS Taysi… <NA> NA
#> 3 GBR_P… 2020-08-01 United… GBR Scotland NHS Shetl… <NA> NA
#> 4 GBR_P… 2020-08-01 United… GBR Scotland NHS Orkney <NA> NA
#> 5 GBR_P… 2020-08-01 United… GBR Scotland NHS Lothi… <NA> NA
#> 6 GBR_P… 2020-08-01 United… GBR Scotland NHS Lanar… <NA> NA
#> 7 GBR_P… 2020-08-01 United… GBR Scotland NHS Highl… <NA> NA
#> 8 GBR_P… 2020-08-01 United… GBR Scotland NHS Great… <NA> NA
#> 9 GBR_P… 2020-08-01 United… GBR Scotland NHS Gramp… <NA> NA
#> 10 GBR_P… 2020-08-01 United… GBR Scotland NHS Forth… <NA> NA
#> # … with more rows, and 7 more variables: confirmed <int>, recovered <int>,
#> # dead <int>, hospitalised <int>, hospitalised_icu <int>, quarantined <int>,
#> # gid <chr>For both tasks, the results in the example were replicated in R using the oxcovid19 functions.
The task here was to retrieve the mobility table from the PostgreSQL server and then extract only those with GOOGLE_MOBILITY as the source and GBR as the country code. Finally, the resulting table is sorted by date.
connect_oxcovid19() %>% ## Connect to PostgreSQL server
get_table(tbl_name = "mobility") %>% ## Retrieve mobility table
filter(source == "GOOGLE_MOBILITY", ## Get only data from `Google`
countrycode == "GBR") %>% ## Get only data from `GBR`
arrange(desc(date)) ## Sort by date
#> # Source: lazy query [?? x 17]
#> # Database: postgres [covid19@covid19db.org:5432/covid19]
#> # Ordered by: desc(date)
#> source date country countrycode adm_area_1 adm_area_2 adm_area_3
#> <chr> <date> <chr> <chr> <chr> <chr> <chr>
#> 1 GOOGL… 2020-11-24 United… GBR Wales Wrexham <NA>
#> 2 GOOGL… 2020-11-24 United… GBR Wales Vale of G… <NA>
#> 3 GOOGL… 2020-11-24 United… GBR Wales Torfaen <NA>
#> 4 GOOGL… 2020-11-24 United… GBR Wales Swansea <NA>
#> 5 GOOGL… 2020-11-24 United… GBR Wales Rhondda, … <NA>
#> 6 GOOGL… 2020-11-24 United… GBR Wales Powys <NA>
#> 7 GOOGL… 2020-11-24 United… GBR Wales Pembrokes… <NA>
#> 8 GOOGL… 2020-11-24 United… GBR Wales Newport <NA>
#> 9 GOOGL… 2020-11-24 United… GBR Wales Neath Por… <NA>
#> 10 GOOGL… 2020-11-24 United… GBR Wales Monmouths… <NA>
#> # … with more rows, and 10 more variables: transit_stations <dbl>,
#> # residential <dbl>, workplace <dbl>, parks <dbl>, retail_recreation <dbl>,
#> # grocery_pharmacy <dbl>, gid <chr>, transit <dbl>, walking <dbl>,
#> # driving <dbl>The results match the results shown in the examples.