Part II

Nth Highest Salary (去重, 排序, iloc索引)

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id 是该表的主键（具有唯一值的列）
该表的每一行都包含雇员的工资信息

编写一个解决方案，从雇员表中找出第n个最高工资。如果没有第n 位最高薪金，则返回null。
Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+
Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+
import pandas as pd

def nth_highest_salary(employee: pd.DataFrame, N: int) -> pd.DataFrame:
    # 去除工资中的重复值，然后按降序排列。
    # 使用 drop_duplicates 方法去重，并使用 sort_values 方法排序
    unique_salaries = employee['salary'].drop_duplicates().sort_values(ascending=False)
 
    if 0 < N <= len(unique_salaries):
        # 从排序后的工资中获取第 N 个最高工资
        result_df = unique_salaries.iloc[N - 1]
        return pd.DataFrame({f'getNthHighestSalary({N})': [result_df]})
    else:
        # 如果 N 超过了唯一薪资的数量，则返回 “None”。
        return pd.DataFrame({f'getNthHighestSalary({N})': [None]})
        

# .iloc 是 pandas 中用于基于 整数位置选择数据的索引方法。
# dataframe.iloc[行索引, 列索引]
# 行索引：要选择的行的整数位置（从 0 开始）。
# 列索引：要选择的列的整数位置（从 0 开始）。
Second Highest Salary (条件判断, iloc索引)
+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
# id 是该表的主键（具有唯一值的列）
# 该表的每一行都包含雇员的工资信息。

# 编写一个解决方案，从雇员表中找出第二高的不同工资。
# 如果没有第二高薪，则返回null（在 Pandas 中返回 None）

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                |
+---------------------+
import pandas as pd

def second_highest_salary(employee: pd.DataFrame) -> pd.DataFrame:

    unique_salaries = employee['salary'].drop_duplicates().sort_values(ascending=True)
    # nlargest(2)：获取 unique_salaries 中的前两个最大值
    # iloc[-1]：选择 nlargest(2) 的最后一个元素（即第二大的工资）
    second_highest = unique_salaries.nlargest(2).iloc[-1] if len(unique_salaries) >=2 else None

    if second_highest is None:
        return pd.DataFrame({'SecondHighestSalary': [None]})
    else:
        return pd.DataFrame({'SecondHighestSalary': [second_highest]})
Department Highest Salary - 最高薪酬部门 (lambda, merge合并, groupby分组)
Table: Employee
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
# id 是该表的主键（具有唯一值的列）。
# departmentId 是部门表中 ID 的外键（引用列）。
# 该表的每一行都显示了员工的 ID、姓名和工资。它还包含其部门的 ID。
Table: Department
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
# id 是该表的主键（具有唯一值的列）。保证部门名称不是空值。
# 该表的每一行都表示一个部门的 ID 及其名称。
Example 1:

Input: 
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 |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+
import pandas as pd

def department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
    if employee.empty or department.empty:
        return pd.DataFrame(columns=['Department','Employee', 'Salary'])
    
    # 在 “departmentId ”和 “id ”列上合并雇员和部门数据框
    merged_df = employee.merge(department, left_on='departmentId', right_on='id', suffixes=('_employee', '_department'))
    
    # 使用 groupby 按 “departmentId ”对数据分组，并应用 lambda 函数获取每个组中薪水最高的员工,删除重复的 “departmentId ”列并重置索引
    highest_salary_df = merged_df.groupby('departmentId').apply(lambda x: x[x['salary'] == x['salary'].max()]).reset_index(drop=True)
    
    # 按照id_employee列排序
    highest_salary_df = highest_salary_df.sort_values(by='id_employee', ascending=True)
    
    # 选择所需列并返回结果
    result_df = highest_salary_df[['name_department', 'name_employee', 'salary']]
    
    # 重新命名指定的列
    result_df.columns = ['Department','Employee', 'Salary']
    
    return result_df
Rank Scores - 排名得分 (rank排序)
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
# id 是该表的主键（具有唯一值的列）
# 该表的每一行都包含一场比赛的得分。得分是一个浮点数值，有两位小数

# 请写出求得分排名的解法。排名的计算应遵循以下规则：
# 分数应从高到低排列
# 如果两个分数相同，则两个分数的排名应相同
# 出现平局后，下一个排名编号应是下一个连续的整数值。换句话说，排名之间不应有空洞
# 按分数降序返回结果表

Example 1:

Input: 
Scores table:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
Output: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+
import pandas as pd

def order_scores(scores: pd.DataFrame) -> pd.DataFrame:
    # 使用 'rank' 方法按分数降序计算排名
    # method='dense' 确保相同的分数有相同的排名，并且排名是连续的整数
    # ascending=False 表示按分数从高到低排序    
    scores['rank'] = scores['score'].rank(method='dense', ascending=False)
    # 创建一个只包含 'score' 和 'rank' 两列的 DataFrame
    # 使用 sort_values 按 'score' 降序排列，确保输出顺序正确
    # reset_index(drop=True) 重置索引并丢弃旧索引
    result_df = scores[['score','rank']].sort_values(by='score', ascending=False).reset_index(drop=True)

    return result_df
Delete Duplicate Email (去重, 排序)
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
# id 是该表的主键（具有唯一值的列）
# 该表的每一行都包含一封电子邮件。电子邮件不包含大写字母

# 编写一个解决方案， 删除所有重复的电子邮件，只保留一个具有最小ID 的唯一电子邮件
Example 1:

Input: 
Person table:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Output: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
import pandas as pd

def delete_duplicate_emails(person: pd.DataFrame) -> None:
    
    person.sort_values(by='id', ascending=True, inplace=True)
    # subset 指定用于识别重复值的列
    # keep 指定在重复项中保留哪一行 
    # inplace 是否在原地修改 DataFrame
    person.drop_duplicates(subset='email', keep='first', inplace=True)
Rearrange Products Table - 重新排列产品表 (melt转换长格式)
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
# product_id 是该表的主键（具有唯一值的列）。
# 表中的每一行表示产品在 3 个不同商店的价格：store 1、store 2 和store 3。
# 如果某个商店没有该产品，则该商店列中的价格为空。

# 编写解决方案，重新排列 "产品 "表，使每一行都包含（产品 ID、商店、价格）。
# 如果某商店没有该产品，则不在结果表中包含该产品_id和商店组合的行。
Example 1:

Input: 
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
Output: 
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
import pandas as pd

def rearrange_products_table(products: pd.DataFrame) -> pd.DataFrame:
    # 使用 melt 将数据转换为长格式; 使用dropna删除空字符; 使用sort排序
    # id_vars=['product_id']：指定 product_id 列保持不变。
    # value_vars=['store1', 'store2', 'store3']：指定需要转换的列。
    # var_name='store'：将列名存放在 store 列中。
    # value_name='price'：将数据值存放在 price 列中。
    products_df = pd.melt(products, id_vars=['product_id'],  value_vars=['store1', 'store2', 'store3'], var_name='store', value_name='price').dropna(how='any', subset='price').sort_values(by='product_id', ascending=True)

    return products_df
Count Salary Categories - 薪金类别计数 (apply, groupby分组, size记录组数量)
+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
# account_id 是该表的主键（具有唯一值的列）。
# 每一行都包含一个银行账户的月收入信息

# 请写出计算每个工资类别的银行账户数的解法。工资类别是
# "Low Salary"：所有工资严格低于 20000 美元
# "Average Salary"：包含范围[$20000, $50000]内的所有工资。
# "High Salary"：所有工资严格高于 50000 美元
# 结果表必须包含所有三个类别。如果某个类别中没有账户，则返回0

Example 1:

Input: 
Accounts table:
+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+
Output: 
+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+
import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
    # 使用 apply 函数将类别添加到新的列中
    accounts['category'] = accounts['income'].apply(categorize_income)
    
    # groupby('category')：按 'category' 列对 DataFrame 进行分组。
    # size()：计算每组的大小，即每个类别的账户数量。
    # reset_index(name='accounts_count')：将分组结果转换为 DataFrame，并将大小列重命名为 'accounts_count'。
    result_df = accounts.groupby('category').size().reset_index(name='accounts_count') 
    
    # 确保所有类别都在结果中apply
    categories = ['Low Salary', 'Average Salary', 'High Salary']

    # set_index('category')：将 'category' 列设置为索引，以便重新索引。
    # reindex(categories, fill_value=0)：使用定义的所有类别重新索引，如果某类别在结果中没有出现，则填充为 0。
    # reset_index()：将索引重置为默认的整数索引，恢复 DataFrame 的标准形式。
    result_df = result_df.set_index('category').reindex(categories, fill_value=0).reset_index() 
    
    return result_df

# 定义一个函数来根据收入分类
def categorize_income(income):
    if income < 20000:
        return 'Low Salary'
    elif 20000 <= income <= 50000:
        return 'Average Salary'
    else:
        return 'High Salary'
Find Total Time Spent by Each Employee (分组, 总数聚合)
+-------------+------+
| Column Name | Type |
+-------------+------+
| emp_id      | int  |
| event_day   | date |
| in_time     | int  |
| out_time    | int  |
+-------------+------+

# (emp_id，event_day，in_time）是该表的主键（具有唯一值的列组合）
# 该表显示员工在办公室的进出情况
# event_day 是事件发生的日期，in_time 是员工进入办公室的时间，out_time 是员工离开办公室的时间
# in_time 和 out_time 介于 1 和 1440 之间
# 保证同一天内没有两个事件在时间上相交，且 in_time < out_time

# 请写出一个解决方案，计算每位员工每天在办公室花费的总时间（分钟）。
# 请注意，在一天之内，一名员工可以多次进入和离开办公室。
# 单次进入办公室所花费的时间为out_time - in_time

Example 1:

Input: 
Employees table:
+--------+------------+---------+----------+
| emp_id | event_day  | in_time | out_time |
+--------+------------+---------+----------+
| 1      | 2020-11-28 | 4       | 32       |
| 1      | 2020-11-28 | 55      | 200      |
| 1      | 2020-12-03 | 1       | 42       |
| 2      | 2020-11-28 | 3       | 33       |
| 2      | 2020-12-09 | 47      | 74       |
+--------+------------+---------+----------+
Output: 
+------------+--------+------------+
| day        | emp_id | total_time |
+------------+--------+------------+
| 2020-11-28 | 1      | 173        |
| 2020-11-28 | 2      | 30         |
| 2020-12-03 | 1      | 41         |
| 2020-12-09 | 2      | 27         |
+------------+--------+------------+
import pandas as pd

def total_time(employees: pd.DataFrame) -> pd.DataFrame:
    # 计算时间差
    employees['total_spent'] = employees['out_time'] - employees['in_time']
    # 按 emp_id 和 event_day 分组并计算总时间
    # .sum() 一个聚合函数，用于计算每个组内 total_spent 列的总和
    # as_index=False: 一个布尔参数，指定在输出结果中是否将分组列设置为索引
    result_df = employees.groupby(['event_day', 'emp_id'], as_index=False)['total_spent'].sum()
    # 重命名列
    result_df = result_df.rename(columns={'event_day': 'day'})
    
    return result_df
Game Play Analysis I (分组, 最小聚合)
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
# (player_id，event_date）是该表的主键（具有唯一值的列组合）。
# 此表显示某些游戏的玩家活动。
# 每一行都是一个玩家的记录，他登录并玩了若干游戏（可能为 0）
# 然后在某天使用某种设备注销。

# 写出求解方案，找出每个玩家的首次登录日期
Example 1:

Input: 
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
Output: 
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+
import pandas as pd

def game_analysis(activity: pd.DataFrame) -> pd.DataFrame:

    activity['event_date'] = pd.to_datetime(activity['event_date'])

    activity_df = activity.groupby('player_id',  as_index=False)['event_date'].min()

    activity_df.rename(columns={'event_date': 'first_login'},inplace=True)
    
    return activity_df
Number of Unique Subjacts Taught by Each Teacher (nunique()方法)
+-------------+------+
| Column Name | Type |
+-------------+------+
| teacher_id  | int  |
| subject_id  | int  |
| dept_id     | int  |
+-------------+------+
# (subject_id，dept_id）是该表的主键（具有唯一值的列组合）
# 表中的每一行表示 teacher_id 的教师在系部 dept_id 中教授 subject_id 的科目

# 请写出计算大学中每位教师教授的独特科目数的解法
Example 1:

Input: 
Teacher table:
+------------+------------+---------+
| teacher_id | subject_id | dept_id |
+------------+------------+---------+
| 1          | 2          | 3       |
| 1          | 2          | 4       |
| 1          | 3          | 3       |
| 2          | 1          | 1       |
| 2          | 2          | 1       |
| 2          | 3          | 1       |
| 2          | 4          | 1       |
+------------+------------+---------+
Output:  
+------------+-----+
| teacher_id | cnt |
+------------+-----+
| 1          | 2   |
| 2          | 4   |
+------------+-----+
import pandas as pd

def count_unique_subjects(teacher: pd.DataFrame) -> pd.DataFrame:
    
    teacher_df = teacher.drop_duplicates(['teacher_id', 'subject_id'], keep='first')
    # nunique()用于计算DataFrame或Series中独特（唯一）值的数量
    result_df = teacher_df.groupby('teacher_id')['subject_id'].nunique().reset_index().rename(columns={'subject_id': 'cnt'})

    return result_df
PreviousPart INextPart III

Last updated

Was this helpful?