title: use a transaction database to calculate the probability of an item appearing in a future transaction using R or SQL
body: <p>I have a database of transactions like in the table below</p>
  
  <pre><code>   user_id order_id    order_number                 product_name     n
     &lt;int&gt;    &lt;int&gt;           &lt;int&gt;                       &lt;fctr&gt;     &lt;int&gt;
      1      11878590           3                       Pistachios     1
      1      11878590           3                           Soda       1
      1      12878790           4                           Yogurt     1
      1      12878790           4                  Cheddar Popcorn     1
      1      12878790           4            Cinnamon Toast Crunch     1
      2      12878791           11          Milk Chocolate Almonds     1
      2      12878791           11                     Half &amp; Half     1
      2      12878791           11                   String Cheese     1
      11     12878792           19                      Whole Milk     1
      11     12878792           19                      Pistachios     1
      11     12878792           19                            Soda     1
      11     12878792           19               Paper Towel Rolls     1
  </code></pre>
  
  <p>The table has multiple users who each have multiple transactions. Some users only have 3 transactions, other users have 15, etc. This is all in one table.</p>
  
  <p>I'm trying to calculate a transition matrix for a markov model. I want to find the probability that an item will be in a new basket given that it was present in the previous basket of transactions. </p>
  
  <p>I want my final table to look something like this</p>
  
  <pre><code>user_id    product_name   probability_present    probability_absent
     1            Soda              .5                    .5
     1             Pistachios       .5                    .5
  </code></pre>
  
  <p>I'm having trouble figuring out how to get the data into a form so that I can calculate the probabilities and specifically coming up with a way to compare all of the t,t-1 combinations.</p>
  
  <p>I have code that I've written to get things into this form, but I'm stuck at this point. I've written my code using the dplyr R package, but I could translate something in SQL into the R code. I can post my code in R if it will be helpful, but it is pretty simple at this point as I just had to do a few joins to get the table into this shape.</p>
  
  <p>What else do I have to do to get the table/values that I'm trying to calculate?</p>