如何使用一个数据框的日期和值，并在另一个数据框中使用此条件进行搜索

Question

我想在依赖于日期的另一个数据框中搜索值(来自一个数据框)

我有一个DatetimeIndex基于1分钟的频率的数据帧。我将Dataframe重新采样到每天5分钟的频率。下面是代码和输出:

agg_dict = {'open': 'first','high': 'max','low': 'min','cls': 'last','vol': 'sum'}
data_5min = data_rth.resample('5min').agg(agg_dict).dropna().round(2).sort_index(ascending=False)
data_daily = data_rth.resample('D').agg(agg_dict).dropna().round(2).sort_index(ascending=False)
data_weekly= data_rth.resample('W').agg(agg_dict).dropna().round(2).sort_index(ascending=False)
data_monthly= data_rth.resample('M').agg(agg_dict).dropna().round(2).sort_index(ascending=False)
print('data_daily','n',data_daily['high'].head())
print('data_5min','n',data_5min['high'].head(24))
output:
data_daily 
time
2021-08-05    441.85
2021-08-04    441.12
2021-08-03    441.28
2021-08-02    440.93
2021-07-30    440.06
Name: high, dtype: float64
data_5min 
time
2021-08-05 16:00:00    441.85
2021-08-05 15:55:00    441.65
2021-08-05 15:50:00    441.39
2021-08-05 15:45:00    441.23
2021-08-05 15:40:00    441.24
2021-08-05 15:35:00    441.11
2021-08-05 15:30:00    440.90
2021-08-05 15:25:00    440.83
2021-08-05 15:20:00    440.78
2021-08-05 15:15:00    440.86
2021-08-05 15:10:00    440.94
2021-08-05 15:05:00    440.96
2021-08-05 15:00:00    440.89
2021-08-05 14:55:00    440.83
2021-08-05 14:50:00    440.87
2021-08-05 14:45:00    440.88
2021-08-05 14:40:00    440.96
2021-08-05 14:35:00    440.88
2021-08-05 14:30:00    440.86
2021-08-05 14:25:00    440.91
2021-08-05 14:20:00    440.96
2021-08-05 14:15:00    440.96
2021-08-05 14:10:00    440.98
2021-08-05 14:05:00    441.12
Name: high, dtype: float64

我现在想看看每天的高点在5分钟帧中显示在哪里。我试着

data_5min['high'].isin(data_daily['high'])
what gives me this output:
time
2021-08-05 16:00:00     True
2021-08-05 15:55:00    False
2021-08-05 15:50:00    False
2021-08-05 15:45:00    False
2021-08-05 15:40:00    False
2021-08-05 15:35:00    False
2021-08-05 15:30:00    False
2021-08-05 15:25:00    False
2021-08-05 15:20:00    False
2021-08-05 15:15:00    False
2021-08-05 15:10:00    False
2021-08-05 15:05:00    False
2021-08-05 15:00:00    False
2021-08-05 14:55:00    False
2021-08-05 14:50:00    False
2021-08-05 14:45:00    False
2021-08-05 14:40:00    False
2021-08-05 14:35:00    False
2021-08-05 14:30:00    False
2021-08-05 14:25:00    False
2021-08-05 14:20:00    False
2021-08-05 14:15:00    False
2021-08-05 14:10:00    False
2021-08-05 14:05:00     True

最后一行的True我不想要。看起来这是data_daily index 2021-08-04的值。我想要的是在data_5min中搜索data_daily中的每个值，但取决于日期。我试着

data_5min['高'].isin (data_daily['高']),data_5min.index.isin (data_daily.index.date)

但是我不让它工作。

如果能帮忙就太好了。

Answer 1

您可以仅使用data_5m使用groupby和DatetimeIndex的.date部分找到每天的峰值:

>>> data_5min.groupby(data_5min.index.date)['high'].idxmax()
time
2021-08-05   2021-08-05 16:00:00
Freq: D, Name: high, dtype: datetime64[ns]

Answer 2

为什么不直接找到5分钟系列的max呢?

# Create Dummy Data
d = {'col1': [1, 2, 2.5, 5, 0, np.nan]}
df = pd.DataFrame(data=d)
print(df)

---

col1
0   1.0
1   2.0
2   2.5
3   5.0
4   0.0
5   NaN

---

# Create new column checking if value is equal to max in Series
df['bool'] = df['col1'] == df['col1'].max()
print(df)

---

输出:

col1   bool
0   1.0  False
1   2.0  False
2   2.5  False
3   5.0   True
4   0.0  False
5   NaN  False

如果有多个天的数据，你已经有了重新采样的数据。你可以把这些合并到5分钟的DataFrame，并做一个bool检查，如果值是相等的。