按python中的时间排序两个日志数据数组
按python中的时间排序两个日志数据数组
提问于 2017-11-27 15:11:58
我想创建一个基于两个数据帧的新矩阵。第一个数组，df1每秒收集数据，第二个数组df2每隔30分钟收集数据。理想情况下，来自df2的数据将添加到df1中以表示正确的时间序列。这些数据在实践中是完全不规则的，如果某些传感器被激活，数据就会随机进入。举例表如下：
df1 = [['10-11', '14:21:01', '65'],
['10-11', '14:21:02', '55'],
['10-11', '14:21:03', '26'],
['12-11', '17:29:58', '89'],
['12-11', '17:29:59', '12'],
['12-11', '17:30:00', '65'],
['12-11', '17:30:01', '3'],
['12-11', '17:30:02', '66'],
['12-11', '17:30:03', '971']]
df2 = [['10-11', '14:30', '9.9','112'],
['10-11', '15:00', '7.8','165'],
['12-11', '17:00', '6.1','154'],
['12-11', '17:30', '6.2','165'],
['12-11', '18:00', '6.5','170']]我想对数据进行排序，例如，df1中数据在14:00:00 - 14:29:59之间的行会将'9.9‘、'112’的值添加到每一行，这对应于df2中的相关值。这样做的想法是，生成的数据框架将显示如下所示的数组：
finaldf = [['10-11', '14:21:01', '65', '9.9','112'],
['10-11', '14:21:02', '55', '9.9','112'],
['10-11', '14:21:03', '26', '9.9','112'],
['12-11', '17:29:58', '89', '6.2','165'],
['12-11', '17:29:59', '12', '6.2','165'],
['12-11', '17:30:00', '65', '6.5','170'],
['12-11', '17:30:01', '3', '6.5','170'],
['12-11', '17:30:02', '66', '6.5','170'],
['12-11', '17:30:03', '971', '6.5','170']]我很抱歉，如果这给人的印象是复杂的，任何帮助解决这个问题，或指出我的正确方向，将不胜感激。
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-11-27 17:40:08
您可以在创建日期时间索引之后使用pd.merge_asof：
df_1 = pd.DataFrame(df1)
df_2 = pd.DataFrame(df2)
df_1 = df_1.set_index(pd.to_datetime(df_1[0]+' ' +df_1[1],format='%m-%d %H:%M:%S'))
df_2 = df_2.set_index(pd.to_datetime(df_2[0]+ ' ' +df_2[1],format='%m-%d %H:%M'))
arr_out = pd.merge_asof(df_1, df_2,
right_index=True, left_index=True,
direction='forward', suffixes=('','_r'))\
.drop(['0_r','1_r'], 1).values.tolist()
arr_out输出：
[['10-11', '14:21:01', '65', '9.9', '112'],
['10-11', '14:21:02', '55', '9.9', '112'],
['10-11', '14:21:03', '26', '9.9', '112'],
['12-11', '17:29:58', '89', '6.2', '165'],
['12-11', '17:29:59', '12', '6.2', '165'],
['12-11', '17:30:00', '65', '6.2', '165'],
['12-11', '17:30:01', '3', '6.5', '170'],
['12-11', '17:30:02', '66', '6.5', '170'],
['12-11', '17:30:03', '971', '6.5', '170']]Stack Overflow用户
发布于 2017-11-27 17:16:21
您可以在df1中创建新列，并通过在df2中迭代行(对于大型DataFrames来说可能非常慢)并使用datetime过滤时间来填充它们。从你的例子
import pandas as pd
import datetime as dt
df1 = [['10-11', '14:21:01', '65'],
['10-11', '14:21:02', '55'],
['10-11', '14:21:03', '26'],
['12-11', '17:29:58', '89'],
['12-11', '17:29:59', '12'],
['12-11', '17:30:00', '65'],
['12-11', '17:30:01', '3'],
['12-11', '17:30:02', '66'],
['12-11', '17:30:03', '971']]
df2 = [['10-11', '14:30', '9.9','112'],
['10-11', '15:00', '7.8','165'],
['12-11', '17:00', '6.1','154'],
['12-11', '17:30', '6.2','165'],
['12-11', '18:00', '6.5','170']]
# convert to pandas DataFrame and name columns
df1 = pd.DataFrame(df1, columns=['date', 'time', 'val1'])
df2 = pd.DataFrame(df2, columns=['date', 'time', 'val2', 'val3'])
finaldf = df1
finaldf['val2'] = -1 # initialize to -1
finaldf['val3'] = -1 # initialize to -1
for i, d, t, v2, v3 in df2.itertuples():
# get the starting time by subtracting 30 minutes
tmin = (dt.datetime.strptime(t, '%H:%M') + dt.timedelta(minutes=-30)).time().strftime("%H:%M:%S")
tmax = t + ":00" # add seconds to end of string
# filter df1 by matching date and time range
index = (finaldf['date'] == d) & (finaldf['time'] >= tmin) & (finaldf['time'] < tmax)
finaldf.loc[index, 'val2'] = v2
finaldf.loc[index, 'val3'] = v3输出
print finaldf
date time val1 val2 val3
0 10-11 14:21:01 65 9.9 112
1 10-11 14:21:02 55 9.9 112
2 10-11 14:21:03 26 9.9 112
3 12-11 17:29:58 89 6.2 165
4 12-11 17:29:59 12 6.2 165
5 12-11 17:30:00 65 6.5 170
6 12-11 17:30:01 3 6.5 170
7 12-11 17:30:02 66 6.5 170
8 12-11 17:30:03 971 6.5 170注意，在这段代码中，我将时间字符串转换为datetime，并调用time()函数来获取时间。更好的方法可能是将整个日期和时间转换为datetime.datetime，并将timedelta应用于整个事件。(我无法从你的数据中推断出是MM-DD还是DD-MM。)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47514150
复制相关文章
相似问题
腾讯云开发者
