HI,
In cases like below:
DataA<- read.table(text="
ID,Status,Date1,Date2
1,A,3-Feb-01,15-May-01
1,B,15-May-01,16-May-01
1,A,16-May-01,3-Sep-01
1,B,3-Sep-01,13-Sep-01
1,C,13-Sep-01,26-Feb-04
2,A,9-Feb-01,24-May-01
2,B,24-May-01,25-May-01
2,A,25-May-01,16-Mar-02
2,A,6-Mar-02,18-Mar-02
2,A,14-Sep-01,6-Mar-02
",sep=",",header=TRUE,stringsAsFactors=FALSE)
library(stringr)
DataA[,3]<- str_trim(DataA[,3])
DataA[,4]<- str_trim(DataA[,4])
DataB<- read.table(text="
ID Date.Accident
1 3-Sep-01
1 20-Jan-05
1 26-Feb-04
2 25-May-01
2 6-Mar-02
",sep="",header=TRUE,stringsAsFactors=FALSE)
DataB[,2]<- str_trim(DataB[,2])
#Needed modification in the 2nd method:
dataNew2<-do.call(rbind,lapply(unique(DataA$ID),function(i){x1<-
DataA[DataA$ID==i,]; x2<- DataB[DataB$ID==i,];
do.call(rbind,lapply(seq_len(nrow(x2)),function(i) {x3<-
unlist(mapply(function(x,y) which(x==y), x1[,3:4],x2[i,2])); x4<-
if(length(x3)==2) x1[x3[which.min(x3)],!names(x1)%in%names(x3[which.max(x3)])]
else if(length(x3)==1) x1[x3,c("ID","Status",names(x3))] else NULL;
if(length(x4)>0) {colnames(x4)[3]<- colnames(DataB)[2]; x4} else NULL})) }))
library(plyr)
res2<- join(dataNew2,DataB,by=c("Date.Accident","ID"),type="right")
res2
# Date.Accident ID Status
#1 3-Sep-01 1 A
#2 20-Jan-05 1 <NA>
#3 26-Feb-04 1 C
#4 25-May-01 2 B
#5 6-Mar-02 2 A
Using the first method:
lst1<-lapply(seq_len(nrow(DataB)),function(i) {x1<-unlist(mapply(function(x,y)
which(x==y),DataA[,3:4],DataB[i,2]));x2<-if(length(x1)==2)
DataA[x1[which.min(x1)],!names(DataA)%in%names(x1[which.max(x1)])] else
if(length(x1)==1) DataA[x1,c("ID","Status",names(x1))] else NULL})
lst2<-lapply(lst1,data.frame)
lst2<-lst2[lapply(lst2,nrow)!=0]
library(plyr)
dataNew<-do.call(rbind,lapply(lst2,function(x) {colnames(x)[3]<-
colnames(DataB)[2];x}))
res<-join(dataNew,DataB,by=c("Date.Accident","ID"),type="right")
identical(res,res2)
#[1] TRUE
A.K.
----- Original Message -----
From: arun <smartpink...@yahoo.com>
To: farnoosh sheikhi <farnoosh...@yahoo.com>
Cc: R help <r-help@r-project.org>
Sent: Saturday, April 13, 2013 12:41 AM
Subject: Re: Comparison of Date format
Hi,
In the example you provided, it looks like the dates in Date2 happens first.
So, I changed it a bit.
DataA<- read.table(text="
ID,Status,Date1,Date2
1,A,3-Feb-01,15-May-01
1,B,15-May-01,16-May-01
1,A,16-May-01,3-Sep-01
1,B,3-Sep-01,13-Sep-01
1,C,13-Sep-01,26-Feb-04
2,A,9-Feb-01,24-May-01
2,B,24-May-01,25-May-01
2,A,25-May-01,16-Mar-02
2,A,6-Mar-02,18-Mar-02
2,A,14-Sep-01,6-Mar-02
",sep=",",header=TRUE,stringsAsFactors=FALSE)
library(stringr)
DataA[,3]<- str_trim(DataA[,3])
DataA[,4]<- str_trim(DataA[,4])
DataB<- read.table(text="
ID Date.Accident
1 3-Sep-01
1 20-Jan-05
1 26-Feb-04
2 6-Mar-02
",sep="",header=TRUE,stringsAsFactors=FALSE)
lst1<-lapply(seq_len(nrow(DataB)),function(i) {x1<-unlist(mapply(function(x,y)
which(x==y),DataA[,3:4],DataB[i,2]));x2<-if(length(x1)==2)
DataA[x1[which.min(x1)],!names(DataA)%in%names(x1[which.max(x1)])] else
if(length(x1)==1) DataA[x1,c("ID","Status",names(x1))] else NULL})
lst2<-lapply(lst1,data.frame)
lst2<-lst2[lapply(lst2,nrow)!=0]
lst2
#[[1]]
# ID Status Date2
#3 1 A 3-Sep-01
#[[2]]
# ID Status Date2
#5 1 C 26-Feb-04
#[[3]]
# ID Status Date1
#9 2 A 6-Mar-02
library(plyr)
dataNew<-do.call(rbind,lapply(lst2,function(x) {colnames(x)[3]<-
colnames(DataB)[2];x}))
res<-join(dataNew,DataB,by=c("Date.Accident","ID"),type="right")
res
# Date.Accident ID Status
#1 3-Sep-01 1 A
#2 20-Jan-05 1 <NA>
#3 26-Feb-04 1 C
#4 6-Mar-02 2 A
#or you can split by ID
lst1New<-lapply(unique(DataA$ID),function(i){x1<- DataA[DataA$ID==i,]; x2<-
DataB[DataB$ID==i,]; do.call(rbind,lapply(seq_len(nrow(x2)),function(i) {x3<-
unlist(mapply(function(x,y) which(x==y), x1[,3:4],x2[i,2])); x4<-
if(length(x3)==2) x1[x3[which.min(x3)],!names(x1)%in%names(x3[which.max(x3)])]
else if(length(x3)==1) x1[x3,c("ID","Status",names(x3))] else NULL})) })
lst1New
#[[1]]
# ID Status Date2
#3 1 A 3-Sep-01
#5 1 C 26-Feb-04
#[[2]]
# ID Status Date1
#9 2 A 6-Mar-02
dataNew1<- do.call(rbind,lapply(lst1New,function(x) {colnames(x)[3]<-
colnames(DataB)[2];x}))
res1<- join(dataNew1,DataB,by=c("Date.Accident","ID"),type="right")
res1
# Date.Accident ID Status
#1 3-Sep-01 1 A
#2 20-Jan-05 1 <NA>
#3 26-Feb-04 1 C
#4 6-Mar-02 2 A
A.K.
________________________________
From: farnoosh sheikhi <farnoosh...@yahoo.com>
To: "smartpink...@yahoo.com" <smartpink...@yahoo.com>
Sent: Friday, April 12, 2013 5:40 PM
Subject: Comparison of Date format
Hi there,
Hope all is well.
I have a complicated data and I need to create a new variable based on the date
and description of the data.
I really appreciate if you can help me.
Here is how data look like:
DataA
DataB
ID Status Date1 Date2
ID Date.Accident
Result
1 A 3-Feb-01 15-May-01
1 3-Sep-01
A
1 B 15-May-01 16-May-01
1 20-Jan-05
NA
1 A 16-May-01 3-Sep-01
1 B 3-Sep-01 13-Sep-01
1 C 13-Sep-01 26-Feb-04
2 A 9-Feb-01 24-May-01
2 6-Mar-02
A
2 B 24-May-01 25-May-01
2 A 25-May-01 6-Mar-02
2 A 6-Mar-02 18-Mar-02
I want to compare dataA to B for each ID. if Date 1 or Date 2 matches to
Date.Accident return the result as status in dataA as a new result in Data B.
The trick here is I have two dates that are matched, but I want the status of
the one that happen first. The sample size of each data is not the same.
I really appreciate your time and help.
Thanks.
______________________________________________
R-help@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.
