Count Student Number in Departments — LeetCode 580 Python Solution

The problem

Count how many students are enrolled in each department, including the departments that have no students at all, and sort the result by that count from largest to smallest, breaking ties by department name in alphabetical order. Student has student_id (int, primary key), student_name (varchar), gender (varchar) and dept_id (int) referencing a department; Department has dept_id (int, primary key) and dept_name (varchar). Return dept_name and student_number.

Example

Input

Department(dept_id, dept_name) = (1, 'Engineering'), (2, 'Science'), (3, 'Law'); Student(student_id, student_name, gender, dept_id) = (1, 'Ana', 'F', 1), (2, 'Ben', 'M', 1), (3, 'Cleo', 'F', 2), (4, 'Dev', 'M', 1)

Output

('Engineering', 3), ('Science', 1), ('Law', 0)

Explanation

Law keeps its row through the left join and counts 0 because it has no students.

Python solution

import pandas as pd

def count_students(student: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
    counts = student.groupby('dept_id')['student_id'].count().reset_index(name='student_number')
    res = department.merge(counts, on='dept_id', how='left')
    res['student_number'] = res['student_number'].fillna(0).astype(int)
    return res[['dept_name', 'student_number']]

Complexity

Related problems