CODEWITHSUNDEEP
python-3.xpandasgroup-byitertools-groupby
Yogesh Kamboj
Yogesh Kamboj

Reputation: 41

How to get corresponding values of other column using idxmax in aggregation

Following is the code

weekly = (df.groupby(['Year','Qtr_Year'], as_index=False).agg(High=('High',  'max'),D_HIGH='High','idxmax')))

I want to use idxmax and find corresponding date in agg. only similarly for idxmin and find date value. like below

weekly = (df.groupby(['Year','Qtr_Year'], as_index=False).agg(High=('High', 'max'),D_HIGH=('High', 'idxmax'), Date_High('Date',----)))

I tried df.loc and corresponding idxmax and idxmin.

Upvotes: 0

Views: 59

Answers (1)

Corralien
Corralien

Reputation: 120479

I tried df.loc and corresponding idxmax and idxmin.

This is the right way. You can extract data in two steps:

# Step 1: extract idxmin and idxmax
idx = df.groupby(['Year', 'Qtr_Year'])['High'].agg(['idxmin', 'idxmax'])

# Step 2: find corresponding rows
df1 = df.loc[idx['idxmin']].add_suffix('_Min').reset_index(drop=True)
df2 = df.loc[idx['idxmax']].add_suffix('_Max').reset_index(drop=True)

# Merge your dataframes
out = pd.concat([df1, df2], axis=1)

Upvotes: 0

Related Questions