CODEWITHSUNDEEP
pythonhivepyspark
Espanta
Espanta

Reputation: 1140

Create 5-minute interval between two timestamp

I have a bunch of data point for each there are two columns: start_dt and end_dt. I am wondering how can I split the time gap between start_dt and end_dt into 5 minutes interval?

For instance,

id+++++++start_tm ++++++++++++++ end_dt

1+++++++2019-01-01 10:00 +++++++ 2019-01-01 11:00

=====================================================

What I am looking for is:

id+++++++start_tm ++++++++++++++ end_dt

1+++++++2019-01-01 10:00 +++++++ 2019-01-01 10:05

1+++++++2019-01-01 10:05 +++++++ 2019-01-01 10:10

1+++++++2019-01-01 10:10 +++++++ 2019-01-01 10:15

1+++++++2019-01-01 10:15 +++++++ 2019-01-01 10:20

==================================================

and so fort

is there any function out of the box to do so?

If not, any help to create this function is wonderful

Upvotes: 1

Views: 3035

Answers (2)

Woods Chen
Woods Chen

Reputation: 620

I don't know pyspark, but if you are using pandas this works. (and pyspark may be similar):

1:create data

import pandas as pd
import numpy as np
data = pd.DataFrame({
    'id':[1, 2],
    'start_tm': pd.date_range('2019-01-01 00:00', periods=2, freq='D'),
    'end_dt': pd.date_range('2019-01-01 00:30', periods=2, freq='D')})
# pandas dataframe is similar to the data in pyspark

output

id  start_tm    end_dt
1   2019-01-01  2019-01-01 00:30:00
2   2019-01-02  2019-01-02 00:30:00

2: split columns

period = np.timedelta64(5, 'm') # 5 minutes
idx = (data['end_dt'] - data['start_tm']) > period
while idx.any():
    new_data = data[idx].copy()
    new_data['start_tm'] = new_data['start_tm'] + period
    data.loc[idx, 'end_dt'] = (data[idx]['start_tm'] + period).values
    data = pd.concat([data, new_data], axis=0)
    idx = (data['end_dt'] - data['start_tm']) > period

output

id  start_tm    end_dt
1   2019-01-01 00:00:00     2019-01-01 00:05:00
2   2019-01-02 00:00:00     2019-01-02 00:05:00
1   2019-01-01 00:05:00     2019-01-01 00:10:00
2   2019-01-02 00:05:00     2019-01-02 00:10:00
1   2019-01-01 00:10:00     2019-01-01 00:15:00
2   2019-01-02 00:10:00     2019-01-02 00:15:00
1   2019-01-01 00:15:00     2019-01-01 00:20:00
2   2019-01-02 00:15:00     2019-01-02 00:20:00
1   2019-01-01 00:20:00     2019-01-01 00:25:00
2   2019-01-02 00:20:00     2019-01-02 00:25:00
1   2019-01-01 00:25:00     2019-01-01 00:30:00
2   2019-01-02 00:25:00     2019-01-02 00:30:00

Upvotes: 1

CryptoFool
CryptoFool

Reputation: 23119

If you have two Python datetime objects representing a timespan, and you just want to break that timespan up into 5 minute intervals represented by datetime objects, you could just do this:

import datetime

d1 = datetime.datetime(2019, 1, 1, 10, 0)
d2 = datetime.datetime(2019, 1, 1, 11, 0)
delta = datetime.timedelta(minutes=5)
times = []
while d1 < d2:
    times.append(d1)
    d1 += delta
times.append(d2)

for i in range(len(times) - 1):
    print("{} - {}".format(times[i], times[i+1]))

Output:

2019-01-01 10:00:00 - 2019-01-01 10:05:00
2019-01-01 10:05:00 - 2019-01-01 10:10:00
2019-01-01 10:10:00 - 2019-01-01 10:15:00
2019-01-01 10:15:00 - 2019-01-01 10:20:00
2019-01-01 10:20:00 - 2019-01-01 10:25:00
2019-01-01 10:25:00 - 2019-01-01 10:30:00
2019-01-01 10:30:00 - 2019-01-01 10:35:00
2019-01-01 10:35:00 - 2019-01-01 10:40:00
2019-01-01 10:40:00 - 2019-01-01 10:45:00
2019-01-01 10:45:00 - 2019-01-01 10:50:00
2019-01-01 10:50:00 - 2019-01-01 10:55:00
2019-01-01 10:55:00 - 2019-01-01 11:00:00

This should handle a period that isn't an even multiple of the delta, giving you a shorter interval at the end.

Upvotes: 2

Related Questions