0

(R) I have a dataframe with rows for dates with an available score only. I want to insert rows with the score 0 on days with no score, and rows with the score 20 on days after the date of death (NA means study object is alive at day 90). Day 1 is defined as the first date with an avaiable score, and I am trying to create a dataframe with exactly 90 rows for each id (day 1 to 90). Do anyone know how to address this? Thanks in advance.

id  date        death       score
1   01-01-2001  04-02-2001  14
1   02-01-2001  04-02-2001  16
1   31-01-2001  04-02-2001  15
1   01-02-2001  04-02-2001  16
1   02-02-2001  04-02-2001  12
2   01-04-2001  NA          5
2   03-04-2001  NA          6
2   05-05-2001  NA          9
2   06-05-2001  NA          2
2   07-05-2001  NA          10
vincentleroy
  • 35
  • 1
  • 5
  • What does "score 0 on days with no score" mean with reference to the sample data set? Change an NA in the score column to 0? I would also review this: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – joncgoodwin Oct 29 '21 at 13:06

1 Answers1

0

Here is something you can try - let me know if this is helpful.

First, make sure your dates are Date format (if not already):

df$date <- as.Date(df$date, format = "%d-%m-%Y")
df$death <- as.Date(df$death, format = "%d-%m-%Y")

You can use tidyverse and lubridate for most functions needed. Also note that padr can be useful for padding dates.

In this case, first group_by id. You can use complete to fill in missing dates. The sequence of dates used will be from the first date to 89 days later (total 90 days). The fill will make the score zero for these additional dates added.

Another fill can be used to include the date of death for each subsequent row added (since it appears to be the structure used already).

Finally, you can mutate the score - if death is not NA and the date for a row comes after death, then make the value 20 (otherwise just retain the current score).

library(tidyverse)
library(lubridate)

df %>%
  group_by(id) %>%
  complete(date = seq(date[1], date[1] + days(89), by = "1 day"), fill = list(score = 0)) %>%
  fill(death, .direction = "down") %>%
  mutate(score = ifelse(!is.na(death) & date > death, 20, score)) %>%
  print(n=180)

Output

# A tibble: 180 x 4
# Groups:   id [2]
       id date       death      score
    <int> <date>     <date>     <dbl>
  1     1 2001-01-01 2001-02-04    14
  2     1 2001-01-02 2001-02-04    16
  3     1 2001-01-03 2001-02-04     0
  4     1 2001-01-04 2001-02-04     0
  5     1 2001-01-05 2001-02-04     0
  6     1 2001-01-06 2001-02-04     0
  7     1 2001-01-07 2001-02-04     0
  8     1 2001-01-08 2001-02-04     0
  9     1 2001-01-09 2001-02-04     0
 10     1 2001-01-10 2001-02-04     0
 11     1 2001-01-11 2001-02-04     0
 12     1 2001-01-12 2001-02-04     0
 13     1 2001-01-13 2001-02-04     0
 14     1 2001-01-14 2001-02-04     0
 15     1 2001-01-15 2001-02-04     0
 16     1 2001-01-16 2001-02-04     0
 17     1 2001-01-17 2001-02-04     0
 18     1 2001-01-18 2001-02-04     0
 19     1 2001-01-19 2001-02-04     0
 20     1 2001-01-20 2001-02-04     0
 21     1 2001-01-21 2001-02-04     0
 22     1 2001-01-22 2001-02-04     0
 23     1 2001-01-23 2001-02-04     0
 24     1 2001-01-24 2001-02-04     0
 25     1 2001-01-25 2001-02-04     0
 26     1 2001-01-26 2001-02-04     0
 27     1 2001-01-27 2001-02-04     0
 28     1 2001-01-28 2001-02-04     0
 29     1 2001-01-29 2001-02-04     0
 30     1 2001-01-30 2001-02-04     0
 31     1 2001-01-31 2001-02-04    15
 32     1 2001-02-01 2001-02-04    16
 33     1 2001-02-02 2001-02-04    12
 34     1 2001-02-03 2001-02-04     0
 35     1 2001-02-04 2001-02-04     0
 36     1 2001-02-05 2001-02-04    20
 37     1 2001-02-06 2001-02-04    20
 38     1 2001-02-07 2001-02-04    20
 39     1 2001-02-08 2001-02-04    20
 40     1 2001-02-09 2001-02-04    20
 41     1 2001-02-10 2001-02-04    20
 42     1 2001-02-11 2001-02-04    20
 43     1 2001-02-12 2001-02-04    20
 44     1 2001-02-13 2001-02-04    20
 45     1 2001-02-14 2001-02-04    20
 46     1 2001-02-15 2001-02-04    20
 47     1 2001-02-16 2001-02-04    20
 48     1 2001-02-17 2001-02-04    20
 49     1 2001-02-18 2001-02-04    20
 50     1 2001-02-19 2001-02-04    20
 51     1 2001-02-20 2001-02-04    20
 52     1 2001-02-21 2001-02-04    20
 53     1 2001-02-22 2001-02-04    20
 54     1 2001-02-23 2001-02-04    20
 55     1 2001-02-24 2001-02-04    20
 56     1 2001-02-25 2001-02-04    20
 57     1 2001-02-26 2001-02-04    20
 58     1 2001-02-27 2001-02-04    20
 59     1 2001-02-28 2001-02-04    20
 60     1 2001-03-01 2001-02-04    20
 61     1 2001-03-02 2001-02-04    20
 62     1 2001-03-03 2001-02-04    20
 63     1 2001-03-04 2001-02-04    20
 64     1 2001-03-05 2001-02-04    20
 65     1 2001-03-06 2001-02-04    20
 66     1 2001-03-07 2001-02-04    20
 67     1 2001-03-08 2001-02-04    20
 68     1 2001-03-09 2001-02-04    20
 69     1 2001-03-10 2001-02-04    20
 70     1 2001-03-11 2001-02-04    20
 71     1 2001-03-12 2001-02-04    20
 72     1 2001-03-13 2001-02-04    20
 73     1 2001-03-14 2001-02-04    20
 74     1 2001-03-15 2001-02-04    20
 75     1 2001-03-16 2001-02-04    20
 76     1 2001-03-17 2001-02-04    20
 77     1 2001-03-18 2001-02-04    20
 78     1 2001-03-19 2001-02-04    20
 79     1 2001-03-20 2001-02-04    20
 80     1 2001-03-21 2001-02-04    20
 81     1 2001-03-22 2001-02-04    20
 82     1 2001-03-23 2001-02-04    20
 83     1 2001-03-24 2001-02-04    20
 84     1 2001-03-25 2001-02-04    20
 85     1 2001-03-26 2001-02-04    20
 86     1 2001-03-27 2001-02-04    20
 87     1 2001-03-28 2001-02-04    20
 88     1 2001-03-29 2001-02-04    20
 89     1 2001-03-30 2001-02-04    20
 90     1 2001-03-31 2001-02-04    20
 91     2 2001-04-01 NA             5
 92     2 2001-04-02 NA             0
 93     2 2001-04-03 NA             6
 94     2 2001-04-04 NA             0
 95     2 2001-04-05 NA             0
 96     2 2001-04-06 NA             0
 97     2 2001-04-07 NA             0
 98     2 2001-04-08 NA             0
 99     2 2001-04-09 NA             0
100     2 2001-04-10 NA             0
101     2 2001-04-11 NA             0
102     2 2001-04-12 NA             0
103     2 2001-04-13 NA             0
104     2 2001-04-14 NA             0
105     2 2001-04-15 NA             0
106     2 2001-04-16 NA             0
107     2 2001-04-17 NA             0
108     2 2001-04-18 NA             0
109     2 2001-04-19 NA             0
110     2 2001-04-20 NA             0
111     2 2001-04-21 NA             0
112     2 2001-04-22 NA             0
113     2 2001-04-23 NA             0
114     2 2001-04-24 NA             0
115     2 2001-04-25 NA             0
116     2 2001-04-26 NA             0
117     2 2001-04-27 NA             0
118     2 2001-04-28 NA             0
119     2 2001-04-29 NA             0
120     2 2001-04-30 NA             0
121     2 2001-05-01 NA             0
122     2 2001-05-02 NA             0
123     2 2001-05-03 NA             0
124     2 2001-05-04 NA             0
125     2 2001-05-05 NA             9
126     2 2001-05-06 NA             2
127     2 2001-05-07 NA            10
128     2 2001-05-08 NA             0
129     2 2001-05-09 NA             0
130     2 2001-05-10 NA             0
131     2 2001-05-11 NA             0
132     2 2001-05-12 NA             0
133     2 2001-05-13 NA             0
134     2 2001-05-14 NA             0
135     2 2001-05-15 NA             0
136     2 2001-05-16 NA             0
137     2 2001-05-17 NA             0
138     2 2001-05-18 NA             0
139     2 2001-05-19 NA             0
140     2 2001-05-20 NA             0
141     2 2001-05-21 NA             0
142     2 2001-05-22 NA             0
143     2 2001-05-23 NA             0
144     2 2001-05-24 NA             0
145     2 2001-05-25 NA             0
146     2 2001-05-26 NA             0
147     2 2001-05-27 NA             0
148     2 2001-05-28 NA             0
149     2 2001-05-29 NA             0
150     2 2001-05-30 NA             0
151     2 2001-05-31 NA             0
152     2 2001-06-01 NA             0
153     2 2001-06-02 NA             0
154     2 2001-06-03 NA             0
155     2 2001-06-04 NA             0
156     2 2001-06-05 NA             0
157     2 2001-06-06 NA             0
158     2 2001-06-07 NA             0
159     2 2001-06-08 NA             0
160     2 2001-06-09 NA             0
161     2 2001-06-10 NA             0
162     2 2001-06-11 NA             0
163     2 2001-06-12 NA             0
164     2 2001-06-13 NA             0
165     2 2001-06-14 NA             0
166     2 2001-06-15 NA             0
167     2 2001-06-16 NA             0
168     2 2001-06-17 NA             0
169     2 2001-06-18 NA             0
170     2 2001-06-19 NA             0
171     2 2001-06-20 NA             0
172     2 2001-06-21 NA             0
173     2 2001-06-22 NA             0
174     2 2001-06-23 NA             0
175     2 2001-06-24 NA             0
176     2 2001-06-25 NA             0
177     2 2001-06-26 NA             0
178     2 2001-06-27 NA             0
179     2 2001-06-28 NA             0
180     2 2001-06-29 NA             0
Ben
  • 28,684
  • 5
  • 23
  • 45