racket99 racket99 - 2 months ago 7
Python Question

Creating a time range in python from a set of custom dates

Let's say I have a set of dates in a DateTimeIndex. There are no times just dates and for each date in the set I would like to have multiple DateTimes. For example for each day I would like index variables every hour from 10am-2pm? I have been using pd.date_range which works well for 1 datetime but not sure how to apply across a list of custom dates.

Answer

Consider a cross join with date + time operation.

# Example data:
#              NumData1  NumData2  NumData3  NumData4  NumData5
# DateExample                                                  
# 2016-10-01   0.299950  0.740431  0.275306  0.168967  0.902464
# 2016-10-02   0.335424  0.751552  0.458261  0.277734  0.204546
# 2016-10-03   0.376473  0.215968  0.757137  0.713013  0.337774
# 2016-10-04   0.078788  0.055791  0.766027  0.507360  0.808768
# 2016-10-05   0.860383  0.920024  0.922637  0.501969  0.097542

df['Date'] = df.index              # CREATE A DATE COLUMN FROM INDEX
df['key'] = 1                      # CROSS JOIN MERGE KEY

timedf = pd.DataFrame({'Hour': [pd.Timedelta(hours=h) for h in list(range(10,15))],
                       'key': 1})

df = pd.merge(df, timedf, on=['key'])                     # CROSS JOIN (M x N cols)
df['Date'] = df['Date'] + df['Hour']                      # DATE + TIME OPERATION 
df = df.set_index('Date').drop(['key', 'Hour'], axis=1)   # CREATE NEW INDEX W/ FINAL COLS

print(df)
#                      NumData1  NumData2  NumData3  NumData4  NumData5
# Date                                                                 
# 2016-10-01 10:00:00  0.299950  0.740431  0.275306  0.168967  0.902464
# 2016-10-01 11:00:00  0.299950  0.740431  0.275306  0.168967  0.902464
# 2016-10-01 12:00:00  0.299950  0.740431  0.275306  0.168967  0.902464
# 2016-10-01 13:00:00  0.299950  0.740431  0.275306  0.168967  0.902464
# 2016-10-01 14:00:00  0.299950  0.740431  0.275306  0.168967  0.902464
# 2016-10-02 10:00:00  0.335424  0.751552  0.458261  0.277734  0.204546
# 2016-10-02 11:00:00  0.335424  0.751552  0.458261  0.277734  0.204546
# 2016-10-02 12:00:00  0.335424  0.751552  0.458261  0.277734  0.204546
# 2016-10-02 13:00:00  0.335424  0.751552  0.458261  0.277734  0.204546
# 2016-10-02 14:00:00  0.335424  0.751552  0.458261  0.277734  0.204546
# 2016-10-03 10:00:00  0.376473  0.215968  0.757137  0.713013  0.337774
# 2016-10-03 11:00:00  0.376473  0.215968  0.757137  0.713013  0.337774
# ...