HI,
I am not sure about your DataN1 column. If there is any identifier to
differentiate the comments (in this case 1,2,3), then it will easier to place
that in the correct column.
My previous solution is not helpful in situations like these:
dat2<-read.table(text="
Row_ID_N, Src_Row_ID, DataN1
1a, 1, This is comment 1
2a, 1, This is comment 2
3a, 2, This is comment 2
4a, 1, This is comment 3
",sep=",",header=TRUE,stringsAsFactors=FALSE)
dat3<-read.table(text="
Row_ID_N, Src_Row_ID, DataN1
1a, 1, This is comment 1
2a, 1, This is comment 2
3a, 2, This is comment 3
4a, 1, This is comment 3
5a, 2, This is comment 2
",sep=",",header=TRUE,stringsAsFactors=FALSE)
library(stringr)
library(plyr)
fun1<- function(data1,data2){
data2$DataN1<- str_trim(data2$DataN1)
res<- merge(data1,data2,by.x=1,by.y=2)
res1<- res[,-5]
res2<-
ddply(res1,.(Row_ID_CR,Data1,Data2,Data3),summarize,DataN1=list(DataN1))
Mx1<- max(sapply(res2[,5],length))
res3<- data.frame(res2[,-5],do.call(rbind,lapply(res2[,5],function(x){
indx<- as.numeric(gsub("[[:alpha:]]","",x))
x[match(seq(Mx1),indx)]
})),stringsAsFactors=FALSE)
colnames(res3)[grep("X",colnames(res3))]<-
paste0("DataComment",gsub("[[:alpha:]]","",colnames(res3)[grep("X",colnames(res3))]))
res3
}
fun1(dat1,dat2)
# Row_ID_CR Data1 Data2 Data3 DataComment1
#1 1 aa bb cc This is comment 1
#2 2 dd ee ff <NA>
# DataComment2 DataComment3
#1 This is comment 2 This is comment 3
#2 This is comment 2 <NA>
fun1(dat1,dat3)
# Row_ID_CR Data1 Data2 Data3 DataComment1
#1 1 aa bb cc This is comment 1
#2 2 dd ee ff <NA>
# DataComment2 DataComment3
#1 This is comment 2 This is comment 3
#2 This is comment 2 This is comment 3
A.K.
----- Original Message -----
From: arun <smartpink...@yahoo.com>
To: Shreya Rawal <rawal.shr...@gmail.com>
Cc: R help <r-help@r-project.org>
Sent: Monday, June 10, 2013 6:41 PM
Subject: Re: [R] Combining CSV data
Hi,
Try this:
dat1<-read.table(text="
Row_ID_CR, Data1, Data2, Data3
1, aa, bb, cc
2, dd, ee, ff
",sep=",",header=TRUE,stringsAsFactors=FALSE)
dat2<-read.table(text="
Row_ID_N, Src_Row_ID, DataN1
1a, 1, This is comment 1
2a, 1, This is comment 2
3a, 2, This is comment 1
4a, 1, This is comment 3
",sep=",",header=TRUE,stringsAsFactors=FALSE)
library(stringr)
dat2$DataN1<-str_trim(dat2$DataN1)
res<- merge(dat1,dat2,by.x=1,by.y=2)
res1<-res[,-5]
library(plyr)
res2<-ddply(res1,.(Row_ID_CR,Data1,Data2,Data3),summarize, DataN1=list(DataN1))
res2
# Row_ID_CR Data1 Data2 Data3
#1 1 aa bb cc
#2 2 dd ee ff
# DataN1
#1 This is comment 1, This is comment 2, This is comment 3
#2 This is comment 1
res3<-data.frame(res2[,-5],t(apply(do.call(rbind,res2[,5]),1,function(x)
{x[duplicated(x)]<-NA;x})))
colnames(res3)[grep("X",colnames(res3))]<-
paste0("DataComment",gsub("[[:alpha:]]","",colnames(res3)[grep("X",colnames(res3))]))
res3
# Row_ID_CR Data1 Data2 Data3 DataComment1
#1 1 aa bb cc This is comment 1
#2 2 dd ee ff This is comment 1
# DataComment2 DataComment3
#1 This is comment 2 This is comment 3
#2 <NA> <NA>
A.K.
----- Original Message -----
From: Shreya Rawal <rawal.shr...@gmail.com>
To: r-help@r-project.org
Cc:
Sent: Monday, June 10, 2013 4:38 PM
Subject: [R] Combining CSV data
Hello R community,
I am trying to combine two CSV files that look like this:
File A
Row_ID_CR, Data1, Data2, Data3
1, aa, bb, cc
2, dd, ee, ff
File B
Row_ID_N, Src_Row_ID, DataN1
1a, 1, This is comment 1
2a, 1, This is comment 2
3a, 2, This is comment 1
4a, 1, This is comment 3
And the output I am looking for is, comparing the values of Row_ID_CR and
Src_Row_ID
Output
ROW_ID_CR, Data1, Data2, Data3, DataComment1,
DataComment2, DataComment3
1, aa, bb, cc, This is
comment1, This is comment2, This is comment 3
2, dd, ee, ff, This is
comment1
I am a novice R user, I am able to replicate a left join but I need a bit
more in the final result.
Thanks!!
[[alternative HTML version deleted]]
______________________________________________
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.
______________________________________________
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.
