[R] merging dataframes

[Roger Bivand]

On Thu, 3 Nov 2005, Gavin Simpson wrote:

> Dear List,
> 
> I often have to merge two or more data frames containing unique row
> names but with some columns (names) common to the two data frames and
> some columns not common. This toy example will explain the kind of setup
> I am talking about:
> 
> mat1 <- as.data.frame(matrix(rnorm(20), nrow = 5))
> mat2 <- as.data.frame(matrix(rnorm(20), nrow = 4))
> rownames(mat1) <- paste("site", 1:5, sep="")
> rownames(mat2) <- paste("site", 6:9, sep="")
> names(mat1) <- paste("species", c(1,3,5,7), sep="")
> names(mat2) <- paste("species", c(2,3,4,7,9), sep="")
> mat1
> mat2
> 
> So sites (rows) are unique across both data frames, but there are only 7
> unique species (columns):
> 
> unique(c(names(mat1), names(mat2)))
> 
> merge(mat1, mat2, all = TRUE)
> 
> gives almost what I want, but it drops or looses the rownames()
> information from the two merged data frames, and it re-orders the rows
> so that one simply cannot write back the correct row names.
> 
> How might I go about merging two data frames as I have described, but
> preserve the row names and more importantly, keep the order of rows the
> same, so that rows from mat1 come before the rows of mat2?

merge(mat1, mat2, all = TRUE, sort=FALSE)

seems to fix the second question. The first is mentioned tangentially in 
the help page details if you are merging on row names, which you are not - 
maybe prepend to both a column called sites:

mat1a <- data.frame(sites=row.names(mat1), mat1)
mat2a <- data.frame(sites=row.names(mat2), mat2)
data.frame(merge(mat1a, mat2a, all = TRUE, sort=FALSE), row.names="sites")

is a bit long-winded, but gets you there.

Roger

> 
> Many thanks,
> 
> Gavin
> 

-- 
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: +47 55 95 93 55; fax +47 55 95 95 43
e-mail: Roger.Bivand at nhh.no