Search code examples
python-3.xpandastime-series

Use start and end dates to create time series dataset with Python

I have a dataset in Python with start and end dates and I need to be able to create a dataset with a row for each year & month in the start to end date range. I have successfully used the pd.date_range function to get the time series values, but now I'm stuck on how to convert this data into a row for each year/month. Here is what I have so far:

import pandas as pd

# Data
data = [['Oceanside', 'Puerto Rico', '2023-05-01', '2023-07-31'],
        ['Lakeside', 'Michigan', '2023-06-01', '2023-07-31'],
        ['Mountaintop', 'Colorado', '2023-04-01', '2023-07-31'],
        ['Desert', 'Arizona', '2023-05-01', '2023-08-30'],
        ['Countryside', 'Tennessee', '2023-02-01', '2023-03-31']]

# Create pandas DataFrame
df = pd.DataFrame(data, columns=['Hotel Name', 'Location', 'Availability Start Date', 'Availability End Date'])

# Create columns with date format
df['Start_Date']= pd.to_datetime(df['Availability Start Date'], dayfirst=True)
df['End_Date']= pd.to_datetime(df['Availability End Date'], dayfirst=True)

# Create time series
df = (pd.concat([pd.DataFrame(pd.date_range(r.Start_Date, r.End_Date, freq="MS"))
               for r in df.itertuples()]))

print(df)

Here is my output:

enter image description here

Here is the dataset that I need to output:

enter image description here

I think I'm close, but I'm not sure how to pass the appropriate key to the function and return the key values with the time series output. Any help is appreciated - thank you!

Solution

  • Quick and easy solution. Create dateranges with frequency month start, then explode the dataframe

    col = 'Year/Month Available'
df[col] = df.apply(lambda r: pd.date_range(r['Start_Date'], r['End_Date'], freq='MS'), axis=1)
result = df[['Hotel Name', 'Location', col]].explode(col)

        Hotel Name     Location Year/Month Available
0    Oceanside  Puerto Rico           2023-05-01
0    Oceanside  Puerto Rico           2023-06-01
0    Oceanside  Puerto Rico           2023-07-01
1     Lakeside     Michigan           2023-06-01
1     Lakeside     Michigan           2023-07-01
2  Mountaintop     Colorado           2023-04-01
2  Mountaintop     Colorado           2023-05-01
2  Mountaintop     Colorado           2023-06-01
2  Mountaintop     Colorado           2023-07-01
3       Desert      Arizona           2023-05-01
3       Desert      Arizona           2023-06-01
3       Desert      Arizona           2023-07-01
3       Desert      Arizona           2023-08-01
4  Countryside    Tennessee           2023-02-01
4  Countryside    Tennessee           2023-03-01