Home > front end >  Pandas Convert Dataframe to Employee/Supervisor Hierarchy
Pandas Convert Dataframe to Employee/Supervisor Hierarchy

Time:07-23

I have a dataframe that is very similar to enter image description here

# rework dataframe
df = df.replace({'None':  pd.NA})
df['E'] = (df['Employee_FN'] ' ' df['Employee_LN']).mask(df['Employee_FN'].isnull())
df['S'] = (df['Supervisor_FN'] ' ' df['Supervisor_LN']).mask(df['Supervisor_FN'].isnull())

attr = df.set_index('E')[['Employee_FN', 'Employee_LN']].to_dict('index')

# generate graph
import networkx as nx

G = nx.from_pandas_edgelist(df.dropna(), source='S', target='E',
                            create_using=nx.DiGraph)

# generate nested dictionary
def make_dic(n, d=None):
    if d is None:
        d = {}
    d.update(attr[n])
    successors = list(G.successors(n))
    if successors:
        d['Reports'] = [make_dic(x) for x in successors]
    return d

out = make_dic(next(nx.topological_sort(G)))

output:

{'Employee_FN': 'Michael',
 'Employee_LN': 'Scott',
 'Reports': [{'Employee_FN': 'Jim',
   'Employee_LN': 'Halpert',
   'Reports': [{'Employee_FN': 'Pam',
     'Employee_LN': 'Beasley',
     'Reports': [{'Employee_FN': 'Ryan',
       'Employee_LN': 'Howard',
       'Reports': [{'Employee_FN': 'Meredith', 'Employee_LN': 'Palmer'},
        {'Employee_FN': 'Kelly', 'Employee_LN': 'Kapoor'}]}]},
    {'Employee_FN': 'Stanley', 'Employee_LN': 'Hudson'}]},
  {'Employee_FN': 'Dwight', 'Employee_LN': 'Schrute'}]}
  • Related