Accessing unused columns in pivot_table

[wabee]

related is #6490

I am new to pandas but have run into a problem which I believe is a bug.

I tried to execute the following:

df_sum=pd.pivot_table(df,rows=['Konskund_MEAB','ProdID'],cols=['Year'], aggfunc=lambda x: np.average(x['snittpris'],
weights=x['Antal_forsandelser'])

This produce a number of errors.

The following commands produce the desired results:

rows = ['Konskund_MEAB','ProdID']
cols = ['Year']
g = df.groupby(rows + columns)
s_av = g.apply(lambda x: np.average(x['snittpris'], weights=x['Antal_forsandelser']))
df_av = s_av.unstack(cols)

Posted originally on Stackoverflow.

Another more knowledgeable user has verified the problem.

---

Reproducible example

df = pd.DataFrame([[1] * 5], columns=['Konskund_MEAB','ProdID', 'Year', 'snittpris', 'Antal_forsandelser'])
rows = ['Konskund_MEAB','ProdID']
cols = ['Year']
aggfunc = lambda x: np.average(x['snittpris'], weights=x['Antal_forsandelser'])

df.pivot_table(index=rows, columns=cols, aggfunc=aggfunc)
# KeyError: 'snittpris'

[hayd]

Thanks for reporting this!

I seem to remember that the issue was something like the aggfunc didn't have access to unused columns, but as I think I recall getting some strange errors about SNDArrays -can't recall what debugging I did exactly to see them...

Simple example, perhaps needs a better one:

df = pd.DataFrame([[1] * 5], columns=['Konskund_MEAB','ProdID', 'Year', 'snittpris', 'Antal_forsandelser'])
rows = ['Konskund_MEAB','ProdID']
cols = ['Year']
aggfunc = lambda x: np.average(x['snittpris'], weights=x['Antal_forsandelser'])

df.pivot_table(index=rows, columns=cols, aggfunc=aggfunc)
# KeyError: 'snittpris'

Expected result:

In [11]: g = df.groupby(rows + cols).apply(aggfunc).unstack(cols)
Out[11]:
Year                  1
Konskund_MEAB ProdID
1             1       1

(Edit: oop, this may be an agg rather than an apply.)

[wabee]

Cannot figure out how to tag this issue properly so the developers get a proper heads-up

[hayd]

(I wasn't sure if this is bug/enhancement, but tagged as a bug... @wabee reporting here is sufficient, just needs someone to find time to explore a fix :) )

[jbrockmendel]

This is difficult to reproduce without having df available. Can you post a MCVE?