Pandas:在多个列中查找具有匹配值的行的强效方法(分层条件)

2022-03-31 00:00:00 python pandas networkx

问题描述

很抱歉标题有些不清楚。我无法言简意赅地描述这个问题。希望我下面的描述可以帮助澄清。欢迎对标题进行任何澄清编辑。

我正在尝试从 pandas 数据帧创建网络x流程图。数据帧记录了订单如何在多家公司之间流动。数据帧中的大多数行都是连接的,并且连接显示在多列中。样本数据如下:

df = pd.DataFrame({'Company': ['A', 'A', 'B', 'B', 'B', 'C', 'C'],
              'event_type':['new', 'route', 'receive', 'execute', 'route', 'receive', 'execute'],
             'event_id': ['110', '120', '200', '210', '220', '300', '310'],
             'prior_event_id': [np.nan, '110', np.nan, '120', '210', np.nan, '300'],
             'route_id': [np.nan, 'foo', 'foo', np.nan, 'bar', 'bar', np.nan]}
             )

数据帧如下所示:

  Company event_type event_id prior_event_id route_id
0       A        new      110            NaN      NaN
1       A      route      120            110      foo
2       B    receive      200            NaN      foo
3       B    execute      210            120      NaN
4       B      route      220            210      bar
5       C    receive      300            NaN      bar
6       C    execute      310            300      NaN
订单通过3个公司:A、B、C,在每个公司内,后一个事件可以通过event_id-prior_event_id对链接到其源事件。但这种方法不适用于属于不同公司的记录。例如,第1行和第2行将仅通过一列route_id匹配。因此,我尝试重新创建的链接机制是一种分层机制,因为如果event_id-prior_event_id列对没有产生任何结果,我将只使用列route_id进行匹配。

下图可能有助于说明链接机制:

我的解决方案很笨拙:

# Make every event unique so as to not confound the linking
df['event_sub'] = df.groupby(df.event_type).cumcount()+1 
df['event'] = df.event_type + ' ' + df.event_sub.astype(str) 

# Find the match based on first matching criterion
replace_dict_event = dict(df[['event_id', 'event']].values)
df['source'] = df['prior_event_id'].apply(lambda x: replace_dict_event.get(x) if replace_dict_event.get(x) else np.nan )
df['target'] = df['event_id'].apply(lambda x: replace_dict_event.get(x) if replace_dict_event.get(x) else np.nan )

# From last step, find the match based on second matching criterion for the unmatched rows 
replace_dict_rtd = dict(df[df.event_type == 'route'][['route_id', 'event']].values)
df.loc[df.event_type == 'receive', 'source'] = df[df.event_type == 'receive']['route_id'].apply(lambda x: replace_dict_rtd.get(x))
df
我基本上使用了apply两次来一步一步地获得匹配。我想知道有没有一种更干净、更有毕达德风格的方法。

我的结果如下:

和我由此创建的网络x图:


解决方案

您有两种不同类型的链接:a)通过匹配prior_event_idevent_id定义的链接,以及b)由route_id定义的链接。使用两组不同的命令来提取两种不同类型的关系是的典型做法(或者仅仅是良好的编码实践)。

也就是说,由于您正在处理表格数据,因此使用合并(特别是内部连接)来提取链接可能比使用带有Apply的字典查找更好。表格数据的数据库针对这类查询进行了优化,而对于大型数据集,您的查找速度会慢得多。

#!/usr/bin/env python
import numpy as np
import matplotlib.pyplot as plt
import pandas as pd

if __name__ == '__main__':

    df = pd.DataFrame({'Company': ['A', 'A', 'B', 'B', 'B', 'C', 'C'],
                       'event_type':['new', 'route', 'receive', 'execute', 'route', 'receive', 'execute'],
                       'event_id': ['110', '120', '200', '210', '220', '300', '310'],
                       'prior_event_id': [np.nan, '110', np.nan, '120', '210', np.nan, '300'],
                       'route_id': [np.nan, 'foo', 'foo', np.nan, 'bar', 'bar', np.nan]}
    )

    # --------------------------------------------------------------------------------
    # a) links established by matching event_id with prior_event_id
    df2 = pd.merge(df, df, left_on='event_id', right_on='prior_event_id', how='inner')

    #       Company_x event_type_x event_id_x prior_event_id_x route_id_x Company_y event_type_y event_id_y prior_event_id_y route_id_y
    # 0         A          new        110              NaN        NaN         A        route        120              110        foo
    # 1         A        route        120              110        foo         B      execute        210              120        NaN
    # 2         B      execute        210              120        NaN         B        route        220              210        bar
    # 3         C      receive        300              NaN        bar         C      execute        310              300        NaN

    # --------------------------------------------------------------------------------
    # b) links established by matching route_id

    # remove events without route ids
    valid = df['route_id'].notna()
    df3 = df['valid']

    #   Company event_type event_id prior_event_id route_id
    # 1       A      route      120            110      foo
    # 2       B    receive      200            NaN      foo
    # 4       B      route      220            210      bar
    # 5       C    receive      300            NaN      bar

    # join on route_id
    df4 = pd.merge(df3, df3, on='route_id', how='inner')

    #   Company_x event_type_x event_id_x prior_event_id_x route_id Company_y event_type_y event_id_y prior_event_id_y
    # 0         A        route        120              110      foo         A        route        120              110
    # 1         A        route        120              110      foo         B      receive        200              NaN
    # 2         B      receive        200              NaN      foo         A        route        120              110
    # 3         B      receive        200              NaN      foo         B      receive        200              NaN
    # 4         B        route        220              210      bar         B        route        220              210
    # 5         B        route        220              210      bar         C      receive        300              NaN
    # 6         C      receive        300              NaN      bar         B        route        220              210
    # 7         C      receive        300              NaN      bar         C      receive        300              NaN

    # remove cases where a company was matched to itself
    valid = df4['Company_x'] != df4['Company_y']
    df5 = df4[valid]

    #       Company_x event_type_x event_id_x prior_event_id_x route_id Company_y event_type_y event_id_y prior_event_id_y
    # 1         A        route        120              110      foo         B      receive        200              NaN
    # 2         B      receive        200              NaN      foo         A        route        120              110
    # 5         B        route        220              210      bar         C      receive        300              NaN
    # 6         C      receive        300              NaN      bar         B        route        220              210

相关文章