1

I have a dataframe that looks like this: 

Date               Name  Provider     Task      StartDateTime       LastDateTime
2020-01-01 00:00:00 Bob     PEM   ED A 7a-4p    2020-01-01 07:00:00 2020-01-01 16:00:00
2020-01-02 00:00:00 Tom     PEM   ED C 10p-2a   2020-01-02 22:00:00 2020-01-03 02:00:00

I would like to list the number of hours between each person's StartDateTime LastDateTime(datetime64[ns]) and then create an updated dataframe to reflect said lists. So for example, the updated dataframe would look like this:

Name    Date         Hour
Bob     2020-01-01    7
Bob     2020-01-01    8
Bob     2020-01-01    9
...
Tom     2020-01-02    22
Tom     2020-01-02    23
Tom     2020-01-03    0
Tom     2020-01-03    1
...

I honestly do not have a solid idea where to start, I've found some articles that may provide a foundation but I'm not sure how to adapt my query to the below code since I want the counts based on the row and hour values. 

def daterange(date1, date2):
    for n in range(int ((date2 - date1).days)+1):
        yield date1 + timedelta(n)

start_dt = date(2015, 12, 20)
end_dt = date(2016, 1, 11)
for dt in daterange(start_dt, end_dt):
    print(dt.strftime("%Y-%m-%d"))

https://www.w3resource.com/python-exercises/date-time-exercise/python-date-time-exercise-50.php

Raven
  • 849
  • 6
  • 17
  • 3
    Does this answer your question? [Calculate Pandas DataFrame Time Difference Between Two Columns in Hours and Minutes](https://stackoverflow.com/questions/22923775/calculate-pandas-dataframe-time-difference-between-two-columns-in-hours-and-minu) – AMC May 19 '20 at 20:18

1 Answers1

2

Let us create the range of datetime then , use explode 

df['Date']=[pd.date_range(x,y , freq='H') for x , y in zip(df.StartDateTime,df.LastDateTime)]
s=df[['Date','Name']].explode('Date').reset_index(drop=True)
s['Hour']=s.Date.dt.hour
s['Date']=s.Date.dt.date
s.head()
         Date Name  Hour
0  2020-01-01  Bob     7
1  2020-01-01  Bob     8
2  2020-01-01  Bob     9
3  2020-01-01  Bob    10
4  2020-01-01  Bob    11
BENY
  • 317,841
  • 20
  • 164
  • 234
  • This looks great two things 1) Getting an error 'AttributeError: 'DataFrame' object has no attribute 'explode' and 2) I'm new to Python, can you please annotate the code. Reading about explode here https://www.w3resource.com/pandas/series/series-explode.php – Raven Apr 11 '20 at 15:26
  • 1
    @Raven explode is new after pandas 0.25.0 if you use old version please update – BENY Apr 11 '20 at 15:29
  • 1
    @Raven also for explode https://stackoverflow.com/questions/53218931/how-to-unnest-explode-a-column-in-a-pandas-dataframe/53218939#53218939 – BENY Apr 11 '20 at 15:30
  • 1
    @Raven about zip https://stackoverflow.com/questions/49783594/for-loop-and-zip-in-python – BENY Apr 11 '20 at 15:31