Calculating subtotals in pandas pivot_table with MultiIndex
<p>I have the following raw data, in a dataframe:</p>

<pre><code>   BROKER    VENUE  QUANTITY
0  BrokerA  Venue_1       300
1  BrokerA  Venue_2       400
2  BrokerA  Venue_2      1400
3  BrokerA  Venue_3       800
4  BrokerB  Venue_2       500
5  BrokerB  Venue_3      1100
6  BrokerC  Venue_1      1000
7  BrokerC  Venue_1      1200
8  BrokerC  Venue_2     17000
</code></pre>

<p>I want to do some summarization of the data to see how much each broker sent to each venue, so I created a pivot_table:</p>

<pre><code>pt = df.pivot_table(index=['BROKER', 'VENUE'], values=['QUANTITY'], aggfunc=np.sum)
</code></pre>

<p>Result, as expected:</p>

<pre><code>                 QUANTITY
BROKER  VENUE            
BrokerA Venue_1     300.0
        Venue_2    1800.0
        Venue_3     800.0
BrokerB Venue_2     500.0
        Venue_3    1100.0
BrokerC Venue_1    2200.0
        Venue_2   17000.0
</code></pre>

<p>I also want how much was sent to each broker overall. and show it in this same table. I can get that information by typing <code>df.groupby('BROKER').sum()</code>, but how can I add this to my pivot table as a column named, say, BROKER_TOTAL?</p>

<p>Note: This question is similar but seems to be on an older version, and my best guess at adapting it to my situation didn't work: <a href="http://stackoverflow.com/questions/15570099/pandas-pivot-tables-row-subtotals">Pandas Pivot tables row subtotals</a></p>

<p>You can create <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.MultiIndex.from_arrays.html" rel="nofollow"><code>MultiIndex.from_arrays</code></a> for <code>df1</code>, <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow"><code>concat</code></a> it to <code>pt</code> and last <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_index.html" rel="nofollow"><code>sort_index</code></a>:</p>

<pre><code>df1 = df.groupby('BROKER').sum()
df1.index = pd.MultiIndex.from_arrays([df1.index + '_total', len(df1.index) * ['']])
print (df1)
                QUANTITY
BrokerA_total       2900
BrokerB_total       1600
BrokerC_total      19200

print (pd.concat([pt, df1]).sort_index())
                       QUANTITY
BROKER        VENUE            
BrokerA       Venue_1       300
              Venue_2      1800
              Venue_3       800
BrokerA_total              2900
BrokerB       Venue_2       500
              Venue_3      1100
BrokerB_total              1600
BrokerC       Venue_1      2200
              Venue_2     17000
BrokerC_total             19200
</code></pre>

python pandas