Pandas: decompress date range to individual dates

pandas, python, time-series

Solution

A bit more than a few lines, but I think it results in what you asked:

Starting with your dataframe:

In [70]: df
Out[70]:
       start_date   end_date  val  row
ticker
AAPL   2014-05-01 2014-05-01   10    0
AAPL   2014-06-05 2014-06-10   20    1
GOOG   2014-06-01 2014-06-15   50    2
MSFT   2014-06-16 2014-06-16  NaN    3
TWTR   2014-01-17 2014-05-17   10    4

First I reshape this dataframe to a dataframe with one `date` column (so every row two times repeated for each date of `start_date` and `end_date` (and I add a counter column called `row`):

In [60]: df['row'] = range(len(df))
In [61]: starts = df[['start_date', 'val', 'row']].rename(columns={'start_date': 'date'})
In [62]: ends = df[['end_date', 'val', 'row']].rename(columns={'end_date':'date'})
In [63]: df_decomp = pd.concat([starts, ends])
In [64]: df_decomp = df_decomp.set_index('row', append=True)
In [65]: df_decomp.sort_index()
Out[65]:
                 date  val
ticker row
AAPL   0   2014-05-01   10
       0   2014-05-01   10
       1   2014-06-05   20
       1   2014-06-10   20
GOOG   2   2014-06-01   50
       2   2014-06-15   50
MSFT   3   2014-06-16  NaN
       3   2014-06-16  NaN
TWTR   4   2014-01-17   10
       4   2014-05-17   10

Based on this new dataframe, I can group it by `ticker` and `row`, and apply a daily `resample` on each of these groups and `fillna` (with method 'pad' to forward fill)

In [66]: df_decomp = df_decomp.groupby(level=[0,1]).apply(lambda x: x.set_index('date').resample('D').fillna(method='pad'))

In [67]: df_decomp = df_decomp.reset_index(level=1, drop=True)

The last command was to drop the now superfluous `row` index level. When we access the AAPL rows, it gives your desired output:

In [69]: df_decomp.loc['AAPL']
Out[69]:
            val
date
2014-05-01   10
2014-06-05   20
2014-06-06   20
2014-06-07   20
2014-06-08   20
2014-06-09   20
2014-06-10   20

Problem

Dataset: I have a 1GB dataset of stocks, which have values between date ranges. There is no overlapping in date ranges and the dataset is sorted on (ticker, start_date). ``` >>> df.head() start_date end_date val ticker AAPL 2014-05-01 2014-05-01 10.0000000000 AAPL 2014-06-05 2014-06-10 20.0000000000 GOOG 2014-06-01 2014-06-15 50.0000000000 MSFT 2014-06-16 2014-06-16 None TWTR 2014-01-17 2014-05-17 10.0000000000 ``` Goal: I want to decompress the dataframe so that I have individual dates instead of date ranges. For example, the AAPL rows would go from being only 2 rows to 7 rows: ``` >>> AAPL_decompressed.head() val date 2014-05-01 10.0000000000 2014-06-05 20.0000000000 2014-06-06 20.0000000000 2014-06-07 20.0000000000 2014-06-08 20.0000000000 ``` I'm hoping there's a nice optimized method from pandas like resample that can do this in a couple lines.

Original source