title: R: calculating the change in a value from one observation to the next within a data frame with cases and observations
body: <p>Hello I'm new to R and am having trouble completing what should be a fairly simple task. I'm sure there is a straightforward solution, but I couldn't find it online (including on StackOverflow) </p>
  
  <p>I have a dataframe with <code>Cases</code>, and <code>Observations</code> and a variable <code>Amount</code>. <code>Cases</code> are factors, <code>observations</code> are integers, and together they form an indices that so that the row containing <code>Case</code> = 3 and <code>Observation</code> = 4 corresponds to the 4th observation of the 3rd Case, and the row containing <code>Case</code> = 4 and <code>Observation</code> = 1 corresponds to the first observation of the 4th case. </p>
  
  <p>I am trying to write a script that calculates the change in Amount from one observation within each case to the next observation within the same case, and then stores that difference in a new column in the dataframe at the row associated with the first these two observation. So when I am done the new column will contain the change in the amount from the current rows observation to the next observation within the same case. </p>
  
  <p>the dataframe is of the form : </p>
  
  <pre><code>case &lt;- c(1,2,3,4,5,6,1,2,3,4,5,6,1,2,3,4)
  obs &lt;- c(rep(1,6),rep(2,6),rep(3,4))
  amount &lt;- c(0,2,12,1,0,20,1,2,22,2,1,50,5,2,100,28)
  d.example &lt;- data.frame(case,obs,amount)
  d.example$case &lt;- as.factor(d.example$case)
  </code></pre>
  
  <blockquote>
  <pre><code>case obs Amount 
  1    1   0
  2    1   2 
  3    1   12
  4    1   1
  5    1   0 
  6    1   20
  1    2   1
  2    2   2
  3    2   22
  4    2   2
  5    2   1
  6    2   50
  1    3   5
  2    3   2
  3    3   100
  4    3   28
  </code></pre>
  </blockquote>
  
  <p>Note: the data is not balanced each case can have a different number of observations </p>
  
  <p>The result should be ( for now I am placing <code>-1</code> in for NA) </p>
  
  <blockquote>
  <pre><code>case obs Amount deltaAmount
  1    1   0      1
  2    1   2      0
  3    1   12     10
  4    1   1      1
  5    1   0      1
  6    1   20     30
  1    2   1      4
  2    2   2      0
  3    2   22     78
  4    2   2      26
  5    2   1      -1
  6    2   50     -1
  1    3   5      -1
  2    3   2      -1
  3    3   100    -1
  4    3   28     -1
  </code></pre>
  </blockquote>
  
  <p>I've been attempting to do this using a nested for loops </p>
  
  <pre><code>deltaAmount &lt;- NULL
  deltaAmount &lt;- rep(-1, length(d$Case))
  d$deltaAmount &lt;- deltaAmount
  
  x &lt;- NULL
  y &lt;- NULL
  
  
  for( i in unique(d$Case)) {   # i is the case index
      x &lt;- NULL
  # set x equal to a vector containing all the observations for the ith case except the first observation 
      x &lt;- subset( unique(d$Observation[which(d$Case == i)]), unique( d$Observation[which(d$Case == i)]) &gt; 1)
  
      for( j in x ) { # j is the observation index (starts at 2 to avoid the error that would occur if we subtract a preceeding obsevation from the first observation)
  
          d$AmountRaised[which(d$Case == i) &amp; which(d$Observation == j)] - d$AmountRaised[which(d$Case == i) &amp; which(d$Observation == j-1)] -&gt; y
          y -&gt; d$deltaAmount[which( d$Case == i &amp; d$Observation == j-1 )] 
  
      }
  }
  </code></pre>
  
  <p>When I run this the command take a very long time to run. Almost as if it is stuck in an infinite loop ( I have yet to run this to its completion) when I terminate the program R states that I have more than 50 warning messages. They are all of the form </p>
  
  <p><code>1: In which(d$Case == i) &amp; which(d$Observation == j) :
    longer object length is not a multiple of shorter object length</code></p>
  
  <p>However the additional column is created and several values have been changed from <code>-1</code> to <code>0</code>. </p>
  
  <p>My data frame is large (770000 rows). </p>
  
  <p>Once I get this to work I will need to do the same thing except with dates using difftime(). I realize I am probably going about this the wrong way (ie there is probably a better way to do this without using nested for loops), but please keep in mind that I need to take the difference between sets of dates, if you suggest a different approach. </p>
  
  <p>Sorry for asking such a long question, I hope I made everything clear. </p>
  
  <p>Thankyou in advance for your help. </p>