pandas 使用total_second从csv中获取Elapsed_time时,当日期交叉时,秒数变为负数

piwo6bdm  于 2023-04-28  发布在  其他
关注(0)|答案(2)|浏览(99)

我尝试使用以下csv数据获取经过的时间,但是当日期交叉时,Elapsed_time中的秒数显示为减号。

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
--------------------------------------------------

我想输出以下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
--------------------------------------------------
wfsdck30

wfsdck301#

我想你想要:

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

输出:

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
z4iuyo4d

z4iuyo4d2#

您需要正确计算行之间的时间差,使用pd.Series.shift()
示例:

import pandas as pd

x = pd.DataFrame(["2023-04-22 23:59:26",  "2023-04-23 00:00:26",  "2023-04-23 00:01:26", "2023-04-23 00:02:26"], columns=['datetime'])

x['x'] = pd.to_datetime(x['datetime'])
(x['x'].shift(-1) - x['x']).dt.total_seconds()

将返回:

0    60.0
1    60.0
2    60.0
3     NaN
Name: x, dtype: float64

相关问题