title: Remove duplicates after converting to long format
body: <p>I'm working on a project for my master's thesis in R, and had to convert some data from wide to long format as I had Submission_Ticker, Submission_Ticker_2 to Submission_Ticker_4 and Comment_Ticker_1 to Comment_Ticker_5, however after converting to long format I have a bunch of duplicates, exemplified in the table below.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Submission_Ticker</th>
  <th>Comment_Ticker</th>
  <th>ID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>TSLA</td>
  <td>TSLA</td>
  <td>1</td>
  </tr>
  <tr>
  <td>TSLA</td>
  <td>NA</td>
  <td>1</td>
  </tr>
  <tr>
  <td>NA</td>
  <td>TSLA</td>
  <td>1</td>
  </tr>
  <tr>
  <td>AAPL</td>
  <td>AAPL</td>
  <td>2</td>
  </tr>
  <tr>
  <td>AAPL</td>
  <td>AMD</td>
  <td>2</td>
  </tr>
  <tr>
  <td>AAPL</td>
  <td>NVDA</td>
  <td>2</td>
  </tr>
  <tr>
  <td>AMD</td>
  <td>AAPL</td>
  <td>2</td>
  </tr>
  <tr>
  <td>AMD</td>
  <td>AMD</td>
  <td>2</td>
  </tr>
  <tr>
  <td>AMD</td>
  <td>NVDA</td>
  <td>2</td>
  </tr>
  <tr>
  <td>TSM</td>
  <td>AAPL</td>
  <td>2</td>
  </tr>
  <tr>
  <td>TSM</td>
  <td>AMD</td>
  <td>2</td>
  </tr>
  <tr>
  <td>TSM</td>
  <td>NVDA</td>
  <td>2</td>
  </tr>
  <tr>
  <td>NA</td>
  <td>AAPL</td>
  <td>2</td>
  </tr>
  <tr>
  <td>NA</td>
  <td>AMD</td>
  <td>2</td>
  </tr>
  <tr>
  <td>NA</td>
  <td>NVDA</td>
  <td>2</td>
  </tr>
  <tr>
  <td>GOOG</td>
  <td>NA</td>
  <td>3</td>
  </tr>
  <tr>
  <td>NA</td>
  <td>GOOG</td>
  <td>4</td>
  </tr>
  <tr>
  <td>NA</td>
  <td>TSLA</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The most common occurance is the one with ID 1, where I'd only like to keep the first row. For the second ID I'd like to remove all occurances with NA. And for ID 3 and 4, i'd obviously like to keep them as is, as they're unique occurances. Therefore I can't really just filter out all NA's in the data, and different combinations as in ID 2. Is there an efficient way to filter out the unwanted rows from the dataset? Working on around 8 million rows on a not too powerful computer, but if it has to be done through a loop I can live with it!</p>
  <p>Edit: Here is the example dataframe. Apologies for the simplifications above.</p>
  <pre><code>
  structure(list(Com_ID = c(11298583L, 11298584L, 11298588L, 11298589L, 
  11298590L, 11298591L, 11298592L, 11298593L, 11298594L, 11298595L, 
  11298596L), ID = c(&quot;idazox&quot;, &quot;idazox&quot;, &quot;idb8eu&quot;, &quot;idb8eu&quot;, &quot;idb8eu&quot;, 
  &quot;idb8eu&quot;, &quot;idb8eu&quot;, &quot;idb8eu&quot;, &quot;idb8eu&quot;, &quot;idb8eu&quot;, &quot;idb8eu&quot;), 
      Submission_Text_Ticker = c(&quot;tsla&quot;, &quot;tsla&quot;, &quot;tsla&quot;, &quot;tsla&quot;, 
      &quot;tsla&quot;, &quot;tsla&quot;, &quot;tsla&quot;, &quot;tsla&quot;, &quot;tsla&quot;, &quot;tsla&quot;, &quot;tsla&quot;), 
      Submission_Text_Ticker_2 = c(NA_character_, NA_character_, 
      NA_character_, NA_character_, NA_character_, NA_character_, 
      NA_character_, NA_character_, NA_character_, NA_character_, 
      NA_character_), Submission_Title_Ticker = c(NA, NA, &quot;amd&quot;, 
      &quot;amd&quot;, &quot;amd&quot;, &quot;amd&quot;, &quot;amd&quot;, &quot;amd&quot;, &quot;amd&quot;, &quot;amd&quot;, &quot;amd&quot;), 
      Submission_Title_Ticker_2 = c(NA, NA, &quot;plug&quot;, &quot;plug&quot;, &quot;plug&quot;, 
      &quot;plug&quot;, &quot;plug&quot;, &quot;plug&quot;, &quot;plug&quot;, &quot;plug&quot;, &quot;plug&quot;), Comment_Ticker = c(NA, 
      NA, NA, &quot;rkt&quot;, NA, NA, NA, &quot;amd&quot;, NA, NA, NA), Comment_Ticker_2 = c(NA, 
      NA, NA, NA, NA, NA, NA, &quot;plug&quot;, NA, NA, NA), Comment_Ticker_3 = c(NA, 
      NA, NA, NA, NA, NA, NA, &quot;tsla&quot;, NA, NA, NA), Comment_Ticker_4 = c(NA, 
      NA, NA, NA, NA, NA, NA, &quot;rkt&quot;, NA, NA, NA), Comment_Ticker_5 = c(NA_character_, 
      NA_character_, NA_character_, NA_character_, NA_character_, 
      NA_character_, NA_character_, NA_character_, NA_character_, 
      NA_character_, NA_character_)), row.names = 858240:858250, class = &quot;data.frame&quot;)
  </code></pre>
  <p>What I really want is for each Com_ID to have all occurances of Submission_Text_Ticker, Submission_Title_Ticker and Comment_Ticker. But preferably only once, so it could be multiple rows, but only each occurance once per Com_ID. So for example if Sub_Text_Ticker 1&amp;2 = AAPL, TSLA, Sub_Title_Ticker 1&amp;2 = TSLA, NA, and Comment_Ticker 1:5 = TSLA, AAPL, NVDA, NA, NA; I would want three rows, where the combinations doesn't really matter as long as the tickers are percerved with the appropriate IDs. In the example above the output could be:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Submission_Title_Ticker</th>
  <th>Submission_Text_Ticker</th>
  <th>Comment_Ticker</th>
  <th>ID</th>
  <th>Com_ID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>TSLA</td>
  <td>AAPL</td>
  <td>NVDA</td>
  <td>im1nq3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>NA</td>
  <td>TSLA</td>
  <td>AAPL</td>
  <td>im1nq3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>NA</td>
  <td>NA</td>
  <td>TSLA</td>
  <td>im1nq3</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>