Level 9 · Who is paid closest to their manager?

One table, two roles

employees stores the reporting line inside itself: manager_id holds the employee_id of the person’s manager. To see both people on one row, merge the table with itself. The left side plays the employee, the right side plays the manager:

bosses = employees[["employee_id", "first_name", "last_name", "salary"]]
pairs = employees.merge(bosses, left_on="manager_id", right_on="employee_id", suffixes=("", "_mgr"))

left_on and right_on are for keys with different names on each side. Because both sides have first_name, last_name, salary and employee_id, pandas needs suffixes to tell them apart. ("", "_mgr") leaves the employee’s columns unchanged and adds _mgr to the manager’s, which reads far better than the default _x and _y. Selecting only four manager columns first keeps the merged table narrow.

A float key meets an int key

manager_id is a float column (1.0, 3.0, …) while employee_id is an int. That’s because the CEO has no manager, and plain pandas can’t store a missing value in an int column, so the whole column became float with NaN for her. The merge still works: 3.0 matches 3. The CEO’s NaN matches nothing, so the inner merge drops her and 59 pairs remain. Use how="left" instead if you need everyone, managers or not, with NaN in the manager columns.

Building the answer

Text columns join with +, and numbers subtract column by column:

pairs["employee"] = pairs["first_name"] + " " + pairs["last_name"]
pairs["salary_gap"] = pairs["salary_mgr"] - pairs["salary"]
pairs.sort_values(["salary_gap", "employee_id"]).head(3)[["employee", "salary_gap"]]

The three closest are support specialists Helen Parker ($22,000 below her lead) and Eric Jimenez ($23,000), then Donna Vasquez, a Senior Software Engineer $25,000 below the Head of Engineering. Nobody earns more than their manager, which is a useful check in itself: a negative gap would be the first thing HR asks about.

Common mistakes

• Merging on="employee_id", which pairs each person with themselves.
• Keeping the default suffixes and then subtracting salary_x from salary_y the wrong way round.
• Expecting the CEO in the result: an inner merge drops rows whose key is NaN.
• Converting manager_id with astype(int) first, which fails on the NaN.