How to get value of column while performing groupby function

user_14777245 0
Viewed 85

Hi I have a dataframe with datetime and high and low during the period. for eg. its 5 minutes data call it df

Datetime High Low
0 2020-12-02 09:15:00 590.349976 584.299988
1 2020-12-02 09:20:00 593.900024 588.750000
2 2020-12-02 09:25:00 594.900024 592.450012
3 2020-12-02 09:30:00 593.849976 591.799988
4 2020-12-02 09:35:00 594.400024 592.200012
5 2020-12-02 09:40:00 596.250000 593.099976
6 2020-12-02 09:45:00 596.849976 593.102476
7 2020-12-02 09:50:00 595.400024 592.950012
8 2020-12-02 09:55:00 596.500000 594.500000

I perform a groupby function to convert the 5 minutes to 15 minutes and get following output.

Datetime High Low
0 2020-12-02 09:15:00 594.900024 584.299988
1 2020-12-02 09:30:00 596.250000 591.799988
2 2020-12-02 09:45:00 596.849976 592.950012

Is it possible while performing groupby function to also get exact datetime of high and low in following desired output.

Datetime High Low High datetime Low Datetime
0 2020-12-02 09:15:00 594.900024 584.299988 2020-12-02 09:25:00 2020-12-02 09:15:00
1 2020-12-02 09:30:00 596.250000 591.799988 2020-12-02 09:40:00 2020-12-02 09:30:00
2 2020-12-02 09:45:00 596.849976 592.950012 2020-12-02 09:45:00 2020-12-02 09:50:00
pandas datetime python
2 Answers

You can group the dataframe by pd.Grouper with a frequency of 15min and aggregate using dictionary containing the aggregation functions:

d = {'High': ('High', 'max'), 'Low': ('Low', 'min'),
     'High date': ('High', 'idxmax'), 'Low date': ('Low', 'idxmin')}

df.set_index('Datetime').groupby(pd.Grouper(freq='15min')).agg(**d)

                     High        Low        High date           Low date
Datetime                                                                           
2020-12-02 09:15:00  594.900024  584.299988 2020-12-02 09:25:00 2020-12-02 09:15:00
2020-12-02 09:30:00  596.250000  591.799988 2020-12-02 09:40:00 2020-12-02 09:30:00
2020-12-02 09:45:00  596.849976  592.950012 2020-12-02 09:45:00 2020-12-02 09:50:00
user_128331660

Try this:

df['Datetime ']=pd.to_datetime(df['Datetime '])
df=df.set_index('Datetime ')
df1=df.resample('15T').max().reset_index()
df2=df.resample('15T').min().reset_index()

df1['High Datetime ']=df1[['High ']].merge(df.reset_index(),how='left')['Datetime ']
df1['Low Datetime ']=df2[['Low']].merge(df.reset_index(),how='left')['Datetime ']

Output:

df1
Out[51]: 
            Datetime        High   ...      High Datetime        Low Datetime 
0 2020-12-02 09:15:00  594.900024  ... 2020-12-02 09:25:00 2020-12-02 09:15:00
1 2020-12-02 09:30:00  596.250000  ... 2020-12-02 09:40:00 2020-12-02 09:30:00
2 2020-12-02 09:45:00  596.849976  ... 2020-12-02 09:45:00 2020-12-02 09:50:00

[3 rows x 5 columns]
user_109535920
Related