Search code examples
pythonpandasexplodeexpand

How to expand and create the following dataset in Pandas

I have a dataset that looks like this:

enter image description here

df = pd.DataFrame({
    'weekstart':['01-Jan-18','08-Jan-18','15-Jan-18','22-Jan-18'],
    'weekend':['07-Jan-18','14-Jan-18','21-Jan-18','28-Jan-18'],
    'Spend':[34235.922,37359.6048,38916.1164,36903.8628],
    'Daily':[4890.846,5337.086,5559.445,5271.98],
})

I would like to expand this out to take the daily value and create a new dataset with daily dates to form something like this:

enter image description here

Thank you!!

Solution
    1. Convert to datetime
    2. Use pd.date_range to create a column of all dates between "weekstart" and "weekend"
    3. Use explode to split into individual rows.
    df["weekstart"] = pd.to_datetime(df["weekstart"])
df["weekend"] = pd.to_datetime(df["weekend"])

df["days"] = df.apply(lambda x: pd.date_range(x["weekstart"], x["weekend"], freq="D"), axis=1)
df = df.explode("days")
output = df[["days", "Daily"]]

>>> output

        days     Daily
0 2018-01-01  4890.846
0 2018-01-02  4890.846
0 2018-01-03  4890.846
0 2018-01-04  4890.846
0 2018-01-05  4890.846
0 2018-01-06  4890.846
0 2018-01-07  4890.846
1 2018-01-08  5337.086
1 2018-01-09  5337.086
1 2018-01-10  5337.086
1 2018-01-11  5337.086
1 2018-01-12  5337.086
1 2018-01-13  5337.086
1 2018-01-14  5337.086
2 2018-01-15  5559.445
2 2018-01-16  5559.445
2 2018-01-17  5559.445
2 2018-01-18  5559.445
2 2018-01-19  5559.445
2 2018-01-20  5559.445
2 2018-01-21  5559.445
3 2018-01-22  5271.980
3 2018-01-23  5271.980
3 2018-01-24  5271.980
3 2018-01-25  5271.980
3 2018-01-26  5271.980
3 2018-01-27  5271.980
3 2018-01-28  5271.980