`pivot_table` args `index` and `observed` don't play well

[luranhe]

Code Sample, a copy-pastable example if possible

cat_df = pd.DataFrame()
cat_df['letters'] = ['a', 'b', 'c']
cat_df['letters'] = cat_df['letters'].astype('category')
cat_df['nums'] = [0, 1, 2]
cat_df['nums'] = cat_df['nums'].astype('category')
cat_df['count'] = 1
# when I set an index, I don't get all the columns!
cat_df.iloc[:2].pivot_table(aggfunc=sum, columns=['nums'], values='count', index='letters')

Problem description

I would expect the default behavior of pivot_table to yield precisely 1 column per category of a categorical grouper when pivoting over a single categorical column. This was not the case when I passed in an index -- I only ended up with the observed categories for columns, even when passing observed=False explicitly!

I am working on an application where I want to pivot with an index and group by multiple columns (e.g. column A with categories a1, a2, ..., column B with categories b1, b2, ..., etc.), and I was surprised to discover that if any combination of categories (e.g. a1, b1, ...) didn't appear in the same row, I would not get a corresponding column for it.

As an aside, I would guess that the correct behavior would be to have all the nans in the output from above by 0, but that is seems important.

Expected Output

nums    0  1  2
letters
a        1.0  0.0  0.0
b        0.0  1.0  0.0

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.4.final.0
python-bits : 64
OS : Linux
OS-release : 4.14.133-88.112.amzn1.x86_64
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 0.25.1
numpy : 1.16.4
pytz : 2019.2
dateutil : 2.8.0
pip : 19.2.2
setuptools : 41.0.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 7.8.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.3.1
sqlalchemy : None
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None

[WillAyd]

I might not completely understand but are you basically saying that a pivot of

>>> cat_df.iloc[:2]
  letters nums  count
0       a    0      1
1       b    1      1

should include the "c" category?

[luranhe]

Yeah, if you pivot on letters, since the corresponding dtype has categories a, b, and c, we should get columns for each of them. In fact cat_df.iloc[:2].pivot_table(aggfunc=sum, columns=['letters'], values='count') works as expected, but cat_df.iloc[:2].pivot_table(aggfunc=sum, columns=['letters'], values='count', index='nums') doesn't have all the columns.

[TomAugspurger]

Can you post the expected output for the buggy case and remove the extraneous calls to pivot_table?

[luranhe]

cat_df = pd.DataFrame()
cat_df['letters'] = ['a', 'b', 'c']
cat_df['letters'] = cat_df['letters'].astype('category')
cat_df['nums'] = [0, 1, 2]
cat_df['nums'] = cat_df['nums'].astype('category')
cat_df['count'] = 1
cat_df.iloc[:2].pivot_table(aggfunc=sum, columns=['nums'], values='count', index='letters')

I expect the output of this to be (and sorry for the bad formatting):

nums    0  1  2
letters
a        1.0  0.0  0.0
b        0.0  1.0  0.0

[TomAugspurger]

Thanks. Can you edit your original post with just the current output and that expected output?

[luranhe]

Done.

[sathyz]

For the case you have discussed, here is the simplified version

>>> df = pd.DataFrame({"letters": ["a", "b", "c"], "nums": [0, 1, 2] , "count": 1 } )
>>> df
  letters  nums  count
0       a     0      1
1       b     1      1
2       c     2      1
>>> df.pivot_table(aggfunc=sum, columns=['nums'], values='count', index='letters')
nums       0    1    2
letters               
a        1.0  NaN  NaN
b        NaN  1.0  NaN
c        NaN  NaN  1.0
>>> 

What you are expecting is 0 in place of NaN, which can be achived by just passing fill_value as

>>> df.pivot_table(aggfunc=sum, columns=['nums'], values='count', index='letters', fill_value=0)
nums     0  1  2
letters         
a        1  0  0
b        0  1  0
c        0  0  1
>>>

[scratchmex]

For the case you have discussed, here is the simplified version

>>> df = pd.DataFrame({"letters": ["a", "b", "c"], "nums": [0, 1, 2] , "count": 1 } )
>>> df
  letters  nums  count
0       a     0      1
1       b     1      1
2       c     2      1
>>> df.pivot_table(aggfunc=sum, columns=['nums'], values='count', index='letters')
nums       0    1    2
letters               
a        1.0  NaN  NaN
b        NaN  1.0  NaN
c        NaN  NaN  1.0
>>> 

What you are expecting is 0 in place of NaN, which can be achived by just passing fill_value as

>>> df.pivot_table(aggfunc=sum, columns=['nums'], values='count', index='letters', fill_value=0)
nums     0  1  2
letters         
a        1  0  0
b        0  1  0
c        0  0  1
>>>

I think you are missing .iloc[:2]

[sathyz]

doesn't make any difference, it just means you need first two rows. just that you won't see the last row.

[luranhe]

@sathyz Thank you for your response. When I add .iloc[:2] I expect it to not make any difference, as you say. Instead I lose the 2 column. Losing the column is my true concern; I am not so worried about seeing NaN values instead of 0's.

[bgollop]

take