I’m trying to get a list of dates between two start and end date pandas columns, with individual IDs. I’ve found an answer that is basically what I’m after (https://stackoverflow.com/a/53409207/14463396) but this only works if the periods between the start and end dates don’t overlap, and I can’t seem to work out how to adapt this/use an alternative method to get the output I’m after.
Below is an example dataframe:
df = pd.DataFrame({'ID' : [1, 2, 3], 'Start Date': ["2021-06-01", "2021-06-02", "2021-06-04"], 'End Date': ["2021-06-03", "2021-06-05", "2021-06-08"]})
And this is what the desired output should be:
ID Start Date End Date Dates 0 1 2021-06-01 2021-06-03 2021-06-01 1 1 2021-06-01 2021-06-03 2021-06-02 2 1 2021-06-01 2021-06-03 2021-06-03 3 2 2021-06-02 2021-06-05 2021-06-02 4 2 2021-06-02 2021-06-05 2021-06-03 5 2 2021-06-02 2021-06-05 2021-06-04 6 2 2021-06-02 2021-06-05 2021-06-05 7 3 2021-06-04 2021-06-08 2021-06-04 8 3 2021-06-04 2021-06-08 2021-06-05 9 3 2021-06-04 2021-06-08 2021-06-06 10 3 2021-06-04 2021-06-08 2021-06-07 11 3 2021-06-04 2021-06-08 2021-06-08
Any help greatly appreciated :)
Advertisement
Answer
Try with create the date list then explode
df['Start Date'] = pd.to_datetime(df['Start Date']) df['End Date'] = pd.to_datetime(df['End Date']) df['Dates'] = [pd.date_range(x, y) for x , y in zip(df['Start Date'],df['End Date'])] df = df.explode('Dates') df Out[108]: ID Start Date End Date Dates 0 1 2021-06-01 2021-06-03 2021-06-01 0 1 2021-06-01 2021-06-03 2021-06-02 0 1 2021-06-01 2021-06-03 2021-06-03 1 2 2021-06-02 2021-06-05 2021-06-02 1 2 2021-06-02 2021-06-05 2021-06-03 1 2 2021-06-02 2021-06-05 2021-06-04 1 2 2021-06-02 2021-06-05 2021-06-05 2 3 2021-06-04 2021-06-08 2021-06-04 2 3 2021-06-04 2021-06-08 2021-06-05 2 3 2021-06-04 2021-06-08 2021-06-06 2 3 2021-06-04 2021-06-08 2021-06-07 2 3 2021-06-04 2021-06-08 2021-06-08