python - groupby(pd.TimeGrouper ('time_interval' )).idxmin() 错误生成的空数据帧

Question

我面临的任务是找到时间序列中测量值在时间序列的等距时间间隔内最小的确切时间。

我尝试使用df.groupby(pd.TimeGrouper('time_interval')).idxmin()执行此任务，但我遇到此方法的意外(可能是错误的)行为:当使用df.groupby(pd.TimeGrouper('time_interval')).idxmin()时方法在具有日期时间索引的数据帧上，该数据帧包含(至少)两行之间大于重新采样间隔的间隔，它会生成一个完全空的数据帧，而不是用“NaT”填充附加间隔(例如 df.groupby(pd.TimeGrouper('time_interval')).xmin() 填充附加间隔)间隔为“NaN”)。有谁知道这个问题的解决方法(或者这个方法是否有错误修复)？我在帖子末尾放置了一个最小的工作示例和一些内联讨论。

干杯，

西蒙

Python版本:Python 3.6.0::Anaconda 4.3.1(64位)

Pandas 版本:0.19.2

import datetime
import pandas as pd

timestamp_list = [1493992554.897, 1493999093.997, 1493999108.733, 1493999116.101, 1493999117.943, 1493999119.785, 1493999121.627, 1493999123.469, 1493999125.311, 1493999127.153, 1493999128.995, 1493999130.837, 1493999132.679, 1493999134.521, 1493999136.363, 1493999138.205, 1493999140.047, 1493999141.889, 1493999143.731, 1493999145.573, 1493999147.415, 1493999149.257, 1493999151.099, 1493999152.941, 1493999154.783, 1493999156.625, 1493999158.467, 1493999160.309, 1493999162.151, 1493999163.993]
value_list = [2.52962e-41, 2.52962e-41, 11.9625, 12.033420000000001, 12.069, 12.0784, 12.080933333333334, 12.080549999999999, 12.080233333333332, 12.078975, 12.033750000000001, 11.9472, 11.910966666666667, 11.902700000000001, 11.899766666666666, 11.898925, 11.898733333333332, 11.8987, 11.921174999999998, 11.982775, 12.010975000000002, 12.019466666666666, 12.021700000000001, 12.0224, 12.0225, 12.0226, 11.95525, 11.776133333333334, 11.65815, 11.624400000000001]

dt_list = [datetime.datetime.fromtimestamp(x) for x in timestamp_list]

time_frame =  pd.DataFrame(index=dt_list, data=value_list)
time_frame.columns = ['value']

time_frame.head()
# Out[11]:
#                                value
# 2017-05-05 15:55:54.897  2.529620e-41  <- Large time diff (larger than resample length)
# 2017-05-05 17:44:53.997  2.529620e-41  <-
# 2017-05-05 17:45:08.733  1.196250e+01
# 2017-05-05 17:45:16.101  1.203342e+01
# 2017-05-05 17:45:17.943  1.206900e+01

# I want to resample this dataframe and determine the min in each interval
# this works fine:

tf_resampled_min = time_frame.groupby(pd.TimeGrouper('60000L')).min()
tf_resampled_min.head()

#Out[13]:    
#                        value
#2017-05-05 15:55:00  2.529620e-41
#2017-05-05 15:56:00           NaN
#2017-05-05 15:57:00           NaN
#2017-05-05 15:58:00           NaN
#2017-05-05 15:59:00           NaN

# I also want to determine the exact time the mmin occured, and here I encounter a problem:

tf_resampled_idxmin = time_frame.groupby(pd.TimeGrouper('60000L')).idxmin()
tf_resampled_idxmin.head()

#Out[14]:
#Empty DataFrame
#Columns: []
#Index: []

# I expected something like:
#                        
#2017-05-05 15:55:00  2017-05-05 15:55:54.897
#2017-05-05 15:56:00           NaT
#2017-05-05 15:57:00           NaT
#2017-05-05 15:58:00           NaT
#2017-05-05 15:59:00           NaT

# With this output I would still be able to determine the minidx in the valid regions, but with the empty dataframe, all information is lost.

# The Problem is indeed the time gap between the first two entries. If I remove them, I get:

timestamp_list2 = [1493999093.997, 1493999108.733, 1493999116.101, 1493999117.943, 1493999119.785, 1493999121.627, 1493999123.469, 1493999125.311, 1493999127.153, 1493999128.995, 1493999130.837, 1493999132.679, 1493999134.521, 1493999136.363, 1493999138.205, 1493999140.047, 1493999141.889, 1493999143.731, 1493999145.573, 1493999147.415, 1493999149.257, 1493999151.099, 1493999152.941, 1493999154.783, 1493999156.625, 1493999158.467, 1493999160.309, 1493999162.151, 1493999163.993]
value_list2 = [2.52962e-41, 11.9625, 12.033420000000001, 12.069, 12.0784, 12.080933333333334, 12.080549999999999, 12.080233333333332, 12.078975, 12.033750000000001, 11.9472, 11.910966666666667, 11.902700000000001, 11.899766666666666, 11.898925, 11.898733333333332, 11.8987, 11.921174999999998, 11.982775, 12.010975000000002, 12.019466666666666, 12.021700000000001, 12.0224, 12.0225, 12.0226, 11.95525, 11.776133333333334, 11.65815, 11.624400000000001]

dt_list2 = [datetime.datetime.fromtimestamp(x) for x in timestamp_list2]
time_frame2 =  pd.DataFrame(index=dt_list2, data=value_list2)
time_frame2.columns = ['value']

tf_resampled_idxmin2 = time_frame2.groupby(pd.TimeGrouper('60000L')).idxmin()
tf_resampled_idxmin2.head()

#Out[20]:
#                                      value
#2017-05-05 17:44:00 2017-05-05 17:44:53.997
#2017-05-05 17:45:00 2017-05-05 17:45:41.889
#2017-05-05 17:46:00 2017-05-05 17:46:03.993

Answer 1

我找到了解决该问题的方法:

import datetime
import pandas as pd
import numpy as np

timestamp_list = [1493992554.897, 1493999093.997, 1493999108.733, 1493999116.101, 1493999117.943, 1493999119.785, 1493999121.627, 1493999123.469, 1493999125.311, 1493999127.153, 1493999128.995, 1493999130.837, 1493999132.679, 1493999134.521, 1493999136.363, 1493999138.205, 1493999140.047, 1493999141.889, 1493999143.731, 1493999145.573, 1493999147.415, 1493999149.257, 1493999151.099, 1493999152.941, 1493999154.783, 1493999156.625, 1493999158.467, 1493999160.309, 1493999162.151, 1493999163.993]
value_list = [2.52962e-41, 2.52962e-41, 11.9625, 12.033420000000001, 12.069, 12.0784, 12.080933333333334, 12.080549999999999, 12.080233333333332, 12.078975, 12.033750000000001, 11.9472, 11.910966666666667, 11.902700000000001, 11.899766666666666, 11.898925, 11.898733333333332, 11.8987, 11.921174999999998, 11.982775, 12.010975000000002, 12.019466666666666, 12.021700000000001, 12.0224, 12.0225, 12.0226, 11.95525, 11.776133333333334, 11.65815, 11.624400000000001]

dt_list = [datetime.datetime.fromtimestamp(x) for x in timestamp_list]

time_frame =  pd.DataFrame(index=dt_list, data=value_list)
time_frame.columns = ['value']

tf_resampled_idxmin = time_frame.resample("60000L").agg([lambda x: np.argmin(x) if len(x) > 0 else np.datetime64('NaT')])
print(tf_resampled_idxmin)

#                                  value
#                               <lambda>
#2017-05-05 15:55:00 2017-05-05 15:55:54.897
#2017-05-05 15:56:00                     NaT
#2017-05-05 16:23:00                     NaT
#2017-05-05 16:24:00                     NaT
#...                                     ...
#2017-05-05 17:17:00                     NaT
#2017-05-05 17:18:00                     NaT
#2017-05-05 17:43:00                     NaT
#2017-05-05 17:44:00 2017-05-05 17:44:53.997
#2017-05-05 17:45:00 2017-05-05 17:45:41.889
#2017-05-05 17:46:00 2017-05-05 17:46:03.993

诀窍是使用 .agg([np.argmin()]) 和 lambda 函数来实现自己版本的 idxmin() 来捕获空列表的情况。