title: Extend a pandas dataframe to include 'missing' weeks
body: <p>I have a pandas dataframe which contains time series data, so the index of the dataframe is of type datetime64 at weekly intervals, each date occurs on the Monday of each calendar week.</p>
  
  <p>There are only entries in the dataframe when an order was recorded, so if there was no order placed, there isn't a corresponding record in the dataframe.  I would like to "pad" this dataframe so that any weeks in a given date range are included in the dataframe and a corresponding zero quantity is entered.  </p>
  
  <p>I have managed to get this working by creating a dummy dataframe, which includes an entry for each week that I want with a zero quantity and then merging these two dataframes and dropping the dummy dataframe column.  This results in a 3rd padded dataframe.  </p>
  
  <p>I don't feel this is a great solution to the problem and being new to pandas wanted to know if there is a more specific and or pythonic way to achieve this, probably without having to create a dummy dataframe and then merge.</p>
  
  <p>The code I used is below to get my current solution:</p>
  
  <pre><code># Create the dummy product
  # Week hold the week date of the order, want to set this as index later
  group_by_product_name = df_all_products.groupby(['Week', 'Product Name'])['Qty'].sum()
  first_date = group_by_product_name.head(1) # First date in entire dataset
  last_date = group_by_product_name.tail().index[-1] # last date in the data set
  bdates = pd.bdate_range(start=first_date, end=last_date, freq='W-MON')
  qty = np.zeros(bdates.shape)
  dummy_product = {'Week':bdates, 'DummyQty':qty}
  df_dummy_product = pd.DataFrame(dummy_product)
  df_dummy_product.set_index('Week', inplace=True)
  
  
  group_by_product_name = df_all_products.groupby('Week')['Qty'].sum()
  df_temp = pd.concat([df_dummy_product, group_by_product_name], axis=1, join='outer')
  df_temp.fillna(0, inplace=True)
  df_temp.drop(columns=['DummyQty'], axis=1, inplace=True)
  </code></pre>
  
  <p>The problem with this approach is sometimes (I don't know why) the indexes don't match correctly, I think somehow the dtype of the index on one of the dataframes loses its type and goes to object instead of staying with dtype datetime64.  So I am sure there is a better way to solve this problem than my current solution. </p>
  
  <p>EDIT</p>
  
  <p>Here is a sample dataframe with "missing entries"</p>
  
  <pre><code>df1 = pd.DataFrame({'Week':['2018-05-28', '2018-06-04',
     '2018-06-11', '2018-06-25'], 'Qty':[100, 200, 300, 500]})
  df1.set_index('Week', inplace=True)
  df1.head()
  </code></pre>
  
  <p>Here is an example of the padded dataframe that contains the additional missing dates between the date range</p>
  
  <pre><code> df_zero = pd.DataFrame({'Week':['2018-05-21', '2018-05-28', '2018-06-04',
     '2018-06-11', '2018-06-18', '2018-06-25', '2018-07-02'], 'Dummy Qty':[0, 0, 0, 0, 0, 0, 0]})
  df_zero.set_index('Week', inplace=True)
  df_zero.head()
  </code></pre>
  
  <p>And this is the intended outcome after concatenating the two dataframes</p>
  
  <pre><code>df_padded = pd.concat([df_zero, df1], axis=1, join='outer')
  df_padded.fillna(0, inplace=True)
  df_padded.drop(columns=['Dummy Qty'], inplace=True)
  df_padded.head(6)
  </code></pre>
  
  <p>Note that the missing entries are added before and between other entries where necessary in the final dataframe.</p>
  
  <p>Edit 2:</p>
  
  <p>As requested here is an example of what the initial product dataframe would look like:</p>
  
  <pre><code>df_all_products = pd.DataFrame({'Week':['2018-05-21', '2018-05-28', '2018-05-21', '2018-06-11', '2018-06-18',
     '2018-06-25', '2018-07-02'], 
                              'Product Name':['A', 'A', 'B', 'A', 'B', 'A', 'A'], 
                              'Qty':[100, 200, 300, 400, 500, 600, 700]})
  </code></pre>