title: accelerating code execution in R to provide counting of all possible combinations of events belonging to certain ID
body: <p>I have a data set that has 3 columns , (ID , D , AE).</p>
  <pre><code>sample=data.frame(
  ID=c(1,1,1,2,2,2),
    D=c('a','b','c','a','c','c'),
    AE=c('m','x','w','y','m','f')
        )
  </code></pre>
  <p>I want to count the number of IDs where all possible combinations that consist of any combination between any two drugs within a certain ID and the AEs corresponding to that ID. please see the image to understand exactly what i mean <a href="https://i.stack.imgur.com/GdWOf.png" rel="nofollow noreferrer">enter image description here</a>.</p>
  <p>Someone could help me with a code that worked perfectly on the small dataset(sample). However the code would take several hours to work on the actual dataset that has (46000 unique IDs , 1600 unique D values and 3200 unique AE values). Actually i had to interrupt the session after 3 hours (while code execution) without getting any output.</p>
  <p>here is the code that i want to improve its performance to suit huge dataset:</p>
  <pre><code>enter code here
  </code></pre>
  <pre><code>library(tidyverse)
  
  combinations &lt;- sample %&gt;%
    mutate(D2 = D) %&gt;%
    group_by(ID) %&gt;%
    expand(crossing(D, D2, AE)) %&gt;% # Get all D1, D2, AE combinations within-ID
    filter(D2 &gt; D) %&gt;% # Deduplicate to unique combinations
    rename(D1 = D) %&gt;%
    ungroup() %&gt;%
    distinct(D1, D2, AE) # Deduplicate across IDs
  ​
  # For a given combination of D1, D2, AE; check how many IDs in sample have that combination
  count_ids &lt;- function(D1_val, D2_val, AE_val, data) {
    data %&gt;%
      group_by(ID) %&gt;%
      mutate(
        has_D1 = if_else(D1_val %in% D, &quot;D1&quot;, &quot;no D1&quot;),
        has_D2 = if_else(D2_val %in% D, &quot;D2&quot;, &quot;no D2&quot;),
        has_AE = if_else(AE_val %in% AE, &quot;AE&quot;, &quot;no AE&quot;)
      ) %&gt;% 
      group_by(has_D1, has_D2, has_AE) %&gt;%
      summarise(n_IDs = n_distinct(ID), .groups = &quot;drop&quot;) %&gt;%
      list(.)
  }
  ​
  combinations %&gt;%
    mutate(data = list(sample)) %&gt;%
    rowwise() %&gt;%
    mutate(data = count_ids(D1, D2, AE, data)) %&gt;% # Get the ID counts for each combination
    unnest(data) %&gt;%
    mutate(colname = str_c(has_D1, has_D2, has_AE, sep = &quot;,&quot;)) %&gt;% # Create a column name for each possibility of the combinations
    select(-starts_with(&quot;has_&quot;)) %&gt;%
    pivot_wider(names_from = colname, values_from = n_IDs, values_fill = 0L) # Spread out to wide format
  </code></pre>
  <p>I would really appreciate your help. Thanks in advance</p>