Find Highest Salary Employees by Department (Merge & Filter) in Pandas
Intuition\nTo solve the problem of finding the employee with the highest salary in each department using Pandas, start by combining the Employee and Department 
# Intuition\nTo solve the problem of finding the employee with the highest salary in each department using Pandas, start by combining the Employee and Department DataFrames. This will allow you to associate each employee with their respective department. Next, determine the highest salary within each department and filter the DataFrame to retain only those employees who have this maximum salary.\n\n# Approach\n1. `Merge DataFrames`: Use pd.merge() to combine the Employee and Department DataFrames based on the departmentId and id columns. This merge will add department names to the employee records.\n\n2. `Rename Columns`: Rename columns to make them more descriptive and select only the columns of interest: Department, Employee, and Salary.\n\n3. `Find Maximum Salary`: Use groupby() along with transform(max) to identify the maximum salary within each department. This will add a new column with the maximum salary for each employee\u2019s department.\n\n4. `Filter Results`: Filter the DataFrame to include only those rows where the employee\u2019s salary matches the maximum salary for their department.\n\n# Complexity\n- Time complexity:$$O(n)$$ for merging and filtering operations assuming efficient internal implementations.\n\n- Space complexity: $$O(n)$$ the space complexity is dominated by the size of the DataFrame, which grows linearly with the number of rows. The merged DataFrame and intermediate results are stored in memory.\n\n# Code\n```\nimport pandas as pd\n\ndef department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:\n    # Merge the Employee and Department DataFrames based on the departmentId\n    merged_df = pd.merge(employee, department, left_on=\'departmentId\', right_on=\'id\', how=\'left\')\n    \n    # Rename columns for clarity and select relevant columns\n    merged_df = merged_df.rename(columns={\'name_x\': \'Employee\', \'name_y\': \'Department\', \'salary\': \'Salary\'})[[\'Department\', \'Employee\', \'Salary\']]\n    \n    # Filter rows where Salary matches the maximum salary within each Department\n    result_df = merged_df[merged_df[\'Salary\'] == merged_df.groupby(\'Department\')[\'Salary\'].transform(max)]\n    \n    return result_df\n```
['Database', 'Pandas']