Search code examples
pythonpandasdate-range

How add date_range between two dates - Python Pandas


I would like to treat the time overlap between some days. As you can see in my df, I have a begin on the date 2019-10-25 and the end at 2019-10-27:

begin                       end                          info
2019-10-25 10:39:58.352073  2019-10-25 10:40:06.266782   toto
2019-10-25 16:35:22.485574  2019-10-27 09:50:31.713179   tata <------ HERE
2019-10-27 09:50:31.713179  2019-10-27 09:50:31.713192   titi
2019-10-28 14:04:33.095633  2019-10-28 14:05:07.639344   tete

I would like to add as many time slots (date 00:00:00; date 23:59:59.9) as there are between these two dates and copy the data info, like these:

2019-10-25 16:35:22.485574  2019-10-25 23:59:59.999999   tata
2019-10-26 00:00:00.000000  2019-10-26 23:59:59.999999   tata
2019-10-27 00:00:00.000000  2019-10-27 09:50:31.713179   tata
  • If date begin is different from end so => calculate the number of days
  • keep the begin and add the new end 'date 23:59:59.9'
  • add new date_range corresponding of number days
  • take the end and add the new begin 'date 00:00:00.0'
  • Fill 'info'

The final expected result:

begin                       end                          info
2019-10-25 10:39:58.352073  2019-10-25 10:40:06.266782   toto

2019-10-25 16:35:22.485574  2019-10-25 23:59:59.999999   tata
2019-10-26 00:00:00.000000  2019-10-26 23:59:59.999999   tata
2019-10-27 00:00:00.000000  2019-10-27 09:50:31.713179   tata

2019-10-27 09:50:31.713179  2019-10-27 09:50:31.713192   titi
2019-10-28 14:04:33.095633  2019-10-28 14:05:07.639344   tete

But I don't know how implement the date_range, fill info, add the specific number of rows.

Thanks your time


Solution

  • Assuming begin and end are already of Timestamp type:

    # Generate a series of Timedeltas for each row
    n = (
        (df['end'].dt.normalize() - df['begin'].dt.normalize())
            .apply(lambda d: [pd.Timedelta(days=i) for i in range(d.days+1)])
            .explode()
    ).rename('n')
    df = df.join(n)
    
    # Adjust the begin and end of each row
    adjusted_begin = np.max([
        df['begin'],
        df['begin'].dt.normalize() + df['n']
    ], axis=0)
    
    adjusted_end = np.min([
        df['end'],
        pd.Series(adjusted_begin).dt.normalize() + pd.Timedelta(days=1, milliseconds=-100)
    ], axis=0)
    
    # Final assembly
    df = df.assign(begin_=adjusted_begin, end_=adjusted_end)
    

    Result:

                           begin                        end  info      n                     begin_                       end_
    0 2019-10-25 10:39:58.352073 2019-10-25 10:40:06.266782  toto 0 days 2019-10-25 10:39:58.352073 2019-10-25 10:40:06.266782
    1 2019-10-25 16:35:22.485574 2019-10-27 09:50:31.713179  tata 0 days 2019-10-25 16:35:22.485574 2019-10-25 23:59:59.900000
    1 2019-10-25 16:35:22.485574 2019-10-27 09:50:31.713179  tata 1 days 2019-10-26 00:00:00.000000 2019-10-26 23:59:59.900000
    1 2019-10-25 16:35:22.485574 2019-10-27 09:50:31.713179  tata 2 days 2019-10-27 00:00:00.000000 2019-10-27 09:50:31.713179
    2 2019-10-27 09:50:31.713179 2019-10-27 09:50:31.713192  titi 0 days 2019-10-27 09:50:31.713179 2019-10-27 09:50:31.713192
    3 2019-10-28 14:04:33.095633 2019-10-28 14:05:07.639344  tete 0 days 2019-10-28 14:04:33.095633 2019-10-28 14:05:07.639344
    

    Trim off the columns you don't need