Highest Salaries in Every Department!
Intuition:We have two tables: one with employee details and one with department details. Our task is to find the highest-paid employee in each department. This
### Intuition:

We have two tables: one with employee details and one with department details. Our task is to find the highest-paid employee in each department. This seems like a straightforward problem of **grouping employees by department** and then finding the **maximum salary** within each group.

### Approach:

1. **Merging the DataFrames**: We start by merging the `employee` and `department` DataFrames on the department ID. This way, we can link each employee to their respective department and get both the employee and department information together. 
   - The `merge()` function is used here because it allows us to combine two tables based on a common key (in this case, `departmentId` and `id`).

2. **Finding the highest salary**: Once the two DataFrames are merged, we need to find the highest salary for each department. 
   - To do this, we use the `groupby('departmentId')['salary'].transform('max')`. 
     - `groupby('departmentId')` groups the data by department.
     - `transform('max')` finds the maximum salary in each group (department) and **repeats** that maximum salary for every employee in the department. This makes it easy to compare each employee's salary with the highest salary in their department.
  
3. **Filtering the Data**: Now that we know the highest salary for each department, we can filter the employees whose salary matches the highest salary using:
   ```python
   merged.loc[merged.groupby('departmentId')['salary'].transform('max') == merged['salary']]
   ```
   - This filters out all employees except the ones with the highest salary in their department.

4. **Renaming the Columns**: After filtering the data, we select the columns we need (`Employee`, `Salary`, and `Department`) and rename them to match the desired output.
   - `name_x` is the employee's name from the `employee` table, and `name_y` is the department's name from the `department` table. We rename these columns to `Employee` and `Department`, respectively.

5. **Returning the Result**: Finally, we return the filtered DataFrame with the selected and renamed columns.

### Why use `groupby()` and `transform('max')`:

- **`groupby()`**: This is a fundamental operation when dealing with group-specific operations like "maximum salary per department." By grouping the data based on `departmentId`, we can perform operations like finding the maximum salary for each department.
  
- **`transform('max')`**: This is key because it doesn't just calculate the maximum salary for each department once and return a single value, it **repeats** the maximum salary for every employee in the group. This is super useful because it allows us to compare every employee's salary to the maximum salary of their department without losing the structure of the DataFrame.

If we used `agg('max')`, we would get a single row per department showing the maximum salary, but that wouldn't allow us to easily filter out the employees who have that maximum salary. With `transform('max')`, we can keep the same number of rows as the original DataFrame, making the comparison much easier.

### Example:

Let's use this approach with an example:

#### Data:

**Employee Table:**

| id  | name  | salary | departmentId |
|-----|-------|--------|--------------|
| 1   | Joe   | 70000  | 1            |
| 2   | Jim   | 90000  | 1            |
| 3   | Henry | 80000  | 2            |
| 4   | Sam   | 60000  | 2            |
| 5   | Max   | 90000  | 1            |

**Department Table:**

| id  | name  |
|-----|-------|
| 1   | IT    |
| 2   | Sales |

#### Merged DataFrame:

| id  | name_x | salary | departmentId | id_y | name_y |
|-----|--------|--------|--------------|------|--------|
| 1   | Joe    | 70000  | 1            | 1    | IT     |
| 2   | Jim    | 90000  | 1            | 1    | IT     |
| 3   | Henry  | 80000  | 2            | 2    | Sales  |
| 4   | Sam    | 60000  | 2            | 2    | Sales  |
| 5   | Max    | 90000  | 1            | 1    | IT     |

#### After Filtering for Highest Salary:

| Department | Employee | Salary |
|------------|----------|--------|
| IT         | Jim      | 90000  |
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |

### Complexity:

- **Time Complexity**:
  - Merging two DataFrames with `merge()` takes **O(n)**, where `n` is the total number of rows in the resulting DataFrame.
  - `groupby()` and `transform('max')` also take **O(n)** because it processes each row individually.
  - So, the overall **time complexity is O(n)**.

- **Space Complexity**:
  - We are storing the merged DataFrame and the filtered result. The space complexity is mainly dependent on the size of the DataFrame, so it’s **O(n)**.

### Code:

```pythondata []
import pandas as pd

def department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
    # Merging the employee and department DataFrames based on departmentId
    merged = employee.merge(department, left_on='departmentId', right_on='id', how='left')

    # Filtering employees whose salary equals the highest salary in their department
    highest_salary = merged.loc[merged.groupby('departmentId')['salary'].transform('max') == merged['salary']]
    
    # Renaming the columns to match the output format
    result = highest_salary[['name_x', 'salary', 'name_y']].rename(columns={ 
        'name_y': 'Department',  # department name column
        'name_x': 'Employee',    # employee name column
        'salary': 'Salary'       # salary column
    })
        
    # Returning the final result in the desired order
    return result[['Department', 'Employee', 'Salary']]
```
![upvote.jpeg](https://assets.leetcode.com/users/images/0eecd514-45b3-478b-ada6-25184cc54507_1741089657.4374201.jpeg)




['Pandas']