title: Transform data from cumulative for year to date to daily
body: <p>I am trying to convert data that show sales as cumulative total sales for the year to date. I want to show sales as they occur by day, not the cumulative figure. </p>
  
  <p>Here is an example of the data:</p>
  
  <pre><code>Product, Geography, Date, SalesThisYear
  Prod_1, Area_A, 20130501, 10
  Prod_2, Area_B, 20130501, 5
  Prod_1, Area_B, 20130501, 3
  Prod_1, Area_a, 20130502, 12
  Prod_2, Area_B, 20120502, 5
  Prod_1, Area_B, 20130502, 4
  ...
  </code></pre>
  
  <p>So the transformed data would look like:</p>
  
  <pre><code>Product, Geography, Date, SalesThisYear*, DailySales
  Prod_1, Area_A, 20130501, 10, 10
  Prod_2, Area_B, 20130501, 5, 5
  Prod_1, Area_B, 20130501, 3, 3
  Prod_1, Area_a, 20130502, 12, 2
  Prod_2, Area_B, 20120502, 3, 0
  Prod_1, Area_B, 20130502, 4, 1
  </code></pre>
  
  <p>This can then be used in later analysis. </p>
  
  <ul>
  <li>In case this makes any difference to the approach, I receive a new data file each day with the latest sales information. Therefore I need to append the new data to the existing data, and work out the daily sales figure. This is why I have kept the SalesThisYear field in the transformed data, so this field can be used to calculate the new DailySales figures when the next data file arrives. </li>
  </ul>
  
  <p>I'm new to <strong>R</strong> so working out what is the best way to solve this problem. I recognize I have two categorical fields, so was anticipating one approach could be used to factor on these fields. My overall thinking was to use a function and then an apply command to run the function against the entire data set. As an overview, my thinking is:</p>
  
  <p>(First load data file into <strong>R</strong>. Append second data file into <strong>R</strong> using rbind.)</p>
  
  <p>Create a function that does the following:</p>
  
  <ol>
  <li>Identify products and geographies using factor/similar</li>
  <li>Identify largest date and second largest date</li>
  <li>For each product and geography combination, find the SalesThisYear value for the appended data and the original data,using the date values obtained in step 2/ -- I'm thinking of using the subset function here. Subtract the two values: this becomes the
  DailySales value. (There would need to be error checking logic in case a new geography or product was introduced) </li>
  <li>Append this new DailySales value to the results.</li>
  </ol>
  
  <p>Data volume is about 120k rows per day, so the standard route of using a for loop in step 3. may not be advisable. </p>
  
  <p>Is the above approach appropriate? Or is there an unknown unknown I need to learn? :)</p>