Filter data with specific condition

user_11006089 0
Viewed 65

I need to find result as per the below condition from the sample dataframe given below:

Datetime Volume Price
2020-08-05 09:15:00 1033 504
2020-08-05 09:15:00 1960 516
2020-08-05 09:15:00 0 450
2020-08-05 09:15:00 1724 520
2020-08-05 09:15:00 0 500
2020-08-05 09:15:00 1870 540
2020-08-05 09:20:00 1024 476
2020-08-05 09:20:00 1980 548
2020-08-05 09:20:00 0 480
2020-08-05 09:20:00 1426 526
2020-08-05 09:20:00 0 586
2020-08-05 09:20:00 1968 588
  1. Find Price at Maximum Volume with group-by on Datetime Column.
  2. Calculate how many Price Values are below Price of Sl No 1 (ignoring rows with zero volume)

I want my result dataframe as below:

Datetime             Volume       Price  Count_below_prc
2020-08-05 09:15:00  1960         516    1
2020-08-05 09:20:00  1980         548    2

For Datetime = 2020-08-05 09:15:00, only one value is below 516 (504, ignoring rows with zero volume) for Datetime = 2020-08-05 09:20:00, two values are below 548 (476 & 526, ignoring rows with zero volume)

pandas-groupby pandas dataframe python-3.x python
1 Answers

try using groupby and apply

def func(row):
    x = (row[row['Volume']==max(row['Volume'])])
    x['Count_below_prc']=(row.loc[row['Price'].lt(x['Price'].values[0]) & row['Volume'].ne(0), 'Price'].count())
    return x

res = df.groupby('Datetime',as_index=False).apply(func).reset_index(drop=True)

res:

Datetime Volume Price Count_below_prc
0 2020-08-05 09:15:00 1960 516 1
1 2020-08-05 09:20:00 1980 548 2
user_66603730
Related