创建查找并返回匹配数据的列

Question

我有一个金融交易的大数据帧（150000 x 25）。此数据框表示一种金融控股账户，因此交易通常“通过”此分类账。例如（下面），位置0中的行显示一笔-123.21美元的交易。位置2中的行是+$123.21的对应（或“耦合”）交易，与类别、类型和来源匹配

我的目标是创建一个新列来标识“耦合”事务的键。因此，第0行的“耦合键”是第2行的键，反之亦然

注意，位置9-14中的行排除了搜索最小和最大匹配的解决方案（@David Erickson previously provided这是沿着这些线的一个很好的答案）。位置9的行显示+10美元的交易。它与位置11中的第一笔-10美元（而不是位置14中的交易）结合在一起。通过这种方式，每个事务与零个或一个其他事务耦合，但不超过一个

import pandas as pd

d_in = {'key' : ['80000001', '80000002', '80000003', '80000004', '80000005', '80000006', '80000007', '80000008', '80000009', '80000010', '80000011', '80000012', '80000013', '80000014', '80000015'], 
        'date' : ['20200901', '20200901', '20200902', '20200902', '20200902','20200903', '20200904', '20200905', '20200905', '20200906', '20200906', '20200906', '20200906', '20200906', '20200906'],
        'category' : ['Z293', 'B993', 'Z293', 'B993', 'W884', 'C123', 'V332', 'C123', 'V332', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213'], 
        'type' : ['tools', 'supplies', 'tools', 'supplies', 'repairs', 'custom', 'misc', 'custom', 'misc', 'technology', 'technology', 'technology', 'technology', 'technology', 'technology'], 
        'source' : ['Q112', 'E443', 'Q112', 'E443', 'P443', 'B334', 'E449', 'B334', 'E449', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32'], 
        'amount' : [-123.21, 3.12, 123.21, -3.12, 9312.00, 312.23, -13.23, -312.23, 13.23, 10, 10, -10, -10, 10, -10]}

df_in = pd.DataFrame(data=d_in)


d_out = {'key' : ['80000001', '80000002', '80000003', '80000004', '80000005', '80000006', '80000007', '80000008', '80000009', '80000010', '80000011', '80000012', '80000013', '80000014', '80000015'], 
        'date' : ['20200901', '20200901', '20200902', '20200902', '20200902','20200903', '20200904', '20200905', '20200905', '20200906', '20200906', '20200906', '20200906', '20200906', '20200906'],
        'category' : ['Z293', 'B993', 'Z293', 'B993', 'W884', 'C123', 'V332', 'C123', 'V332', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213'], 
        'type' : ['tools', 'supplies', 'tools', 'supplies', 'repairs', 'custom', 'misc', 'custom', 'misc', 'technology', 'technology', 'technology', 'technology', 'technology', 'technology'], 
        'source' : ['Q112', 'E443', 'Q112', 'E443', 'P443', 'B334', 'E449', 'B334', 'E449', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32'], 
        'amount' : [-123.21, 3.12, 123.21, -3.12, 9312.00, 312.23, -13.23, -312.23, 13.23, 10, 10, -10, -10, 10, -10], 
    'coupling_key' : ['80000003', '80000004', '80000001', '80000002', 'none', '80000008', '80000009', '80000006', '80000007', '80000012', '80000013', '80000010', '80000011', '80000015', '80000014']}

df_out = pd.DataFrame(data=d_out)   

我所探索的大多数解决方案都涉及到按功能分组。我目前正在考虑groupby（…）.nth（…）函数。我怀疑解决方案可能还涉及.mask或.duplicated（）

Answer 1

您可以执行以下操作：

步骤1：设置transform功能：

def coupling(ser):
    keys = ser.index
    values = ser.values
    couples = [None] * len(ser)
    free = {*range(len(ser))}
    while free:
        i = min(free)
        j = i + 1
        while j < len(ser):
            if (values[j] == -values[i]
                    and j in free):
                couples[i], couples[j] = keys[j], keys[i]
                free.remove(j)
                break
            j += 1
        free.remove(i)
    return couples

步骤2：应用于组：

df_out = df_in.set_index('key')
group = ['category', 'type', 'source']
df_out['coupling_key'] = (df_out[group + ['amount']]
                          .groupby(group)
                          .transform(coupling))
df_out.reset_index(drop=False, inplace=True)

结果:

         key      date category        type source   amount coupling_key
0   80000001  20200901     Z293       tools   Q112  -123.21     80000003
1   80000002  20200901     B993    supplies   E443     3.12     80000004
2   80000003  20200902     Z293       tools   Q112   123.21     80000001
3   80000004  20200902     B993    supplies   E443    -3.12     80000002
4   80000005  20200902     W884     repairs   P443  9312.00         None
5   80000006  20200903     C123      custom   B334   312.23     80000008
6   80000007  20200904     V332        misc   E449   -13.23     80000009
7   80000008  20200905     C123      custom   B334  -312.23     80000006
8   80000009  20200905     V332        misc   E449    13.23     80000007
9   80000010  20200906     Z213  technology   QQ32    10.00     80000012
10  80000011  20200906     Z213  technology   QQ32    10.00     80000013
11  80000012  20200906     Z213  technology   QQ32   -10.00     80000010
12  80000013  20200906     Z213  technology   QQ32   -10.00     80000011
13  80000014  20200906     Z213  technology   QQ32    10.00     80000015
14  80000015  20200906     Z213  technology   QQ32   -10.00     80000014

（我假设date列的顺序与示例中相同。）

Answer 2

另一个解决方案是，尝试使用“纯熊猫”函数（不管这意味着什么！）

要理解以下内容，请执行以下步骤

1. 我们按“类别”、“类型”、“来源”和“金额”进行分组
2. 在每一组中，我们将有相同abs数量但符号不同的行。因此，我们然后按“数量”分组，将正数中的行标记为1到n，将负数中的行标记为1到n（因此cumcount()）
3. …通过匹配第一个元素的正片与第一个元素的负片、第二个元素的正片与第二个元素的负片等进行分组
4. 组_match将有一个在步骤3中匹配的['key1'，'key2']列表
5. 其余的只是将这些列表拉到一起（我们还希望每个['key1'，'key2']都有['key2'，'key1']，因此其中有一行reversed(..)），转换成一个数据帧，并连接到原始数据帧

第5步可能会做得更优雅，但这是可行的

match = []
for _, df2 in df_in.groupby([df_in['category'], df_in['type'], df_in['source'], df_in['amount'].abs()], as_index=False):
   group_match = df2.groupby(df2.groupby(['amount']).cumcount())['key'].apply(list)
   match.extend(group_match)
   match.extend([list(reversed(m)) for m in group_match])

match_df = pd.DataFrame(data = match, columns = ['key', 'coupling_key']).drop_duplicates()
df_out = df_in.merge(match_df, on='key')

生成所需的df_输出：

    key         date        category type       source  amount  coupling_key
0   80000001    20200901    Z293    tools       Q112    -123.21 80000003
1   80000002    20200901    B993    supplies    E443    3.12    80000004
2   80000003    20200902    Z293    tools       Q112    123.21  80000001
3   80000004    20200902    B993    supplies    E443    -3.12   80000002
4   80000005    20200902    W884    repairs     P443    9312.00 None
5   80000006    20200903    C123    custom      B334    312.23  80000008
6   80000007    20200904    V332    misc        E449    -13.23  80000009
7   80000008    20200905    C123    custom      B334    -312.23 80000006
8   80000009    20200905    V332    misc        E449    13.23   80000007
9   80000010    20200906    Z213    technology  QQ32    10.00   80000012
10  80000011    20200906    Z213    technology  QQ32    10.00   80000013
11  80000012    20200906    Z213    technology  QQ32    -10.00  80000010
12  80000013    20200906    Z213    technology  QQ32    -10.00  80000011
13  80000014    20200906    Z213    technology  QQ32    10.00   80000015
14  80000015    20200906    Z213    technology  QQ32    -10.00  80000014

如果amount列中有零，并且它们应该根据下面的注释进行匹配，我们可以如下修改循环

for _, df2 in df_in.groupby([df_in['category'], df_in['type'], df_in['source'], df_in['amount'].abs()], as_index=False):
   if (df2['amount'].iloc[0] == 0):
      group_match = df2.groupby([i//2 for i in range(len(df2))])['key'].apply(list)
   else:
      group_match = df2.groupby(df2.groupby(['amount']).cumcount())['key'].apply(list)
   match.extend(group_match)
   match.extend([list(reversed(m)) for m in group_match])

将df_in扩展为这样（注意末尾的三个0行：

d_in = {'key' : ['80000001', '80000002', '80000003', '80000004', '80000005', '80000006', '80000007', '80000008', '80000009', '80000010', '80000011', '80000012', '80000013', '80000014', '80000015', '1', '2', '3'], 
        'date' : ['20200901', '20200901', '20200902', '20200902', '20200902','20200903', '20200904', '20200905', '20200905', '20200906', '20200906', '20200906', '20200906', '20200906', '20200906', '20200906', '20200906', '20200906'],
        'category' : ['Z293', 'B993', 'Z293', 'B993', 'W884', 'C123', 'V332', 'C123', 'V332', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213', 'Z213'], 
        'type' : ['tools', 'supplies', 'tools', 'supplies', 'repairs', 'custom', 'misc', 'custom', 'misc', 'technology', 'technology', 'technology', 'technology', 'technology', 'technology','technology', 'technology', 'technology'], 
        'source' : ['Q112', 'E443', 'Q112', 'E443', 'P443', 'B334', 'E449', 'B334', 'E449', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32', 'QQ32'], 
        'amount' : [-123.21, 3.12, 123.21, -3.12, 9312.00, 312.23, -13.23, -312.23, 13.23, 10, 10, -10, -10, 10, -10,0,0,0]}

我们得到（忽略与前面相同的行）

    key date    category    type    source  amount  coupling_key
15  1   20200906    Z213    technology  QQ32    0.00    2
16  2   20200906    Z213    technology  QQ32    0.00    1
17  3   20200906    Z213    technology  QQ32    0.00    None