Been

[rakib06]

import pandas as pd

def calculate_hierarchy_rollup(
    df,
    emp_col='Employee ID',
    sup_col='Supervisor ID',
    count_col='PRCount',
    output_col='PRCountSum'
):

    # Keep one row per employee
    temp_df = (
        df[[emp_col, sup_col, count_col]]
        .drop_duplicates(subset=[emp_col])
        .copy()
    )

    # Build hierarchy
    children_map = (
        temp_df.groupby(sup_col)[emp_col]
        .apply(list)
        .to_dict()
    )

    count_map = (
        temp_df.set_index(emp_col)[count_col]
        .to_dict()
    )

    memo = {}

    def get_rollup_sum(emp_id):

        if emp_id in memo:
            return memo[emp_id]

        total = count_map.get(emp_id,0)

        for child in children_map.get(emp_id,[]):
            total += get_rollup_sum(child)

        memo[emp_id] = total

        return total

    result_df = df.copy()

    result_df[output_col] = (
        result_df[emp_col]
        .apply(get_rollup_sum)
    )

    return result_df

[rakib06]

import pandas as pd

def calculate_hierarchy_rollup(
    df,
    emp_col='Employee ID',
    sup_col='Supervisor ID',
    count_col='PRCount',
    output_col='PRCountSum'
):

    # child mapping
    children_map = (
        df.groupby(sup_col)[emp_col]
        .apply(lambda x: list(set(x)))   # duplicate child remove only
        .to_dict()
    )

    count_map = (
        df.set_index(emp_col)[count_col]
        .to_dict()
    )

    memo = {}

    def get_rollup_sum(emp_id):

        if emp_id in memo:
            return memo[emp_id]

        total = count_map.get(emp_id, 0)

        children = children_map.get(emp_id, [])

        for child in children:
            total += get_rollup_sum(child)

        memo[emp_id] = total

        return total

    result_df = df.copy()

    result_df[output_col] = (
        result_df[emp_col]
        .apply(get_rollup_sum)
    )

    return result_df

[rakib06]

import pandas as pd

def calculate_hierarchy_rollup(
    df,
    emp_col='Employee ID',
    sup_col='Supervisor ID',
    count_col='PRCount',
    output_col='PRCountSum'
):

    result_df = df.copy()

    # employee -> own count
    count_map = (
        result_df
        .set_index(emp_col)[count_col]
        .fillna(0)
        .to_dict()
    )

    # supervisor -> child list
    children_map = (
        result_df.groupby(sup_col)[emp_col]
        .apply(list)
        .to_dict()
    )

    memo = {}

    def rollup(emp_id):

        # already calculated
        if emp_id in memo:
            return memo[emp_id]

        # start with own value
        total = count_map.get(emp_id, 0)

        # add children's total first
        children = children_map.get(emp_id, [])

        child_total = 0
        for child in children:
            child_total += rollup(child)

        total += child_total

        memo[emp_id] = total
        return total

    result_df[output_col] = (
        result_df[emp_col]
        .apply(rollup)
    )

    return result_df

[rakib06]

import pandas as pd
from collections import defaultdict

def calculate_hierarchy_rollup(
    df,
    emp_col='Employee ID',
    sup_col='Supervisor ID',
    count_col='PRCount',
    output_col='PRCountSum'
):

    result_df = df.copy()

    # employee own count
    count_map = (
        result_df
        .set_index(emp_col)[count_col]
        .to_dict()
    )

    # supervisor -> child map
    children_map = defaultdict(list)

    for _, row in result_df.iterrows():
        children_map[row[sup_col]].append(
            row[emp_col]
        )

    memo = {}

    def rollup(emp):

        if emp in memo:
            return memo[emp]

        total = count_map.get(emp, 0)

        for child in children_map.get(emp, []):
            total += rollup(child)

        memo[emp] = total

        return total


    result_df[output_col] = (
        result_df[emp_col]
        .apply(rollup)
    )

    # ---------------- DEBUG SECTION ----------------

    all_emp = set(result_df[emp_col])

    all_supervisors = set(
        result_df[sup_col].dropna()
    )

    # supervisor id exists but not as employee id
    missing_supervisors = (
        all_supervisors - all_emp
    )

    if missing_supervisors:

        print("\n⚠ Missing Supervisor IDs found:")
        print(list(missing_supervisors))

        orphan_employees = result_df[
            result_df[sup_col]
            .isin(missing_supervisors)
        ][emp_col].tolist()

        print(
            "\n⚠ Employees disconnected from hierarchy:"
        )
        print(orphan_employees)

    else:
        print(
            "\n✓ No disconnected employees found"
        )

    return result_df

[rakib06]

# ---------------- DEBUG SECTION ----------------

all_emp = set(result_df[emp_col])

all_supervisors = set(
    result_df[sup_col].dropna()
)

# Missing supervisors
missing_supervisors = (
    all_supervisors - all_emp
)

if missing_supervisors:

    print("\n⚠ Missing Supervisor IDs found:")
    print(list(missing_supervisors))

    orphan_employees = result_df[
        result_df[sup_col]
        .isin(missing_supervisors)
    ][[emp_col, sup_col]]

    print(
        "\n⚠ Employees disconnected from hierarchy:"
    )
    print(orphan_employees)

else:
    print("\n✓ No disconnected employees found")


# ---------- Multiple supervisor check ----------

multiple_supervisors = (
    result_df
    .groupby(emp_col)[sup_col]
    .nunique()
)

multiple_supervisors = (
    multiple_supervisors[
        multiple_supervisors > 1
    ]
)

if len(multiple_supervisors) > 0:

    print(
        "\n⚠ Employees with multiple supervisors found:"
    )

    multi_df = result_df[
        result_df[emp_col]
        .isin(multiple_supervisors.index)
    ][[emp_col, sup_col]]

    print(
        multi_df
        .sort_values(emp_col)
    )

else:
    print(
        "\n✓ No employee has multiple supervisors"
    )

[rakib06]

import pandas as pd
from collections import defaultdict

def calculate_hierarchy_rollup(
    df,
    emp_col='Employee ID',
    sup_col='Supervisor ID',
    count_col='PRCount',
    output_col='PRCountSum'
):

    result_df = df.copy()

    # ---- DEBUG: invalid rows ----
    invalid = result_df[
        (result_df['PRCount'] == 1) &
        (result_df['NPRCount'] == 1)
    ]

    if len(invalid) > 0:
        print("\n⚠ Employee has both PR and NPR = 1")
        print(invalid[[emp_col,'PRCount','NPRCount']])

    # Employee -> own contribution ONLY from selected count column
    count_map = dict(
        zip(
            result_df[emp_col],
            result_df[count_col].fillna(0)
        )
    )

    # Supervisor -> children
    children_map = defaultdict(set)

    for _, row in result_df.iterrows():
        children_map[row[sup_col]].add(
            row[emp_col]
        )

    memo = {}

    def rollup(emp_id):

        if emp_id in memo:
            return memo[emp_id]

        # Only this selected column contributes
        total = count_map.get(emp_id, 0)

        for child in children_map.get(emp_id, set()):
            total += rollup(child)

        memo[emp_id] = total
        return total

    result_df[output_col] = (
        result_df[emp_col]
        .apply(rollup)
    )

    return result_df

[rakib06]

import pandas as pd
from collections import defaultdict

def calculate_hierarchy_rollup(
    df,
    emp_col='Employee ID',
    sup_col='Supervisor ID',
    count_col='PRCount',
    output_col='PRCountSum',
    level_col='level'
):

    result_df = df.copy()

    # ==========================
    # Employee -> own count
    # ==========================

    count_map = {}

    for _, row in result_df.iterrows():

        emp = row[emp_col]

        if emp not in count_map:
            count_map[emp] = row[count_col]
        else:
            count_map[emp] += row[count_col]


    # ==========================
    # Supervisor -> Child map
    # ==========================

    children_map = defaultdict(set)

    for _, row in result_df.iterrows():

        sup = row[sup_col]
        emp = row[emp_col]

        children_map[sup].add(emp)


    # ==========================
    # Bottom → Top Rollup
    # ==========================

    memo = {}

    def rollup(emp):

        if emp in memo:
            return memo[emp]

        total = count_map.get(emp,0)

        for child in children_map.get(emp,set()):

            total += rollup(child)

        memo[emp] = total

        return total


    result_df[output_col] = (
        result_df[emp_col]
        .apply(rollup)
    )


    # =====================================================
    # DEBUG SECTION
    # =====================================================

    print("\n")
    print("="*50)
    print("DEBUG START")
    print("="*50)


    # -----------------------------------------
    # Actual vs Rollup
    # -----------------------------------------

    actual_total = result_df[count_col].sum()

    boss_total = result_df.loc[
        result_df[level_col]==0,
        output_col
    ].iloc[0]

    print(f"\nActual Total = {actual_total}")
    print(f"Boss Rollup Total = {boss_total}")
    print(f"Difference = {actual_total-boss_total}")


    # -----------------------------------------
    # Missing Supervisor IDs
    # -----------------------------------------

    all_emp = set(
        result_df[emp_col]
    )

    all_sup = set(
        result_df[sup_col]
        .dropna()
    )

    missing_supervisors = (
        all_sup-all_emp
    )

    print("\n")

    if len(missing_supervisors)>0:

        print("⚠ Missing Supervisor IDs:")

        print(
            list(missing_supervisors)
        )

    else:

        print(
            "✓ No Missing Supervisor IDs"
        )


    # -----------------------------------------
    # Multiple Supervisor Check
    # -----------------------------------------

    multiple_supervisors = (

        result_df
        .groupby(emp_col)[sup_col]
        .nunique()

    )

    multiple_supervisors = (

        multiple_supervisors[
            multiple_supervisors>1
        ]

    )

    print("\n")

    if len(multiple_supervisors)>0:

        print(
            "⚠ Employees with multiple supervisors:"
        )

        multi_df = result_df[

            result_df[emp_col]
            .isin(
                multiple_supervisors.index
            )

        ][[emp_col,sup_col]]

        print(
            multi_df.sort_values(
                emp_col
            )
        )

    else:

        print(
            "✓ No employee has multiple supervisors"
        )


    # -----------------------------------------
    # Find reachable employees
    # -----------------------------------------

    top_boss = result_df.loc[
        result_df[level_col]==0,
        emp_col
    ].iloc[0]


    reachable = set()


    def collect(emp):

        if emp in reachable:
            return

        reachable.add(emp)

        for child in children_map.get(
            emp,
            set()
        ):
            collect(child)


    collect(top_boss)


    # -----------------------------------------
    # Missing PR employees
    # -----------------------------------------

    missing_people = result_df[

        (result_df[count_col]==1)
        &
        (~result_df[emp_col]
          .isin(reachable))
        &
        (result_df['NPRCount']==0)

    ][

        [
            emp_col,
            sup_col,
            count_col,
            'NPRCount'
        ]

    ]


    print("\n")

    if len(missing_people)>0:

        print(
            "⚠ Missing employees from hierarchy:"
        )

        print(
            missing_people
        )

    else:

        print(
            "✓ No missing employees"
        )


    print("\n")
    print("="*50)
    print("DEBUG END")
    print("="*50)

    return result_df

[rakib06]

import pandas as pd
import numpy as np

def assign_management_tier(df, top_employee_id):
    """
    Calculates the Management Tier (MT) for a hierarchy.
    Safely handles rows that are missing an 'Employee ID'.
    """
    # Create a copy to avoid modifying the original dataframe
    result_df = df.copy()
    
    # Initialize the new 'MT' column with NaN (Not a Number)
    result_df['MT'] = np.nan
    
    # Set MT = 0 for the target top employee
    result_df.loc[result_df['Employee ID'] == top_employee_id, 'MT'] = 0
    
    current_mt = 0
    
    while True:
        # Get a list of VALID Employee IDs at the current MT level
        # We use .notna() and != "" to ensure we don't accidentally grab blank/NaN IDs
        valid_emp_mask = (result_df['MT'] == current_mt) & (result_df['Employee ID'].notna()) & (result_df['Employee ID'] != "")
        current_level_emps = result_df.loc[valid_emp_mask, 'Employee ID'].tolist()
        
        # If no valid employees are found at this level, we've reached the bottom
        if not current_level_emps:
            break
            
        # Find all rows where the Supervisor ID belongs to someone in the current level
        # This will correctly assign MT to a row even if its own Employee ID is missing!
        subordinates_mask = (result_df['Supervisor ID'].isin(current_level_emps)) & (result_df['MT'].isna())
        
        # If no subordinates are found, break the loop
        if not subordinates_mask.any():
            break
            
        # Assign the next tier (MT + 1) to these subordinates
        result_df.loc[subordinates_mask, 'MT'] = current_mt + 1
        
        # Move on to the next tier for the next iteration
        current_mt += 1
        
    # Fill remaining unassigned rows (outside the hierarchy) with -1
    result_df['MT'] = result_df['MT'].fillna(-1)
        
    return result_df

# ==========================================
# Example Usage
# ==========================================

# 1. Create dummy data (Notice the last row has NO Employee ID, but reports to 100)
data = {
    "Supervisor ID": [None, 100, 100, 101, 102, 103, 104, 999, 100],
    "Employee ID":   [100,  101, 102, 103, 104, 105, 106, 888, None], 
    "Country_v2":    ["US", "UK", "US", "CA", "UK", "IN", "AU", "US", "JP"]
}
df = pd.DataFrame(data)

# 2. Run the function
df_with_mt = assign_management_tier(df, top_employee_id=100)

print(df_with_mt)