title: Pandas sum and totals using loc or iloc selections on multindex
body: <p>I am trying to update this table 1 (Level I, Level II, and Level III) by using pandas iloc or loc with the dataset referenced below. I am open to a better way than loc and iloc if there are suggestions.  </p>
  
  <p><em>Table 1</em></p>
  
  <p><a href="https://i.stack.imgur.com/1o0jK.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1o0jK.jpg" alt="enter image description here"></a></p>
  
  <p><strong><em>Example 1</em></strong></p>
  
  <p>If I want the table to update with new information for the 1102 selection for Pay Grade 13 and Level III I would use the following pd.loc code:</p>
  
  <pre><code>jobseries = '1102'
  result = df.loc[('3',jobseries),'13']
  print (result)
  14.0
  </code></pre>
  
  <p><strong><em>Example 2:</em></strong>  This works too.</p>
  
  <pre><code>jobseries = '1102'
  result = df.loc[('3',jobseries),'13'].sum()
  print (result)
  14
  </code></pre>
  
  <p>However, the challenge is when I need to select multiple indexes or multiple columns.</p>
  
  <p><strong>MULTIPLE ROWS</strong></p>
  
  <p>Now, if I want to update Table 1, Total for all Level I, instead of doing some type of df.isin, I need o do the following:     </p>
  
  <p>Example 3:</p>
  
  <pre><code>total = df.loc[('1',jobseries),'07'] + df.loc[('1',jobseries),'09'] + and so on...
  print (total)
  32
  </code></pre>
  
  <p>This works but I believe eventually will throw a RuntimeWarning:  invalid value encountered in long_scalars.  So its not the best way to do this. Any recommendations?</p>
  
  <p><strong>MULTIPLE COLUMNs</strong></p>
  
  <p>Now, if I want to update Table 1, # certs for Level I, Level II, and Level III, and any given grade level, I can't figure out the code.  I've tried the following but its throwing a keyError.  I've tried multiple ways of doing this and still cannot figure it out: </p>
  
  <p><strong><em>Example 4:</em></strong></p>
  
  <pre><code>jobseries = '1102'
  result = df.loc[('1','2','3',jobseries),'All']
  print (result)
  KeyError: "None of [[('1', '2', '3', '1102')]] are in the [index]"
  </code></pre>
  
  <p>This is strange because if I check my index the keyError confuses me.</p>
  
  <pre><code>df.index:
  
  MultiIndex(levels=[['1', '2', '3', 'All'], ['', '0301', '0341', '0342', '0343', '0501', '0560', '0810', '0850', '1101', '1102', '1105', '1106', '1109', '1145', '1146', '1170', '1410']],
             labels=[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3], [2, 3, 4, 6, 7, 9, 10, 11, 12, 13, 16, 17, 2, 8, 9, 10, 11, 1, 3, 4, 5, 9, 10, 11, 14, 15, 16, 0]],
             names=['Level', 'JobSeries'])
  </code></pre>
  
  <p>I've also tried df.xs:</p>
  
  <p><strong><em>Example 5:</em></strong></p>
  
  <pre><code>jobseries = '1102'
  result = df.xs(jobseries, level=1)
  print (result)
  
          01   07   08   09   11    12    13   14   15  All
  Level                                                    
  1      1.0  0.0  0.0  9.0  8.0   9.0   6.0  0.0  0.0   15
  2      0.0  0.0  0.0  4.0  6.0  12.0   6.0  1.0  0.0   13
  3      1.0  0.0  0.0  0.0  1.0  11.0  14.0  9.0  3.0   14
  </code></pre>
  
  <p><strong>CHANGES IN ROWS OR COLUMNS</strong></p>
  
  <p>The other challenge is that if the dataset changes and index or rows change the pd.loc and pd.iloc will throw a key error.  Is there anyway around this?</p>
  
  <p>df:</p>
  
  <pre><code>                 01   07   08    09    11    12    13   14   15  All
  Level JobSeries                                                      
  1     0341       0.0  0.0  0.0   0.0   0.0   1.0   0.0  0.0  0.0    1
        0342       0.0  0.0  1.0   0.0   0.0   0.0   0.0  0.0  0.0    1
        0343       0.0  0.0  0.0   0.0   0.0   2.0   0.0  0.0  0.0    2
        0560       0.0  0.0  0.0   1.0   0.0   0.0   0.0  0.0  0.0    1
        0810       0.0  0.0  0.0   0.0   1.0   0.0   0.0  0.0  0.0    1
        1101       0.0  0.0  0.0   0.0   0.0   1.0   0.0  0.0  0.0    1
        1102       1.0  0.0  0.0   9.0   8.0   9.0   6.0  0.0  0.0   15
        1105       0.0  7.0  3.0   5.0   0.0   0.0   0.0  0.0  0.0    9
        1106       0.0  2.0  0.0   0.0   0.0   0.0   0.0  0.0  0.0    2
        1109       0.0  0.0  0.0   0.0   2.0   0.0   0.0  0.0  0.0    2
        1170       0.0  0.0  0.0   0.0   1.0   2.0   0.0  0.0  0.0    3
        1410       0.0  0.0  0.0   0.0   0.0   0.0   1.0  0.0  0.0    1
  2     0341       0.0  0.0  0.0   0.0   0.0   0.0   1.0  0.0  0.0    1
        0850       0.0  0.0  0.0   0.0   0.0   0.0   1.0  0.0  0.0    1
        1101       0.0  0.0  0.0   0.0   0.0   0.0   1.0  1.0  0.0    2
        1102       0.0  0.0  0.0   4.0   6.0  12.0   6.0  1.0  0.0   13
        1105       0.0  0.0  1.0   0.0   0.0   0.0   0.0  0.0  0.0    1
  3     0301       0.0  0.0  0.0   0.0   0.0   0.0   0.0  0.0  1.0    1
        0342       0.0  0.0  0.0   0.0   0.0   0.0   0.0  0.0  1.0    1
        0343       0.0  0.0  0.0   0.0   0.0   0.0   0.0  0.0  1.0    1
        0501       0.0  0.0  0.0   0.0   0.0   0.0   1.0  0.0  0.0    1
        1101       0.0  0.0  0.0   0.0   0.0   0.0   2.0  1.0  0.0    2
        1102       1.0  0.0  0.0   0.0   1.0  11.0  14.0  9.0  3.0   14
        1105       0.0  1.0  0.0   0.0   0.0   0.0   0.0  0.0  0.0    1
        1145       0.0  0.0  0.0   0.0   0.0   0.0   1.0  0.0  0.0    1
        1146       0.0  0.0  0.0   0.0   0.0   0.0   0.0  1.0  0.0    1
        1170       0.0  0.0  0.0   0.0   0.0   1.0   1.0  0.0  0.0    2
  All              2.0  8.0  4.0  11.0  11.0  14.0  15.0  9.0  4.0   17
  </code></pre>
  
  <p><strong>Reference:</strong></p>
  
  <p>pd.loc:  <a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.loc.html" rel="nofollow noreferrer">https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.loc.html</a></p>
  
  <p>pd.xs: <a href="https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.xs.html" rel="nofollow noreferrer">https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.xs.html</a></p>
  
  <p>pd.iloc:  <a href="https://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-integer" rel="nofollow noreferrer">https://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-integer</a></p>