R_closest date

Try using ave(), as in
     dat1WithMean <- within(dat1, Mean <- ave(OBS_VALUE, PT_ID, DAYS_DIFF, FUN = mean))

Bill Dunlap
Spotfire, TIBCO Software
wdunlap tibco.com
-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf
Of arun
Sent: Thursday, September 06, 2012 12:21 PM
To: Weijia Wang
Cc: R help
Subject: Re: [R] R_closest date

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.

R_closest date

Thread (2 messages)