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
Advertisement
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