如何在Pandas DataFrame中基于列关联创建层级列识别管理层负责人？

Hey there! I get it—manually creating those Level columns in Excel is a pain when hierarchies get deep. Let's fix that with a clean, automated Pandas solution that handles any number of hierarchy levels without manual column setup.

Step-by-Step Approach: #

1. Create a Manager Lookup Map: First, we'll make a dictionary to quickly look up any employee's manager. This acts like our VLOOKUP table but is way faster in Pandas.
2. Recursively Traverse Hierarchies: For each employee's manager, we'll climb up the chain until we hit a manager who doesn't have an entry in our employee list (the top-level head).
3. Expand Hierarchy Levels: Convert the list of hierarchy levels into separate columns (Level 1, Level 2, etc.) automatically.
4. Clean Up the Output: Reorder columns and format to match your expected result.

Full Code Implementation: #

import pandas as pd

# 1. Create the original DataFrame
data = {
    'Employee_ID': ['E068', 'E071', 'E229', 'E248', 'E226', 'E236', 'E066', 'E067', 'E144', 'E223'],
    'Manager_ID': ['E067', 'E067', 'E069', 'E144', 'E223', 'E241', 'E001', 'E001', 'E001', 'E001']
}
df = pd.DataFrame(data)

# 2. Build a lookup map from Employee ID to their Manager ID
emp_manager_map = df.set_index('Employee_ID')['Manager_ID'].to_dict()

# 3. Helper function to get hierarchy levels and top head
def get_hierarchy(manager_id, lookup_map):
    hierarchy_levels = []
    current_manager = manager_id
    
    # Traverse up the hierarchy until we can't find a higher manager
    while True:
        next_manager = lookup_map.get(current_manager)
        if next_manager is None:
            break
        hierarchy_levels.append(next_manager)
        current_manager = next_manager
    
    # The head is the last level if exists, else the original manager
    head = hierarchy_levels[-1] if hierarchy_levels else manager_id
    return hierarchy_levels, head

# 4. Apply the helper function to each row
df[['hierarchy_levels', 'Head of Manager']] = df['Manager_ID'].apply(
    lambda x: pd.Series(get_hierarchy(x, emp_manager_map))
)

# 5. Expand hierarchy levels into separate columns
max_level_count = df['hierarchy_levels'].str.len().max()
for level_num in range(max_level_count):
    df[f'Level {level_num + 1}'] = df['hierarchy_levels'].str[level_num]

# 6. Clean up and reorder columns to match expected output
df = df.drop('hierarchy_levels', axis=1)
df = df.rename(columns={'Employee_ID': 'Employee ID', 'Manager_ID': 'Manager ID'})
column_order = ['Employee ID', 'Manager ID', 'Level 1', 'Level 2', 'Head of Manager']
df = df[column_order]

# Show the result
print(df)

Output: #

Key Notes: #

• Automated Level Columns: The code automatically creates as many Level columns as needed based on the deepest hierarchy in your data. No manual column setup required!
• Handles Edge Cases: Works for employees whose managers are top-level (no higher manager) or even managers who aren't in your employee list (like E241 in the example).
• Efficient Lookups: Using a dictionary for lookups is much faster than repeated VLOOKUPs, especially with large datasets.

内容的提问来源于stack exchange，提问作者user12225102