Fill columns with 0 in pivot table pandas

Question

In one of column if it is missing how can I fill it with 0 instead of leaving it empty?

MRE:

df = pd.DataFrame({"day":[3, 3, 3, 3, 3, 3, 3],
                   "hour":[15, 15, 15, 15, 19, 20, 21],
                   "location":["A", "A", "B", "C", "D", "A", "B"],
                   "numbers": [323, 2354, 124, 56, 45, 11, 44]})

df.pivot_table(index="location",
           columns=["day", "hour"],
           values = "numbers",
           fill_value=0,
           aggfunc="count").unstack().to_frame()

Which outputs:

                            0
day   hour    location  
                 A          2
                 B          1
       15        C          0
                 D          0

                 A          0 
                 B          0
       19        C          0
                 D          1

                 A          1
                 B          0
 3      20       C          0
                 D          0

and so on.. I've just left space in between for better readability.

As you can see hour goes from 15 to 19. I want to include hours= 16, 17, 18 which all locations and all rows in 0 column as 0.

So desired output would be:

                            0
day   hour    location  
                 A          2
                 B          1
       15        C          0
                 D          0

                 A          0
                 B          0
       16        C          0
                 D          0

                 A          0
                 B          0
       17        C          0
                 D          0

                 A          0
                 B          0
       18        C          0
                 D          0

                 A          0 
                 B          0
       19        C          0
                 D          1

                 A          1
                 B          0
 3      20       C          0
                 D          0

How can I achieve this? Thanks in advance.

user_6361531 · Answer

Let's add reindex method with a pd.MultIndex created use the from_product of unique arrays and range of hours.

df.pivot_table(index="location",
           columns=["day", "hour"],
           values = "numbers",
           fill_value=0,
           aggfunc="count").unstack().to_frame()\
  .reindex(pd.MultiIndex.from_product([df['day'].unique(),
                                       np.arange(df['hour'].min(),
                                                 df['hour'].max()),
                                       df['location'].unique()]), 
           fill_value=0)

Output:

Fill columns with 0 in pivot table pandas

1 Answers