Home When using total_second to get Elapsed_time from csv, the number of seconds becomes minus when the date is crossed

Questions

When using total_second to get Elapsed_time from csv, the number of seconds becomes minus when the date is crossed

April 25, 2023

I tried to get the elapsed time using the following csv data, but the number of seconds in Elapsed_time is displayed as a minus sign when the date is crossed.

import numpy as np
import datetime as dt
import matplotlib.pyplot as plt
import pylab as pl
import pandas as pd 

dft = pd.read_csv("/content/drive/MyDrive/toa_data/to_csv_out_columns.csv", encoding="UTF-8")
dft["Time"] = pd.to_datetime(dft["Time"])
dft["second"] = pd.to_timedelta(dft["Time"].dt.time.astype(str)).dt.total_seconds()#.div(60)

dft = dft.set_index("second")
#dft.index = pd.to_datetime(dft.index)
dft['diff'] = dft.index.to_series().diff()
dft["Elapsed_time"]=dft["diff"].cumsum()

dft.head(5)

second  Time    Elapsed_time    value   diff
72446   2023-04-22 20:07:26     3.16    
72506   2023-04-22 20:08:26 60  3.16    60
72566   2023-04-22 20:09:26 120 3.17    60
72626   2023-04-22 20:10:26 180 3.18    60
--------------omission-----------------------------------
86306   2023-04-22 23:58:26 13860   3.41    60
86366   2023-04-22 23:59:26 13920   3.41    60
26  2023-04-23 0:00:26  -72420  3.41    -86340
86  2023-04-23 0:01:26  -72360  3.41    60
146 2023-04-23 0:02:26  -72300  3.41    60
--------------------------------------------------

I would like to output the following csv.

second  Time    Elapsed_time    value   diff
72446   2023-04-22 20:07:26     3.16    
72506   2023-04-22 20:08:26 60  3.16    60
72566   2023-04-22 20:09:26 120 3.17    60
72626   2023-04-22 20:10:26 180 3.18    60
--------------omission----------------------------
86306   2023-04-22 23:58:26 13860   3.41    60
86366   2023-04-22 23:59:26 13920   3.41    60
86426   2023-04-23 0:00:26  13960   3.41    60
86486   2023-04-23 0:01:26  14020   3.41    60
86546   2023-04-23 0:02:26  14080   3.41    60
--------------------------------------------------

>Solution :

I think you want:

dft["Elapsed_time"] = dft["Time"].sub(dft['Time'].iloc[0]).dt.total_seconds().div(60)

Output:

    second                Time  Elapsed_time  value     diff
0   72446 2023-04-22 20:07:26           0.0    NaN      NaN
1   72506 2023-04-22 20:08:26           1.0   3.16     60.0
2   72566 2023-04-22 20:09:26           2.0  60.00      NaN
3   72626 2023-04-22 20:10:26           3.0  60.00      NaN
4   86306 2023-04-22 23:58:26         231.0   3.41     60.0
5   86366 2023-04-22 23:59:26         232.0   3.41     60.0
6      26 2023-04-23 00:00:26         233.0   3.41 -86340.0
7      86 2023-04-23 00:01:26         234.0   3.41     60.0
8     146 2023-04-23 00:02:26         235.0   3.41     60.0