title: Add Sum Subtotal to multiple layers of pandas pivot
body: <p>I am looking to add subtotals as done in <a href="https://stackoverflow.com/questions/63866792/adding-subtotals-to-multiple-layers-of-pandas-pivot-table">adding subtotals to multiple layers of pandas pivot-table</a> but ONLY including the sum aggregate, no others. I want to group by 'city', then 'name'.</p>
  <p>When I modify the above code to only calculate the sum of rating I get the following error:  <code>NotImplementedError: &gt; 1 ndim Categorical are not supported at this time</code></p>
  <p>Any suggestions? Here is my code:</p>
  <pre><code>data = {&quot;name&quot;:['paul','daniel','paul','john','daniel','daniel','john'],
          &quot;food&quot;:['cream','chocolate','chocolate','cream','jam','butter','jam'],
          &quot;city&quot;:['LA','NY','LA','NY','LA','NY','NY'],
          &quot;rating&quot;:[2,3,4,5,1,3,9]}
  
  df = pd.DataFrame(data)
  
  
  df1 = pd.pivot_table(df,values = 'rating', index=['city', 'name', 'food'], aggfunc=['sum'], margins=True, margins_name=&quot;Total&quot;)
  
  
  
  df2 = df.groupby(['city','name']).agg(['sum'])
  df2 = df2.rename(index=lambda x: x+'_total', level=1)
  df2 = df2.swaplevel(0, 1, axis=1)
  df2 = df2.assign(food='').set_index('food', append=True)
  
  df3 = df.groupby('city').agg(['sum'])
  df3.index = pd.MultiIndex.from_arrays([df3.index + '_total', len(df3.index) * ['']])
  df3 = df3.assign(name='', food='').set_index(['name','food'], append=True)
  df3 = df3.swaplevel(0,1, axis=1)
  
  df_out = pd.concat([df1,df2,df3]).sort_index()
  df_out
  </code></pre>
  <p>I am ultimately trying to replicate a pivot table like below and write to excel.</p>
  <p><img src="https://i.stack.imgur.com/WyeXb.png" alt="enter image description here" /></p>
  <p>Adding dtypes:</p>
  <pre><code>df.info() 
  &lt;class 'pandas.core.frame.DataFrame'&gt;
  RangeIndex: 7 entries, 0 to 6
  Data columns (total 4 columns):
   #   Column  Non-Null Count  Dtype 
  ---  ------  --------------  ----- 
   0   name    7 non-null      object
   1   food    7 non-null      object
   2   city    7 non-null      object
   3   rating  7 non-null      int64 
  dtypes: int64(1), object(3)
  memory usage: 352.0+ bytes
  
  df1.info() 
  &lt;class 'pandas.core.frame.DataFrame'&gt;
  MultiIndex: 8 entries, ('LA', 'daniel', 'jam') to ('Total', '', '')
  Data columns (total 1 columns):
   #   Column         Non-Null Count  Dtype
  ---  ------         --------------  -----
   0   (sum, rating)  8 non-null      int64
  dtypes: int64(1)
  memory usage: 795.0+ bytes
  
  df2.info() 
  &lt;class 'pandas.core.frame.DataFrame'&gt;
  MultiIndex: 4 entries, ('LA', 'daniel_total', '') to ('NY', 'john_total', '')
  Data columns (total 2 columns):
   #   Column         Non-Null Count  Dtype 
  ---  ------         --------------  ----- 
   0   (sum, food)    4 non-null      object
   1   (sum, rating)  4 non-null      int64 
  dtypes: int64(1), object(1)
  memory usage: 607.0+ bytes
  </code></pre>