Hi Arun,
Sorry to bother you again with this. When I was trying it out over the
weekend with different data files I realized that its not exactly working
the way it should.
For this file B:
ROW_ID |SRC_ROW_ID |NOTE
1a |1 |Comment 1
2a |2 |Comment 1a
3a |2 |Comment 2a
4a |1 |Comment 2
5a |1 |Comment 3
I am getting the following output for the comment columns:
NOTE comment Comment.1a Comment.2a
Comment 1a Comment 1a|Comment 2a Comment 2 Comment 3
Comment 1 Comment 1|Comment 2| Comment 3 Comment 2 Comment 3
Where as the expected result should be:
NOTE comment Commentx Commentx Commentx
Comment 1a Comment 1a|Comment 2a Comment 1a Comment 2a
Comment 1 Comment 1|Comment 2| Comment 3 Comment 1 Comment 2
Comment 3
I am not really concerned about the column heading, they can all be same or
even blank. Just the values needs to be under different columns.
Thanks again!!
On Wed, Jun 12, 2013 at 9:25 AM, arun <smartpink...@yahoo.com> wrote:
> Hi,
> No problem.
> Arun
>
>
>
>
>
>
> ________________________________
> From: Shreya Rawal <rawal.shr...@gmail.com>
> To: arun <smartpink...@yahoo.com>
> Cc: R help <r-help@r-project.org>
> Sent: Wednesday, June 12, 2013 9:15 AM
> Subject: Re: [R] Combining CSV data
>
>
>
> Ah that makes sense. This looks perfect now. Thanks for your help on this!
>
>
>
>
> On Wed, Jun 12, 2013 at 9:10 AM, arun <smartpink...@yahoo.com> wrote:
>
> HI Shreya,
> >#Looks like you run the two line code as a single line.
> >
> >
> >result3<-
>
> >data.frame(result2[,-5],read.table(text=as.character(result2$comment),sep="|",fill=TRUE,na.strings=""),stringsAsFactors=FALSE)
> >
> >
> >colnames(result3)[5:7]<- paste0("DataComment",1:3)
> >
> > result3
> >
> ># 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.
> >
> >
> >
> >________________________________
> >From: Shreya Rawal <rawal.shr...@gmail.com>
> >To: arun <smartpink...@yahoo.com>
> >Cc: R help <r-help@r-project.org>; jim holtman <jholt...@gmail.com>
> >Sent: Wednesday, June 12, 2013 8:58 AM
> >
> >Subject: Re: [R] Combining CSV data
> >
> >
> >
> >Great, thanks Arun, but I seem to be running into this error. Not sure
> what did I miss.
> >
> >>
> result<-data.frame(final_ouput[,-5],read.table(text=as.character(final_output$comment),sep="|",fill=TRUE,na.strings=""),stringsAsFactors=FALSE)colnames(result)[5:7]<-paste0("DataComment",1:3)
> >Error: unexpected symbol in
> "result<-data.frame(final_ouput[,-5],read.table(text=as.character(final_output$comment),sep="|",fill=TRUE,na.strings=""),stringsAsFactors=FALSE)colnames"
> >
> >
> >
> >On Tue, Jun 11, 2013 at 5:09 PM, arun <smartpink...@yahoo.com> wrote:
> >
> >
> >>
> >>
> >>HI,
> >>You could use:
> >>result3<-
> data.frame(result2[,-5],read.table(text=as.character(result2$comment),sep="|",fill=TRUE,na.strings=""),stringsAsFactors=FALSE)
> >>colnames(result3)[5:7]<- paste0("DataComment",1:3)
> >>
> >>A.K.
> >>________________________________
> >>From: Shreya Rawal <rawal.shr...@gmail.com>
> >>To: arun <smartpink...@yahoo.com>
> >>Sent: Tuesday, June 11, 2013 4:22 PM
> >>
> >>Subject: Re: [R] Combining CSV data
> >>
> >>
> >>
> >>Hey Arun,
> >>
> >>I guess you could guide me with this a little bit. I have been working
> on the solution Jim suggested (and also because that I could understand it
> with my little knowledge of R :))
> >>
> >>So with these commands I am able to get the data in this format:
> >>
> >>> fileA <- read.csv(text = "Row_ID_CR, Data1, Data2, Data3
> >>+ 1, aa, bb, cc
> >>+ 2, dd, ee, ff", as.is = TRUE)
> >>>
> >>> fileB <- read.csv(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", as.is =
> TRUE)
> >>>
> >>> # get rid of leading/trailing blanks on comments
> >>> fileB$DataN1 <- gsub("^ *| *$", "", fileB$DataN1)
> >>>
> >>> # merge together
> >>> result <- merge(fileA, fileB, by.x = 'Row_ID_CR', by.y = "Src_Row_ID")
> >>>
> >>> # now partition by Row_ID_CR and aggregate the comments
> >>> result2 <- do.call(rbind,
> >>+ lapply(split(result, result$Row_ID_CR), function(.grp){
> >>+ cbind(.grp[1L, -c(5,6)], comment = paste(.grp$DataN1, collapse
> = '|'))
> >>+ })
> >>+ )
> >>
> >>Row_ID_CR Data1 Data2 Data3
> comment
> >>1 1 aa bb cc
> This is comment 1| This is comment 2| This
> is comment 3
> >>2 2 dd ee ff
> This is comment 1| This is Comment 2
> >>
> >>I can even split the last column by
> this: strsplit(as.character(result2$comment), split='\\|')
> >>
> >>[[1]]
> >>[1] "This is comment 1" "This is comment 2" " This is comment 3"
> >>
> >>[[2]]
> >>[1] "This is comment 1" "This is comment 2"
> >>
> >>
> >>but now I am not sure how to combine everything together. I guess by now
> you must have realized how new I am to R :)
> >>
> >>Thanks!!
> >>Shreya
> >>
> >>
> >>
> >>
> >>
> >>
> >>On Tue, Jun 11, 2013 at 1:02 PM, arun <smartpink...@yahoo.com> wrote:
> >>
> >>Hi,
> >>>If the dataset is like this with the comments in the order:
> >>>
> >>>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)
> >>>
> >>>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 1 #
> >>>
> >>>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){
> >>> c(x,rep(NA,Mx1-length(x)))
> >>>
> >>> })),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 This is
> comment 1
> >>># DataComment2 DataComment3
> >>>#1 This is comment 2 This is comment 3
> >>>#2 <NA> <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 This is
> comment 1
> >>> # DataComment2 DataComment3
> >>>#1 This is comment 2 This is comment 3
> >>>
> >>>#2 This is comment 2 <NA>
> >>>
> >>>
> >>>Otherwise, you need to provide an example that matches the real dataset.
> >>>A.K.
> >>>
> >>>________________________________
> >>>From: Shreya Rawal <rawal.shr...@gmail.com>
> >>>To: arun <smartpink...@yahoo.com>
> >>>Cc: R help <r-help@r-project.org>
> >>>Sent: Tuesday, June 11, 2013 12:22 PM
> >>>
> >>>Subject: Re: [R] Combining CSV data
> >>>
> >>>
> >>>
> >>>Hi Arun,
> >>>
> >>>Thanks for your reply. Unfortunately the Comments are just text in the
> real data. There is no way to differentiate based on the value of the
> Comments column. I guess because of that reason I couldn't get your
> solution to work properly. Do you think I can try it for a more general
> case where we don't merger/split the comments based on the values?
> >>>
> >>>Thanks for your help, I appreciate!
> >>>
> >>>
> >>>
> >>>On Mon, Jun 10, 2013 at 10:14 PM, arun <smartpink...@yahoo.com> wrote:
> >>>
> >>>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.
> >>>>
> >>>>
> >>>
> >>
> >
>
[[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.
