일치하는 데이터를 찾아 반환하는 Pandas 열 만들기

Nov 13 2020

큰 DataFrame (150,000 x 25)의 금융 거래가 있습니다. 이 DataFrame은 거래가 종종이 원장을 "통과"하는 일종의 재무 보유 계정을 나타냅니다. 예를 들어 (아래) 위치 0의 행은-$123.21 transaction. The row in position 2 is the corresponding (or "coupled") transaction for +$123.21 및 카테고리, 유형 및 소스와 일치합니다.

내 목표는 "결합 된"트랜잭션의 키를 식별하는 새 열을 만드는 것입니다. 따라서 행 0의 "커플 링 키"는 행 2의 키이고 그 반대의 경우도 마찬가지입니다.

위치 9-14의 행은 최소 및 최대 일치를 검색하는 솔루션을 배제합니다 ( @David Erickson은 이전에 해당 행을 따라 훌륭한 답변을 제공 했습니다). 위치 9의 행은 +$10 transaction. It is coupled with the first -$10, 위치 11에서 발견되었습니다 (위치 14에서 발견 된 트랜잭션이 아님). 이러한 방식으로 각 트랜잭션은 0 개 또는 하나의 다른 트랜잭션과 결합되지만 둘 이상은 아닙니다.

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)   

내가 탐색 한 대부분의 솔루션에는 pandas groupby 함수가 포함됩니다. 현재 groupby (...). nth (...) 함수를 고려 중입니다. 솔루션에 .mask 또는 .duplicated ()가 포함될 수도 있다고 생각합니다.

답변

1 piterbarg Nov 13 2020 at 15:17

또 다른 해결책은 '순수 판다'기능을 유지하려는 것입니다.

아래를 이해하기위한 단계는 다음과 같습니다.

  1. 'category', 'type', 'source'및 abs ( 'amount')별로 그룹화합니다.
  2. 각 그룹 내에서 복근 양은 같지만 부호가 다른 행이 있습니다. 그래서 우리는 '양'별로 그룹화하고, 1에서 n까지의 양수와 1에서 n까지의 음수 내에서 행에 레이블을 붙입니다 (따라서 cumcount()).
  3. ... 긍정의 첫 번째 요소와 네거티브의 첫 번째 요소, 긍정의 두 번째 요소와 네거티브의 두 번째 요소 등을 일치시켜 그룹화
  4. group_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_out을 생성합니다.


    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열에 0 이 있고 아래 주석에 따라 일치해야하는 경우 다음과 같이 루프를 수정할 수 있습니다.

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
2 Timus Nov 13 2020 at 07:22

다음을 수행 할 수 있습니다.

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열이 예와 같이 정렬 되었다고 가정합니다 .)