176. Second Highest Salary

문제 : 표에서 두 번째로 높은 고유한 급여를 찾는 솔루션을 작성해라 employee.
두 번째로 높은 급여가 없으면 null(return None in Pandas) 을 반환한다.

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.

 

Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

접근법
1. 높은 급여, 낮은 급여로 말하기에 이를 내림차순, 오름차순인 정렬로 접근한다.
2. 고유해야하므로 데이터의 중복이 있을시, 첫줄을 남기고 제거한다.
3. 만약 중복 제거 이후 데이터가 한줄만 남았다면, 결과는 NaN이 나와야한다.

중복 제거 : drop_duplicates
고유한다(disctinct) : unique()
데이터 프레임 생성 :pd.DataFrame({'SecondHighestSalary' : [np.NaN]})
열 제거 : df.drop()
머리 행 출력 : head()
꼬리 행 출력 : tail()

답:

import pandas as pd

def second_highest_salary(employee: pd.DataFrame) -> pd.DataFrame:
    df = employee.drop_duplicates('salary', keep = 'first')
    if len(df['salary'].unique()) < 2 :
        return pd.DataFrame({'SecondHighestSalary' : [np.NaN]})
    df = df.sort_values('salary', ascending = False).rename({'salary':'SecondHighestSalary'}, axis=1)
    df.drop("id", axis = 1, inplace = True)
    return df.head(2).tail(1)

 

rename의 axis 역할 : 변화시킬 열 이름 지정