Students Report By Geography — LeetCode 618 Python Solution

The problem

Pivot the students into three columns named America, Asia and Europe, each column listing that continent's students in alphabetical order. The rows line up by position, and a continent that runs out of students is padded with null. The Student table has one row per student and may contain duplicate rows: name (varchar) and continent (varchar, one of America, Asia or Europe).

Example

Student table:

| name  | continent |
| ----- | --------- |
| Diego | America   |
| Hana  | Asia      |
| Ines  | Europe    |
| Mateo | America   |
| Yuki  | Asia      |

Result:

| America | Asia | Europe |
| ------- | ---- | ------ |
| Diego   | Hana | Ines   |
| Mateo   | Yuki | null   |

Each continent is sorted on its own and then written down its column, so the second row pads Europe with null because it has only one student.

Python solution

import pandas as pd

def students_report_by_geography(student: pd.DataFrame) -> pd.DataFrame:
    df = student.sort_values('name').copy()
    df['rn'] = df.groupby('continent').cumcount()
    wide = df.pivot(index='rn', columns='continent', values='name')
    for continent in ('America', 'Asia', 'Europe'):
        if continent not in wide:
            wide[continent] = None
    res = wide[['America', 'Asia', 'Europe']].rename_axis(None, axis=1)
    return res.reset_index(drop=True)

Complexity

Related problems