Python pandas time delta to timestamp

user_19885406 0
Viewed 39

I have imported a dataframe from an Excel file. The file contains a table of speed and duration in minutes. My ultimate aim is to convert this to a dataframe with even time steps at 1 second intervals. The speed should be repeated for each timestep. This is for import into another tool.

I'm struggling to understand where to start here. The only method I can find that seems like it might work is iterating over each row to calculate the timestamp and appending to a new dataframe. This seems really inefficient.

Can someone give me a steer here?

Sample input is below.

speed time
0 5 0.75
1 6 3.12
2 14 5.10
3 38 19.11
4 7 4.32
5 5 1.50
6 3 11.10
7 3 1.50
8 9 3.60
9 3 19.11
10 38 5.10
11 5 3.12
12 4 0.75

Sample output

timestamp speed
0 00:00:00 5
1 00:00:01 5
... ... ...
45 00:00:45 6
46 00:00:46 6
47 00:00:47 6
.. .. ..
pandas timedelta datetime python
1 Answers

you can create a datetime index from the accumulated sum of the duration column, then re-index to 1-second frequency. When reindexing, use method="bfill" to fill backwards the existing values. Ex:

import pandas as pd

df = pd.DataFrame({"speed": [5,6,14],
                   "time": [0.75,3.12,5.10]})

# arbitrary date as reference
refdate = pd.Timestamp("2022-01-01")
df.index = refdate + pd.to_timedelta(df["time"].cumsum(), unit="min").dt.floor("s")

df = df.reindex(
    pd.date_range(refdate, df.index.max(), freq='1s'),
    method="bfill"
    )

df[df.index > "2022-01-01 00:00:43"]
Out[62]: 
                     speed  time
2022-01-01 00:00:44      5  0.75
2022-01-01 00:00:45      5  0.75
2022-01-01 00:00:46      6  3.12
2022-01-01 00:00:47      6  3.12
2022-01-01 00:00:48      6  3.12
                   ...   ...
2022-01-01 00:08:54     14  5.10
2022-01-01 00:08:55     14  5.10
2022-01-01 00:08:56     14  5.10
2022-01-01 00:08:57     14  5.10
2022-01-01 00:08:58     14  5.10
user_101974180
Related