Re: [R] Combine tables with different dimensions

From: Marc Schwartz <MSchwartz_at_mn.rr.com>
Date: Mon 08 May 2006 - 01:45:00 EST

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@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@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@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@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 Received on Mon May 08 01:48:33 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Mon 08 May 2006 - 04:10:01 EST.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.