I have a data frame which have three columns User Name, Designation, and Vertical Report. User Name has a designation and to whom the username report is in Vertical Report column.

DATASET


USERNAME              DESIGNATION CODE    VERTICAL REPORT
RAJKUMAR.MALVIYA           BM              GOURAV.MOD
GOURAV.MOD                 ASM             PRASANNA.NAIK
PRASANNA.NAIK              RSM             MILIND.DESHMUKH
SANJAY.BHATNAGAR           NSM             ARUN
UMANG.GOHIL1               BM              HIREN.JASANI
MILIND.DESHMUKH            ZSM             SANJAY.BHATNAGAR 
HIREN.JASANI               ASM             BHAVIN.GANDHI
SACHIN.PAWAR               NSM             ARUN
BHAVIN.GANDHI              DSM             ANURAG.JOSHI
ANURAG.JOSHI               ZSM             SACHIN.PAWAR
SACHIN.PAWAR               NSM             ARUN
SANGRAM.KEDARI             BM              NIKHIL.BELKHEDE
NIKHIL.BELKHEDE            DSM             SACHIN.PAWAR 
SACHIN.PAWAR               NSM             ARUN

This is my data set In which First user RAJKUMAR.MALVIYA had Designation Code BM and his Vertical Report person is GOURAV.MOD similarly GOURAV.MOD has designation ASM and his Vertical Report person is PRASANNA.NAIK AND so on. this makes a hierarchy of usernames and vertical report based on their designation and I want to get solved this hierarchy in python. And if their is missing any Designation it should be filled with L plus COLUMNNUMBER number.

SOURCE CODE

`# Initialize designation dictionary
designations = ['BM', 'ASM', 'CSM', 'DSM', 'RSM', 'ZSM', 'NSM']
designation_dict = {designation: [] for designation in designations}

# Iterate through each row in the Data Frame
for _, row in df.iterrows():
    username = row['User Name']
    designation = row['Designation Code']
    vertical = row['Vertical Report']



for _, row in df.iterrows():
    username = row['User Name']
    designation = row['Designation Code']
    vertical = row['Vertical Report']

    # Check if the designation is not 'DST'
    if designation.strip() and designation != 'DST':
        # Append username to the appropriate designation list
        designation_dict[designation].append(username)

        # Find the row corresponding to the vertical report
        search row = df[df['User Name'] == vertical]

        # If a match is found, update username, designation, and vertical
        if not search_row.empty:
            username = search_row.iloc[0]['User Name']
            print(username)
            designation = search_row.iloc[0]['Designation Code']
            print(designation)
            vertical = search_row.iloc[0]['Vertical Report']
            print(vertical)

# Fill any remaining blank cells with 'BLANK'
max_length = max(len(lst) for lst in designation_dict.values())
for key in designation_dict:
    while len(designation_dict[key]) < max_length:
        designation_dict[key].append('BLANK')

# Create DataFrame from the designation dictionary
output_df = pd.DataFrame(designation_dict)
`

EXPECTED OUTPUT DATAFRAME

BM                ASM           CSM   DSM             RSM           ZSM               NSM
RAJKUMAR.MALVIYA  GOURAV.MOD     L3   L4              PRASANNA.NAIK MILIND.DESHMUK SANJAY.BHATNAG 
UMANG.GOHIL1      HIREN.JASANI   L3   BHAVIN.GANDHI   L5            ANURAG.JOSHI   SACHIN.PAWAR
SANGRAM.KEDARI    L2             L3   NIKHIL.BELKHEDE L5            L6             SACHIN.PAWAR