0

I am trying to run a piece of code to calculate the number of days between 2 dates, whenever a status is changed to the value 1.

below is a sample of the data frame:

sample of data

I can calculate using the following code between 2 dates no problem, it is when there is a status change between each change i need to calculate the number of days.

df$Date2_Date1 <- difftime(df$Date2,df$Date1, units = c("hours"))
tgogos
  • 23,218
  • 20
  • 96
  • 128
nigel griffin
  • 1
  • 1
  • 3
    Please don't post data as an image, but include it in your question for others to use. That way it is easier for others to help you. – Axeman Oct 27 '17 at 08:52
  • 3
    You can follow this link: [How to make a great R reproducible example?](http://stackoverflow.com/questions/5963269) – Sotos Oct 27 '17 at 08:53
  • Something like: `library(dplyr); left_join(d, d %>% filter(status == 1) %>% mutate(diff = difftime(Date2, Date1, units = c("hours")))` – Axeman Oct 27 '17 at 08:55

1 Answers1

1

With the provided dataframe (one alteration: added an / in the 4th date) and one edit in the previous code (unit instead of unist):

library(dplyr)


id= c(1,1,1,1,1,2,2,2,2,3,3,3,3) 
status = c(0,0,0,1,0,0,0,1,0,0,0,0,0) 
date =c('08/01/2017','09/01/2017','10/01/2017','11/01/2017', 
    '13/01/2017','16/01/2017','17/01/2017','18/01/2017', 
    '19/01/2017','20/01/2017','21/01/2017','23/01/2017', '24/01/2017')

data <-data.frame(id,status,date)%>%
  mutate(date=as.Date(date,"%d/%m/%Y"))

data%>%group_by(id,status)%>%
   summarise(date = min(date))%>%
   summarise(min = min(date),
         max = max(date),
         n = n(),
         Diff = difftime(max,min,unit=c("hours")),
         Diff = ifelse(n==2, Diff,NA))

gives

enter image description here

Dries
  • 470
  • 4
  • 24
  • tried the above code:library(dplyr) df%>%group_by(id,status)%>% summarise(date = min(date))%>% summarise(min = min(date), max = max(date), n = n(), Diff = difftime(max,min,unist=c("hours")), Diff = ifelse(n==2, Diff,NA)) – nigel griffin Oct 27 '17 at 09:14
  • I assume it wasn't ok? If you provide the code to reproduce the dataframe above, I'll test it myself and make the necessary changes – Dries Oct 27 '17 at 09:17
  • sorry its a dataset i pull in from a .csv file – nigel griffin Oct 27 '17 at 09:34
  • sorry here is a quick data frame: id= c(1,1,1,1,1,2,2,2,2,3,3,3,3) status = c(0,0,0,1,0,0,0,1,0,0,0,0,0) date =c('08/01/2017','09/01/2017','10/01/2017','11/012017', '13/01/2017','16/01/2017','17/01/2017','18/01/2017', '19/01/2017','20/01/2017','21/01/2017','23/01/2017', '24/01/2017') data <-data.frame(id,status,date) – nigel griffin Oct 27 '17 at 09:51
  • thanks a lot Dries, that seems to work after a little adjusting to my data set, just had to convert data set .csv to a data frame....until next time cheers – nigel griffin Oct 27 '17 at 12:39
  • the code works fine as the data frame I made a sample off, but when I put the code into my data set, it will not populate the diff column, everything else runs fine: just get N/A – nigel griffin Oct 27 '17 at 13:19
  • Looking at your output (which you shouldn't display by replacing my answer with your outcome), it has got all n=1. n=1, means that that id only has one value for status (0 I suppose) and so, there is no status change. Thus, diff=NA – Dries Oct 28 '17 at 16:19
  • Hiya Dries,don't what happened with your Answer, I have duplicates and for some reason overwrote, but today I can see your answer. What n=1 status change not sure what you mean by this sorry – nigel griffin Oct 30 '17 at 08:41