python - Pandas create new date rows and forward fill column values

Question

I have a dataframe like this:

id     date       value
 1  12/01/2016      5 
 1  25/02/2016      7 
 1  10/03/2017      13 
 2  02/04/2016      0 
 2  06/07/2016      1 
 2  18/04/2017      6 

For each id there is a start date with a value and every few months there is another row with a date and a value. I'd like to create a timeseries of each id. So I'd like to insert new rows with the next day (until today), where the value will be forward filled from the previous row.

So the dataframe becomes:

id     date       value
 1  12/01/2016      5 
 1  13/01/2016      5 
 1  14/01/2016      5 
 1  15/01/2016      5
 ...
 1  20/04/2017      13 
 ...
 2  18/04/2017      6 
 2  19/04/2017      6 
 2  20/04/2017      6 

Eg answer to a similar question: Appending datetime rows and forward filling data in pandas dataframe

But my dataframe isn't datetime indexed.

Appreciate any help!

Answer 1

Consider a groupby and merge approach:

import pandas as pd
from io import StringIO
from datetime import date

txt= """
id     date       value
 1  12/01/2016      5 
 1  25/02/2016      7 
 1  10/03/2017      13 
 2  02/04/2016      0 
 2  06/07/2016      1 
 2  18/04/2017      6 
"""

df = pd.read_table(StringIO(txt), sep="s+", parse_dates=[1], dayfirst=True)

def expand_dates(ser):
    return pd.DataFrame({'date': pd.date_range(ser['date'].min(), date.today(), freq='D')})

newdf = df.groupby(['id']).apply(expand_dates).reset_index()
          .merge(df, how='left')[['id', 'date', 'value']].ffill()