Python: Date range into time series with corresponding values

My data set is much larger so I have simplified it.

I want to convert the dataframe into a time-series.

The bit I am stuck on:

I have overlapping date ranges, where I have a smaller date range inside a larger one, as shown by row 0 and row 1, where row 1 and row 2 are inside the date range of row 0.

df:
        date1      date2      reduction
0  2016-01-01 - 2016-01-05       7.0
1  2016-01-02 - 2016-01-03       5.0
2  2016-01-03 - 2016-01-04       6.0
3  2016-01-05 - 2016-01-12       10.0

How I want the output to look:

        date1      date2     reduction
0  2016-01-01 2016-01-02        7.0
1  2016-01-02 2016-01-03        5.0
2  2016-01-03 2016-01-04        6.0
3  2016-01-04 2016-01-05        7.0
4  2016-01-05 2016-01-06        10.0
5  2016-01-06 2016-01-07        10.0
6  2016-01-07 2016-01-08        10.0
7  2016-01-08 2016-01-09        10.0
8  2016-01-09 2016-01-10        10.0
9  2016-01-10 2016-01-11        10.0
10 2016-01-11 2016-01-12        10.0

Answer

I think this does what you want…

import pandas as pd
import datetime 
first={'date1':[datetime.date(2016,1,1),datetime.date(2016,1,2),datetime.date(2016,1,6),datetime.date(2016,1,7),
           datetime.date(2016,1,8),datetime.date(2016,1,9),datetime.date(2016,1,10),datetime.date(2016,1,11)],
  'date2':[datetime.date(2016,1,5),datetime.date(2016,1,3),datetime.date(2016,1,7),datetime.date(2016,1,8),
           datetime.date(2016,1,9),datetime.date(2016,1,10),datetime.date(2016,1,11),datetime.date(2016,1,12)],
  'reduction':[7,5,3,2,9,3,8,3]}
df=pd.DataFrame.from_dict(first)
blank = pd.DataFrame(index=pd.date_range(df["date1"].min(), df["date2"].max()))
blank["r1"] = blank.join(df[["date1", "reduction"]].set_index("date1"), how="left")["reduction"]
blank["r2"] = blank.join(df[["date2", "reduction"]].set_index("date2"), how="left")["reduction"]
blank["r2"] = blank["r2"].shift(-1)
tmp = blank[pd.notnull(blank).any(axis=1)][pd.isnull(blank).any(axis=1)].reset_index().melt(id_vars=["index"])
tmp = tmp.sort_values(by="index").bfill()
blank1 = pd.DataFrame(index=pd.date_range(tmp["index"].min(), tmp["index"].max()))
tmp = blank1.join(tmp.set_index("index"), how="left").bfill().reset_index().groupby("index")["value"].first()
blank["r1"] = blank["r1"].combine_first(blank.join(tmp, how="left")["value"])
final = pd.DataFrame(data={"date1": blank.iloc[:-1, :].index, "date2": blank.iloc[1:, :].index, "reduction":blank["r1"].iloc[:-1].fillna(5).values})

Output:

        date1      date2  reduction
0  2016-01-01 2016-01-02        7.0
1  2016-01-02 2016-01-03        5.0
2  2016-01-03 2016-01-04        7.0
3  2016-01-04 2016-01-05        7.0
4  2016-01-05 2016-01-06        5.0
5  2016-01-06 2016-01-07        3.0
6  2016-01-07 2016-01-08        2.0
7  2016-01-08 2016-01-09        9.0
8  2016-01-09 2016-01-10        3.0
9  2016-01-10 2016-01-11        8.0
10 2016-01-11 2016-01-12        3.0