我想计算Value
在范围内(41 - 46(的时间段,并在下面的df
保持相同的值。Value
应仅在发生更改时更新,否则保持不变。
Id Timestamp Value
34213951 34214809 2012-05-01 08:33:47.127 41.5
34214252 34215110 2012-05-01 08:39:06.270 41.5
34214423 34215281 2012-05-01 08:41:56.240 40.5
34214602 34215460 2012-05-01 08:44:55.777 39.5
34214873 34215731 2012-05-01 08:49:25.600 38.5
34215071 34215929 2012-05-01 08:53:04.593 37.5
34215342 34216200 2012-05-01 08:56:47.257 36.5
34216007 34216865 2012-05-01 09:07:24.370 34.5
34216443 34217301 2012-05-01 09:14:46.120 33.5
34216884 34217742 2012-05-01 09:22:51.907 32.5
34217190 34218048 2012-05-01 09:29:00.023 31.5
34217803 34218661 2012-05-01 09:40:08.483 30.5
34218381 34219239 2012-05-01 09:50:20.440 30.5
34218382 34219240 2012-05-01 09:50:22.317 32.5
34218388 34219246 2012-05-01 09:50:26.067 37.5
34218389 34219247 2012-05-01 09:50:27.940 39.0
34218392 34219250 2012-05-01 09:50:29.817 39.5
34218393 34219251 2012-05-01 09:50:31.690 40.5
34218396 34219254 2012-05-01 09:50:35.440 41.0
34218789 34219647 2012-05-01 09:56:55.327 41.0
34218990 34219848 2012-05-01 10:00:07.847 40.0
跟:
def samevalue(df):
df = df.reset_index(drop=True)
dataframe = []
flag = 0
start_time = []
start_value = []
end_time = []
end_value = []
for i in range(len(df.index)):
if flag == 0:
if ((df.loc[i, 'Value']>=41) and
(df.loc[i, 'Value']<=46)):
start_time = df.loc[i, 'Timestamp']
start_value = df.loc[i, 'Value']
flag = 1
elif flag == 1:
if (df.loc[i, 'Data'] != start_temp):
end_time = df.loc[i, 'Timestamp']
end_value = df.loc[i, 'Value']
flag = 0
dataframe.append([start_time, end_time, start_value, end_value])
data1 = pd.DataFrame(dataframe, columns= ["StartTime", "EndTime", "StartValue", "EndValue"])
return data1
samevalue(df)
实际输出:
StartTime EndTime StartValue EndValue
0 2012-05-01 08:33:47.127 [] 41.5 []
1 2012-05-01 08:33:47.127 2012-05-01 08:41:56.240000 41.5 40.5
2 2012-05-01 09:50:35.440 2012-05-01 08:41:56.240000 41.0 40.5
3 2012-05-01 09:50:35.440 2012-05-01 10:00:07.847000 41.0 40
预期输出:
StartTime EndTime StartValue EndValue
0 2012-05-01 08:33:47.127 2012-05-01 08:41:56.240 41.5 40.5
1 2012-05-01 09:50:35.440 2012-05-01 10:00:07.847 41.0 40.0
我本以为EndTime
总是在StartTime
之后,但事实并非如此。我错过了什么吗?
这是一种矢量化的方法。主要使用shift
来比较相邻的行。
df["in_range"] = (df.Value >= 41) & (df.Value <= 46)
df["end_of_range"] = df.in_range.shift() & ~df.in_range
df["start_of_range"] = ~df.in_range.shift(1).fillna(False) & df.in_range
此时,数据帧是(我删除了索引和 Id,以获得更好的可见性(:
Timestamp Value in_range end_of_range start_of_range
0 2012-05-01 08:33:47.127 41.5 True False True
1 2012-05-01 08:39:06.270 41.5 True False False
2 2012-05-01 08:41:56.240 40.5 False True False
3 2012-05-01 08:44:55.777 39.5 False False False
...
我现在创建两个数据帧 - 一个用于所有"范围开始"记录,另一个用于所有"范围结束"记录:
starts = df[df.start_of_range][["Timestamp", "Value"]]
ends = df[df.end_of_range][["Timestamp", "Value"]]
# reset the index of these two dataframe, so I can easility concat them later.
starts.index = range(len(starts))
ends.index = range(len(starts))
">开始"和"结束"的值现在是:
Timestamp Value
0 2012-05-01 08:33:47.127 41.5
1 2012-05-01 09:50:35.440 41.0
Timestamp Value
0 2012-05-01 08:41:56.240 40.5
1 2012-05-01 10:00:07.847 40.0
现在剩下的就是concat
两个新创建的数据帧,以便每个开始记录与其相应的结束记录对齐。
res = pd.concat([starts, ends], axis=1)
res.columns = ["StartTime", "EndTime", "StartValue", "EndValue"]
结果是:
StartTime EndTime StartValue EndValue
0 2012-05-01 08:33:47.127 41.5 2012-05-01 08:41:56.240 40.5
1 2012-05-01 09:50:35.440 41.0 2012-05-01 10:00:07.847 40.0