Dates fill in for date range and fillna
<p>I am querying my database to show records from the past week. I am then aggregating the data and transposing it in python and pandas into a DataFrame.
In this table I am attempting to show what occurred on each day in the past 7 week, however, on some days no events occur. In these cases, the date is missing altogether. I am looking for an approach to append the dates that are not present (but are part of the date range specified in the query) so that I can then fillna with any value I wish for the other missing columns.</p>

<p>In some trials I have the data set into a pandas Dataframe where the dates are the index and in others the dates are a column. I am preferably looking to have the dates as the top index - so group by name, stack purchase and send_back and dates are the 'columns'.</p>

<p>Here is an example of how the dataframe looks now and what I am looking for:</p>

<p>Dates set in query for - 01.08.2016 - 08.08.2016. The dataframe looks liks so:</p>

<pre><code>       |  dates       | name     | purchase | send_back
   0  01.08.2016    Michael     120          0
   1  02.08.2016    Sarah       100         40
   2  04.08.2016    Sarah       55           0
   3  05.08.2016    Michael     80          20
   4  07.08.2016    Sarah       130          0
</code></pre>

<p>After:</p>

<pre><code>     | dates       | name     | purchase | send_back
   0 01.08.2016    Michael      120          0
   1 02.08.2016    Sarah        100          40
   2 03.08.2016    -            0            0
   3 04.08.2016    Sarah        55           0
   4 05.08.2016    Michael      80           20
   5 06.08.2016    -            0            0
   6 07.08.2016    Sarah        130          0
   7 08.08.2016    Sarah        0            35
   8 08.08.2016    Michael      20           0 
</code></pre>

<p>Printing the following:</p>

<pre><code> df.index
</code></pre>

<p>gives:</p>

<pre><code> 'Index([   u'dates',u'name',u'purchase',u'send_back'],
      dtype='object')

RangeIndex(start=0, stop=1, step=1)'
</code></pre>

<p>I appreciate any guidance.</p>

<p>assuming you have the following DF:</p>

<pre><code>In [93]: df
Out[93]:
               name  purchase  send_back
dates
2016-08-01  Michael       120          0
2016-08-02    Sarah       100         40
2016-08-04    Sarah        55          0
2016-08-05  Michael        80         20
2016-08-07    Sarah       130          0
</code></pre>

<p>you can resample and replace:</p>

<pre><code>In [94]: df.resample('D').first().replace({'name':{np.nan:'-'}}).fillna(0)
Out[94]:
               name  purchase  send_back
dates
2016-08-01  Michael     120.0        0.0
2016-08-02    Sarah     100.0       40.0
2016-08-03        -       0.0        0.0
2016-08-04    Sarah      55.0        0.0
2016-08-05  Michael      80.0       20.0
2016-08-06        -       0.0        0.0
2016-08-07    Sarah     130.0        0.0
</code></pre>

python pandas