使用Pandas的Python调用复杂函数进行groupby.agg

Question

Below is my dataframe

    Txn_Key Send_Agent           Send_Time            Pay_Time  Send_Amount  \
0         NaN  ANO080012 2012-05-31 02:25:00 2012-05-31 21:43:00       490.00
1         NaN  AUK359401 2012-05-31 11:25:00 2012-05-31 11:57:00       616.16
2         NaN  ACL000105 2012-05-31 13:07:00 2012-05-31 17:36:00       193.78
3         NaN  AED420319 2012-05-31 10:50:00 2012-05-31 11:34:00       999.43
4         NaN  ARA030210 2012-05-30 12:14:00 2012-05-31 04:16:00       433.29
5         NaN  AJ5020114 2012-05-31 02:37:00 2012-05-31 04:31:00       378.00
6         NaN  A11171047 2012-05-31 09:39:00 2012-05-31 10:08:00       865.34
  Pay_Amount        MTCN      Send_Phone  Refund_Flag       time_diff
0         475.68  9323625903        97549829          NaN 0 days 19:18:00
1         600.87  3545067820    440000000000          NaN 0 days 00:32:00
2         185.21  1453132764            0511          NaN 0 days 04:29:00
3         963.04  4509062067    971566016900          NaN 0 days 00:44:00
4         423.75  6898279087             144          NaN 0 days 16:02:00
5         377.99  5170985243    963954932506          NaN 0 days 01:54:00
6         833.89  5352719100      0644798854          NaN 0 days 00:29:00

所以，当下一行的Send_Amount相同时，我需要一个计数。使用lambda的groupby应用程序非常好：

^{pr2}$

…..：）

但是类似的lambda函数在groupby.agg组. 在

grouped=txn.groupby('Send_Agent')

x=grouped.agg({'Send_Amount':'mean','Pay_Amount':'mean','time_diff':'min','MTCN':'size','Send_Phone':'nunique','Refund_Flag':'count','Send_Amount':'lambda txn1 : (txn1.Send_Amount.shift() == txn1.Send_Amount).cumsum()'})

AttributeError: 'Series' object has no attribute 'Send_Amount'

所以，我编写了一个单独的函数来做同样的事情，并在我的groupby.agg组在

 def repeat_count(x):
if x==x.shift():
 ....:         cumsum()


x = grouped.agg({'Send_Amount':'mean','Pay_Amount':'mean','time_diff':'min','MTCN':'size','Send_Phone':'nunique','Refund_Flag':'count','Send_Amount':repeat_count(x)})

     ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

如果cumsum能很好地与团队合作按应用程序为什么它在函数中不起作用。在

Answer 1

通常，Send_Agent列将包含重复项（否则，按Send_Agent分组没有意义）。此外，(x==x.shift()).cumsum()将返回一个序列，其行数与每个Send_Agent组中的重复行数相同。在

df.groupby(...).agg(func)要求func返回标量（如浮点）。func不允许返回序列。（相反，func在使用df.groupby(...).apply(func)时可以返回Series甚至DataFrame。）

---

如果要计算组中相邻行数相等，可以使用sum()代替cumsum()。例如

import numpy as np
import pandas as pd
pd.options.display.width = 1000
nan = np.nan
txn = pd.DataFrame(
    {'MTCN': [0, 9323625903, 3545067820, 1453132764, 4509062067, 6898279087, 5170985243, 5352719100], 
     'Pay_Amount': [1, 475.68, 600.87, 185.21, 963.04, 423.75, 377.99, 833.89],
     'Pay_Time': ['2012-05-31 10:08:00', '2012-05-31 21:43:00', '2012-05-31 11:57:00', '2012-05-31 17:36:00', 
                  '2012-05-31 11:34:00', '2012-05-31 04:16:00', '2012-05-31 04:31:00', 
                  '2012-05-31 10:08:00'], 
     'Refund_Flag': [nan, nan, nan, nan, nan, nan, nan, nan], 
     'Send_Amount': [865.34, 490.0, 616.16, 193.78, 999.43, 433.29, 378.0, 865.34],
     'Send_Phone': [3, 97549829, 440000000000, 511, 971566016900, 144, 963954932506, 644798854],
     'Send_Time': ['2012-05-31 09:39:00', '2012-05-31 02:25:00', '2012-05-31 11:25:00', '2012-05-31 13:07:00', 
                   '2012-05-31 10:50:00', '2012-05-30 12:14:00', '2012-05-31 02:37:00', 
                   '2012-05-31 09:39:00'], 
     'Txn_Key': [nan, nan, nan, nan, nan, nan, nan, nan],
     'Send_Agent': ['A11171047', 'ANO080012', 'AUK359401', 'ACL000105', 'AED420319', 
                    'ARA030210', 'AJ5020114', 'A11171047'], 
     'time_diff': ['0 days 00:29:00', '0 days 19:18:00', '0 days 00:32:00', '0 days 04:29:00', 
                   '0 days 00:44:00', '0 days 16:02:00', '0 days 01:54:00', 
                   '0 days 00:29:00', ]} )
txn['time_diff'] = pd.to_timedelta(txn['time_diff']) 

grouped = txn.groupby('Send_Agent')

def repeat_count(s):
    return (s.shift() == s).sum()

result = grouped.agg(
    {'Pay_Amount':'mean',
     'time_diff':'min',
     'MTCN':'size',
     'Send_Phone':'nunique',
     'Refund_Flag':'count',
     'Send_Amount': ['mean', repeat_count]})
print(result)

收益率

^{pr2}$

（我添加了一个额外的行，这样repeat_count不总是返回0。）

---

使用DataFrame.groupby(...).apply(func)时，传递给func的对象是数据帧。因此

txn1.groupby('Send_Agent').apply(
    lambda txn1 : (txn1.Send_Amount.shift() == txn1.Send_Amount).cumsum())

之所以有效，是因为lambda内的txn1是一个带有Send_Amount列的数据帧。在

---

相反，当您使用DataFrame.groupby(...).agg({'col': func})时，传递给func的对象是由col指定的列中的值组成的序列。因此

x = grouped.agg({'Send_Amount':'mean','Pay_Amount':'mean','time_diff':'min','MTCN':'size','Send_Phone':'nunique','Refund_Flag':'count','Send_Amount':lambda txn1 : (txn1.Send_Amount.shift() == txn1.Send_Amount).cumsum()})

引发了AttributeError: 'Series' object has no attribute 'Send_Amount'，因为传递给lambda函数（并绑定到变量txn1）的序列没有Send_Amount属性。在

---

如果您使用repeat_count：

def repeat_count(x):
    if x==x.shift():
        return x.cumsum()

然后if x==x.shift()上升

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

因为x==x.shift()是一个序列，if expression导致在布尔上下文中对{}求值。也就是说，expression.__bool__()被调用。__bool__必须返回True或False或引发异常。因此，if x==x.shift()要有意义，(x==x.shift()).__bool__()必须返回True或False。在

Series.__bool__()总是引发上面的ValueError，因为Pandas（按设计）不会猜测当所有的值都为真时，或者当任何值为真时，或者当序列仅仅是非空时，它是否应该返回True，等等。。。。ValueError消息为您指明了正确的方向。通常，通过调用(x==x.shift()).any()或(x==x.shift()).all()等明确表示您想要什么样的布尔值来解决这个问题

---

关于性能的注意事项：通常，将groupby/agg与自定义函数一起使用，不如使用count或{}这样的内置方法。因此，通常需要找出一种方法（如果可能的话）用内置方法来表达计算。在这种情况下，您可以对整个数据帧进行一次预备计算，然后允许您使用groupby/agg/sum：

txn = txn.sort_values(by='Send_Agent')
txn['repeat'] = ((txn['Send_Agent'].shift() == txn['Send_Agent']) 
                 & (txn['Send_Agent'].shift() == txn['Send_Agent']))

grouped = txn.groupby('Send_Agent')
result = grouped.agg(
    {'Pay_Amount':'mean',
     'time_diff':'min',
     'MTCN':'size',
     'Send_Phone':'nunique',
     'Refund_Flag':'count',
     'Send_Amount': 'mean',
     'repeat':'sum'})
print(result)