expand.grid function for data.frames in R

Question

I have 2 data.frames with the following columns.

1) A,B,C,D 2) E,F,G,H

What I'd like to do, is create a new data.frame, which has a row for each element of expand.grid(1[,B]2[,F]) and would keep all other columns and values associated with the values of col B and col F from the original data.frames

I am currently doing this using 2 for loops and this is creating a pretty large running time since the data.frames I'm dealing with are rather large.

Here is a screenshot of what I am looking for:

> aa
  A B C D
1 1 x 3 5
2 2 y 4 6
> bb
  E F  G  H
1 7 j  9 11
2 8 k 10 12
> cc
  A B C D E F  G  H
1 1 x 3 5 7 j  9 11
2 2 y 4 6 7 j  9 11
3 1 x 3 5 8 k 10 12
4 2 y 4 6 8 k 10 12

Answer 1

I think , you are looking for :

merge(aa,bb)

  A B C D E F  G  H
1 1 x 3 5 7 j  9 11
2 2 y 4 6 7 j  9 11
3 1 x 3 5 8 k 10 12
4 2 y 4 6 8 k 10 12

Answer 2

At one point I adapted the code in expand.grid to make it easier to group clumps of columns. Here's the code

#available from
#https://gist.github.com/MrFlick/00e2c589a2fa4b6d91f2

Expand.Grid<-function (..., stringsAsFactors = TRUE) 
{
    nargs <- length(args <- list(...))
    if (!nargs) 
        return(as.data.frame(list()))
    if (nargs == 0L) 
        return(as.data.frame(list()))
    Names <- function(x) {if(!is.null(names(x))) names(x) else rep("",length(x))}
    Paste <- function(...) {a<-list(...); r<-do.call("paste", c(list(sep="."),
        a[sapply(a, function(x) !is.character(x) || any(nzchar(x)))]));
        nx <- max(sapply(a, length))
        if (length(r)) return(rep(r, length.out=nx)) else return(rep("", nx))
    }
    contribcols <- sapply(args, function(x) ifelse(class(x)=="data.frame", ncol(x), 1))
    outargs <- sum(contribcols)
    cargs <- vector("list", outargs)
    nmc <- paste0("Var", seq.int(sum(contribcols)))
    nm <- unlist(lapply(seq_along(args), function(x) if(class(args[[x]])=="data.frame") {
        Paste(Names(args)[x], Names(args[[x]])) } else {Names(args)[x]}))
    if (is.null(nm)) 
        nm <- nmc
    else if (any(ng0 <- !nzchar(nm))) 
        nm[ng0] <- nmc[ng0]
    names(cargs) <- make.unique(make.names(nm))
    rep.fac <- 1L
    d <- sapply(args, function(x) ifelse(class(x)=="data.frame", nrow(x), length(x)))
    orep <- prod(d)
    if (orep == 0L) {
        i<-1
        for (a in seq_along(args)) {
            if (contribcols[a]==1) {
                args[[a]]=list(a)
            }
            for(j in seq_len(contribcols[a])) {
                cargs[[i]] <- args[[a]][[j]][FALSE]
                i <- i+1
            }
        }
    } else {    
        i<-1
        for (a in seq_along(args)) {
            nx <- d[a]
            orep <- orep/nx
            x<-args[[a]]
            if (contribcols[a]==1) {
                x<-list(x)
            }
            for(j in seq_len(contribcols[a])) {
                y <- x[[j]]
                y <- y[rep.int(rep.int(seq_len(nx), rep.int(rep.fac, 
                    nx)), orep)]
                if (stringsAsFactors && !is.factor(y) && is.character(y)) 
                    y <- factor(y, levels = unique(y))
                cargs[[i]] <- y
                i <- i+1
            }
            rep.fac <- rep.fac * nx
        }
    }
    rn <- .set_row_names(as.integer(prod(d)))
    structure(cargs, class = "data.frame", row.names = rn)
}

And then you could use it like

aa<-read.table(text="  A B C D
1 1 x 3 5
2 2 y 4 6", header=T)

bb<-read.table(text="  E F  G  H
1 7 j  9 11
2 8 k 10 12", header=T)

Expand.Grid(aa,bb)
#   A B C D E F  G  H
# 1 1 x 3 5 7 j  9 11
# 2 2 y 4 6 7 j  9 11
# 3 1 x 3 5 8 k 10 12
# 4 2 y 4 6 8 k 10 12

It also allows for additional combinations not directly applicable to this question such as

#combine any number of data.frames and atomic vectors
Expand.Grid(aa,other=1:2, bb)
#give columns a prefix
Expand.Grid(x=aa,y=aa)

Answer 3

You can expand.grid the row numbers:

myg <- expand.grid(aa=1:nrow(aa),bb=1:nrow(bb))
cbind(aa[myg$aa,],bb[myg$bb,])

The row names in the result are a little ugly:

    A B C D E F  G  H
1   1 x 3 5 7 j  9 11
2   2 y 4 6 7 j  9 11
1.1 1 x 3 5 8 k 10 12
2.1 2 y 4 6 8 k 10 12