title: How to classify variable count on multiple condition in R
body: <p>I have following DF:</p>
  <p>DF&lt;-</p>
  <pre><code>ID       Var1         Var2              Type
  IR-1     A1           X1,X2,X3          New
  IR-2                                    Old
  IR-3     A2           X1,X4             New
  IR-4     A1           X1,X2,X3          New
  IR-4     A3           X1,X2,X3          New
  IR-4     A2           X1,X2,X3          New
  IR-5     A1           X1,X3             New
  IR-5     A2           X1,X3             New
  IR-5     A3           X1,X3             New
  IR-6                                    New
  IR-7     A2           X1,X2,X3          New
  IR-8                  X1,X2,X3          New
  IR-9     A2           X8                New
  IR-10                                   Old
  </code></pre>
  <p>Required Output</p>
  <pre><code>Variables   Excl_Count   %         A1   A2   A3   X1   X2   X3   X4   GT   XN   XP   X8   KP   KL  
  Total       10           100.00%   3    5    2    6    4    5    1    0    0    0    1    0    0
  Blank_Var1  4             40.00%   0    0    0    1    1    1    0    0    0    0    0    0    0
  Blank_Var2  3             30.00%   0    0    0    0    0    0    0    0    0    0    0    0    0
  Blank_Both  3             30.00%   0    0    0    0    0    0    0    0    0    0    0    0    0
  Blank_New   1             33.33%   0    0    0    0    0    0    0    0    0    0    0    0    0
  Blank_Old   2             66.66%   0    0    0    0    0    0    0    0    0    0    0    0    0
  Non_Blank   7             70.00%   3    5    2    6    4    5    1    0    0    0    1    0    0
  </code></pre>
  <p>By utilizing the <code>df</code>, I want to understand the distribution of <code>Var1</code> across <code>Var1</code> and <code>Var2</code> combine for a unique <code>ID</code>.</p>
  <p>Where,</p>
  <ul>
  <li>Total = Count of Unique <code>ID</code> and in horizontal row count of variable hit (i.e var1 &amp; var2) inclusive for those <code>ID</code></li>
  <li>Excl_count = If a particular <code>ID</code> has only 1 value as part of either <code>Var1</code> or <code>Var2</code></li>
  <li>Blank_Var1 = Count of unique <code>ID</code> where <code>Var1</code> is Null/NA/Blank or 0</li>
  <li>Blank_Var2 = Count of unique <code>ID</code> where <code>Var2</code> is Null/NA/Blank or 0</li>
  <li>Blank_Both = Count of unique <code>ID</code> where both <code>Var1</code> and <code>Var2</code> are either Null/NA/Blank or 0</li>
  <li>Blank_New = Count of unique <code>ID</code> where both <code>Var1</code> and <code>Var2</code> are either Null/NA/Blank or 0 having <code>Type</code> = New</li>
  <li>Blank_Old = Count of unique <code>ID</code> where both <code>Var1</code> and <code>Var2</code> are either Null/NA/Blank or 0 having <code>Type</code> = Old</li>
  <li>Non_Blank = Count of unique <code>ID</code> where either <code>Var1</code> or <code>Var2</code> are not Null/NA/Blank or 0</li>
  <li>A1 to KL are count corresponding to each row.</li>
  </ul>
  <p>Below is the code that I have tried but not working as expected -</p>
  <pre><code>library(RMySQL)
  library(dplyr)
  library(tidyverse)
      
  # Count Total
      Total &lt;- DF %&gt;%
        dplyr::group_by(ID) %&gt;%
        dplyr::mutate(count = n())
      # Excl_Count 
      Excl_Count  &lt;- DF %&gt;% 
        dplyr::group_by(ID) %&gt;%
        dplyr::summarize(&quot;Count&quot; = n_distinct(ID))
      # Blank_Var1
      Blank_Var1 &lt;- DF %&gt;% dplyr::filter(Var1 ==  '') %&gt;%
        dplyr::group_by(ID) %&gt;%
        dplyr::summarize(&quot;Count&quot; = sum(count))
      # Blank_Var2
      Blank_Var2 &lt;- DF %&gt;% dplyr::filter(Var2 ==  '') %&gt;%
        dplyr::group_by(ID) %&gt;%
        dplyr::summarize(&quot;Count&quot; = sum(count))
      # Blank_Both
      Blank_Both &lt;- DF %&gt;% dplyr::filter(Var1 ==  '' &amp; Var2 == '') %&gt;%
        dplyr::group_by(ID) %&gt;%
        dplyr::summarize(&quot;Count&quot; = sum(count))
      # Blank_New
      Blank_New &lt;- DF %&gt;% dplyr::filter(Var1 ==  '' &amp; Type == 'New') %&gt;%
        dplyr::group_by(ID) %&gt;%
        dplyr::summarize(&quot;Count&quot; = sum(count))
      # Blank_Old
      Blank_Old &lt;- DF %&gt;% dplyr::filter(Var1 ==  '' &amp; Type == 'Old') %&gt;%
        dplyr::group_by(ID) %&gt;%
        dplyr::summarize(&quot;Count&quot; = sum(count))
  </code></pre>
  <p>Dput&lt;-</p>
  <pre><code>structure(list(ID = c(&quot;IR-1&quot;, &quot;IR-2&quot;, &quot;IR-3&quot;, &quot;IR-4&quot;, &quot;IR-4&quot;, 
  &quot;IR-4&quot;, &quot;IR-5&quot;, &quot;IR-5&quot;, &quot;IR-5&quot;, &quot;IR-6&quot;, &quot;IR-7&quot;, &quot;IR-8&quot;, &quot;IR-9&quot;, 
  &quot;IR-10&quot;), Var1 = c(&quot;A1&quot;, &quot;&quot;, &quot;A2&quot;, &quot;A1&quot;, &quot;A2&quot;, &quot;A3&quot;, &quot;A1&quot;, &quot;A2&quot;, 
  &quot;A3&quot;, &quot;&quot;, &quot;A2&quot;, &quot;&quot;, &quot;A2&quot;, &quot;&quot;), Var2 = c(&quot;X1,X2,X3&quot;, &quot;&quot;, &quot;X1,X4&quot;, 
  &quot;X1,X2,X3&quot;, &quot;X1,X2,X3&quot;, &quot;X1,X2,X3&quot;, &quot;X1,X3&quot;, &quot;X1,X3&quot;, &quot;X1,X3&quot;, 
  &quot;&quot;, &quot;X1,X2,X3&quot;, &quot;X1,X2,X3&quot;, &quot;X8&quot;, &quot;&quot;), Type = c(&quot;New&quot;, &quot;Old&quot;, 
  &quot;New&quot;, &quot;New&quot;, &quot;New&quot;, &quot;New&quot;, &quot;New&quot;, &quot;New&quot;, &quot;New&quot;, &quot;New&quot;, &quot;New&quot;, 
  &quot;New&quot;, &quot;New&quot;, &quot;Old&quot;)), class = &quot;data.frame&quot;, row.names = c(NA, 
  -14L))
  </code></pre>