Add rows with consecutive dates

Question

I have this dataframe:

ClientID    ArrivalDate         DepartureDate    TotalRevenue
191609   2019-01-15 00:00:00 2019-01-17 00:00:00     5720
213156   2019-01-15 00:00:00 2019-01-16 00:00:00     2130

And I would like to create a new column, 'ReferenceDate', with value equal to 'ArrivalDate'. Then, I want to add a new row with all same information but 'ReferenceDate' increased one day, and repeat this process until 'ReferenceDate' is equal to 'DepartureDate'. This should be done for each ClientID. Final result should look like this:

ClientID     ArrivalDate         DepartureDate   TotalRevenue    ReferenceDate
191609   2019-01-15 00:00:00 2019-01-17 00:00:00    5720      2019-01-15 00:00:00
191609   2019-01-15 00:00:00 2019-01-17 00:00:00    5720      2019-01-16 00:00:00
191609   2019-01-15 00:00:00 2019-01-17 00:00:00    5720      2019-01-17 00:00:00
213156   2019-01-15 00:00:00 2019-01-16 00:00:00    2130      2019-01-15 00:00:00
213156   2019-01-15 00:00:00 2019-01-16 00:00:00    2130      2019-01-16 00:00:00

Is it possible?

If you look at https://stackoverflow.com/questions/22132525/add-column-with-number-of-days-between-dates-in-dataframe-pandas you will see how to calculate data and dates and iterate between them. Then make a new dataframe and add rows as needed. — Paul Brennan, Jun 28 '22 at 14:19

score 2 · Answer 1 · answered Jun 28 '22 at 14:21

You can add the dates with pandas.date_range, then explode:

(df
 .assign(ReferenceDate=[pd.date_range(a,d, freq='1d') for a,d in
                        zip(df['ArrivalDate'], df['DepartureDate'])])
 .explode('ReferenceDate')
)

output:

   ClientID          ArrivalDate        DepartureDate  TotalRevenue ReferenceDate
0    191609  2019-01-15 00:00:00  2019-01-17 00:00:00          5720    2019-01-15
0    191609  2019-01-15 00:00:00  2019-01-17 00:00:00          5720    2019-01-16
0    191609  2019-01-15 00:00:00  2019-01-17 00:00:00          5720    2019-01-17
1    213156  2019-01-15 00:00:00  2019-01-16 00:00:00          2130    2019-01-15
1    213156  2019-01-15 00:00:00  2019-01-16 00:00:00          2130    2019-01-16

Add rows with consecutive dates

1 Answers1