import pandas as pd
import time

df = pd.read_excel("Main Data File - Copy.XLSX").dropna()
superviserData = df.groupby(["Supv-pernr", "Supv-Name", "Supv Level"]).groups.keys()
supervisers = df["Supv-pernr"].unique().tolist()
supervisers.remove("A")

def countBelowLevel(perner):
    global counts
    totalCount = 0
    if counts.has_key(perner):
        totalCount += counts[perner]
    else:
        belowLevels = list(df[df["Supv-pernr"] == perner]["Perner"])
        totalCount += len(belowLevels)
        for b in belowLevels:
            counts[b] = countBelowLevel(b)
            totalCount += counts[b]
    return totalCount

t1 = time.time()
counts = {}
for s in supervisers:
    counts[s] = countBelowLevel(s)
print(time.time() - t1)

supvPernr = []
supvName = []
supvLevel = []
count = []
for tpl in superviserData:
    if tpl[0] == "A":
        belowLevels = list(df[(df["Supv-pernr"] == tpl[0]) & (df["Supv-Name"] == tpl[1])]["Perner"])
        totalCount = 0
        for b in belowLevels: totalCount += counts[b]
        count.append(totalCount + 1)
    elif tpl[0] in supvPernr:
        continue
    else:
        count.append(counts[tpl[0]] + 1)
    supvPernr.append(tpl[0])
    supvName.append(tpl[1])
    supvLevel.append(tpl[2])

pd.DataFrame({
    "Supv-pernr": supvPernr,
    "Supv-Name": supvName,
    "Supv-Level": supvLevel,
    "Followers": count
})[["Supv-pernr", "Supv-Name", "Supv-Level", "Followers"]].to_excel("result.xlsx", index=False)