Lighthouse

Level 2 · Measure the return rate in pandas

From Angela Rivera, Head of Customer Support

“We agreed it: returned lines over sold lines for completed orders, by category, plus the refund value and the reasons. Before you split anything, show me how returns sit across order statuses, because I know the refunded orders get processed differently.”

Your goal

Step 1 of 3. Check where returns sit. For every order line, whatever its quantity, set result to a DataFrame with columns status, lines (the number of order lines), returned_lines (lines whose order_item_id appears in returns) and return_rate_pct (returned_lines * 100 / lines, rounded to 1), sorted by status, with a default index.

Use the orders DataFramenot done yet
Use the order_items DataFramenot done yet
Show exactly status lines returned_lines return_rate_pctnot done yet

Step 1 · your code

Python

Loading the editor…

Reference solution

ResultPreview of orders · press Run to see yours

Preview of orders
order_id	customer_id	order_date	status	channel
1001	125	2023-01-01 20:51:19	completed	web
1002	156	2023-01-02 07:58:46	completed	web
1003	22	2023-01-02 10:25:27	completed	marketplace
1004	48	2023-01-02 11:21:51	completed	marketplace

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 products · press Run to see yours

Preview of products
product_id	name	category	subcategory	unit_price
101	Vale Velvet Loveseat	Furniture	Sofas	1659.00
102	Alder Oak Coffee Table	Furniture	Tables	919.00
103	Orchard Rattan Dining Chair	Furniture	Chairs	789.00
104	Upland Oak Bookcase	Furniture	Storage	429.00

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 products · press Run to see yours

Preview of products
product_id	name	category	subcategory	unit_price
101	Vale Velvet Loveseat	Furniture	Sofas	1659.00
102	Alder Oak Coffee Table	Furniture	Tables	919.00
103	Orchard Rattan Dining Chair	Furniture	Chairs	789.00
104	Upland Oak Bookcase	Furniture	Storage	429.00

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.

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

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

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

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

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

Before you compute a rate, look at what the returns belong to. Every return row points to one order line, and every line belongs to an order with a status. If returns behave very differently by status, one rate for everything would hide that.

The quickest way to mark returned lines is isin, which gives one True or False per line:

lines["returned"] = lines["order_item_id"].isin(returns["order_item_id"])

Summing a boolean column counts the True values, so ("returned", "sum") in a named aggregation is the number of returned lines. The result of step 1 shows refunded orders at 96.6% and completed orders at 3.3%: two different processes, which is why the rest of the case uses completed orders only.

The rate and the money

Step 2 needs the refund amount too, so it merges returns with how="left". Every sold line stays; returned lines get their refund_amount, the others get NaN. Two aggregations then do the counting for you:

("order_item_id", "size") counts every line, the denominator;
("refund_amount", "count") counts non-missing values, the returned lines.

From the same table come the return rate, which tells Support where the work is, and the refund share, refunds divided by net sales, which tells Finance where the money goes. Compute both percentages from the unrounded sums and round last.

Reasons as columns

Step 3 starts from returns, since only returned lines have a reason. pd.crosstab counts every category and reason pair, and normalize="index" turns each row into shares of that row:

shares = pd.crosstab(r["category"], r["reason"], normalize="index") * 100

Each row then adds up to 100%, the pandas version of dividing by the category’s own COUNT(*).

Reading the result

category	return_rate_pct	refund_share_pct	top reason
Textiles	4.7	5.1	changed_mind 57.9%
Furniture	4.4	4.7	damaged 46.0%
Lighting	3.8	4.3	damaged 38.3%
Kitchen	2.0	2.5	changed_mind 40.6%

Two different problems show up. Textiles come back because customers change their minds: color, feel or size not as expected. Furniture and Lighting come back broken. Those need different fixes.

Pitfalls

merge is an inner join unless you say how="left"; with returns that silently drops every line that was not returned.
"count" skips NaN and "size" does not; mixing them up gives a rate of 100% or of 0%.
Some changed-mind refunds on Furniture and Outdoor are 15% below the line value: a restocking fee. Compare refunds with sales, not with returned line values, when you talk about money lost.

Level 2 · Measure the return rate in pandas

Your goal

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 Angela gets, and why

DataFrames

The lesson

What you are checking, and why

The rate and the money

Reasons as columns

Reading the result

Pitfalls

Lantern lit!

Level 2 · Measure the return rate in pandas

The request

Your goal

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 Angela gets, and why

DataFrames

The lesson

What you are checking, and why

The rate and the money

Reasons as columns

Reading the result

Pitfalls

Cookie choices