calculating size of a given node based on an edgelist in pandas

Question

I've got a tabular dataset that is basically an edgelist and a series of nodes that represent an orgchart. The fields are name of employee, their manager's name and whether or not that employee is a manager themselves or not (1/0)

The data (df) is as follows:

df:
ID  Full Name   Manager Name  manager
1571    BB       CC            1
1573    AA       BB            1
1579    CC       BB            0
1609    M        BB            0
1613    N        BB            0
1646    O        BB            0
1650    P        BB            0
1651    Q        BB            0
1652    R        BB            0
1580    D        AA            0
1581    EE       AA            1
1596    F        AA            0
1597    G        AA            0
1599    H        AA            0
1601    I        AA            0
1602    J        AA            0
1603    K        AA            0
1607    L        AA            0
1607    z        EE            0
1652    S        DD            0

What I'm trying to do is create a function that can take as input the name of a worker and calculate the size of their "org" which includes not just their direct reports, but the folks that report to their direct reports and so on and so on ... so it basically needs to be a recursive function. I want to then apply that function to every row in my source dataset

In the data example above, the output of the following function should be as follows:

estimate_org_size('BB') = 18

This is because BB has 8 direct reports. One of these, AA in turn has 9 direct reports. One of AA's reports (EE) has one direct report. So altogether BB ends up with an org size of 8+9+1 = 18

Here's what I've tried that hasn't worked:

managers = df["Manager Name"].unique().tolist()

def estimate_reports(name):
    if name not in managers:
        return 0
    else:
        return df[df["Manager Name"]==name]['Full Name'].nunique()


def estimate_org_size(name, running_sum=0):
    if name not in managers:
        return 0
    else:
        s = running_sum
        df2 = df[df["Manager Name"]==name]
        l = df2.apply(lambda x: estimate_reports(x["Full Name"]), axis=1)  #layer 1 of direct total
        s =  s + l.sum()  #layer 1 of direct total
        l1 = pd.concat( [df2[["Full Name", "Manager Name", 'manager']], pd.DataFrame(l)], axis=1 )
        if l1['manager'].sum() > 0:
            l1.apply( lambda x: estimate_org_size(x["Full Name"], running_sum=s), axis=1 )
            return s
        else:
            return s

sample = df[df['Full Name']=='BB']

sample.apply(lambda x: estimate_org_size(x["Full Name"], running_sum=0), axis=1)  #<- Not working

Any help would be super appreciated. Thanks

user_15438033 · Answer

Seems like a network problem:

Here's one way to solve:

Convert the dataframe to a network graph
Find the largest network in a NetworkX graph.
Evaluate the size

# pip install networkx
import networkx as nx

G = nx.Graph()
G = nx.from_pandas_edgelist(df, 'FullName', 'ManagerName')

cur_graph = G  # whatever graph you're working with

if not nx.is_connected(cur_graph):
    # get a list of unconnected networks
    sub_graphs = [G.subgraph(c) for c in nx.connected_components(G)]

    main_graph = sub_graphs[0]

    # find the largest network in that list
    for sg in sub_graphs:
        if len(sg.nodes()) > len(main_graph.nodes()):
            main_graph = sg

    cur_graph = main_graph

print(cur_graph.size())

If you want some visualization you can try:

from matplotlib.pyplot import figure
figure(figsize=(10, 8))
nx.draw_shell(cur_graph, with_labels=True)

calculating size of a given node based on an edgelist in pandas

1 Answers

OUTPUT: