在 Dataframe 中查找最大体积和高于该体积的数据计数 [英] Find max volume and data count above that volume in a Dataframe

问题描述

我有一个示例数据框，如下所示.我需要根据以下条件找到结果.

I have a sample dataframe as below. I need to find result as per the below condition.

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         521
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         551
2020-08-05 09:20:00  1426         526
2020-08-05 09:20:00     0         586
2020-08-05 09:20:00  1968         518

1. 在日期时间列中使用分组依据查找最大交易量的价格.
2. 计算有多少价格值高于 Sl No 1 的价格(忽略交易量为零的行)

我想要我的结果数据框如下:

I want my result dataframe as below:

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

对于 Datetime = 2020-08-05 09:15:00，只有两个值高于 516(520 和 540)和对于 Datetime = 2020-08-05 09:20:00，没有大于 548 的值(忽略零交易量的行)

For Datetime = 2020-08-05 09:15:00, only two values are above 516 (520 and 540) and for Datetime = 2020-08-05 09:20:00, no values are above 548 (ignoring rows with zero volume)

推荐答案

尝试:

# positive volume
pos_vol = df.query('Volume!=0')

# rows with max volume by time
s = pos_vol.groupby('Datetime').Volume.idxmax()

# extract the output
out = df.loc[s].set_index(['Datetime'])

# map the datetime to the price corresponding to the max volume 
aligned_prc = pos_vol['Datetime'].map(out['Price'])

# count by datetime
out['Count_abv'] = (pos_vol['Price'].gt(aligned_prc)
                    .groupby(pos_vol['Datetime']).sum()
                   )

输出:

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

这篇关于在 Dataframe 中查找最大体积和高于该体积的数据计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！