Pivot Table Margins aren't correctly showing NaN values despite dummy variables

[pshMsGsAZL2n07ohWBCAGX9cC]

Code Sample, a copy-pastable example if possible

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

fig = plt.figure()

data = [['Auto', 'A', 10], ['Auto', 'A', 2], ['Telco', 'B', 15], ['Manf', 'C'],
        ['Retail', 'A', ], ['Retail', 'B', ]] 
df = pd.DataFrame(data, columns = ['INDUSTRY', 'PRODUCT', 'COST'])

table_byindustry = pd.pivot_table(df, index=['INDUSTRY'], columns=['PRODUCT'], values='COST',
                                  fill_value=0, margins=True, margins_name='Total', aggfunc='count')
ax = sns.heatmap(table_byindustry, cmap='RdYlGn', linewidths=.1, annot=True, vmin=0, vmax=100, fmt="g", xticklabels=True)
ax.set_ylabel("Industry", fontsize=12)
ax.set_xlabel("Product", fontsize=12, labelpad=1)
ax.xaxis.tick_bottom()
ax.set_ylim(bottom=5,top=0)

Problem description

Pivot Table Margins ("Total") isn't showing 0 values for rows, yet will do so for columns (e.g., Product C). As a result, the heatmap is empty for the "Total" column related to the rows ('Manf' and 'Retail') in my example. It is treating those rows as NaN for Margin, and setting fill_value at 0 doesn't fix it. See attached image. Thank you for your efforts to fix this issue.

Expected Output

Based on the color map, there should be a color for every cell in the pivot table, including the Margins. In addition, if the value is 0, it should be shown when the margins=True. It seems as if the treatment is inconsistent (i.e., view the total for Product C vs. the totals for 'Manf' and 'Retail' in Industry).

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.5.final.0
python-bits : 64
OS : Linux
OS-release : 4.19.88-1-MANJARO
machine : x86_64
processor :
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 0.25.3
numpy : 1.17.4
pytz : 2019.3
dateutil : 2.8.1
pip : 19.3.1
setuptools : 42.0.2.post20191203
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : 0.4.0
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.10.3
IPython : 7.10.2
pandas_datareader: None
bs4 : 4.8.1
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : 3.1.1
numexpr : None
odfpy : None
openpyxl : 3.0.2
pandas_gbq : None
pyarrow : 0.13.0
pytables : None
s3fs : None
scipy : 1.3.2
seaborn : 0.9.0
sqlalchemy : None
tables : None
xarray : None
xlrd : 1.2.0
xlwt : 1.3.0
xlsxwriter : None


[TomAugspurger]

I'm not sure how the plotting stuff is related, but based on the title this sounds like #3729. Let me know if not.

[pshMsGsAZL2n07ohWBCAGX9cC]

No, this issue is different from #3729. This issue isn’t dropping entire NaN rows like #3729, and my issue already uses the dummy variables suggested in that post as a workaround. My issue is dropping a NaN total in the margin row (but not column), while the dummy variables appear fine. I would suggest that you re-open it. Thanks again, and happy holidays.

[pshMsGsAZL2n07ohWBCAGX9cC]

Forgot to add in first clarification. This issue occurs without doing a heatmap plot. That is, the row margin total shows NaN, despite zeros for the row elements. Column margin total doesn’t have this issue.

[pshMsGsAZL2n07ohWBCAGX9cC]

I'm not sure how the plotting stuff is related, but based on the title this sounds like #3729. Let me know if not.

Tom, I added a clarification about a week ago, saying that I didn't think it was a duplicate. Can you please take a look? Apologies in advance, as I'm relatively new to github and don't understand exactly how issues are "re-opened". Thanks.

[TomAugspurger]

@pshMsGsAZL2n07ohWBCAGX9cC can you make a minimal example? http://matthewrocklin.com/blog/work/2018/02/28/minimal-bug-reports Is seaborn / matplotlib necessary, or does just

In [2]: table_byindustry
Out[2]:
PRODUCT   A  B  C  Total
INDUSTRY
Auto      2  0  0    2.0
Manf      0  0  0    NaN
Retail    0  0  0    NaN
Telco     0  1  0    1.0
Total     2  1  0    3.0

display the issue? Is it that the total for Manf and Retail are NaN, rather than 0?

[pshMsGsAZL2n07ohWBCAGX9cC]

@TomAugspurger Seaborn and matplotlib aren't necessary, although that's how I first saw the problem. The dataframe (table_byindustry) shows the issue, which you correctly summarized. Total for the Manf and Retail rows are NaN, rather than 0. Thanks for re-opening it and sharing how I can better report future bugs with minimal information.