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>Your index is of <code>object</code> type and you must convert it to <code>datetime</code> format.</p>

<pre><code># Converting the object date to datetime.date
df['dates'] = df['dates'].apply(lambda x: datetime.strptime(x, "%d.%m.%Y"))

# Setting the index column
df.set_index(['dates'], inplace=True)

# Choosing a date range extending from first date to the last date with a set frequency
new_index = pd.date_range(start=df.index[0], end=df.index[-1], freq='D')
new_index.name = df.index.name

# Setting the new index
df = df.reindex(new_index)

# Making the required modifications
df.ix[:,0], df.ix[:,1:] = df.ix[:,0].fillna('-'), df.ix[:,1:].fillna(0)

print (df)

               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>

<hr>

<p>Let's suppose you have data for a single day (<em>as mentioned in the comments section</em>) and you would like to fill the other days of the week with null values:</p>

<p><strong>Data Setup:</strong></p>

<pre><code>df = pd.DataFrame({'dates':['01.08.2016'], 'name':['Michael'], 
                   'purchase':[120], 'send_back':[0]})
print (df)

        dates     name  purchase  send_back
0  01.08.2016  Michael       120          0
</code></pre>

<p><strong>Operations:</strong></p>

<pre><code>df['dates'] = df['dates'].apply(lambda x: datetime.strptime(x, "%d.%m.%Y"))
df.set_index(['dates'], inplace=True)

# Setting periods as 7 to account for the end of the week
new_index = pd.date_range(start=df.index[0], periods=7, freq='D')
new_index.name = df.index.name

# Setting the new index
df = df.reindex(new_index)
print (df)

               name  purchase  send_back
dates                                   
2016-08-01  Michael     120.0        0.0
2016-08-02      NaN       NaN        NaN
2016-08-03      NaN       NaN        NaN
2016-08-04      NaN       NaN        NaN
2016-08-05      NaN       NaN        NaN
2016-08-06      NaN       NaN        NaN
2016-08-07      NaN       NaN        NaN
</code></pre>

<p>Incase you want to fill the null values with 0's, you could do:</p>

<pre><code>df.fillna(0, inplace=True)
print (df)
               name  purchase  send_back
dates                                   
2016-08-01  Michael     120.0        0.0
2016-08-02        0       0.0        0.0
2016-08-03        0       0.0        0.0
2016-08-04        0       0.0        0.0
2016-08-05        0       0.0        0.0
2016-08-06        0       0.0        0.0
2016-08-07        0       0.0        0.0
</code></pre>

python pandas