# Combine multiple tables into one Classic List Threaded 5 messages Open this post in threaded view
|

## Combine multiple tables into one

 This post has NOT been accepted by the mailing list yet. Hi,  I am trying to combine multiple tables into one, where the elements that are created as a result of the merge to be filled with zeroes. In other words, to go from this: #Create tables to combine row1 <- c(1,1) row2 <- c(1,2) row3 <- c(0,1) row4 <- c(0,4) table1 <- rbind(row1,row2) table2 <- rbind(row3, row4) table1           [,1] [,2] row1    1    1 row2    1    2 table2           [,1] [,2] row3    0    1 row4    0    4To this: #What the combined table should look like if things worked out newrow1 <- c(1,1,0,0) newrow2 <- c(1,2,0,0) newrow3 <- c(0,0,0,1) newrow4 <- c(0,0,0,4) combinedtable <- rbind(newrow1, newrow2, newrow3, newrow4) combinedtable                   [,1] [,2] [,3] [,4] newrow1    1    1    0    0 newrow2    1    2    0    0 newrow3    0    0    0    1 newrow4    0    0    0    4I tried merge() but it doesn't make any sense here, and I also tried to melt() the orginal data that table1 and table2 are created from to re-created a "combined" table from one step further back from this but couldn't get it to form the right table. If that would be an easier solution, here is the starting point that table1 and table2 are created from and also the create.table() function I wrote to create them: create.table <- function(a,b){     obs <- unique(c(a,b))     squ <- matrix(rep(0,length(obs)^2),ncol=length(obs))     for(i in 1:length(obs)){         for(j in 1:length(obs)){             squ[i,j] <- sum((a==obs[i])*(b==obs[j]), na.rm=TRUE)         }     }     squ } #Mock data sampleid <- c(1:5) Q1before <- c(0,0,1,0,1) Q1after <- c(0,1,0,0,1) Q2before <- c(1,0,0,0,0) Q2after <- c(0,0,0,0,0) #This is what my real data looks like #It may be easier to reformat this df to a format that could then use my create.table() function to get to the combined table endpoint? mydf <- as.data.frame(cbind(sampleid,Q1before, Q1after, Q2before, Q2after)) create.table(mydf\$Q1before, mydf\$Q1after) create.table(mydf\$Q2before, mydf\$Q2after)I hope at least some of this makes sense. Thank you for your input, you guys are always the best!
Open this post in threaded view
|

## Re: Combine multiple tables into one

 Hi, May be this helps: dat1<- as.data.frame(table1)  dat2<- as.data.frame(table2) names(dat2)<-c("V3","V4") library(plyr) res<-join(dat1,dat2,type="full")  res[is.na(res)]<- 0  res #  V1 V2 V3 V4 #1  1  1  0  0 #2  1  2  0  0 #3  0  0  0  1 #4  0  0  0  4  combinedtable<-as.matrix(res)  colnames(combinedtable)<- NULL  combinedtable #     [,1] [,2] [,3] [,4] #[1,]    1    1    0    0 #[2,]    1    2    0    0 #[3,]    0    0    0    1 #[4,]    0    0    0    4 A.K. >Hi, >  >I am trying to combine multiple tables into one, where the elements that are created as a result of the merge to be filled with zeroes. > >In other words, to go from this: > >#Create tables to combine >row1 <- c(1,1) >row2 <- c(1,2) >row3 <- c(0,1) >row4 <- c(0,4) >table1 <- rbind(row1,row2) >table2 <- rbind(row3, row4) >table1  >         [,1] [,2] >row1    1    1 >row2    1    2 >table2   >        [,1] [,2] >row3    0    1 >row4    0    4 > >To this: > >#What the combined table should look like if things worked out >newrow1 <- c(1,1,0,0) >newrow2 <- c(1,2,0,0) >newrow3 <- c(0,0,0,1) >newrow4 <- c(0,0,0,4) >combinedtable <- rbind(newrow1, newrow2, newrow3, newrow4) >combinedtable   >                [,1] [,2] [,3] [,4] >newrow1    1    1    0    0 >newrow2    1    2    0    0 >newrow3    0    0    0    1 >newrow4    0    0    0    4 > >I tried merge() but it doesn't make any sense here, and I also tried to melt() the orginal data  that table1 and table2 are created from to re-created >a "combined" table from one step further back from this but couldn't get it to form the right table. > >If that would be an easier solution, here is the starting point that table1 and table2 are created from and also the create.table() function I wrote to >create them: > >create.table <- function(a,b){  >   obs <- unique(c(a,b))   >  squ <- matrix(rep(0,length(obs)^2),ncol=length(obs))    > for(i in 1:length(obs)){     >    for(j in 1:length(obs)){   >          squ[i,j] <- sum((a==obs[i])*(b==obs[j]), na.rm=TRUE)   >      }   >  }   >  squ >} > >#Mock data >sampleid <- c(1:5) >Q1before <- c(0,0,1,0,1) >Q1after <- c(0,1,0,0,1) >Q2before <- c(1,0,0,0,0) >Q2after <- c(0,0,0,0,0) > >#This is what my real data looks like >#It may be easier to reformat this df to a format that could then use my create.table() function to get to the combined table endpoint? >mydf <- as.data.frame(cbind(sampleid,Q1before, Q1after, Q2before, Q2after)) >create.table(mydf\$Q1before, mydf\$Q1after) >create.table(mydf\$Q2before, mydf\$Q2after) > >I hope at least some of this makes sense. Thank you for your input, you guys are always the best! ______________________________________________ [hidden email] mailing list https://stat.ethz.ch/mailman/listinfo/r-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.
Open this post in threaded view
|

## Re: Combine multiple tables into one

 add2blocks <- function(m1, m2) { res <- cbind(m1, matrix(0, dim(m2), dim(m2)) )                     res <- rbind(res,  cbind( matrix(0, dim(m1), dim(m1)), m2) ) }  new <- add2block(table1, table2)  new #---------------      [,1] [,2] [,3] [,4] row1    1    1    0    0 row2    1    2    0    0 row3    0    0    0    1 row4    0    0    0    4 On May 1, 2013, at 12:37 PM, arun wrote: > > > Hi, > May be this helps: > dat1<- as.data.frame(table1) >  dat2<- as.data.frame(table2) > names(dat2)<-c("V3","V4") > library(plyr) > res<-join(dat1,dat2,type="full") >  res[is.na(res)]<- 0 >  res > #  V1 V2 V3 V4 > #1  1  1  0  0 > #2  1  2  0  0 > #3  0  0  0  1 > #4  0  0  0  4 >  combinedtable<-as.matrix(res) >  colnames(combinedtable)<- NULL >  combinedtable > #     [,1] [,2] [,3] [,4] > #[1,]    1    1    0    0 > #[2,]    1    2    0    0 > #[3,]    0    0    0    1 > #[4,]    0    0    0    4 > A.K. > > > >> Hi, >> >  >I am trying to combine multiple tables into one, where the > elements that are created as a result of the merge to be filled with > zeroes. >> >> In other words, to go from this: >> >> #Create tables to combine >> row1 <- c(1,1) >> row2 <- c(1,2) >> row3 <- c(0,1) >> row4 <- c(0,4) >> table1 <- rbind(row1,row2) >> table2 <- rbind(row3, row4) >> table1 >  >         [,1] [,2] >> row1    1    1 >> row2    1    2 >> table2 >   >        [,1] [,2] >> row3    0    1 >> row4    0    4 >> >> To this: >> >> #What the combined table should look like if things worked out >> newrow1 <- c(1,1,0,0) >> newrow2 <- c(1,2,0,0) >> newrow3 <- c(0,0,0,1) >> newrow4 <- c(0,0,0,4) >> combinedtable <- rbind(newrow1, newrow2, newrow3, newrow4) >> combinedtable >   >                [,1] [,2] [,3] [,4] >> newrow1    1    1    0    0 >> newrow2    1    2    0    0 >> newrow3    0    0    0    1 >> newrow4    0    0    0    4 >> >> I tried merge() but it > doesn't make any sense here, and I also tried to melt() the orginal data > that table1 and table2 are created from to re-created >a "combined" > table from one step further back from this but couldn't get it to form > the right table. >> >> If that would be an easier solution, here is the starting point > that table1 and table2 are created from and also the create.table() > function I wrote to >create them: >> >> create.table <- function(a,b){ >  >   obs <- unique(c(a,b)) >   >  squ <- matrix(rep(0,length(obs)^2),ncol=length(obs)) >    > for(i in 1:length(obs)){ >     >    for(j in 1:length(obs)){ >   >          squ[i,j] <- sum((a==obs[i])*(b==obs[j]), na.rm=TRUE) >   >      } >   >  } >   >  squ >> } >> >> #Mock data >> sampleid <- c(1:5) >> Q1before <- c(0,0,1,0,1) >> Q1after <- c(0,1,0,0,1) >> Q2before <- c(1,0,0,0,0) >> Q2after <- c(0,0,0,0,0) >> >> #This is what my real data looks like >> #It may be easier to reformat this df to a format that could then > use my create.table() function to get to the combined table endpoint? >> mydf <- as.data.frame(cbind(sampleid,Q1before, Q1after, Q2before, Q2after)) >> create.table(mydf\$Q1before, mydf\$Q1after) >> create.table(mydf\$Q2before, mydf\$Q2after) >> >> I hope at least some of this makes sense. Thank you for your input, you guys are always the best! > > ______________________________________________ > [hidden email] 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. David Winsemius Alameda, CA, USA ______________________________________________ [hidden email] mailing list https://stat.ethz.ch/mailman/listinfo/r-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.