title: How can I improve the performance of my data cleaning code that currently uses ddply by using data.table?
body: <p>I am trying to clean data using ddply but it is running very slowly on 1.3M rows. </p>
  
  <p>Sample code:</p>
  
  <pre><code>#Create Sample Data Frame
  num_rows &lt;- 10000
  df &lt;- data.frame(id=sample(1:20, num_rows, replace=T), 
                  Consumption=sample(-20:20, num_rows, replace=T), 
                  StartDate=as.Date(sample(15000:15020, num_rows, replace=T), origin = "1970-01-01"))
  df$EndDate &lt;- df$StartDate + 90
  #df &lt;- df[order(df$id, df$StartDate, df$Consumption),]
  #Are values negative? 
  # Needed for subsetting in ddply rows with same positive and negative values
  df$Neg &lt;- ifelse(df$Consumption &lt; 0, -1, 1)
  df$Consumption &lt;- abs(df$Consumption)
  </code></pre>
  
  <p>I have written a function to remove rows where there is a consumption value in one row that is identical but negative to a consumption value in another row (for the same id).</p>
  
  <pre><code>#Remove rows from a data frame where there is an equal but opposite consumption value
  #Should ensure only one negative value is removed for each positive one. 
  clean_negatives &lt;- function(x3){
    copies &lt;- abs(sum(x3$Neg))
    sgn &lt;- ifelse(sum(x3$Neg) &lt;0, -1, 1) 
    x3 &lt;- x3[0:copies,]
    x3$Consumption &lt;- sgn*x3$Consumption
    x3$Neg &lt;- NULL
    x3}
  </code></pre>
  
  <p>I then use ddply to apply that function to remove these erroneous rows in the data</p>
  
  <pre><code>ptm &lt;- proc.time()
  df_cleaned &lt;- ddply(df, .(id,StartDate, EndDate, Consumption),
                      function(x){clean_negatives(x)})
  proc.time() - ptm
  </code></pre>
  
  <p>I was hoping I could use data.table to make this go faster but I couldn't work out how to employ data.table to help. </p>
  
  <p>With 1.3M rows, so far it is taking my desktop all day to compute and still hasn't finished. </p>