title: find First/ Last observation value by group?
body: <p>I am trying to find the <strong>first/last observation by group</strong>. I tired both R and excel (because it is so slow in R so I tried excel). The <strong>excel</strong> took less than <strong>one</strong> <strong>second</strong>, but <strong>R</strong> took <strong>8 MINUTES!!!</strong>. The code logic for both are almost the same.</p>
  
  <p>The data is a <strong>panel data regarding purchasing fruit</strong>. The same shopper could buy multiple times at different time. <strong>I have 233,000 observations.</strong> Data is like(sorted by day shopper first and day):</p>
  
  <pre><code>    Day Shopper Choice
      1   A   apple
      2   A   apple
      1   B   Banana
      1   C   apple
      2   C   Banana
      3   C   apple
      1   D   berry
      2   D   berry
  </code></pre>
  
  <p>My r code for the first obseravtion. I want to tag the first observation in a group by indicator "1", a new column.</p>
  
  <pre><code> for (i in 1:n)
   { ifelse (dt$shopper[i+1]==dt$shopper[i],newcol[i+1]&lt;-0,newcol[i+1]&lt;-1)
    }
  </code></pre>
  
  <p>My excel code is: 
            if(B2&lt;>B1,1,0)</p>
  
  <p>I need the <strong>repurchase matrix</strong> given the same <strong>shopper</strong>. To define the  repurchase : the repurchase of the "1st purchase" is the "2nd purchase"; And the repurchase of the "2nd purchase" is the "3rd purchase" The last purchase has no repurchase. Sorry it sounds like a twister.
  so my solution is get two choice col and move the second col one row above so I can calculate the repurchase matrix by shopper/or aggregate.The desired output for <strong>tagging</strong> first obs by group should be as follow. With the  columns of choice and choice 2 and I can calculate the repurchase matrix by nrow.</p>
  
  <pre><code>Day Shopper Choice  tagging choice 2
   1  A   apple      0       *apple*
   2  A   apple      *apple*  0
   1  B   Banana     0        0
   1  C   apple      0        Banana
   2  C   Banana     Banana   apple 
   3  C   apple      apple    0
   1  D   berry      0        *berry*
   2  D   berry      *berry*  0
  </code></pre>
  
  <p>[<strong>update</strong>]. If there is only one purchase for this user, there is no repurchase. If the purchase is the last purchase of the user, there is no repurchase. So the final repurchase matrix by choice in this case is </p>
  
  <pre><code>        second  inside bracket are the probability  
  first   apple banana berry   
  apple   1 (0.5)  1      0
  banana  1        0 (0)  0
  berry   0        0      1 (1)
  </code></pre>
  
  <p>Below is how I calculate the repurchase matrix after tagging the first purchase of the user. i is the row (apple, banana, Berry etc) and j (apple, banana, Berry etc) is the column. [the speed is ok give the fact that I repurchase matrix is 40*40 after tagging and adding the seconding choice column)</p>
  
  <pre><code>    for (i in 1:n){
      for(j in 1:n){
      repurchase_matrix[i,j]=nrow(dt[dt[,1]==i&amp;dt[,2]==j,])}}
  </code></pre>