[R] Combining CSV data

[arun]

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.shreya at gmail.com>
To: r-help at 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 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.