[Python] Excel Data Merge with Sheet-Based Status Priority [closed]

I need to process an Excel file with multiple sheets, where each sheet contains a list of companies with different statuses. The sheet order determines the priority of these statuses.

Processing Flow
Sheet 4: Contains the complete list of companies (reference base)
Sheet 2: If a company from Sheet 4 is present here, its status becomes "Unrealised" (unless already marked otherwise)
Sheet 3: If a company from Sheet 4 is present here, its status becomes "Realised" (this overrides "Unrealised" if previously set)



here is my code



But my final dataframe gives me this and i can't figure why .



Continue reading...