Creating a Data Frame of the Number of Pairings based on an Event Column
I have a data frame that contains one column which indicates an event ID. There is another column that indicates the products used in that event. Each product would only be used one time for an event and each event contains at least one product. I would like to know how many times each product is used with every other product. Some sample data is below:
Product 1 and Product 2 occur in the same event twice (Event 1 and Event 4). So I would want to return a '2' for that match. Product 1 and Product 7 never occur in the same event, so I'd want to return a 0 for that pair. For 'matches' between the same item, I am comfortable returning the total number of times that product is used.
There are two formats that are possible and I don't have a preference for which I'd like to see returned.
A short and fat data frame that has the products running across the tops as column headers and the side as row headers. The body of this data frame would be populated by the number of matches.
A long, narrow data frame where there are two columns that will serve to represent all possible combinations of Product pairings and then a third column representing the number of times they match.
I have been experimenting with expand.grid with nothing to show for it.