I have data that looks like this (note dates are in DD-MM-YYYY format):
ID date drug score
A 28/08/2016 2 3
A 29/08/2016 1 4
A 30/08/2016 2 4
A 2/09/2016 2 4
A 3/09/2016 1 4
A 4/09/2016 2 4
B 8/08/2016 1 3
B 9/08/2016 2 4
B 10/08/2016 2 3
B 11/08/2016 1 3
C 30/11/2016 2 4
C 2/12/2016 1 5
C 3/12/2016 2 1
C 5/12/2016 1 4
C 6/12/2016 2 4
C 8/12/2016 1 2
C 9/12/2016 1 2
For 'drug': 1=drug taken, 2=no drug taken.
I need to summarise for each ID:
- 0day: the mean score for days when a drug was taken.
- -1day: the mean score for the days immediately prior to when the drug was taken.
- +1day: the mean score for the days immediately after the drug was taken.
If a drug was taken 2 days in a row (eg the last 2 rows of the example) then these scores should not be counted in the -1day or +1day calculations (i.e., each of the last two rows would contribute to the 0day score but would not contribute to the other metrics).
So for this example data, I would need an output table like this:
-1day 0day +1day
A 3.5 4 4
B 3 3 4
C 3.25 2.5
Note that there is not a record for all dates and that the -1day and +1day calculations need to be based on the actual dates and not just the records in the dataset.
I have no idea how to do this.
I also have two additional bonus questions:
I will most likely also need to calculate -2day and +2day scores, so need to be able to adapt an answer to do that.
How could I calculate a NoDrug score, which is the mean of all days that are not within 5 days of a drug taking day.
Here is code to generate a dataframe with this example data:
data<-data.frame(ID=c("A","A","A","A","A","A","B","B","B","B","C","C","C","C","C","C","C"),
date=as.Date(c("28/08/2016","29/08/2016","30/08/2016","2/09/2016","3/09/2016","4/09/2016","8/08/2016","9/08/2016","10/08/2016","11/08/2016","30/11/2016","2/12/2016","3/12/2016","5/12/2016","6/12/2016","8/12/2016","9/12/2016"),format= "%d/%m/%Y"),
drug=c(2,1,2,2,1,2,1,2,2,1,2,1,2,1,2,1,1),
score=c(3,4,4,4,4,4,3,4,3,3,4,5,1,4,4,2,2))