Prepare a clean handoff · Data Playground

The question

Prepare a compact café billing extract while preserving the incoming evidence.

df Billing handoff

Your inputs

`df`

Repeated order_id values are confirmed export duplicates. Unknown prices need repair and cannot be zero-filled. Keep df unchanged.

Field meanings and units

order_id: Order or order-line identifier, as defined by this brief.
drink: Recorded drink label.
size: Recorded serving size.
price: Price in dollars; unparseable text needs the stated policy.
tip: Tip in dollars.
shift: AM or PM service period.
date: Calendar date supplied as YYYY-MM-DD text unless the task states it is invalid.

`df` · first 5 of 16 rows
`order_id`	`drink`	`size`	`price`	`tip`	`shift`	`date`
C007	LATTE	Small	4.5	0.5	AM	2026-04-04
C001	" Latte "	Small	4.5	0	AM	2026-04-01
C004	" Mocha"	Large	5.2	None	AM	2026-04-02
C002	tea	Large	2.8	1	PM	2026-04-01
C003	LATTE	Small	4.5	0.5	AM	2026-04-02

View all 16 rows

`df` · complete input
`order_id`	`drink`	`size`	`price`	`tip`	`shift`	`date`
C007	LATTE	Small	4.5	0.5	AM	2026-04-04
C001	" Latte "	Small	4.5	0	AM	2026-04-01
C004	" Mocha"	Large	5.2	None	AM	2026-04-02
C002	tea	Large	2.8	1	PM	2026-04-01
C003	LATTE	Small	4.5	0.5	AM	2026-04-02
C005	" Latte "	Small	invalid	0	PM	2026-04-03
C006	tea	Large	2.8	1	AM	2026-04-03
C008	" Mocha"	Large	None	None	PM	2026-04-04
C009	" Latte "	Small	4.5	0	AM	2026-04-05
C010	tea	Large	2.8	1	AM	2026-04-05
C011	LATTE	Small	4.5	0.5	PM	2026-04-06
C012	" Mocha"	Large	5.2	None	AM	2026-04-06
C013	" Latte "	Small	4.5	0	AM	2026-04-07
C014	tea	Large	2.8	1	PM	2026-04-07
C004	" Mocha"	Large	5.2	None	AM	2026-04-02
C016	" Mocha"	Large	5.2	None	AM	2026-04-08

The editable setup in Your Python creates these inputs when you run it.

Your task · Prepare a clean handoff

Prepare a compact café billing extract while preserving the incoming evidence.

cleanDataFrameremoved_countnumber

Deliverables

Make the outputs listed below. Use each name exactly as shown. Then choose Check answer.

Python name key

Teal outlines name table columns. Violet left rules name Python objects. Dotted rules mark syntax, functions and parameters. Use each name exactly as shown.

Words in this task

numeric: a number, rather than text

Billing handoffOutputcleanDataFrame
1. Create clean after keeping the first row per order_id.
2. Trim and lowercase drink; parse price as numeric and exclude unknown prices.
3. Keep only order_id, drink and price.
Excluded source rowsOutputremoved_countnumber
1. Store len(df) minus len(clean) in removed_count, including duplicate copies and unusable-price rows.

Data policy Keep all supplied source tables unchanged. Work on separate outputs.

Help Hints & explained solution

Choose the amount of help you need. Opening one hint does not reveal the others.

Hint 1 — Think

Which losses are intentional policy decisions rather than accidental data loss?

Hint 2 — Tools

drop_duplicates(), str.strip(), str.lower(), to_numeric() and dropna()

Revisit: Remove duplicates · Clean text · Convert numeric text · Drop missing rows · Sort and reset

Hint 3 — Approach

Remove confirmed extra copies, standardise labels, validate prices and prepare only the requested handoff fields.

Explained solution

Add these lines below # Your work in the editor. The supplied input setup is already there.

clean = df.drop_duplicates(subset=['order_id'], keep='first').copy()
clean['drink'] = clean.drink.str.strip().str.lower()
clean['price'] = pd.to_numeric(clean.price, errors='coerce')
clean = clean.dropna(subset=['price'])[['order_id', 'drink', 'price']]
removed_count = len(df) - len(clean)
clean

View supplied setup

This code is already at the top of the editor.

import pandas as pd

# Supplied input: df (16 rows)
df = pd.DataFrame(
    [
        ('C007', 'LATTE', 'Small', 4.5, 0.5, 'AM', '2026-04-04'),
        ('C001', ' Latte ', 'Small', 4.5, 0, 'AM', '2026-04-01'),
        ('C004', ' Mocha', 'Large', 5.2, None, 'AM', '2026-04-02'),
        ('C002', 'tea', 'Large', 2.8, 1, 'PM', '2026-04-01'),
        ('C003', 'LATTE', 'Small', 4.5, 0.5, 'AM', '2026-04-02'),
        ('C005', ' Latte ', 'Small', 'invalid', 0, 'PM', '2026-04-03'),
        ('C006', 'tea', 'Large', 2.8, 1, 'AM', '2026-04-03'),
        ('C008', ' Mocha', 'Large', None, None, 'PM', '2026-04-04'),
        ('C009', ' Latte ', 'Small', 4.5, 0, 'AM', '2026-04-05'),
        ('C010', 'tea', 'Large', 2.8, 1, 'AM', '2026-04-05'),
        ('C011', 'LATTE', 'Small', 4.5, 0.5, 'PM', '2026-04-06'),
        ('C012', ' Mocha', 'Large', 5.2, None, 'AM', '2026-04-06'),
        ('C013', ' Latte ', 'Small', 4.5, 0, 'AM', '2026-04-07'),
        ('C014', 'tea', 'Large', 2.8, 1, 'PM', '2026-04-07'),
        ('C004', ' Mocha', 'Large', 5.2, None, 'AM', '2026-04-02'),
        ('C016', ' Mocha', 'Large', 5.2, None, 'AM', '2026-04-08'),
    ],
    columns=['order_id', 'drink', 'size', 'price', 'tip', 'shift', 'date'],
)

Why these steps exist

The confirmed duplicate policy is applied before validating retained prices, so later copies cannot silently replace the first record.
The final extract includes only billing fields; the original table remains available for investigating excluded records.

Another valid approach

A sequence of explicit intermediate copies or a readable chain is valid. Keep the duplicate policy ahead of eligibility filtering.