title: pandas groupby operation with missing data
body: <p>In a pandas dataframe I have a column that looks like:</p>
  
  <pre><code>0         M
  1         E
  2         L
  3       M.1
  4       M.2
  5       M.3
  6       E.1
  7       E.2
  8       E.3
  9       E.4
  10      L.1
  11      L.2
  12    M.1.a
  13    M.1.b
  14    M.1.c
  15    M.2.a
  16    M.3.a
  17    E.1.a
  18    E.1.b
  19    E.1.c
  20    E.2.a
  21    E.3.a
  22    E.3.b
  23    E.4.a
  </code></pre>
  
  <p>I need to group all the value where the first elements are <code>E, M, or L</code> and then, for each group, I need to create a subgroup where the index is <code>1, 2, or 3</code> which will contain a record for each <code>lowercase letter</code> (a,b,c, ...)
  Potentially the solution should work for any number of levels concatenate elements (in this case the number of levels is 3 (eg: A.1.a))</p>
  
  <pre><code>0    1    2
  E    1    a
            b
            c
       2    a
       3    a
            b
       4    a
  L    1
       2
  M    1    a
            b
            c
       2    a
       3    a
  </code></pre>
  
  <p>I tried with:</p>
  
  <pre><code>df.groupby([0,1,2]).count()
  </code></pre>
  
  <p>But the result is missing the <em>L level</em> because it doesn't have records at the last sub-level</p>
  
  <p>A workaround is to add a dummy variable and then remove it ... like:</p>
  
  <pre><code>df[2][(df[0]=='L') &amp; (df[2].isnull()) &amp; (df[1].notnull())]='x'
  df = df.replace(np.nan,' ', regex=True)
  df.sort_values(0, ascending=False, inplace=True)
  newdf = df.groupby([0,1,2]).count()
  </code></pre>
  
  <p>which gives:</p>
  
  <pre><code>0    1    2
  E    1    a
            b
            c
       2    a
       3    a
            b
       4    a
  L    1    x
       2    x
  M    1    a
            b
            c
       2    a
       3    a
  </code></pre>
  
  <p>I then deal with the <code>dummy</code> entry <code>x</code> later in my code ...</p>
  
  <p>how can avoid this ackish way to use <code>groupby</code> ?</p>