Doing conditional calculations with datetime and pandas

Question

I have some trade data as seen in the image. There can be many entries for a single instant in time. I would like to do some calculations based on PRICE and SIZE at each moment in time. I would like to do something like:

total_size = eur_jpy_order.groupby(['TIMESTAMP', 'BUY_SELL_FLAG']).sum()
avg_price = eur_jpy_order.groupby(['TIMESTAMP', 'BUY_SELL_FLAG']).mean()

and then take just have a dataframe with TIMESTAMP, BUY_SELL_FLAG, total_size and avg_price, with a single entry for each time step. I can't find a simple way of doing this. Could anyone advise?

Please, replace your image by the plain text data. – Corralien Aug 26 '21 at 06:17 — Corralien, Aug 26 '21 at 06:17

Corralien · Accepted Answer · 2021-08-26T06:27:20.390

1

I think you can use agg:

>>> eur_jpy_order

           BUY_SELL_FLAG  PRICE  SIZE
TIMESTAMP
1                      1     10    10
1                      1     20    20
1                      1     30    30
1                      2     40    40

>>> eur_jpy_order.groupby(['TIMESTAMP', 'BUY_SELL_FLAG']) \
                 .agg(total_size=('SIZE', 'sum'),
                      avg_price=('PRICE', 'mean'))

                         total_size  avg_price
TIMESTAMP BUY_SELL_FLAG
1         1                      60       20.0
          2                      40       40.0

edited Aug 26 '21 at 06:27

answered Aug 26 '21 at 06:22

Corralien

109,409
8
28
52

Use the link posted by @jezrael to get complete explanation of aggregation possibilities. – Corralien Aug 26 '21 at 06:28

Doing conditional calculations with datetime and pandas

1 Answers1