Expand grid of all possible combinations within groups

Question

I am facing the following problem. I have a list of M&A transactions, each transactions includes data on (1) acquirer, (2) vendor, (3) target. The data is structured in a way that the relationship can be n:n:n and looks similar to the following:

dealid acquirer target vendor
1      FirmA    FirmB  FirmC
1      FirmD           FirmE
2      .....................

So the problem is that the rows within a deal have no meaning per se, so e.g., FirmD is also a co-acquirer for FirmB.

I now need to create all possible acquirer-target-vendor combinations within each dealid. I have managed to expand grids with the expand.grid function or simply via merge. However, I do not know how to expand a grid of all possible combinations within groups.

Answer 1

You could do that with dplyr and expand from tidyr.

df <- read.table(text="dealid acquirer target vendor
1      FirmA    FirmB  FirmC
1      FirmD    NA     FirmE
2      FirmA    NA     FirmC
2      FirmD    NA     FirmE
2      FirmG    FirmF  FirmE",header=TRUE,stringsAsFactors=FALSE)

library(dplyr);library(tidyr)
df%>%
  group_by(dealid)%>%
  expand(acquirer, target, vendor)

   dealid acquirer target vendor
    <int>    <chr>  <chr>  <chr>
 1      1    FirmA  FirmB  FirmC
 2      1    FirmA  FirmB  FirmE
 3      1    FirmD  FirmB  FirmC
 4      1    FirmD  FirmB  FirmE
 5      2    FirmA  FirmF  FirmC
 6      2    FirmA  FirmF  FirmE
 7      2    FirmD  FirmF  FirmC
 8      2    FirmD  FirmF  FirmE
 9      2    FirmG  FirmF  FirmC
10      2    FirmG  FirmF  FirmE