Add rows with consecutive dates

user_18015184 0
Viewed 36

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?

pandas rows add datetime python
1 Answers

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
user_163434640
Related