Combine multiple tables into one

classic Classic list List threaded Threaded
5 messages Options
AHJ
Reply | Threaded
Open this post in threaded view
|

Combine multiple tables into one

AHJ
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    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!
Reply | Threaded
Open this post in threaded view
|

Re: Combine multiple tables into one

arun kirshna


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.
Reply | Threaded
Open this post in threaded view
|

Re: Combine multiple tables into one

David Winsemius
add2blocks <- function(m1, m2) { res <- cbind(m1, matrix(0, dim(m2)[1], dim(m2)[2]) )
                    res <- rbind(res,  cbind( matrix(0, dim(m1)[1], dim(m1)[2]), 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-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Combine multiple tables into one

djmuseR
Isn't this just a block diagonal matrix?

library(pracma)
blkdiag(table1, table2)
     [,1] [,2] [,3] [,4]
[1,]    1    1    0    0
[2,]    1    2    0    0
[3,]    0    0    0    1
[4,]    0    0    0    4


Dennis

On Wed, May 1, 2013 at 5:41 PM, David Winsemius <[hidden email]> wrote:

> add2blocks <- function(m1, m2) { res <- cbind(m1, matrix(0, dim(m2)[1], dim(m2)[2]) )
>                     res <- rbind(res,  cbind( matrix(0, dim(m1)[1], dim(m1)[2]), 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-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[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.
Reply | Threaded
Open this post in threaded view
|

Re: Combine multiple tables into one

arun kirshna
#or

library(magic)
 adiag(table1,table2) #rownames are preserved
#     [,1] [,2] [,3] [,4]
#row1    1    1    0    0
#row2    1    2    0    0
#row3    0    0    0    1
#row4    0    0    0    4
A.K.

----- Original Message -----
From: Dennis Murphy <[hidden email]>
To: David Winsemius <[hidden email]>
Cc: arun <[hidden email]>; R help <[hidden email]>
Sent: Wednesday, May 1, 2013 10:47 PM
Subject: Re: [R] Combine multiple tables into one

Isn't this just a block diagonal matrix?

library(pracma)
blkdiag(table1, table2)
     [,1] [,2] [,3] [,4]
[1,]    1    1    0    0
[2,]    1    2    0    0
[3,]    0    0    0    1
[4,]    0    0    0    4


Dennis

On Wed, May 1, 2013 at 5:41 PM, David Winsemius <[hidden email]> wrote:

> add2blocks <- function(m1, m2) { res <- cbind(m1, matrix(0, dim(m2)[1], dim(m2)[2]) )
>                     res <- rbind(res,  cbind( matrix(0, dim(m1)[1], dim(m1)[2]), 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-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.


______________________________________________
[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.