Fill a pandas dataframe (of predetermined size) with results from a similar dataframe (of size less than or equivalent to the original)

Question

I'd like some feedback/suggestions on how to improve the following. Specifically I want to know if what I'm doing is reliable and fast, or if there is a better way to accomplish this.

The problem:

I have some dataset containing counts of sales made at different times throughout the day, across different locations/shops. Let's say there are 4 different shops contained in this data (A, B, C, D), and there are 4 different time bins in the day [0,1,2,3]. I query this and return a query dataset, but the issue I have is that for this query there may be no transactions for a certain time bin. Or there may be no transactions even for a specific shop (maybe there was a rat infestation and it closed for the day).

Nevertheless, the end result must have the same number of rows (4 locations x 4 time bins), and simply contain zeros if there were no transactions there. In other words, I want records for all possible occurrences, even if they were not returned by the query itself.

Example:

import pandas as pd
# Specify the complete list of possible time bins
max_timebins = 3
bin_nums = list(range(max_timebins + 1))
# Specify the complete list of shops
shop_ids = ['A', 'B','C','D']
# Make a dataframe for all possible results without the counts
# This is just a dataframe with an index and no columns... this feels a little strange to me but it worked...
dat = {'shop':[], 'timebin':[]}
for shop in shop_ids:
 dat['shop']+=[shop]*len(bin_nums)
 dat['timebin'] += bin_nums
df_all = pd.DataFrame(dat)
df_all = df_all.set_index(list(dat.keys()))
# Example of a result of a query
dfq = pd.DataFrame(
 {
 'shop':['A', 'A', 'A', 'A',
 'B', 'B',
 'C', 'C', 'C',
 'D'],
 'time_bins':[0,1,2,3,
 0, 3,
 0,2,3,
 2],
 'counts':[100,220, 300, 440,
 500, 660,
 120, 340, 90,
 400]}).set_index(['shop', 'time_bins'])
result_df = pd.concat([df_all, dfq], axis=1).fillna(0).astype(int)

Answer 1

You can create the index directly:

pd.MultiIndex.from_product(
 (shop_ids, range(max_timebins + 1)), names=("shop", "timebin")
)

Here is how you can achieve the same result (including the full index even if there is no data) in a simpler way:

import pandas as pd
# Specify the complete list of possible time bins
max_timebins = 3
# Specify the complete list of shops
shop_ids = ["A", "B", "C", "D"]
target_index = pd.MultiIndex.from_product(
 (shop_ids, range(max_timebins + 1)), names=("shop", "timebin")
)
# Example of a result of a query
dfq = pd.DataFrame(
 {
 "shop": ["A", "A", "A", "A", "B", "B", "C", "C", "C", "D"],
 "time_bins": [0, 1, 2, 3, 0, 3, 0, 2, 3, 2],
 "counts": [100, 220, 300, 440, 500, 660, 120, 340, 90, 400],
 }
).set_index(["shop", "time_bins"])
result_df = dfq.reindex(target_index, fill_value=pd.NA)
print(result_df)

Output:

 counts
shop timebin 
A 0 100
 1 220
 2 300
 3 440
B 0 500
 1 <NA>
 2 <NA>
 3 660
C 0 120
 1 <NA>
 2 340
 3 90
D 0 <NA>
 1 <NA>
 2 400
 3 <NA>

There might be a better solution, but that would require knowing a bit more context.