How to make prescription data frame wider in r by date, not by individual

user_14169119 0
Viewed 64

I have long form patient prescription data and want to create a wider data frame where each line represents a different prescription delivery. So some patients will have only one row, but those with multiple deliveries will have several rows (1 for each prescription delivery). I have only previously used the pivot commands in a very simple manner, but am struggling as I am only getting back 1 row for each patient, when I want 1 row for each date of prescription delivery for each patient.

I have a very simple data frame of patient id, date of prescription delivery and the code corresponding to the prescription they recieved.

id = id = factor(c("1001","1001","1001","1002","1002","1002","1002","1002","1003","1003"))
date = c("2013-10-31","2013-11-30","2013-12-31","2013-08-28","2013-08-28","2013-09-30",
         "2013-09-30","2013-02-15","2013-02-15","2013-02-15")
atc_code = c("C07AA05","C07AA05","C07AA05","A10BA02","C09CA01","A10BA02",
                       "C09CA01","A10BA02","A10BA02","C07AA05")

date1 <- as.Date(date, format = "%Y-%m-%d")
df <- data.frame(id,
                 date1,
                atc_code)
df
#>      id      date1 atc_code
#> 1  1001 2013-10-31  C07AA05
#> 2  1001 2013-11-30  C07AA05
#> 3  1001 2013-12-31  C07AA05
#> 4  1002 2013-08-28  A10BA02
#> 5  1002 2013-08-28  C09CA01
#> 6  1002 2013-09-30  A10BA02
#> 7  1002 2013-09-30  C09CA01
#> 8  1002 2013-02-15  A10BA02
#> 9  1003 2013-02-15  A10BA02
#> 10 1003 2013-02-15  C07AA05
Created on 2021-12-04 by the reprex package (v2.0.1)

What I would like the data frame to look like:

df
#>     id       date atc_code_1 atc_code_2
#> 1 1001 2013-10-31    C07AA05         NA
#> 2 1001 2013-11-30    C07AA05         NA
#> 3 1001 2013-12-31    C07AA05         NA
#> 4 1002 2013-08-28    A10BA02    C09CA01
#> 5 1002 2013-09-30    A10BA02    C09CA01
#> 6 1002 2013-02-15    A10BA02         NA
#> 7 1003 2013-02-15    A10BA02    C07AA05

In reality, a patient can have many more deliveries in the year and many more prescriptions in a single delivery, but I kept it simple for the example. Any help would be greatly appreciated.

What I need to do is create a new variable with mutate (a disease) that uses combinations of prescription in a single delivery to define (ie did a patient get x and y prescription or did they get x but not y prescription), so if this can be achieved by a series of group_bys or something else, that would work too.

Thank you!

pivot r
4 Answers

Using data.table:
(the order of the records is different than what you wanted)

library(data.table)
setDT(df)
dcast(df, id + date1 ~ rowid(id, date1, prefix = 'atc_code_'), 
      value.var = 'atc_code')

    id      date1 atc_code_1 atc_code_2
1: 1001 2013-10-31    C07AA05       <NA>
2: 1001 2013-11-30    C07AA05       <NA>
3: 1001 2013-12-31    C07AA05       <NA>
4: 1002 2013-02-15    A10BA02       <NA>
5: 1002 2013-08-28    A10BA02    C09CA01
6: 1002 2013-09-30    A10BA02    C09CA01
7: 1003 2013-02-15    A10BA02    C07AA05
user_153444420

I think this is what you want:

library(tidyverse)
df <- df %>% group_by(id, date1) %>% mutate(num_scripts = row_number())
df_wide <- df %>% pivot_wider(names_from = num_scripts, values_from = atc_code)

Edit: changed the column names to match yours

df_wide <- df %>% pivot_wider(names_from = num_scripts, values_from = atc_code, names_prefix = "atc_code_")
user_175831980

Consider ave to add a running group count of id and date1 grouping and then reshape wide. (Below num is needed as a temp variable for ave but any number field can be used.)

df <- within(df, {
  atc_num <- ave(1:nrow(df), id, date1, FUN=seq_along)
}) |> reshape(
  idvar = c("id", "date1"),
  timevar = "atc_num",
  direction = "wide"
) 

df
    id      date1 atc_code.1 atc_code.2
1 1001 2013-10-31    C07AA05       <NA>
2 1001 2013-11-30    C07AA05       <NA>
3 1001 2013-12-31    C07AA05       <NA>
4 1002 2013-08-28    A10BA02    C09CA01
6 1002 2013-09-30    A10BA02    C09CA01
8 1002 2013-02-15    A10BA02       <NA>
9 1003 2013-02-15    A10BA02    C07AA05
user_14224510

You can use tidyverse. For each group (i.e., id and date1), we mutate a new column to give a designation to each record (which we will use to pivot to new columns). Then, you can use pivot_wider with the category names and the atc_codes to put in the desired format.

library(tidyverse)

df %>%
  dplyr::group_by(id, date1) %>%
  dplyr::mutate(category = paste0("atc_code_", 1:n())) %>%
  tidyr::pivot_wider(names_from = category, values_from = atc_code) %>%
  dplyr::arrange(id, date1)

Output

# A tibble: 7 × 4
# Groups:   id [3]
  id    date1      atc_code_1 atc_code_2
  <fct> <date>     <chr>      <chr>     
1 1001  2013-10-31 C07AA05    NA        
2 1001  2013-11-30 C07AA05    NA        
3 1001  2013-12-31 C07AA05    NA        
4 1002  2013-02-15 A10BA02    NA        
5 1002  2013-08-28 A10BA02    C09CA01   
6 1002  2013-09-30 A10BA02    C09CA01   
7 1003  2013-02-15 A10BA02    C07AA05

Data

df <- structure(
  list(
    id = structure(
      c(1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L,
        3L, 3L),
      .Label = c("1001", "1002", "1003"),
      class = "factor"
    ),
    date1 = structure(
      c(
        16009, 16039, 16070, 15945, 15945, 15978, 15978, 
        15751, 15751, 15751
      ),
      class = "Date"
    ),
    atc_code = c(
      "C07AA05", "C07AA05",  "C07AA05", "A10BA02", "C09CA01", 
      "A10BA02", "C09CA01", "A10BA02", "A10BA02", "C07AA05"
    )
  ),
  class = "data.frame",
  row.names = c(NA, -10L)
)
user_152931910
Related