应用自定义groupby聚合函数在pandas python中输出二进制结果 [英] Applying a custom groupby aggregate function to output a binary outcome in pandas python

问题描述

我有一个交易者交易数据集，其中感兴趣的变量是 Buy / Sell ，它是二进制的，并且取值为1，交易是买入，0如果是卖出。一个例子如下所示：

pre $ 交易者买入/卖出
A 1
A 0
B 1
B 1
B 0
C 1
C 0
C 0


我想为每个交易者计算净值买/卖，这样如果交易者有超过50％的交易为如果买入价低于50％，那么他将拥有 Buy / Sell 1，那么他将拥有 Buy / Sell 0，如果它恰好是50％，他将有NA（并且在未来的计算中将被忽略）。



因此对于交易者A来说，买入比例是（买入数量）/（交易者总数）= 1/2 = 0.5这就给出了NA。



对于交易者B来说，它是2/3 = 0.67其中给出一个1



对于交易者C来说，它是1/3 = 0.33这给出了0



表应该看起来像这样：

 交易者买入/卖出
 A NA 
 B 1 
 C 0 
  

最终，我想计算总合计在这种情况下为1的购买数量以及在这种情况下为2的总交易总数（无视NAs）。我对第二个表格不感兴趣，我只对总购买数量和总计数（总数）买入/卖出。

我如何在Pandas中做到这一点？

解决方案

  import numpy as np 
 import pandas as pd 
 
'df = pd.DataFrame（{'Buy / Sell'：[1,0,1,1,0,1,0,0]，
'Trader'：['A'，'A'，' B'，'B'，'B'，'C'，'C'，'C']}）
 
分组= df.groupby（['Trader']）
结果（''买/卖']。agg（['sum'，'count']）
 means = grouped ['Buy / Sell']。mean（）
 result ['买入/卖出'] = np.select（condlist = [平均值> 0.5，意味着<0.5]，选择list = [1，0]，
 default = np.nan）
 print（result）
  

收益率

$ pre $ code $买入/卖出总数
交易者
A NaN 1 2
B 1 2 3
C 0 1 3


---

我的原始答案使用了一个自定义的聚合器， categorize ：

  def categorize（x）：
m = x.mean（）
 return 1 if m> 0.5否则0如果m < 0.5 else np.nan 
 result = df.groupby（['Trader']）['Buy / Sell']。agg（[categorize，'sum'，'count']）
 result = result .rename（columns = {'categorize'：'Buy / Sell'}）
  

函数可能会很方便，与使用内置的
聚合器相比，使用自定义函数时性能通常会降低
（比如 groupby / agg / mean ）。内置的聚合器是
Cythonized，而自定义函数将性能降低为纯Python
for循环速度。

速度的差别是当团体的数量是
大时特别重要。例如，对于具有1000个组的10000行DataFrame，

  import numpy as np 
将pandas导入为pd 
 np.random.seed（2017）
 N = 10000 
 df = pd.DataFrame（{
'买入/卖出'：np.random.randint（2，size = N ），
'Trader'：np.random.randint（1000，size = N）}）
 
 def using_select（df）：
 grouped = df.groupby（[''交易者']）
结果=分组['买/卖']。agg（['sum'，'count']）
 means =分组['Buy / Sell']。mean（）
 result ['Buy / Sell'] = np.select（condlist = [平均值> 0.5，意味着<0.5]，choicelist = [1,0]，
 default = np.nan）
返回结果
 
 def categorize（x）：
m = x.mean（）
 return 1 if m> 0.5否则0如果m < 0.5 else np.nan 
 
 def using_custom_function（df）：
 result = df.groupby（['Trader']）['Buy / Sell']。agg（[categorize，'sum '，'count']）
 result = result.rename（columns = {'categorize'：'Buy / Sell'}）
返回结果
  

using_select 比 using_custom_function 快50倍以上：

 在[69]中：％timeit using_custom_function（df）
 10个循环，每个循环最好3：132 ms 
 
在[70]中：％timeit using_select（df）
 100个循环，最好是3：每循环2.46 ms 
 
 In [71]：132 / 2.46 
 Out [71]：53.65853658536585 
  

I have a dataset of trader transactions where the variable of interest is Buy/Sell which is binary and takes on the value of 1 f the transaction was a buy and 0 if it is a sell. An example looks as follows:

Trader     Buy/Sell
  A           1
  A           0
  B           1
  B           1
  B           0
  C           1
  C           0
  C           0

I would like to calculate the net Buy/Sell for each trader such that if the trader had more than 50% of trades as a buy, he would have a Buy/Sell of 1, if he had less than 50% buy then he would have a Buy/Sell of 0 and if it were exactly 50% he would have NA (and would be disregarded in future calculations).

So for trader A, the buy proportion is (number of buys)/(total number of trader) = 1/2 = 0.5 which gives NA.

For trader B it is 2/3 = 0.67 which gives a 1

For trader C it is 1/3 = 0.33 which gives a 0

The table should look like this:

Trader     Buy/Sell
  A           NA
  B           1
  C           0 

Ultimately i want to compute the total aggregated number of buys, which in this case is 1, and the aggregated total number of trades (disregarding NAs) which in this case is 2. I am not interested in the second table, I am just interested in the aggregated number of buys and the aggregated total number (count) of Buy/Sell.

How can I do this in Pandas?

解决方案

import numpy as np
import pandas as pd

df = pd.DataFrame({'Buy/Sell': [1, 0, 1, 1, 0, 1, 0, 0],
                   'Trader': ['A', 'A', 'B', 'B', 'B', 'C', 'C', 'C']})

grouped = df.groupby(['Trader'])
result = grouped['Buy/Sell'].agg(['sum', 'count'])
means = grouped['Buy/Sell'].mean()
result['Buy/Sell'] = np.select(condlist=[means>0.5, means<0.5], choicelist=[1, 0], 
    default=np.nan)
print(result)

yields

        Buy/Sell  sum  count
Trader                      
A            NaN    1      2
B              1    2      3
C              0    1      3

---

My original answer used a custom aggregator, categorize:

def categorize(x):
    m = x.mean()
    return 1 if m > 0.5 else 0 if m < 0.5 else np.nan
result = df.groupby(['Trader'])['Buy/Sell'].agg([categorize, 'sum', 'count'])
result = result.rename(columns={'categorize' : 'Buy/Sell'})

While calling a custom function may be convenient, performance is often significantly slower when you use a custom function compared to the built-in aggregators (such as groupby/agg/mean). The built-in aggregators are Cythonized, while the custom functions reduce performance to plain Python for-loop speeds.

The difference in speed is particularly significant when the number of groups is large. For example, with a 10000-row DataFrame with 1000 groups,

import numpy as np
import pandas as pd
np.random.seed(2017)
N = 10000
df = pd.DataFrame({
    'Buy/Sell': np.random.randint(2, size=N),
    'Trader': np.random.randint(1000, size=N)})

def using_select(df):
    grouped = df.groupby(['Trader'])
    result = grouped['Buy/Sell'].agg(['sum', 'count'])
    means = grouped['Buy/Sell'].mean()
    result['Buy/Sell'] = np.select(condlist=[means>0.5, means<0.5], choicelist=[1, 0], 
        default=np.nan)
    return result

def categorize(x):
    m = x.mean()
    return 1 if m > 0.5 else 0 if m < 0.5 else np.nan

def using_custom_function(df):
    result = df.groupby(['Trader'])['Buy/Sell'].agg([categorize, 'sum', 'count'])
    result = result.rename(columns={'categorize' : 'Buy/Sell'})
    return result

using_select is over 50x faster than using_custom_function:

In [69]: %timeit using_custom_function(df)
10 loops, best of 3: 132 ms per loop

In [70]: %timeit using_select(df)
100 loops, best of 3: 2.46 ms per loop

In [71]: 132/2.46
Out[71]: 53.65853658536585

这篇关于应用自定义groupby聚合函数在pandas python中输出二进制结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！