title: cumsum in grouped data with dplyr
body: <p>I have a data frame <code>df</code> (which can be downloaded <a href="https://drive.google.com/file/d/0B0hmj2HaczvnV1l3WHVKWkdwam8/view?usp=sharing" rel="nofollow">here</a>) referred to a register of companies that looks something like this: </p>
  
  <pre><code>    Provider.ID        Local.Authority month year entry exit total
  1  1-102642676           Warwickshire    10 2010     2    0     2
  2  1-102642676                   Bury    10 2010     1    0     1
  3  1-102642676                   Kent    10 2010     1    0     1
  4  1-102642676                  Essex    10 2010     1    0     1
  5  1-102642676                Lambeth    10 2010     2    0     2
  6  1-102642676            East Sussex    10 2010     5    0     5
  7  1-102642676       Bristol, City of    10 2010     1    0     1
  8  1-102642676              Liverpool    10 2010     1    0     1
  9  1-102642676                 Merton    10 2010     1    0     1
  10 1-102642676          Cheshire East    10 2010     2    0     2
  11 1-102642676               Knowsley    10 2010     1    0     1
  12 1-102642676        North Yorkshire    10 2010     1    0     1
  13 1-102642676   Kingston upon Thames    10 2010     1    0     1
  14 1-102642676               Lewisham    10 2010     1    0     1
  15 1-102642676              Wiltshire    10 2010     1    0     1
  16 1-102642676              Hampshire    10 2010     1    0     1
  17 1-102642676             Wandsworth    10 2010     1    0     1
  18 1-102642676                  Brent    10 2010     1    0     1
  19 1-102642676            West Sussex    10 2010     1    0     1
  20 1-102642676 Windsor and Maidenhead    10 2010     1    0     1
  21 1-102642676                  Luton    10 2010     1    0     1
  22 1-102642676                Enfield    10 2010     1    0     1
  23 1-102642676               Somerset    10 2010     1    0     1
  24 1-102642676         Cambridgeshire    10 2010     1    0     1
  25 1-102642676             Hillingdon    10 2010     1    0     1
  26 1-102642676               Havering    10 2010     1    0     1
  27 1-102642676               Solihull    10 2010     1    0     1
  28 1-102642676                 Bexley    10 2010     1    0     1
  29 1-102642676               Sandwell    10 2010     1    0     1
  30 1-102642676            Southampton    10 2010     1    0     1
  31 1-102642676               Trafford    10 2010     1    0     1
  32 1-102642676                 Newham    10 2010     1    0     1
  33 1-102642676         West Berkshire    10 2010     1    0     1
  34 1-102642676                Reading    10 2010     1    0     1
  35 1-102642676             Hartlepool    10 2010     1    0     1
  36 1-102642676              Hampshire     3 2011     1    0     1
  37 1-102642676                   Kent     9 2011     0    1    -1
  38 1-102642676        North Yorkshire    12 2011     0    1    -1
  39 1-102642676         North Somerset    12 2012     2    0     2
  40 1-102642676                   Kent    10 2014     1    0     1
  41 1-102642676               Somerset     1 2016     0    1    -1
  </code></pre>
  
  <p>My goal is to create a variable that reflects the cumulative sum of the last variable (<code>total</code>) for each <code>Local.Authority</code> and each <code>year</code>. <code>total</code> is just the difference between <code>entry</code> and <code>exit</code>. I have tried to carry out this operation by applying <code>dplyr</code> on the following basis: </p>
  
  <pre><code>library(dplyr)
   df.1 = df %&gt;% group_by(Local.Authority, year) %&gt;%
    mutate(cum.total = cumsum(total)) %&gt;%
    arrange(year, month, Local.Authority)
  </code></pre>
  
  <p>Yielding the (wrong) result below: </p>
  
  <pre><code>&gt; df.1
  Source: local data frame [41 x 8]
  Groups: Local.Authority, year [41]
  
     Provider.ID  Local.Authority month  year entry  exit total cum.total
          &lt;fctr&gt;           &lt;fctr&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;     &lt;int&gt;
  1  1-102642676           Bexley    10  2010     1     0     1        35
  2  1-102642676            Brent    10  2010     1     0     1        25
  3  1-102642676 Bristol, City of    10  2010     1     0     1        13
  4  1-102642676             Bury    10  2010     1     0     1         3
  5  1-102642676   Cambridgeshire    10  2010     1     0     1        31
  6  1-102642676    Cheshire East    10  2010     2     0     2        17
  7  1-102642676      East Sussex    10  2010     5     0     5        12
  8  1-102642676          Enfield    10  2010     1     0     1        29
  9  1-102642676            Essex    10  2010     1     0     1         5
  10 1-102642676        Hampshire    10  2010     1     0     1        23
  ..         ...              ...   ...   ...   ...   ...   ...       ...
  </code></pre>
  
  <p>I have confirmed these results by checking levels in the variable <code>Local.Authority</code> that appear in various years (for example Kent):</p>
  
  <pre><code>&gt; check = df.1 %&gt;% filter(Local.Authority == "Kent")
  &gt; check
  Source: local data frame [3 x 8]
  Groups: Local.Authority, year [3]
  
    Provider.ID Local.Authority month  year entry  exit total cum.total
         &lt;fctr&gt;          &lt;fctr&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;     &lt;int&gt;
  1 1-102642676            Kent    10  2010     1     0     1         4
  2 1-102642676            Kent     9  2011     0     1    -1        42
  3 1-102642676            Kent    10  2014     1     0     1        44
  </code></pre>
  
  <p>Where it should be: </p>
  
  <pre><code>Provider.ID Local.Authority month  year entry  exit total cum.total
         &lt;fctr&gt;          &lt;fctr&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;     &lt;int&gt;
  1 1-102642676            Kent    10  2010     1     0     1         1
  2 1-102642676            Kent     9  2011     0     1    -1         0
  3 1-102642676            Kent    10  2014     1     0     1         1
  </code></pre>
  
  <p>Would anyone know what may be happening for getting these results out of the cumsum? Many thanks in advance.</p>