I have a Pandas dataframe with many columns. Two of then are timestamp (start
and end
).
start end value string
2021-12-01 14:00:00 2021-12-01 14:35:00 1 a
2021-12-01 17:00:00 2021-12-01 17:30:00 2 b
2021-12-01 14:00:00 2021-12-01 16:00:00 3 c
I need to standardize a timestamp marker (column time
below) from 5 to 5 minutes repeting the values from the other columns value
and string
that belongs to the same time interval, like this:
time start end value string
2021-12-01 14:00:00 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a
2021-12-01 14:05:00 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a
2021-12-01 14:10:00 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a
2021-12-01 14:15:00 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a
2021-12-01 14:20:00 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a
2021-12-01 14:25:00 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a
2021-12-01 14:30:00 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a
2021-12-01 17:00:00 2021-12-01 17:00:00 2021-12-01 17:30:00 2 b
2021-12-01 17:05:00 2021-12-01 17:00:00 2021-12-01 17:30:00 2 b
....
There are many intersections on time intervals, so I woldn't be able to use df.resample
with DatetimeIndex
.
You can create a date range with pd.date_range
for each row, and then explode
them:
new_df = df.assign(time=df.apply(lambda x: pd.date_range(x['start'], x['end'], freq='5min'), axis=1)).explode('time').reset_index(drop=True)
Output:
>>> new
start end value string time
0 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a 2021-12-01 14:00:00
1 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a 2021-12-01 14:05:00
2 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a 2021-12-01 14:10:00
3 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a 2021-12-01 14:15:00
4 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a 2021-12-01 14:20:00
5 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a 2021-12-01 14:25:00
6 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a 2021-12-01 14:30:00
7 2021-12-01 14:00:00 2021-12-01 14:35:00 1 a 2021-12-01 14:35:00
8 2021-12-01 17:00:00 2021-12-01 17:30:00 2 b 2021-12-01 17:00:00
9 2021-12-01 17:00:00 2021-12-01 17:30:00 2 b 2021-12-01 17:05:00
...