title: What is the R equivalent of SQL "SELECT * FROM table GROUP BY c1, c2"?
body: <p>I want to reduce my data frame (<strong>EDIT:</strong> in a cpu-efficient way) to rows with unique values of the pair c3, c4, while keeping all columns. In other words I want to transform my data frame</p>
  <pre><code>&gt; df &lt;- data.frame(c1=seq(7), c2=seq(4, 10), c3=c(&quot;A&quot;, &quot;B&quot;, &quot;B&quot;, &quot;C&quot;, &quot;B&quot;, &quot;A&quot;, &quot;A&quot;), c4=c(1, 2, 3, 3, 2, 2, 1))
    c1 c2 c3 c4
  1  1  4  A  1
  2  2  5  B  2
  3  3  6  B  3
  4  4  7  C  3
  5  5  8  B  2
  6  6  9  A  2
  7  7 10  A  1
  </code></pre>
  <p>to the data frame</p>
  <pre><code>  c1 c2 c3 c4
  1  1  4  A  1
  2  2  5  B  2
  3  3  6  B  3
  4  4  7  C  3
  6  6  9  A  2
  </code></pre>
  <p>where the values of c1 and c2 could be any value which occurs for a unique pair of c3, c4. Also the order of the resulting data frame is not of importance.</p>
  <p><strong>EDIT:</strong> My data frame has around 250 000 rows and 12 columns and should be grouped by 2 columns – therefore <strong>I need a CPU-efficient solution</strong>.</p>
  <h2>Working but unsatisfactory alternative</h2>
  <p>I solved this problem with</p>
  <pre><code>&gt; library(sqldf)
  &gt; sqldf(&quot;Select * from df Group By c3, c4&quot;)
  </code></pre>
  <p>but in order to speed up and parallelize my program I have to eliminate the calls to sqldf.</p>
  <p><strong>EDIT:</strong> Currently the sqldf solution clocks at 3.5 seconds. I consider this a decent time. The problem is that I cannot start various queries in parallel therefore I am searching for an alternative way.</p>
  <h2>Not working attempts</h2>
  <h3>duplicate()</h3>
  <pre><code>&gt; df[duplicated(df, by=c(&quot;c3&quot;, &quot;c4&quot;)),]
  [1] c1 c2 c3 c4
  &lt;0 rows&gt; (or 0-length row.names)
  </code></pre>
  <p>selects duplicate rows and does not select rows where only columns c3 and c4 are duplicates.</p>
  <h3>aggregate()</h3>
  <pre><code>&gt; aggregate(df, by=list(df$c3, df$c4))
  Error in match.fun(FUN) : argument &quot;FUN&quot; is missing, with no default
  </code></pre>
  <p>aggregate requires a function applied to all lines with the same values of c3 and c4</p>
  <h3>data.table's by</h3>
  <pre><code>&gt; library(data.table)
  &gt; dt &lt;- data.table(df)
  &gt; dt[,list(c1, c2) ,by=list(c3, c4)]
      c3 c4 c1 c2
  1:  A  1  1  4
  2:  A  1  7 10
  3:  B  2  2  5
  4:  B  2  5  8
  5:  B  3  3  6
  6:  C  3  4  7
  7:  A  2  6  9
  </code></pre>
  <p>does not kick out the rows which have non-unique values of c3 and c4, whereas</p>
  <pre><code>&gt; dt[ ,length(c1), by=list(c3, c4)]
     c3 c4 V1
  1:  A  1  2
  2:  B  2  2
  3:  B  3  1
  4:  C  3  1
  5:  A  2  1
  </code></pre>
  <p>does discard the values of c1 and c2 and reduces them to one dimension as specified with the passed function <code>length</code>.</p>