在子集datafram上调用用户定义函数

Question

我正在对Pandas DataFrame的子集创建一个count函数，并打算导出一个仅包含groupby条件和计数结果的字典/电子表格数据。你知道吗

In [1]: df = pd.DataFrame([[Buy, A, 123, NEW, 500, 20190101-09:00:00am], [Buy, A, 124, CXL, 500, 20190101-09:00:01am], [Buy, A, 125, NEW, 500, 20190101-09:00:03am], [Buy, A, 126, REPLACE, 300, 20190101-09:00:10am], [Buy, B, 210, NEW, 1000, 20190101-09:10:00am], [Sell, B, 345, NEW, 200, 20190101-09:00:00am], [Sell, C, 412, NEW, 100, 20190101-09:00:00am], [Sell, C, 413, NEW, 200, 20190101-09:01:00am], [Sell, C, 414, CXL, 50, 20190101-09:02:00am]], columns=['side', 'sender', 'id', 'type', ''quantity', 'receive_time'])

Out[1]: 
   side  sender  id    type     quantity  receive_time 
0  Buy   A       123   NEW      500       20190101-09:00:00am
1  Buy   A       124   CXL      500       20190101-09:00:01am
2  Buy   A       125   NEW      500       20190101-09:00:03am
3  Buy   A       126   REPLACE  300       20190101-09:00:10am
4  Buy   B       210   NEW      1000      20190101-09:10:00am
5  Buy   B       345   NEW      200       20190101-09:00:00am
6  Sell  C       412   NEW      100       20190101-09:00:00am
7  Sell  C       413   NEW      200       20190101-09:01:00am
8  Sell  C       414   CXL      50        20190101-09:02:00am

count函数如下（mydf作为dataframe的子集传入）：

def ordercount(mydf):
   num = 0.0
   if mydf.type == 'NEW':
      num = num + mydf.qty
   elif mydf.type == 'REPLACE':
      num = mydf.qty
   elif mydf.type == 'CXL':
      num = num - mydf.qty
   else: 
      pass
   orderdict = dict.fromkeys([mydf.side, mydf.sender, mydf.id], num)
   return orderdict

从csv读取数据后，我按一些标准对其进行分组，并按时间排序：

df = pd.read_csv('xxxxxxxxx.csv, sep='|', header=0, engine='python', names=col_names)
sorted_df = df.groupby(['side', 'sender', 'id']).apply(lambda_df:_df.sort_values(by=['time']))

然后对排序后的数据调用前面定义的函数：

print(sorted_df.agg(ordercount))

但是，值错误不断地出现，说太多的行调用。你知道吗

计算数据的函数方法可能效率不高，但我能想到的最直接的方法是匹配订单类型并相应地计算数量。我希望程序输出一个只显示side、sender、id和counted数量的表。有没有办法做到这一点？谢谢。你知道吗

预期产量：

   side   sender   total_order_num   trade_date 
0  Buy    A        300               20190101
1  Buy    B        1200              20190101
2  Sell   C        250               20190101

Answer 1

我相信您的函数不容易一次应用，因为您正在根据行执行不同的操作。如果您只有+和-作为您的操作，但是您replace在某个点上指定值，然后继续执行其他操作，则这是可以的。因此，循环可能更简单，或者您可以花一些时间来使用一个好的函数来完成任务。你知道吗

这就是我所拥有的。我真正做的只是改变你的ordercount，这样它就可以直接对一个子集进行操作，你可以通过简单的分组得到。您可以在分组之前按时间排序，也可以在ordercount函数中进行排序。希望这有点帮助。你知道吗

import pandas as pd
df = pd.DataFrame([['Buy', 'A', 123, 'NEW', 500, '20190101-09:00:00am'],
                   ['Buy', 'A', 124, 'CXL', 500, '20190101-09:00:01am'],
                   ['Buy', 'A', 125, 'NEW', 500, '20190101-09:00:03am'],
                   ['Buy', 'A', 126, 'REPLACE', 300, '20190101-09:00:10am'],
                   ['Buy', 'B', 210, 'NEW', 1000, '20190101-09:10:00am'],
                   ['Buy', 'B', 345, 'NEW', 200, '20190101-09:00:00am'],
                   ['Sell', 'C', 412, 'NEW', 100, '20190101-09:00:00am'],
                   ['Sell', 'C', 413, 'NEW', 200, '20190101-09:01:00am'],
                   ['Sell', 'C', 414, 'CXL', 50, '20190101-09:02:00am']],
columns=['side', 'sender', 'id', 'type', 'quantity', 'receive_time'])

df['receive_time'] = pd.to_datetime(df['receive_time'])
df['receive_date'] = df['receive_time'].dt.date # you do not need the time stamps


def ordercount(mydf):
    mydf_ = mydf.sort_values('receive_time')[['type', 'quantity']].copy()
    num = 0
    for val in mydf_.values:
        type_, quantity = val
        # val is going to be a list like ['NEW', 500]. All I am doing above is unpack the list into two variables.
        # You can find many resources on unpacking iterables
        if type_ == 'NEW':
            num += quantity
        elif type_ == 'REPLACE':
            num = quantity
        elif type_ == 'CXL':
            num -= quantity
        else:
            pass
    return num

mydf = df.groupby(['side', 'sender', 'receive_date']).apply(ordercount).reset_index()

输出：

|  |    |     |          -|   |
|    | side   | sender   | receive_date        |    0 |
|  |    |     |          -|   |
|  0 | Buy    | A        | 2019-01-01 00:00:00 |  300 |
|  |    |     |          -|   |
|  1 | Buy    | B        | 2019-01-01 00:00:00 | 1200 |
|  |    |     |          -|   |
|  2 | Sell   | C        | 2019-01-01 00:00:00 |  250 |
|  |    |     |          -|   |

可以根据需要轻松重命名列“0”。我仍然不确定你的trade_date是如何定义的。你的数据只有一个日期吗？当你有一次以上的约会时会发生什么？你是在开min吗？。。。你知道吗

编辑：如果您尝试使用此数据帧，您可以看到具有预期工作日期的组。你知道吗

df = pd.DataFrame([['Buy', 'A', 123, 'NEW', 500, '20190101-09:00:00am'],
                   ['Buy', 'A', 124, 'CXL', 500, '20190101-09:00:01am'],
                   ['Buy', 'A', 125, 'NEW', 500, '20190101-09:00:03am'],
                   ['Buy', 'A', 126, 'REPLACE', 300, '20190101-09:00:10am'],
                   ['Buy', 'B', 210, 'NEW', 1000, '20190101-09:10:00am'],
                   ['Buy', 'B', 345, 'NEW', 200, '20190101-09:00:00am'],
                   ['Sell', 'C', 412, 'NEW', 100, '20190101-09:00:00am'],
                   ['Sell', 'C', 413, 'NEW', 200, '20190101-09:01:00am'],
                   ['Sell', 'C', 414, 'CXL', 50, '20190101-09:02:00am'],
                   ['Buy', 'A', 123, 'NEW', 500, '20190102-09:00:00am'],
                   ['Buy', 'A', 124, 'CXL', 500, '20190102-09:00:01am'],
                   ['Buy', 'A', 125, 'NEW', 500, '20190102-09:00:03am'],
                   ['Buy', 'A', 126, 'REPLACE', 300, '20190102-09:00:10am'],
                   ['Buy', 'B', 210, 'NEW', 1000, '20190102-09:10:00am'],
                   ['Buy', 'B', 345, 'NEW', 200, '20190102-09:00:00am'],
                   ['Sell', 'C', 412, 'NEW', 100, '20190102-09:00:00am'],
                   ['Sell', 'C', 413, 'NEW', 200, '20190102-09:01:00am'],
                   ['Sell', 'C', 414, 'CXL', 50, '20190102-09:02:00am']],
columns=['side', 'sender', 'id', 'type', 'quantity', 'receive_time'])