Create new rows and repeat the values based on time interval if they belong to

user_17735460 0
Viewed 195

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.

pandas rows repeat datetime python
1 Answers

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
...
Admin1
Related