replace summative content for episode on "read case data"

**scenario:** tables are collected from separate database systems at different moments (report and followup). we can use their primary keys to access, filter, and join them to get a linelist.

``` r
# read data ---------------------------------------------------------------

library(outbreaks)
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
```

``` r
library(purrr)

# read data
dat <- outbreaks::mers_korea_2015 %>%
purrr::pluck("linelist") %>%
dplyr::as_tibble()

dat %>% dplyr::glimpse()
#> Rows: 162
#> Columns: 15
#> $ id "SK_1", "SK_2", "SK_3", "SK_4", "SK_5", "SK_6", "SK_7",…
#> $ age 68, 63, 76, 46, 50, 71, 28, 46, 56, 44, 79, 49, 49, 35,…
#> $ age_class "60-69", "60-69", "70-79", "40-49", "50-59", "70-79", "…
#> $ sex M, F, M, F, M, M, F, F, M, M, F, F, M, M, M, M, M, F, M…
#> $ place_infect Middle East, Outside Middle East, Outside Middle East, …
#> $ reporting_ctry South Korea, South Korea, South Korea, South Korea, Sou…
#> $ loc_hosp "Pyeongtaek St. Mary, Hospital, Pyeongtaek, Gyeonggi", …
#> $ dt_onset 2015-05-11, 2015-05-18, 2015-05-20, 2015-05-25, 2015-0…
#> $ dt_report 2015-05-19, 2015-05-20, 2015-05-20, 2015-05-26, 2015-0…
#> $ week_report 2015_21, 2015_21, 2015_21, 2015_22, 2015_22, 2015_22, 2…
#> $ dt_start_exp 2015-04-18, 2015-05-15, 2015-05-16, 2015-05-16, 2015-0…
#> $ dt_end_exp 2015-05-04, 2015-05-20, 2015-05-16, 2015-05-20, 2015-0…
#> $ dt_diag 2015-05-20, 2015-05-20, 2015-05-21, 2015-05-26, 2015-0…
#> $ outcome Alive, Alive, Dead, Alive, Alive, Dead, Alive, Alive, A…
#> $ dt_death NA, NA, 2015-06-04, NA, NA, 2015-06-01, NA, NA, NA, NA…
```

``` r

# assumption: in this dataset we have two tables: report and followup (death)
table_report <- dat %>% dplyr::select(id:dt_diag)
table_followup <- dat %>% dplyr::select(id,outcome,dt_death)

# database management -----------------------------------------------------

library(DBI)
library(RSQLite)

# Create a temporary SQLite database in memory
database_outbreak <- DBI::dbConnect(
drv = RSQLite::SQLite(),
dbname = ":memory:"
)

# Store the dataframes as a table for the database
# in the SQLite database
DBI::dbWriteTable(
conn = database_outbreak,
name = "report",
value = table_report
)

DBI::dbWriteTable(
conn = database_outbreak,
name = "followup",
value = table_followup
)

database_outbreak
#>
#> Path: :memory:
#> Extensions: TRUE
```

``` r

# query data --------------------------------------------------------------

library(dplyr)
# library(dbplyr)

# Query data using dplyr verbs
database_report <- dplyr::tbl(database_outbreak, "report")
database_followup <- dplyr::tbl(database_outbreak, "followup")

database_report_query <- database_report %>%
dplyr::select(id,age,sex,dt_onset,dt_report) %>%
filter(sex == "F")

# Show SQL query
database_report_query %>%
dplyr::show_query()
#>
#> SELECT `id`, `age`, `sex`, `dt_onset`, `dt_report`
#> FROM `report`
#> WHERE (`sex` = 'F')
```

``` r

# Join tables
database_join_tables <- database_report_query %>%
dplyr::left_join(database_followup)
#> Joining with `by = join_by(id)`
```

``` r

database_join_tables %>%
dplyr::show_query()
#>
#> SELECT `LHS`.*, `outcome`, `dt_death`
#> FROM (
#> SELECT `id`, `age`, `sex`, `dt_onset`, `dt_report`
#> FROM `report`
#> WHERE (`sex` = 'F')
#> ) AS `LHS`
#> LEFT JOIN `followup`
#> ON (`LHS`.`id` = `followup`.`id`)
```

``` r

# Collect query and join
database_collect <- database_join_tables %>%
dplyr::collect()

database_collect
#> # A tibble: 63 × 7
#> id age sex dt_onset dt_report outcome dt_death
#>
#> 1 SK_2 63 F 16573 16575 Alive NA
#> 2 SK_4 46 F 16580 16581 Alive NA
#> 3 SK_7 28 F 16576 16583 Alive NA
#> 4 SK_8 46 F 16581 16584 Alive NA
#> 5 SK_11 79 F 16575 16584 Alive NA
#> 6 SK_12 49 F 16576 16584 Alive NA
#> 7 SK_18 77 F 16575 16587 Alive NA
#> 8 SK_21 59 F 16578 16588 Alive NA
#> 9 SK_22 39 F 16582 16588 Alive NA
#> 10 SK_25 57 F NA 16589 Dead 16587
#> # ℹ 53 more rows
```

``` r

# # Read data from the 'cases' table
# result <- DBI::dbReadTable(
# conn = database_con,
# name = "cases"
# )

# close connection --------------------------------------------------------

# Previously created database objects - readable
database_outbreak
#>
#> Path: :memory:
#> Extensions: TRUE
```

``` r
database_report
#> # Source: table<`report`> [?? x 13]
#> # Database: sqlite 3.46.0 [:memory:]
#> id age age_class sex place_infect reporting_ctry loc_hosp dt_onset
#>
#> 1 SK_1 68 60-69 M Middle East South Korea Pyeongt… 16566
#> 2 SK_2 63 60-69 F Outside Middle … South Korea Pyeongt… 16573
#> 3 SK_3 76 70-79 M Outside Middle … South Korea Pyeongt… 16575
#> 4 SK_4 46 40-49 F Outside Middle … South Korea Pyeongt… 16580
#> 5 SK_5 50 50-59 M Outside Middle … South Korea 365 Yeo… 16580
#> 6 SK_6 71 70-79 M Outside Middle … South Korea Pyeongt… 16579
#> 7 SK_7 28 20-29 F Outside Middle … South Korea Pyeongt… 16576
#> 8 SK_8 46 40-49 F Outside Middle … South Korea Seoul C… 16581
#> 9 SK_9 56 50-59 M Outside Middle … South Korea Pyeongt… NA
#> 10 SK_10 44 40-49 M Outside Middle … China Pyeongt… 16576
#> # ℹ more rows
#> # ℹ 5 more variables: dt_report , week_report , dt_start_exp ,
#> # dt_end_exp , dt_diag
```

``` r
database_followup
#> # Source: table<`followup`> [?? x 3]
#> # Database: sqlite 3.46.0 [:memory:]
#> id outcome dt_death
#>
#> 1 SK_1 Alive NA
#> 2 SK_2 Alive NA
#> 3 SK_3 Dead 16590
#> 4 SK_4 Alive NA
#> 5 SK_5 Alive NA
#> 6 SK_6 Dead 16587
#> 7 SK_7 Alive NA
#> 8 SK_8 Alive NA
#> 9 SK_9 Alive NA
#> 10 SK_10 Alive NA
#> # ℹ more rows
```

``` r
database_join_tables
#> # Source: SQL [?? x 7]
#> # Database: sqlite 3.46.0 [:memory:]
#> id age sex dt_onset dt_report outcome dt_death
#>
#> 1 SK_2 63 F 16573 16575 Alive NA
#> 2 SK_4 46 F 16580 16581 Alive NA
#> 3 SK_7 28 F 16576 16583 Alive NA
#> 4 SK_8 46 F 16581 16584 Alive NA
#> 5 SK_11 79 F 16575 16584 Alive NA
#> 6 SK_12 49 F 16576 16584 Alive NA
#> 7 SK_18 77 F 16575 16587 Alive NA
#> 8 SK_21 59 F 16578 16588 Alive NA
#> 9 SK_22 39 F 16582 16588 Alive NA
#> 10 SK_25 57 F NA 16589 Dead 16587
#> # ℹ more rows
```

``` r

# Close the database connection
DBI::dbDisconnect(conn = database_outbreak)

# Previously created database objects - not readable
database_outbreak
#>
#> DISCONNECTED
```

``` r
database_report
#> Error in `collect()`:
#> ! Failed to collect lazy table.
#> Caused by error:
#> ! Invalid or closed connection
```

``` r
database_followup
#> Error in `collect()`:
#> ! Failed to collect lazy table.
#> Caused by error:
#> ! Invalid or closed connection
```

``` r
database_join_tables
#> Error in `collect()`:
#> ! Failed to collect lazy table.
#> Caused by error:
#> ! Invalid or closed connection
```

^{Created on 2024-08-07 with [reprex v2.1.0](https://reprex.tidyverse.org)}