Adding rows (changes in dosing) to my data frame in R

I‘m working with clinical data that I wanna arrange in a format so i can model with it later.
my current dataset looks like this:

# create a data frame
df <- data.frame(ID = c(1,1,1,2,2,3,3,3),
                 DOSE = c(100, NA, NA, 200, NA, 300, NA, NA),
                 TIME = c(NA, 1, 2, NA, 3, NA, 1, 2),
                 Drug_concentration = c(NA, 5, 6.5, 3, 4, 8, 10, 12))

As you can see each first row of a patient ID only contains the dose, other variables (TIME, drug concentration) are filled in with a full stop. After that initial row the dose column gets a full stop and the other variables are filled in.

My question: certain patients have gotten dose changes throughout their treatment. I wanna add those dose changes to my dataset but do not know how i can efficiently do this using R. Let’s say patient 1 had a dose change from 100 to 50 mg at TIME = 2 and patient 3 had a dose change from 300 mg to 500 mg at TIME = 2. i would want my dataset to look like this:

I’ve tried using dyplyr but im not that good at R sadly

Here's one approach using tidyverse:

Assuming we have:

dose_changes <- data.frame(ID = c(1, 3),
                           DOSE = c(50, 500),
                           TIME = c(2, 2))

I'll add a TIME = 0 for the missing rows, since I want to make sure these sort first for each ID. I also adjust the dose_changes TIME values to make sure their TIME X is sorted so that it appears before any measurements at TIME X (since the drug change implicitly happened at some unspecified time prior). Then I combine in the dose_changes data, arrange y ID and TIME, fill down (the default fill direction) the missing DOSEs, and finally remove the rows w/o Drug_concentration observations.

library(tidyverse)
df %>%
  mutate(TIME = if_else(is.na(TIME), 0, TIME)) %>%
  bind_rows(dose_changes %>% mutate(TIME = TIME - 0.1) %>%
  arrange(ID, TIME) %>%
  group_by(ID) %>%
  fill(DOSE) %>%
  filter(!is.na(Drug_concentration)) %>%
  ungroup()


# A tibble: 7 × 4
     ID  DOSE  TIME Drug_concentration
  <dbl> <dbl> <dbl>              <dbl>
1     1   100     1                5  
2     1    50     2                6.5
3     2   200     0                3  
4     2   200     3                4  
5     3   300     0                8  
6     3   300     1               10  
7     3   500     2               12 

Your desired output format is not right for modelling in R, but I assume you have a good reason for requesting it. I've certainly worked with command-line analysis packages in the past that ask for data in weird ways.

Anyway, here.

library(tidyr)
library(dplyr)


df <- data.frame(
                  ID = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 3L),
                DOSE = c(100L, NA, NA, NA, 200L, NA, NA, 300L, NA, NA, NA),
                TIME = c(NA, 1L, 2L, 3L, NA, 1L, 3L, NA, 1L, 2L, 3L),
  Drug.concentration = c(NA, 5, 6.5, 8, NA, 3, 4, NA, 8, 10, 12)
      )


good_table <- 
    df %>%
    # 1. Fill NAs
    group_by(ID) %>% 
    fill(everything(), .direction = "down") %>% 
    ungroup() %>% 
    # 2. Add a timepoint zero for sorting
    mutate(TIME = replace_na(TIME, 0)) %>% 
    # 3. Replace NAs in Drug.concentration with -99 so that they sort to the top.
    mutate(Drug.concentration = replace_na(Drug.concentration, -99)) %>% 
    # 4. Add your rows
    add_row(ID   = c(1, 3), 
            DOSE = c(50, 500), 
            TIME = c(2, 2), 
            Drug.concentration = c(-99, -99)) %>% 
    # 5. Arrange the output so that it is in the order you want.
    arrange(ID, TIME, Drug.concentration)

good_table
#> # A tibble: 13 × 4
#>       ID  DOSE  TIME Drug.concentration
#>    <dbl> <dbl> <dbl>              <dbl>
#>  1     1   100     0              -99  
#>  2     1   100     1                5  
#>  3     1    50     2              -99  
#>  4     1   100     2                6.5
#>  5     1   100     3                8  
#>  6     2   200     0              -99  
#>  7     2   200     1                3  
#>  8     2   200     3                4  
#>  9     3   300     0              -99  
#> 10     3   300     1                8  
#> 11     3   500     2              -99  
#> 12     3   300     2               10  
#> 13     3   300     3               12

output_table <-
    good_table %>% 
    # 6. Everything has to be Character type for your dots
    mutate(across(everything(), as.character)) %>% 
    # 7. Only the first original values of DOSE are kept.
    mutate(DOSE = if_else(duplicated(DOSE), ".", DOSE)) %>% 
    # 8. If DOSE is . , then keep its values
    mutate(across(c(TIME, Drug.concentration), 
                  function(orig_value) {
                      if_else(DOSE == ".", orig_value, ".")
                  }))


output_table
#> # A tibble: 13 × 4
#>    ID    DOSE  TIME  Drug.concentration
#>    <chr> <chr> <chr> <chr>             
#>  1 1     100   .     .                 
#>  2 1     .     1     5                 
#>  3 1     50    .     .                 
#>  4 1     .     2     6.5               
#>  5 1     .     3     8                 
#>  6 2     200   .     .                 
#>  7 2     .     1     3                 
#>  8 2     .     3     4                 
#>  9 3     300   .     .                 
#> 10 3     .     1     8                 
#> 11 3     500   .     .                 
#> 12 3     .     2     10                
#> 13 3     .     3     12

^{Created on 2023-04-01 with reprex v2.0.2}

PS. The first set of code you gave to generate df is wrong, it doesn't generate the table you pasted underneath it. Please double-check that for next time. Your code also generated NAs instead of dots, unlike the table underneath it. I've kept the NAs for this example, but if your data in fact has dots, then you'll need to insert this between lines 13 and 14 to create the NAs and get the data into their proper types.

    mutate(across(c(ID, DOSE, TIME), as.integer)) %>% 
    mutate(across(Drug.concentration, as.numeric)) %>% 

You can use which to get the row and use rbind to insert the line.

i <- which(df$ID == 1 & df$TIME == 2)
df <- rbind(df[1:(i-1),], setNames(data.frame(1, 50, NA, NA), names(df)), df[i:nrow(df),])
i <- which(df$ID == 3 & df$TIME == 2)
df <- rbind(df[1:(i-1),], setNames(data.frame(3, 500, NA, NA), names(df)), df[i:nrow(df),])
df
#   ID DOSE TIME Drug_concentration
#1   1  100   NA                 NA
#2   1   NA    1                5.0
#3   1   50   NA                 NA
#31  1   NA    2                6.5
#4   2  200   NA                3.0
#5   2   NA    3                4.0
#6   3  300   NA                8.0
#7   3   NA    1               10.0
#11  3  500   NA                 NA
#8   3   NA    2               12.0