python dataframe做类似oracle connect_的事情吗？

Question

在oracle中，我可以对父子层次结构执行类似的操作。它很方便。现在我在pandas dataframe中做同样的操作吗

with tab1zx as (
select 1 as id, null as parent_id from dual union
select 2 as id, 1 as parent_id from dual union
select 3 as id, 2 as parent_id from dual union
select 4 as id, 2 as parent_id from dual union
select 5 as id, 4 as parent_id from dual union
select 6 as id, 4 as parent_id from dual union
select 7 as id, 1 as parent_id from dual union
select 8 as id, 7 as parent_id from dual union
select 9 as id, 1 as parent_id from dual union
select 10 as id, 9 as parent_id from dual union
select 11 as id, 10 as parent_id from dual union
select 12 as id, 9 as parent_id from dual

)
--select * from tab1zx
SELECT id,
       parent_id,
       RPAD('.', (level-1)*2, '.') || id AS tree,
       level,
       CONNECT_BY_ROOT id AS root_id,
       LTRIM(SYS_CONNECT_BY_PATH(id, '-'), '-') AS path,
       CONNECT_BY_ISLEAF AS leaf
FROM   tab1zx
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR id
ORDER SIBLINGS BY id;

如果我有一个python数据帧，我该怎么做呢

^{pr2}$

Answer 1

可以尝试生成笛卡尔积并过滤它：

In [32]: df.assign(k=0) \
           .merge(df.assign(k=0), on='k', suffixes=['1','2']) \
           .query("id1 == parent_id2")
Out[32]:
    id1 parent_id1  k id2 parent_id2
1     1             0   2          1
6     1             0   7          1
8     1             0   9          1
14    2          1  0   3          2
15    2          1  0   4          2
40    4          2  0   5          4
41    4          2  0   6          4
79    7          1  0   8          7
105   9          1  0  10          9
107   9          1  0  12          9
118  10          9  0  11         10

PS AFAIK在熊猫中没有甲骨文的hierarchical queries的有效类似物