문제
Table: Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.
Write a solution to find the employees who earn more than their managers.
Return the result table in any order.
예시
Input:
Employee table:
+----+-------+--------+-----------+
| id | name | salary | managerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
+----+-------+--------+-----------+
Output:
+----------+
| Employee |
+----------+
| Joe |
+----------+
Explanation: Joe is the only employee who earns more than his manager.
문제 풀이
1. merge해서 employee와 manager 매칭 (suffixes 사용 X -> x와 y 할당됨)
import pandas as pd
def find_employees(employee: pd.DataFrame) -> pd.DataFrame:
merged = pd.merge(employee, employee, left_on = 'managerId', right_on = 'id', how = 'left', suffixes=('_employee', '_manager'))
return merged
| id_employee | name_employee | salary_employee | managerId_employee | id_manager | name_manager | salary_manager | managerId_manager |
| ----------- | ------------- | --------------- | ------------------ | ---------- | ------------ | -------------- | ----------------- |
| 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | null |
| 2 | Henry | 80000 | 4 | 4 | Max | 90000 | null |
| 3 | Sam | 60000 | null | null | null | null | null |
| 4 | Max | 90000 | null | null | null | null | null |
2. manager가 없는 행 제외
import pandas as pd
def find_employees(employee: pd.DataFrame) -> pd.DataFrame:
merged = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left', suffixes=('_employee', '_manager'))
filtered = merged[merged['id_manager'].notnull()]
return filtered
| id_employee | name_employee | salary_employee | managerId_employee | id_manager | name_manager | salary_manager | managerId_manager |
| ----------- | ------------- | --------------- | ------------------ | ---------- | ------------ | -------------- | ----------------- |
| 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | null |
| 2 | Henry | 80000 | 4 | 4 | Max | 90000 | null |
3. salary_employee가 salary_manager보다 높은 행 출력
import pandas as pd
def find_employees(employee: pd.DataFrame) -> pd.DataFrame:
merged = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left', suffixes=('_employee', '_manager'))
filtered = merged[merged['id_manager'].notnull()]
result = filtered[filtered['salary_employee'] > filtered['salary_manager']]
return result
| id_employee | name_employee | salary_employee | managerId_employee | id_manager | name_manager | salary_manager | managerId_manager |
| ----------- | ------------- | --------------- | ------------------ | ---------- | ------------ | -------------- | ----------------- |
| 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | null |
4. 요구 조건에 맞추어 컬럼명 수정 후 출력
import pandas as pd
def find_employees(employee: pd.DataFrame) -> pd.DataFrame:
merged = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left', suffixes=('_employee', '_manager'))
filtered = merged[merged['id_manager'].notnull()]
result = filtered[filtered['salary_employee'] > filtered['salary_manager']]
result = result.rename(columns={'name_employee': 'Employee', 'name_manager': 'Manager'})
return result[['Employee']]
| Employee |
| -------- |
| Joe |
* notnull, salary 조건 한 줄로
import pandas as pd
def find_employees(employee: pd.DataFrame) -> pd.DataFrame:
merged = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left', suffixes=('_employee', '_manager'))
filtered = merged[(merged['id_manager'].notnull()) & (merged['salary_employee'] > merged['salary_manager'])]
result = filtered.rename(columns={'name_employee': 'Employee', 'name_manager': 'Manager'})
return result[['Employee']]
파이썬을 독학하시는 분들에게 도움이 되길 바라며,
혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.
문제
Table: Employee +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | salary | int | | managerId | int | +-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.
Write a solution to find the employees who earn more than their managers.
Return the result table in any order.
예시
Input: Employee table: +----+-------+--------+-----------+ | id | name | salary | managerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | Null | | 4 | Max | 90000 | Null | +----+-------+--------+-----------+ Output: +----------+ | Employee | +----------+ | Joe | +----------+
Explanation: Joe is the only employee who earns more than his manager.
문제 풀이
1. merge해서 employee와 manager 매칭 (suffixes 사용 X -> x와 y 할당됨)
import pandas as pd def find_employees(employee: pd.DataFrame) -> pd.DataFrame: merged = pd.merge(employee, employee, left_on = 'managerId', right_on = 'id', how = 'left', suffixes=('_employee', '_manager')) return merged
| id_employee | name_employee | salary_employee | managerId_employee | id_manager | name_manager | salary_manager | managerId_manager | | ----------- | ------------- | --------------- | ------------------ | ---------- | ------------ | -------------- | ----------------- | | 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | null | | 2 | Henry | 80000 | 4 | 4 | Max | 90000 | null | | 3 | Sam | 60000 | null | null | null | null | null | | 4 | Max | 90000 | null | null | null | null | null |
2. manager가 없는 행 제외
import pandas as pd def find_employees(employee: pd.DataFrame) -> pd.DataFrame: merged = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left', suffixes=('_employee', '_manager')) filtered = merged[merged['id_manager'].notnull()] return filtered
| id_employee | name_employee | salary_employee | managerId_employee | id_manager | name_manager | salary_manager | managerId_manager | | ----------- | ------------- | --------------- | ------------------ | ---------- | ------------ | -------------- | ----------------- | | 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | null | | 2 | Henry | 80000 | 4 | 4 | Max | 90000 | null |
3. salary_employee가 salary_manager보다 높은 행 출력
import pandas as pd def find_employees(employee: pd.DataFrame) -> pd.DataFrame: merged = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left', suffixes=('_employee', '_manager')) filtered = merged[merged['id_manager'].notnull()] result = filtered[filtered['salary_employee'] > filtered['salary_manager']] return result
| id_employee | name_employee | salary_employee | managerId_employee | id_manager | name_manager | salary_manager | managerId_manager | | ----------- | ------------- | --------------- | ------------------ | ---------- | ------------ | -------------- | ----------------- | | 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | null |
4. 요구 조건에 맞추어 컬럼명 수정 후 출력
import pandas as pd def find_employees(employee: pd.DataFrame) -> pd.DataFrame: merged = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left', suffixes=('_employee', '_manager')) filtered = merged[merged['id_manager'].notnull()] result = filtered[filtered['salary_employee'] > filtered['salary_manager']] result = result.rename(columns={'name_employee': 'Employee', 'name_manager': 'Manager'}) return result[['Employee']]
| Employee | | -------- | | Joe |
* notnull, salary 조건 한 줄로
import pandas as pd def find_employees(employee: pd.DataFrame) -> pd.DataFrame: merged = pd.merge(employee, employee, left_on='managerId', right_on='id', how='left', suffixes=('_employee', '_manager')) filtered = merged[(merged['id_manager'].notnull()) & (merged['salary_employee'] > merged['salary_manager'])] result = filtered.rename(columns={'name_employee': 'Employee', 'name_manager': 'Manager'}) return result[['Employee']]
파이썬을 독학하시는 분들에게 도움이 되길 바라며,
혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.