How do I get the date diff based on a different column

Question

I have a data fame with data like the following:

df <- read.table( text = "ID           Date              Value
001          2012-01-15        8.6
003          2000-05-03        10.1
001          2005-03-30        7.3
1002         2013-07-05        1.4" , h = TRUE , stringsAsFactors = FALSE )

#    ID       Date Value
#1    1 2012-01-15   8.6
#2    3 2000-05-03  10.1
#3    1 2005-03-30   7.3
#4 1002 2013-07-05   1.4

I am trying to get the initial date per ID and then get the number of months of each subsequent value for that ID. I can get the min date for each ID simply enough using aggregate but I am stumped on how to do the rest.

Any thoughts on how to accomplish this? I am brand new to R so any pointers would be most appreciated.

The number of months between the min and max date for each ID? Please show what you have tried thus far. — Simon O'Hanlon, Sep 13 '13 at 16:31
Welcome to SO! Thanks for the nice, little test data set. You make it much easier to help you if you please also provide your desired output. Cheers. — Henrik, Sep 13 '13 at 16:34

score 0 · Answer 1 · edited May 23 '17 at 12:11

0

I'd do something like below. For months instead of days, look at this question: Number of months between two dates

for(i in df$ID){   
  dates <- as.Date(as.vector(df[df$ID==i,"Date"]))   
  if(length(dates)>1){
    for(j in 2:length(dates)){
      days <- as.double(difftime(dates1[j],dates1[1],units="days"))
      //do something
    }   
  } 
}

edited May 23 '17 at 12:11

Community

1
1

answered Sep 13 '13 at 16:59

Ben

421
3
10

score 0 · Answer 2 · edited May 23 '17 at 10:25

I might combine a ddply and transform solution with the method used in this previous post, which would require installing and loading packages plyr and zoo. I'm not sure if including the months since the minimum date as 0 is useful to you, though.

# Load packages    
require(plyr)
require(zoo)

# Convert "Date" to a date
df$Date = as.Date(df$Date)
ddply(df, .(ID), transform, 
      mon.since.min = (as.yearmon(Date) - as.yearmon(min(Date)))*12 )

How do I get the date diff based on a different column

2 Answers2