使用groupby查找列最大值的日期和值

Question

我试图找出wipro close price是max每year的日期。（什么日期，什么价格？）以下是我尝试过的一些代码的示例：

import pandas as pd
import numpy as np
from nsepy import get_history
import datetime as dt

start = dt.datetime(2015, 1, 1)
end = dt.datetime.today()
wipro=get_history(symbol='WIPRO', start = start, end = end)
wipro.index = pd.to_datetime(wipro.index)

# This should get me my grouped results
wipro_agg = wipro.groupby(wipro.index.year).Close.idxmax()

Answer 1

您可以简单地调用“max”，就像调用“idxmax”一样

In [25]: df_ids = pd.DataFrame(wipro.groupby(wipro.index.year).Close.idxmax())
In [26]: df_ids['price'] = wipro.groupby(wipro.index.year).Close.max()
In [27]: df_ids.rename({'Close': 'date'}, axis= 1).set_index('date')
Out[27]: 
             price
date
2015-03-03  672.45
2016-04-20  601.25
2017-06-06  560.55
2018-12-19  340.70
2019-02-26  387.65

Answer 2

解决这个问题需要两个步骤。首先，获得每年的最高价格。然后，找到该实例的确切日期。你知道吗

# Find max price each year:
# note: specific format to keep as a dataframe
wipro_max_yr = wipro.groupby(wipro.index.dt.year)[['Close']].max()

# Now, do an inner join to find exact dates
wipro_max_dates = wipro_max_yr.merge(wipro, how='inner')