Timestamp range to hours Python Pandas

Viewed 132

Is it a way to split timestamp range to hours ?

FROM:

Person start stop
Tom 2019-01-01 12:15:00 2019-01-01 14:25:00

TO:

Person start stop
Tom 2019-01-01 12:15:00 2019-01-01 13:00:00
Tom 2019-01-01 13:00:00 2019-01-01 14:00:00
Tom 2019-01-01 14:00:00 2019-01-01 14:25:00
1 Answers

First get all the ranges between start.floor('h') and stop.ceil('h') with hour frequency using pd.date_range, then return start, range from second to second last, and stop, it'll give a list, so explode it, assign stop to this range by shifting it by -1, and finally assign start to the range, and dropna rows (this na will appear due to the effect of shift, and is not required.)

def getRange(row):
    rang = pd.date_range(row['start'].floor('h'), row['stop'].ceil('h'), freq='h')
    return [row['start']] + rang.to_list()[1:-1] + [row['stop']]


df =(df.assign(range=df.apply(getRange, axis=1))
.drop(columns=['start', 'stop'])
.explode('range')
  )
df = df.assign(stop=df['range'].shift(-1)).dropna().rename(columns={'range':'start'})

OUTPUT:

  Person               start                stop
0    Tom 2019-01-01 12:15:00 2019-01-01 13:00:00
0    Tom 2019-01-01 13:00:00 2019-01-01 14:00:00
0    Tom 2019-01-01 14:00:00 2019-01-01 14:25:00
Related