[R] Combine tables with different dimensions

[Marc Schwartz]

In addition to Gabor's solution, there is one using base functions. This
involves some of the subtleties of converting t1 and t2 to data frames
explicitly using the matrix method, rather than the default table
method, avoiding the default expansion of the table.

We use the 'all' and 'sort' arguments in merge() to adjust the result to
include all matching and non-matching rows, also leaving the rows in the
order of the initial tables.

Next sort the output columns in numeric order, since the default output
will be based on sequencing the matching columns first (in this case, 5
and 7). We also convert to a matrix to allow setting duplicate rownames.

Finally, fixup the rownames.

Thus:

> res <- merge(as.data.frame.matrix(t1), as.data.frame.matrix(t2), 
               all = TRUE, sort = FALSE)

# Note the column order of 5, 7, 6, ...
> res
   5  7  6  8  9 10
1  5  0  0  5  0  0
2  0  0  5  0  5  0
3  0  5  0  0  0  5
4 15  0 NA NA NA NA
5  0 15 NA NA NA NA


# Sort the columns by numeric order
# and convert to matrix to allow duplicate
# rownames
> res <- as.matrix(res[, order(as.numeric(colnames(res)))])

> res
   5  6  7  8  9 10
1  5  0  0  5  0  0
2  0  5  0  0  5  0
3  0  0  5  0  0  5
4 15 NA  0 NA NA NA
5  0 NA 15 NA NA NA


# Now fixup rownames
> rownames(res) <- c(rownames(t1), rownames(t2))

> res
   5  6  7  8  9 10
0  5  0  0  5  0  0
1  0  5  0  0  5  0
2  0  0  5  0  0  5
0 15 NA  0 NA NA NA
1  0 NA 15 NA NA NA


I might also note, as Gabor points out that if you need the resultant
object as a table, you can use:

  as.table(res)

However, note that the default table print method sets NA values to "",
thus you would get:

> as.table(res)
   5  6  7  8  9 10
0  5  0  0  5  0  0
1  0  5  0  0  5  0
2  0  0  5  0  0  5
0 15     0
1  0    15

You can therefore explicitly use:

> print(as.table(res), na.print = "NA")
   5  6  7  8  9 10
0  5  0  0  5  0  0
1  0  5  0  0  5  0
2  0  0  5  0  0  5
0 15 NA  0 NA NA NA
1  0 NA 15 NA NA NA

Just depends upon your ultimate intent.

HTH,

Marc Schwartz


On Sun, 2006-05-07 at 10:14 -0400, Gabor Grothendieck wrote:
> Try this.  We first create a zoo object from the transpose of each
> table using the column names as the times in the time series.  Then
> merge the series, transpose them back and fix up the rownames.
> Finally, if needed, coerce the result to class "table".
> 
> library(zoo)
> z1 <- zoo(t(t1), as.numeric(colnames(t1)))
> z2 <- zoo(t(t2), as.numeric(colnames(t2)))
> out <- coredata(t(merge(z1, z2)))
> rownames(out) <- sub("[.].*", "", rownames(out)) # fix up rownames
> out # at this point result is of class "matrix"
> as.table(out)  # only need this if you need result to be class "table"
> 
> or if they are in a list, L:
> 
> library(zoo)
> L <- list(t1, t2)
> f <- function(x) zoo(t(x), as.numeric(colnames(x)))
> out <- coredata(t(do.call("merge", lapply(L, f))))
> rownames(out) <- sub("[.].*", "", rownames(out)) # fix up rownames
> out # at this point result is of class "matrix"
> as.table(out)  # only need this if you need result to be class "table"
> 
> On 5/7/06, Bernd Weiss <bernd.weiss at uni-koeln.de> wrote:
> > Suppose, I have two tables t1 and t2 which are constrcted as follows:
> >
> > x1 <- c(rep(0:2,10))
> > x2 <- c(rep(0:1,15))
> >
> > y1 <- c(rep(5:10,5))
> > y1 <- as.factor(y1)
> >
> > y2 <- c(rep(c(5,7),15))
> > y2 <- as.factor(y2)
> >
> > t1 <- table(x1,y1)
> > t2 <- table(x2,y2)
> >
> >
> > > t1
> >   y1
> > x1  5 6 7 8 9 10
> >  0 5 0 0 5 0  0
> >  1 0 5 0 0 5  0
> >  2 0 0 5 0 0  5
> > > t2
> >   y2
> > x2   5  7
> >  0 15  0
> >  1  0 15
> >
> > Now, I would like to merge[1] t1 and t2 so that each missing
> > categorie in t2 (compared to t1) is set to NA.
> >
> >     5  6  7  8  9  10
> >  0  5  0  0  5  0   0
> >  1  0  5  0  0  5   0
> >  2  0  0  5  0  0   5
> >  0 15 NA  0 NA NA  NA
> >  1  0 NA 15 NA NA  NA
> >
> > Of course, this is a toy example and I need to combine t1 to t15.
> >
> > It is important to keep the rownames as well as the colnames.
> >
> > Any help is appreciated.
> >
> > Bernd
> >
> >
> > [1] If I could via rbind, which, of course, is not possible,
> > dim(t1)[2]!=dim(t2)[2].
> >
> > ______________________________________________
> > R-help at stat.math.ethz.ch mailing list
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
> >
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html