Lighthouse

Level 2 · Check and summarise headcount and pay in pandas

From Laura Bennett, Head of Finance

“We agreed the plan: headcount, payroll and typical pay by department, then direct reports per manager. The HR export has been hand-edited over the years, so first tell me whether it holds together: anyone listed twice, managers who aren't on the list, salaries that look wrong.”

Your goal

Step 1 of 3. Check the table. Merge employees with a copy of its own employee_id column (renamed mgr_id) using how='left', left_on='manager_id', right_on='mgr_id' and indicator=True. Set result to a one-row DataFrame with employees (rows), distinct_ids, distinct_names (distinct first_name + ' ' + last_name), no_manager (manager_id is missing), unknown_manager (manager_id present but no match) and min_salary and max_salary.

Use the employees DataFramenot done yet
Show exactly employees distinct_ids distinct_names no_manager unknown_manager min_salary max_salarynot done yet
1 row, sorted as askednot done yet

Step 1 · your code

Python

Loading the editor…

Reference solution

ResultPreview of employees · press Run to see yours

Preview of employees
employee_id	first_name	last_name	department	title
1	Catherine	Hale	Executive	Chief Executive Officer
2	Robert	Kim	Executive	Chief Operating Officer
3	Megan	Collins	Sales	Head of Sales
4	Chloe	Gutierrez	Sales	Account Executive

Mote checks your result

Press Run. Each row that matches the goal becomes a firefly in your lantern.

Step 2 · your code

Python

Loading the editor…

Reference solution

ResultPreview of employees · press Run to see yours

Preview of employees
employee_id	first_name	last_name	department	title
1	Catherine	Hale	Executive	Chief Executive Officer
2	Robert	Kim	Executive	Chief Operating Officer
3	Megan	Collins	Sales	Head of Sales
4	Chloe	Gutierrez	Sales	Account Executive

Mote checks your result

Press Run. Each row that matches the goal becomes a firefly in your lantern.

Step 3 · your code

Python

Loading the editor…

Reference solution

ResultPreview of employees · press Run to see yours

Preview of employees
employee_id	first_name	last_name	department	title
1	Catherine	Hale	Executive	Chief Executive Officer
2	Robert	Kim	Executive	Chief Operating Officer
3	Megan	Collins	Sales	Head of Sales
4	Chloe	Gutierrez	Sales	Account Executive

Mote checks your result

Press Run. Each row that matches the goal becomes a firefly in your lantern.

DataFrames

Loaded for you as pandas DataFrames, plus pd and np.

employees60 rows · 8 columns

employee_idinteger
first_namevarchar
last_namevarchar
departmentvarchar
titlevarchar
manager_idinteger
hire_datedate
salaryinteger

First 5 rows of employees
employee_id	first_name	last_name	department	title	manager_id	hire_date	salary
1	Catherine	Hale	Executive	Chief Executive Officer	NaN	2016-03-01	285000
2	Robert	Kim	Executive	Chief Operating Officer	1	2017-01-09	235000
3	Megan	Collins	Sales	Head of Sales	1	2018-04-16	172000
4	Chloe	Gutierrez	Sales	Account Executive	3	2020-01-21	72000
5	Frank	Bell	Sales	Account Executive	3	2023-05-21	73000

customers2,018 rows · 9 columns

customer_idinteger
first_namevarchar
last_namevarchar
emailvarchar
cityvarchar
statevarchar
signup_datedate
segmentvarchar
referral_sourcevarchar

First 5 rows of customers
customer_id	first_name	last_name	email	city	state	signup_date	segment	referral_source
1	Owen	Hudson	owen.hudson@icloud.com	San Francisco	CA	2022-06-02	consumer	search
2	Douglas	Mendoza	dmendoza69@gmail.com	Atlanta	GA	2022-06-02	consumer	NaN
3	Henry	Brooks	hbrooks@lakesidecafe.com	Los Angeles	CA	2022-06-04	business	NaN
4	Brian	Graham	bgraham@bluecedarhomes.com	Seattle	WA	2022-06-06	business	pinterest
5	Kyle	Jimenez	kjimenez27@yahoo.com	Indianapolis	IN	2022-06-07	consumer	NaN

products120 rows · 8 columns

product_idinteger
namevarchar
categoryvarchar
subcategoryvarchar
unit_pricedecimal
unit_costdecimal
launched_ondate
is_activeboolean

First 5 rows of products
product_id	name	category	subcategory	unit_price	unit_cost	launched_on	is_active
101	Vale Velvet Loveseat	Furniture	Sofas	1659.00	NaN	2023-09-11	true
102	Alder Oak Coffee Table	Furniture	Tables	919.00	503.50	2024-05-16	true
103	Orchard Rattan Dining Chair	Furniture	Chairs	789.00	429.87	2023-05-27	true
104	Upland Oak Bookcase	Furniture	Storage	429.00	214.70	2022-12-20	true
105	Avery Leather Bed Frame	Furniture	Beds	449.00	232.70	2021-08-31	true

orders12,000 rows · 7 columns

order_idinteger
customer_idinteger
order_datetimestamp
statusvarchar
channelvarchar
shipping_feedecimal
coupon_codevarchar

First 5 rows of orders
order_id	customer_id	order_date	status	channel	shipping_fee	coupon_code
1001	125	2023-01-01 20:51:19	completed	web	0.00	NaN
1002	156	2023-01-02 07:58:46	completed	web	0.00	NaN
1003	22	2023-01-02 10:25:27	completed	marketplace	4.99	WELCOME10
1004	48	2023-01-02 11:21:51	completed	marketplace	4.99	WELCOME10
1005	82	2023-01-02 11:43:36	completed	web	0.00	WELCOME10

order_items30,338 rows · 6 columns

order_item_idinteger
order_idinteger
product_idinteger
quantityinteger
unit_pricedecimal
discount_pctdecimal

First 5 rows of order_items
order_item_id	order_id	product_id	quantity	unit_price
1	1001	121	9	31.99
2	1001	154	3	11.99
3	1002	154	4	11.99
4	1002	194	1	149.00
5	1002	193	2	19.99

returns1,673 rows · 5 columns

return_idinteger
order_item_idinteger
returned_ondate
reasonvarchar
refund_amountdecimal

First 5 rows of returns
return_id	order_item_id	returned_on	reason	refund_amount
1	14	2023-01-10	damaged	83.69
2	42	2023-01-10	wrong_item	19.99
3	105	2023-01-14	wrong_item	10.99
4	24	2023-01-16	changed_mind	26.99
5	126	2023-01-16	late	20.99

web_sessions40,000 rows · 7 columns

session_idinteger
customer_idinteger
started_attimestamp
devicevarchar
sourcevarchar
pages_viewedinteger
convertedboolean

First 5 rows of web_sessions
session_id	customer_id	started_at	device	source	pages_viewed	converted
1	NaN	2023-01-01 23:02:24	mobile	paid	1	false
2	18	2023-01-01 15:07:49	desktop	paid	1	false
3	NaN	2023-01-01 16:55:46	desktop	direct	7	false
4	NaN	2023-01-01 12:43:28	mobile	organic	3	false
5	NaN	2023-01-01 13:06:21	mobile	social	2	false

marketing_spend3,276 rows · 3 columns

spend_datedate
channelvarchar
amountdecimal

First 5 rows of marketing_spend
spend_date	channel	amount
2023-01-01	paid	492.99
2023-01-01	email	37.38
2023-01-01	social	200.53
2023-01-02	paid	709.13
2023-01-02	email	64.13

The lesson

What you are checking, and why

An HR export looks tidy, but it is often edited by hand: people are re-entered after a role change, managers leave and their reports keep pointing at them. Before you total anything, check three things.

Is anyone listed twice? Ids are unique by design, so count distinct names as well.
Does every reporting line lead somewhere? A manager_id that matches no employee_id is an orphan. Only the CEO should have no manager at all.
Are the salaries plausible? min() and max() catch a monthly figure typed as yearly, or a missing zero.

The reporting check needs a self-merge: the table merged with a copy of itself, one side for the employee and one for the manager. how="left" keeps every employee, and indicator=True adds a _merge column that says whether a manager row was found:

checked = e.merge(managers, how="left", left_on="manager_id", right_on="mgr_id", indicator=True)
checked["_merge"].value_counts()   # both 59, left_only 1

The one left_only row is the CEO, whose manager_id is missing. An orphan would be a left_only row that does have a manager_id. Here there are none.

Headcount, payroll, mean and median

Step 2 is one groupby with named aggregation. The interesting pair is mean and median. The mean adds every salary and divides; the median is the middle salary when they are sorted. In a department of seven where one person is the head, the head’s salary moves the mean a lot and the median hardly at all:

department	headcount	mean_salary	median_salary
Engineering	8	149,750	139,500
Data & Analytics	7	108,000	92,000
Sales	9	84,000	73,000
Customer Support	9	59,333	50,000

For “what does a typical person here earn”, report the median. Payroll is still the sum: that is what the budget pays.

Span of control

Span of control is the number of people who report directly to a manager. It is a common input to org design: too narrow and the company is top-heavy, too wide and managers cannot keep up. With an inner merge, every row is one reporting line, so counting rows per manager gives the span. Keep only the columns you need on the left side, or the two title columns collide.

manager_id is a float column because of the CEO’s missing value. Merging it with the integer mgr_id still matches 3.0 with 3; pandas compares the values, not the types.

The spans here run from 2 (Anna Chen, Sales Manager) to 7. Support’s two managers have 5 and 3.

Pitfalls

An inner merge in the check hides the very rows you are looking for.
Grouping by name merges two managers who share one; group by id in a big company.
Department means of 2 or 5 people can reveal individual salaries. Keep them out of reports that go beyond Finance and HR.

Level 2 · Check and summarise headcount and pay in pandas

Your goal

This case unlocks when you finish any one of:

Step 1 · your code

Reference solution

Mote checks your result

Step 2 · your code

Reference solution

Mote checks your result

Step 3 · your code

Reference solution

Mote checks your result

What Laura gets, and why

DataFrames

The lesson

What you are checking, and why

Headcount, payroll, mean and median

Span of control

Pitfalls

Lantern lit!

Level 2 · Check and summarise headcount and pay in pandas

The request

Your goal

This case unlocks when you finish any one of:

Step 1 · your code

Reference solution

Mote checks your result

Step 2 · your code

Reference solution

Mote checks your result

Step 3 · your code

Reference solution

Mote checks your result

What Laura gets, and why

DataFrames

The lesson

What you are checking, and why

Headcount, payroll, mean and median

Span of control

Pitfalls

Cookie choices