Project employees iii

# Table: Project
# 
# +-------------+---------+
# | Column Name | Type    |
# +-------------+---------+
# | project_id  | int     |
# | employee_id | int     |
# +-------------+---------+
# (project_id, employee_id) is the primary key (combination of columns with unique values) of this table.
# employee_id is a foreign key (reference column) to Employee table.
# Each row of this table indicates that the employee with employee_id is working on the project with project_id.
# 
#  
# 
# Table: Employee
# 
# +------------------+---------+
# | Column Name      | Type    |
# +------------------+---------+
# | employee_id      | int     |
# | name             | varchar |
# | experience_years | int     |
# +------------------+---------+
# employee_id is the primary key (column with unique values) of this table.
# Each row of this table contains information about one employee.
# 
#  
# 
# Write a solution to report the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.
# 
# Return the result table in any order.
# 
# The result format is in the following example.
#
# Example 1:
# Input:
# Project table:
# +-------------+-------------+
# | project_id  | employee_id |
# +-------------+-------------+
# | 1           | 1           |
# | 1           | 2           |
# | 1           | 3           |
# | 2           | 1           |
# | 2           | 4           |
# +-------------+-------------+
# Employee table:
# +-------------+--------+------------------+
# | employee_id | name   | experience_years |
# +-------------+--------+------------------+
# | 1           | Khaled | 3                |
# | 2           | Ali    | 2                |
# | 3           | John   | 3                |
# | 4           | Doe    | 2                |
# +-------------+--------+------------------+
# Output:
# +-------------+---------------+
# | project_id  | employee_id   |
# +-------------+---------------+
# | 1           | 1             |
# | 1           | 3             |
# | 2           | 1             |
# +-------------+---------------+
# Explanation: Both employees with id 1 and 3 have the most experience among the employees of the first project. For the second project, the employee with id 1 has the most experience.

import pandas as pd

def project_employees(project: pd.DataFrame, employee: pd.DataFrame) -> pd.DataFrame:
  merged = project.merge(employee, on='employee_id')
  max_exp = merged.groupby('project_id')['experience_years'].transform('max')
  result = merged[merged['experience_years'] == max_exp]
  return result[['project_id', 'employee_id']]