Flatten nested JSON records

Pandas data engineering interview problem. Difficulty: intermediate. Pattern: JSON. About 16 minutes. Part of the Pro drill bank.

Turn a list of nested order dicts into 1 row per line item with the order fields repeated. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

JSON data often arrives as nested dictionaries. Here, orders is a Python list of dicts. Each order has an order_id, a nested customer dict (with id and tier), and a list items of line items, each with sku, qty and price. Build a table with 1 row for each line item, and repeat the order fields on each row. The columns are order_id, customer_id, tier, sku, qty and price. An order with no items produces no rows. Sort by order_id and then sku, and reset the index. The editor builds orders (a list, not a DataFrame) and ends with result = pd.DataFrame(orders). Change the last line. pd.json_normalize with record_path and meta is made for this.

Requirements

• Use pd.json_normalize with record_path="items" to get 1 row for each line item.
• Bring the order fields in with meta, including the nested customer fields.
• Rename the columns to customer_id and tier, and select the 6 columns in order.
• Sort by order_id, sku, reset the index and store the DataFrame in result.

Constraints

• An order with an empty items list gives no rows.
• Nested fields are named with a dot after normalizing: customer.id, customer.tier.
• The final column order is order_id, customer_id, tier, sku, qty, price.

Examples

Input: orders = [ {"order_id": 1, "customer": {"id": "c1", "tier": "gold"}, "items": [{"sku": "B", "qty": 1, "price": 9.5}, {"sku": "A", "qty": 2, "price": 5.0}]}, {"order_id": 2, "customer": {"id": "c2", "tier": "basic"}, "items": []}, {"order_id": 3, "customer": {"id": "c1", "tier": "gold"}, "items": [{"sku": "C", "qty": 4, "price": 1.25}]}, ] Output: order_id | customer_id | tier | sku | qty | price 1 | c1 | gold | A | 2 | 5 1 | c1 | gold | B | 1 | 9.5 3 | c1 | gold | C | 4 | 1.25 Order 1 has 2 items, order 2 has none and disappears, order 3 has one: 3 rows, each repeating its order and customer fields.

Topics: lakebench, pandas, json, nested, line items.

More interview problems · All interview problems · Learn data engineering