1

I am trying to compute a simple pivot table with margins. It works fine with margins=False but as soon as i try to set margins to true I get a Key Error. How can I fix this?

Data

Code:

sp_outlook_distr.pivot_table(index=["Type"],
columns=sp_outlook_distr["Date"].dt.year, aggfunc={"Date": np.count_nonzero}, 
fill_value=0, margins=True)

Error:

---------------------------------------------------------------------------
KeyError  Traceback (most recent call last)
<ipython-input-87-fdd674d20a28> in <module>()
      1 # Distribution table - Outlook
----> 2 sp_outlook_distr.pivot_table(index=["Type"], columns=sp_outlook_distr["Date"].dt.year, aggfunc={"Date": np.count_nonzero}, fill_value=0, margins=True)

~/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py in pivot_table(self, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name)
   5298                            aggfunc=aggfunc, fill_value=fill_value,
   5299                            margins=margins, dropna=dropna,
-> 5300                            margins_name=margins_name)
   5301 
   5302     def stack(self, level=-1, dropna=True):

~/anaconda3/lib/python3.6/site-packages/pandas/core/reshape/pivot.py in pivot_table(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name)
    122                              cols=columns, aggfunc=aggfunc,
    123                              observed=dropna,
--> 124                              margins_name=margins_name, fill_value=fill_value)
    125 
    126     # discard the top level

~/anaconda3/lib/python3.6/site-packages/pandas/core/reshape/pivot.py in _add_margins(table, data, values, rows, cols, aggfunc, observed, margins_name, fill_value)
    149             raise ValueError(msg)
    150 
--> 151     grand_margin = _compute_grand_margin(data, values, aggfunc, margins_name)
    152 
    153     # could be passed a Series object with no 'columns'

~/anaconda3/lib/python3.6/site-packages/pandas/core/reshape/pivot.py in _compute_grand_margin(data, values, aggfunc, margins_name)
    219                     grand_margin[k] = getattr(v, aggfunc)()
    220                 elif isinstance(aggfunc, dict):
--> 221                     if isinstance(aggfunc[k], compat.string_types):
    222                         grand_margin[k] = getattr(v, aggfunc[k])()
    223                     else:


KeyError: 'Sovereign'
alpenmilch411
  • 483
  • 1
  • 5
  • 18
  • What you want to reach with margins=True? – MisterMonk Nov 26 '18 at 20:48
  • I want to see totals for each year and each type – alpenmilch411 Nov 26 '18 at 22:23
  • Then you dont need margins or? – MisterMonk Nov 26 '18 at 22:37
  • Sorry if I expressed myself unclearly. The current setting with margins false gives me the number of occurrences by year AND type. In addition I would like to see the total count of types over all years and the total count per year over all types. So if in 2016 I have 20 of type positive and 23 of type negative, I would like to see 43 as subtotal. I hope that clarifies my question – alpenmilch411 Nov 27 '18 at 00:00
  • I think this is duplicated here: https://stackoverflow.com/questions/15570099/pandas-pivot-tables-row-subtotals – MisterMonk Nov 27 '18 at 09:53

1 Answers1

0

Try using values parameter in pivot table:

sp_outlook_distr.pivot_table(
    index=["Type"],
    columns=sp_outlook_distr["Date"].dt.year,
    values=["Date"],
    aggfunc={"Date": np.count_nonzero}, 
    fill_value=0,
    margins=True)
Mo. Atairu
  • 753
  • 8
  • 15
Shivani Sinha
  • 1
  • 3