Wrangling with dplyr for Medical Emergency Team and Respond Blue calls

[genghiskhan]

Dear Consultants

Aim = Respond Blue is more resource intensive than Medical Emergency Team (MET) calls for CPR, so it supersedes to create a service quantity as 1 in the result dataframe. The result df is something of a pivot longer from tidyr.

Issue = the original data are in sql database which needs union query and excluded record temporary tables to get the result dataset.

I am hoping tidyverse will help for my urgent statistical prediction at CPR workload.

repex is here.

Thank you very much.

# dplyr met 18072025

metblue <- tibble::tribble(
             ~id,                 ~dttm, ~incidentkey, ~met, ~blue,
              1L, "2025-07-18 08:00:00",           1L,   1L,    0L,
              1L, "2025-07-18 08:00:00",           2L,   0L,    1L,
              2L, "2025-07-18 09:00:00",           1L,   1L,    1L,
              3L, "2025-07-18 10:00:00",           1L,   0L,    1L,
              3L, "2025-07-18 10:10:00",           2L,   1L,    0L
             )
metblue
#> # A tibble: 5 x 5
#>      id dttm                incidentkey   met  blue
#>   <int> <chr>                     <int> <int> <int>
#> 1     1 2025-07-18 08:00:00           1     1     0
#> 2     1 2025-07-18 08:00:00           2     0     1
#> 3     2 2025-07-18 09:00:00           1     1     1
#> 4     3 2025-07-18 10:00:00           1     0     1
#> 5     3 2025-07-18 10:10:00           2     1     0

metblue.result <- tibble::tribble(
                    ~id,                 ~dttm, ~service, ~incidentkey, ~quantity,
                     1L, "2025-07-18 08:00:00",   "blue",           2L,        1L,
                     2L, "2025-07-18 09:00:00",   "blue",           1L,        1L,
                     3L, "2025-07-18 10:00:00",   "blue",           1L,        1L,
                     3L, "2025-07-18 10:10:00",    "met",           2L,        1L
                    )
metblue.result
#> # A tibble: 4 x 5
#>      id dttm                service incidentkey quantity
#>   <int> <chr>               <chr>         <int>    <int>
#> 1     1 2025-07-18 08:00:00 blue              2        1
#> 2     2 2025-07-18 09:00:00 blue              1        1
#> 3     3 2025-07-18 10:00:00 blue              1        1
#> 4     3 2025-07-18 10:10:00 met               2        1

Created on 2025-07-18 with reprex v2.1.1

[FJCC]

This gets you most of what you want. It's not clear to me how the quantity column is calculated.

library(tidyverse)
metblue <- tibble::tribble(
  ~id,                 ~dttm, ~incidentkey, ~met, ~blue,
  1L, "2025-07-18 08:00:00",           1L,   1L,    0L,
  1L, "2025-07-18 08:00:00",           2L,   0L,    1L,
  2L, "2025-07-18 09:00:00",           1L,   1L,    1L,
  3L, "2025-07-18 10:00:00",           1L,   0L,    1L,
  3L, "2025-07-18 10:10:00",           2L,   1L,    0L
)
blue <- metblue |> filter(blue == 1) |> select(id, dttm, incidentkey)
met <- metblue |> filter(met == 1) |> select(id, dttm, incidentkey)
JoinedDF <- full_join(blue, met, by = c("id", "dttm"), suffix = c("_blue", "_met"))
JoinedDF <- JoinedDF |> mutate(service = ifelse(!is.na(incidentkey_blue), "blue", "met"),
                               incidentkey = ifelse(!is.na(incidentkey_blue), incidentkey_blue, incidentkey_met)) |> 
  select(id, dttm, service, incidentkey)
JoinedDF
#> # A tibble: 4 × 4
#>      id dttm                service incidentkey
#>   <int> <chr>               <chr>         <int>
#> 1     1 2025-07-18 08:00:00 blue              2
#> 2     2 2025-07-18 09:00:00 blue              1
#> 3     3 2025-07-18 10:00:00 blue              1
#> 4     3 2025-07-18 10:10:00 met               2

^{Created on 2025-07-17 with reprex v2.1.1}

[genghiskhan]

Hi FJCC,
Appreciate your prompt solution.
Quantity is = when every entry or every row counted as 1 service provided whether it is met or blue
Are you able to add?
Anyway, I am still grateful to you.
G

[mduvekot]

metblue %>% print() %>% 
  pivot_longer(-c(id, dttm, incidentkey), names_to = "service") %>%
  filter(value != 0) %>% 
  reframe(.by = c(id, dttm, incidentkey, service), quantitity = sum(value))

[genghiskhan]

Thank you very much.
Quantity is there now.
Appreciated.
G