Pandas DataFrame insert / fill missing rows from previous dates
<p>I have a <code>DataFrame</code> consisting of <code>date</code>s, other columns and a numerical value, where some value combinations in "other columns" could be missing, and I want to populate them from previous <code>date</code>s.</p>

<p>Example. Say the <code>DataFrame</code> is like below. You can see on <code>2016-01-01</code>, we have data for <code>(LN, A)</code>, <code>(LN, B)</code>, <code>(NY, A)</code> and <code>(NY, B)</code> on columns <code>(location, band)</code>.</p>

<pre>
        date  location  band  value
0 2016-01-01        LN     A   10.0
1 2016-01-01        LN     B    5.0
2 2016-01-01        NY     A    9.0
3 2016-01-01        NY     B    6.0
4 2016-01-02        LN     A   11.0
5 2016-01-02        NY     B    7.0
6 2016-01-03        NY     A   10.0
</pre>

<p>Then you notice on <code>2016-01-02</code>, we only have <code>(LN, A)</code> and <code>(NY, B)</code>, but <code>(LN, B)</code> and <code>(NY, A)</code> are missing. Again, on <code>2016-01-03</code>, only <code>(NY, A)</code> is available; all other three combinations are missing.</p>

<p>What I want to do is to populate the missing combinations of each date from its predecessor. Say for <code>2016-01-02</code>, I would like to add two more rows, "rolled over" from <code>2016-01-01</code>: <code>(LN, B, 5.0)</code> and <code>(NY, A, 9.0)</code> for columns <code>(location, band, value)</code>. Same for <code>2016-01-03</code>. So as to make the whole thing like below:</p>

<pre>
        date  location  band  value
 0 2016-01-01        LN     A   10.0
 1 2016-01-01        LN     B    5.0
 2 2016-01-01        NY     A    9.0
 3 2016-01-01        NY     B    6.0
 4 2016-01-02        LN     A   11.0
 5 2016-01-02        NY     B    7.0
 6 2016-01-03        NY     A   10.0
 7 2016-01-02        LN     B    5.0
 8 2016-01-02        NY     A    9.0
 9 2016-01-03        LN     A   11.0
10 2016-01-03        LN     B    5.0
11 2016-01-03        NY     B    7.0
</pre>

<p>Note rows 7-11 are populated from rows 1, 2, 4, 7 and 5, respectively. The order is not really important as I can always sort afterwards if all the data I need is present.</p>

<p>Anyone to help? Thanks a lot!</p>

<p>My solution, in summary using the product operation to get all the combinations in a multi index, then some stacking and ffill().</p>

<pre><code>df =pd.DataFrame({'date': {0: '2016-01-01', 1: '2016-01-01', 2: '2016-01-01', 3: '2016-01-01', 4: '2016-01-02', 5: '2016-01-02', 6: '2016-01-03'}, 'band': {0: 'A', 1: 'B', 2: 'A', 3: 'B', 4: 'A', 5: 'B', 6: 'A'}, 'location': {0: 'LN', 1: 'LN', 2: 'NY', 3: 'NY', 4: 'LN', 5: 'NY', 6: 'NY'}, 'value': {0: 10, 1: 5, 2: 9, 3: 6, 4: 11, 5: 7, 6: 10}})
unique_dates = df['date'].unique()
df.set_index(['date','location','band'],inplace=True)
idx = pd.MultiIndex.from_product([unique_dates,['LN','NY'],['A','B']])
df  = df.reindex(idx)
df = df.unstack(level=[2,1])
</code></pre>

<p>which produces:</p>

<pre><code>             value                      
                 A      B       A      B
                LN     LN      NY     NY
2016-01-01 10.0000 5.0000  9.0000 6.0000
2016-01-02 11.0000    nan     nan 7.0000
2016-01-03     nan    nan 10.0000    nan
</code></pre>

<p>and finally:</p>

<pre><code>df = df.ffill()
df = df.stack().stack()
print df


                  value
2016-01-01 LN A 10.0000
              B  5.0000
           NY A  9.0000
              B  6.0000
2016-01-02 LN A 11.0000
              B  5.0000
           NY A  9.0000
              B  7.0000
2016-01-03 LN A 11.0000
              B  5.0000
           NY A 10.0000
              B  7.0000
</code></pre>

python pandas dataframe