Missing values in Pandas Pivot table?

Question

I have a data set that looks like the following:

student     question                        answer   number
Bob         How many donuts in a dozen?       A        1
Sally       How many donuts in a dozen?       C        1
Edward      How many donuts in a dozen?       A        1
....
Edward      What colour is the sky?           C        1
Marvin      What colour is the sky?           D        1

From which I wrote some code that generates a pivot table to total up the results of a test, like so:

data = pd.pivot_table(df,index=['question'],columns = ['answer'],aggfunc='count',fill_value = 0)

                                   number
                     answer     A    B   C   D
       question
How many donuts in a dozen?     1    4   3   2
What colour is the sky?         1    9   0   0

From there I'm creating a heatmap from the pivot table for visualization purposes. Generally this works. However, if for some reason there are no students in the selected set who have chosen one of the answers (say, no one selected "D" for any questions) then that column doesn't show up in the heatmap; the column is left off.

How can I ensure that all the required columns display in the heatmap, even if no one selected that answer?

Answer 1

I think an even simpler approach would be to add 'dropna = False' to the pivot table parameters, default behavior is set to 'True'. This worked for me in a similar situation with time series data that contained large swaths of days with NaNs.

pd.pivot_table(dropna = False)

Answer 2

You can take all possible answers and reindex your result. For example, in the small sample you have provided, no student selected B. Let's say your options are A, B, C, D:

---

answers = [*'ABCD']

res = df.pivot_table(
  index='question',
  columns='answer',
  values='number',
  aggfunc='sum',
  fill_value=0
).reindex(answers, axis=1, fill_value=0)

answer                       A  B  C  D
question
How many donuts in a dozen?  2  0  1  0
What colour is the sky?      0  0  1  1

---

The corresponding heatmap:

import matplotlib.pyplot as plt
import seaborn as sns
sns.heatmap(res, annot=True)
plt.tight_layout()
plt.show()