[R] R_closest date

arun smartpink111 at yahoo.com
Thu Sep 6 21:20:54 CEST 2012



HI,

For your question to split by two variables, try this:
dat1<-read.table(text="
ID         PT_ID  BASE     IDX_DT   OBS_DATE OBS_VALUE CATEGORY DAYS_DIFF   LDL_BASE  rf
118485 9624295 164.2 2006-11-21 2009-02-17     157.6        2  819  2006-11-20 2.5
118486 9624295 164.2 2006-11-21 2006-09-30     208.0        2  -52  2006-11-20 2.5
118487 9624295 164.2 2006-11-21 2008-04-09     123.8        2  505  2006-11-20 2.5
118488 9624295 164.2 2006-11-21 2008-02-26      17.4        1  462  2006-11-20 2.5
118489 9624295 164.2 2006-11-21 2008-02-26     139.0        2  462  2006-11-20 2.5
118490 9624295 164.2 2006-11-21 2007-07-02     107.2        2  223  2006-11-25 2.5
118491 9624295 164.2 2006-11-21 2007-02-27      86.0        1   98  2006-11-25 2.5
118492 9624295 164.2 2006-11-21 2008-09-09     131.2        2  658  2006-11-25 2.5
118485 9624296 164.2 2006-11-21 2009-02-17     157.6        2  819  2006-11-26 2.5
118486 9624296 164.2 2006-11-21 2006-09-30     208.0        2  -52  2006-11-26 2.5
118487 9624296 164.2 2006-11-21 2008-04-09     123.8        2  505  2006-11-26 2.5
118488 9624296 164.2 2006-11-21 2008-02-26      17.4        1  462  2006-11-27 2.5
118489 9624296 164.2 2006-11-21 2008-02-26     139.0        2  462  2006-11-27 2.5
118490 9624296 164.2 2006-11-21 2007-07-02     107.2        2  223  2006-11-27 2.5
118491 9624296 164.2 2006-11-21 2007-02-27      86.0        1   98  2006-11-27 2.5
118492 9624296 164.2 2006-11-21 2008-09-09     131.2        2  658  2006-11-27 2.5
 ",sep="",header=TRUE)

dat2<-split(dat1,list(dat1$LDL_BASE,dat1$PT_ID))
dat3<-list()
 for(i in seq_along(dat2)){
 dat3[[i]]<-list()
 dat3[[i]]<-ddply(dat2[[i]],.(DAYS_DIFF),summarize,Mean=mean(OBS_VALUE))
 }
dat3
do.call(rbind,dat3)
#   DAYS_DIFF  Mean
#1        -52 208.0
#2        462  78.2
#3        505 123.8
#4        819 157.6
#5         98  86.0
#6        223 107.2
#7        658 131.2
#8        -52 208.0
#9        505 123.8
#10       819 157.6
#11        98  86.0
#12       223 107.2
#13       462  78.2
#14       658 131.2


#Not sure whether this will work or not in your huge dataset.  May be you can try lapply() also.  

A.K.
________________________________
From: Weijia Wang <wwang.nyu at gmail.com>
To: arun <smartpink111 at yahoo.com> 
Sent: Thursday, September 6, 2012 12:21 PM
Subject: Re: [R] R_closest date


Hi, Arun

Do you have idea about good package that split HUGE dataframe by two variables?

I was trying to use 'ddply' to calculate a mean of LDL-C values which had same date for every patient. 

Therefore, I need to break down my dataframe, first by patient ID, then by the date of the LDL-C, and finally calculate the mean, if there are multiple LDL-C on a same day.

The example is:

         PT_ID  BASE     IDX_DT   OBS_DATE OBS_VALUE CATEGORY DAYS_DIFF   LDL_BASE  rf
118485 9624295 164.2 2006-11-21 2009-02-17     157.6        2  819 days 2006-11-20 2.5
118486 9624295 164.2 2006-11-21 2006-09-30     208.0        2  -52 days 2006-11-20 2.5
118487 9624295 164.2 2006-11-21 2008-04-09     123.8        2  505 days 2006-11-20 2.5
118488 9624295 164.2 2006-11-21 2008-02-26      17.4        1  462 days 2006-11-20 2.5
118489 9624295 164.2 2006-11-21 2008-02-26     139.0        2  462 days 2006-11-20 2.5
118490 9624295 164.2 2006-11-21 2007-07-02     107.2        2  223 days 2006-11-20 2.5
118491 9624295 164.2 2006-11-21 2007-02-27      86.0        1   98 days 2006-11-20 2.5
118492 9624295 164.2 2006-11-21 2008-09-09     131.2        2  658 days 2006-11-20 2.5
         REDUCTION            GOAL  FAILURE
118485  0.04019488     NOT AT GOAL     FAIL
118486 -0.26674787 PRE NOT AT GOAL NOT FAIL
118487  0.24604141         AT GOAL NOT FAIL
118488  0.89403167         AT GOAL NOT FAIL
118489  0.15347138     NOT AT GOAL     FAIL
118490  0.34713764         AT GOAL NOT FAIL
118491  0.47624848         AT GOAL NOT FAIL
118492  0.20097442     NOT AT GOAL NOT FAIL

So, this patient has two LDL-C readings on '462 days', therefore, I want to get a mean of these 17.4 and 139.0.

'ddply' did give me a mean when running on a test dataframe, but when I used it on my dataframe with 200,000ish observations, the computer run for like 5 hours and return error. Do you have idea about other good function, that focuses on split and apply function, and rbind?

Best
Weijia


On Mon, Sep 3, 2012 at 2:08 AM, wwang.nyu <wwang.nyu at gmail.com> wrote:

That is actually a great idea, thanks again!
>
>Weijia Wang
>
>
>On Sep 2, 2012, at 12:12 PM, arun <smartpink111 at yahoo.com> wrote:
>
>> Hi,
>> No problem.
>>
>> If you use join() instead of merge(), the original order of columns may not get altered.
>>
>> dat3<-aggregate(DAYS_DIFF~PT_ID,data=dat1,min)
>> library(plyr)
>>  join(dat1,dat3,type="inner")
>> #Joining by: PT_ID, DAYS_DIFF
>>  # PT_ID     IDX_DT   OBS_DATE DAYS_DIFF OBS_VALUE CATEGORY
>> #1  4549 2002-08-21 2002-08-20        -1       183        2
>> #2  4839 2006-11-28 2006-11-28         0       179        2
>> A.K.
>>
>>
>>
>>
>>
>>
>> ________________________________
>> From: Weijia Wang <wwang.nyu at gmail.com>
>> To: arun <smartpink111 at yahoo.com>
>> Sent: Saturday, September 1, 2012 5:11 PM
>> Subject: Re: [R] R_closest date
>>
>>
>> Thank you Arun, for your help again.
>>
>> Best
>> ______________________________
>> WANG WEIJIA
>> Graudate Research and Teaching Assistant
>> Department of Environmental Medicine
>> New York University, School of Medicine
>> wwang.nyu at gmail.com
>>
>>
>>
>>
>> On Sep 1, 2012, at 5:04 PM, arun <smartpink111 at yahoo.com> wrote:
>>
>> Hi,
>>> Try this:
>>> dat1 <- read.table(text="
>>>   PT_ID    IDX_DT  OBS_DATE DAYS_DIFF OBS_VALUE CATEGORY
>>> 13  4549 2002-08-21 2002-08-20        -1      183        2
>>> 14  4549 2002-08-21 2002-11-14        85        91        1
>>> 15  4549 2002-08-21 2003-02-18      181        89        1
>>> 16  4549 2002-08-21 2003-05-15      267      109        2
>>> 17  4549 2002-08-21 2003-12-16      482        96        1
>>> 128  4839 2006-11-28 2006-11-28        0      179        2
>>> ", header=TRUE)
>>> dat3<-aggregate(DAYS_DIFF~PT_ID,data=dat1,min)
>>> merge(dat1,dat3)
>>> #  PT_ID DAYS_DIFF     IDX_DT   OBS_DATE OBS_VALUE CATEGORY
>>> #1  4549        -1 2002-08-21 2002-08-20       183        2
>>> #2  4839         0 2006-11-28 2006-11-28       179        2
>>>
>>> #or,
>>> dat2<- tapply(dat1$DAYS_DIFF,dat1$PT_ID,min)
>>> dat4<-data.frame(PT_ID=row.names(data.frame(dat2)),DAYS_DIFF=dat2)
>>>  row.names(dat4)<-1:nrow(dat4)
>>> merge(dat1,dat4)
>>> #  PT_ID DAYS_DIFF     IDX_DT   OBS_DATE OBS_VALUE CATEGORY
>>> #1  4549        -1 2002-08-21 2002-08-20       183        2
>>> #2  4839         0 2006-11-28 2006-11-28       179        2
>>> A.K.
>>>
>>>
>>>
>>>
>>>
>>> ----- Original Message -----
>>> From: WANG WEIJIA <wwang.nyu at gmail.com>
>>> To: "r-help at R-project.org" <r-help at r-project.org>
>>> Cc:
>>> Sent: Saturday, September 1, 2012 1:10 PM
>>> Subject: [R] R_closest date
>>>
>>> Hi,
>>>
>>> I have encountered an issue about finding a date closest to another date
>>>
>>> So this is how the data frame looks like:
>>>
>>>     PT_ID     IDX_DT   OBS_DATE DAYS_DIFF OBS_VALUE CATEGORY
>>> 13   4549 2002-08-21 2002-08-20        -1       183        2
>>> 14   4549 2002-08-21 2002-11-14        85        91        1
>>> 15   4549 2002-08-21 2003-02-18       181        89        1
>>> 16   4549 2002-08-21 2003-05-15       267       109        2
>>> 17   4549 2002-08-21 2003-12-16       482        96        1
>>> 128  4839 2006-11-28 2006-11-28         0       179        2
>>>
>>> I need to find, the single observation, which has the closest date of 'OBS_DATE' to 'IDX_DT'.
>>>
>>> For example, for 'PT_ID' of 4549, I need row 13, of which the OBS_DATE is just one day away from IDX_DT.
>>>
>>> I was thinking about using abs(), and I got this:
>>>
>>> baseline<- function(x){
>>> +
>>> +  #remove all uncessary variables
>>> +  baseline<- x[,c("PT_ID","DAYS_DIFF")]
>>> +
>>> +  #get a list of every unique ID
>>> +  uniqueID <- unique(baseline$PT_ID)
>>> +
>>> +  #make a vector that will contain the smallest DAYS_DIFF
>>> +  first <- rep(-99,length(uniqueID))
>>> +
>>> +  i = 1
>>> +  #loop through each unique ID
>>> +  for (PT_ID in uniqueID){
>>> +
>>> +  #for each iteration get the smallest DAYS_DIFF for that ID
>>> +  first[i] <- min(baseline[which(baseline$PT_ID==PT_ID),abs(baseline$DAYS_DIFF)])
>>> +
>>> +  #up the iteration counter
>>> +  i = i + 1
>>> +
>>> +  }
>>> +  #make a data frame with the lowest DAYS_DIFF and ID
>>> +  newdata <- data.frame(uniqueID,first)
>>> +  names(newdata) <- c("PT_ID","DAYS_DIFF")
>>> +
>>> +  #return the data frame containing the lowest GPI for each ID
>>> +  return(newdata)
>>> +  }
>>>
>>> ldl.b<-baseline(ldl) #get all baseline ldl patient ID, total 11368 obs, all unique#
>>>> Error in `[.data.frame`(baseline, which(baseline$PT_ID == PT_ID), abs(baseline$DAYS_DIFF)) :
>>>   undefined columns selected
>>>
>>> Can anyone help me in figuring out how to get the minimum value of the absolute value of DAYS_DIFF for unique ID?
>>>
>>> Thanks a lot
>>>     [[alternative HTML version deleted]]
>>>
>>> ______________________________________________
>>> R-help at r-project.org mailing list
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>>>
>>>
>




More information about the R-help mailing list