title: How to normalize pandas multiindex dataframe?
body: <p>I have a questions on normalizing the counts in a grouped dataframe.</p>
  <p>My data looks like this:</p>
  <pre><code>import pandas as pd
  
  data = [{'system': 'S1', 'id': '1', 'output': ['apple', 'pear']},
      {'system': 'S1', 'id': '2', 'output': []},
      {'system': 'S1', 'id': '3', 'output': []},
      {'system': 'S2', 'id': '4', 'output': ['apple', 'grape']},
      {'system': 'S2', 'id': '5', 'output': ['apple']}] 
  
  df = pd.DataFrame(data) 
  </code></pre>
  <p>which looks like this in table format:</p>
  <pre><code>  system id          output
  0     S1  1   [apple, pear]
  1     S1  2              []
  2     S1  3              []
  3     S2  4  [apple, grape]
  4     S2  5         [apple]
  </code></pre>
  <p>How can I get normalized counts per output per system?</p>
  <p>It should look like this:</p>
  <pre><code>system  output  perc
  S1      apple   0.33
  S1      pear    0.33
  S2      apple   1.0
  S2      grape   0.5
   
  </code></pre>
  <p>Meaning that <code>apple</code> and <code>pear</code> appear in a third of all <code>S1</code> outputs, <code>apple</code> appears in all <code>S2</code> outputs, <code>grape</code> appears in half of the <code>S2</code> outputs.</p>
  <p>I tried to explode the outputs per system and get separate counts of IDs per system, but merging them loses the <code>output</code> column:</p>
  <pre><code>outputs = df.explode('output').groupby(['system', 'output']).count()                                                                                                                                        
  counts = df.groupby('system').agg('count').id
  pd.merge(outputs, counts, on=&quot;system&quot;)
  </code></pre>