Pipe output of one data.frame to another using dplyr
<p>I have two data.frames--one look-up table that tells me a set products included  in a group. Each group has <em>at least</em> one product of Type 1 and Type 2. </p>

<p>The second data.frame tells me details about the transaction. Each transaction can have one of the following products:</p>

<p>a) Only product<strong>s</strong> of Type 1 from one of the groups</p>

<p>b) Only product<strong>s</strong> of Type 2 from one of the groups</p>

<p>c) Product of Type 1 and Type 2 <em>from the same group</em></p>

<p>For my analysis, I am interested in finding out c) above i.e. how many transactions have products of Type 1 <em>and</em> Type 2 (<em>from the same group</em>) sold. We will ignore the transaction altogether if Product of Type 1 and that of Type 2 from different groups that are sold in the same transaction. </p>

<p>Thus, each product of Type 1 or Type 2 MUST belong to the same group. </p>

<p>Here's my look up table:</p>

<pre><code>&gt; P_Lookup
   Group ProductID1 ProductID2
  Group1          A          1
  Group1          B          2
  Group1          B          3
  Group2          C          4
  Group2          C          5
  Group2          C          6
  Group3          D          7
  Group3          C          8
  Group3          C          9
  Group4          E         10
  Group4          F         11
  Group4          G         12
  Group5          H         13
  Group5          H         14
  Group5          H         15 
</code></pre>

<p>For instance, I won't have Product G and Product 15 in one transaction because they belong to different group.  </p>

<p>Here are the transactions:</p>

<pre><code>  TransactionID ProductID ProductType
             a1         A           1
             a1         B           1
             a1         1           2
             a2         C           1
             a2         4           2
             a2         5           2
             a3         D           1
             a3         C           1
             a3         7           2
             a3         8           2
             a4         H           1
             a5         1           2
             a5         2           2
             a5         3           2
             a5         3           2
             a5         1           2
             a6         H           1
             a6        15           2
</code></pre>

<p><strong>My Code:</strong></p>

<p>Now, I was able to write code using <code>dplyr</code> for shortlisting transactions from one group. However, I am not sure how I can vectorize my code for <em>all</em> groups.</p>

<p>Here's my code:</p>

<pre><code>P_Groups&lt;-unique(P_Lookup$Group)
Chosen_Group&lt;-P_Groups[5]

P_Group_Ind &lt;- P_Trans %&gt;%
group_by(TransactionID)%&gt;%
dplyr::filter((ProductID %in% unique(P_Lookup[P_Lookup$Group==Chosen_Group,]$ProductID1)) | 
(ProductID %in% unique(P_Lookup[P_Lookup$Group==Chosen_Group,]$ProductID2)) ) %&gt;%
mutate(No_of_PIDs = n_distinct(ProductType)) %&gt;%
mutate(Group_Name = Chosen_Group)

P_Group_Ind&lt;-P_Group_Ind[P_Group_Ind$No_of_PIDs&gt;1,]
</code></pre>

<p>This works well as long as I manually select each group i.e. by setting <code>Chosen_Group</code>. However, I am not sure how I can automate this. One way, I am thinking is to use for loop, but I know that the beauty of R is vectorization, so I want to stay away from using for loop. </p>

<p>I'd sincerely appreciate any help. I have spent almost two days on this. I looked at <a href="https://stackoverflow.com/questions/33611657/using-dplyr-in-for-loop-in-r">using dplyr in for loop in r</a>, but it seems this thread is talking about a different issue.</p>

<hr>

<p><strong>DATA:</strong>
Here's <code>dput</code> for <code>P_Trans</code>:</p>

<pre><code>structure(list(TransactionID = c("a1", "a1", "a1", "a2", "a2", 
"a2", "a3", "a3", "a3", "a3", "a4", "a5", "a5", "a5", "a5", "a5", 
"a6", "a6"), ProductID = c("A", "B", "1", "C", "4", "5", "D", 
"C", "7", "8", "H", "1", "2", "3", "3", "1", "H", "15"), ProductType = c(1, 
1, 2, 1, 2, 2, 1, 1, 2, 2, 1, 2, 2, 2, 2, 2, 1, 2)), .Names = c("TransactionID", 
"ProductID", "ProductType"), row.names = c(NA, 18L), class = "data.frame")
</code></pre>

<p>Here's <code>dput</code> for <code>P_Lookup</code>:</p>

<pre><code>structure(list(Group = c("Group1", "Group1", "Group1", "Group2", 
"Group2", "Group2", "Group3", "Group3", "Group3", "Group4", "Group4", 
"Group4", "Group5", "Group5", "Group5"), ProductID1 = c("A", 
"B", "B", "C", "C", "C", "D", "C", "C", "E", "F", "G", "H", "H", 
"H"), ProductID2 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 
14, 15)), .Names = c("Group", "ProductID1", "ProductID2"), row.names = c(NA, 
15L), class = "data.frame")
</code></pre>

<hr>

<p>Here's the <code>dput()</code> after adding a product to P_Trans that doesn't exist in the look-up table:</p>

<pre><code>structure(list(TransactionID = c("a1", "a1", "a1", "a2", "a2", 
"a2", "a3", "a3", "a3", "a3", "a4", "a5", "a5", "a5", "a5", "a5", 
"a6", "a6", "a7"), ProductID = c("A", "B", "1", "C", "4", "5", 
"D", "C", "7", "8", "H", "1", "2", "3", "3", "1", "H", "15", 
"22"), ProductType = c(1, 1, 2, 1, 2, 2, 1, 1, 2, 2, 1, 2, 2, 
2, 2, 2, 1, 2, 3)), .Names = c("TransactionID", "ProductID", 
"ProductType"), row.names = c(NA, 19L), class = "data.frame")
</code></pre>