Recursive SQL CTE query in Pandas?

Question

how do I elegantly port the below recursive SQL query to Pandas python code? Somehow I don't see a straightforward way without writing own recursive function? Python sample code: ``` import datetime import numpy as np import pandas as pd import pandas.io.data from pandas import Series, DataFrame data = { 'ID': [1,2,3,4,5,6,7,8], 'Name': ['Keith','Josh','Robin','Raja','Tridip','Arijit','Amit','Dev'], 'MgrID': [0,1,1,2,0,5,5,6] } df = pd.DataFrame.from_dict(data) df.set_index('ID', inplace=True, drop=False, append=False) df.ix[df.query('MgrID >0')['MgrID']] ``` Trying to get this: ``` nLevel ID Name ================================ 1 6 Arijit 2 8 Dev 1 1 Keith 2 2 Josh 2 3 Robin 3 4 Raja 1 5 Tridip 2 7 Amit ``` Recursive SQL Query: ``` ;WITH Employee (ID, Name, MgrID) AS ( SELECT 1, 'Keith', NULL UNION ALL SELECT 2, 'Josh', 1 UNION ALL SELECT 3, 'Robin', 1 UNION ALL SELECT 4, 'Raja', 2 UNION ALL SELECT 5, 'Tridip', NULL UNION ALL SELECT 6, 'Arijit', NULL UNION ALL SELECT 7, 'Amit', 5 UN…

Accepted Answer

First, you need to correct the typo in python code `MgrID` list: `[0,1,1,2,0,0,5,6]`

Second, if this job is done recursively in SQL, why do you expect Python/Pandas can do it without recursive method? It isn't too hard:

```
def nlevel(id, mgr_dict=df.MgrID, _cache={0:0}):
    if id in _cache:
        return _cache[id]

return 1+nlevel(mgr_dict[id],mgr_dict)

df['nLevel'] = df.ID.map(nlevel)

print df[['nLevel','ID','Name']]
```

Then the output(`nLevel`) is what you need (except the order, which I don't understand from your SQL):

```
    nLevel  ID    Name
ID                    
1        1   1   Keith
2        2   2    Josh
3        2   3   Robin
4        3   4    Raja
5        1   5  Tridip
6        1   6  Arijit
7        2   7    Amit
8        2   8     Dev

[8 rows x 3 columns]
```

Recursive SQL CTE query in Pandas?

Solution

Problem