PY 09: Load into pandas

Track: Code (Python client + raw REST) Prerequisite: PY_08, and a working pandas install.

The task

Turn a client.query(...) result into a pandas DataFrame, then recompute pricing power and sell-through with pandas instead of plain Python loops.

Starter steps

Fetch rows for one designer, covering both sold and unsold lots, with the fields needed for both metrics:

import pandas as pd

rows = client.query(
    "all_watches_data",
    fields=["designer", "status", "usd_price_decimal", "sale_estimates_high_usd_price", "sale_date"],
    filters=[{"field": "designer", "op": "eq", "value": "Van Cleef & Arpels"}],
    limit=1000,
)
df = pd.DataFrame(rows)
print(df.shape)
print(df.head())

Compute sell-through with pandas:

sell_through_rate = (df["status"] == "sold").mean()
print(round(sell_through_rate, 3))

Compute pricing power with pandas, restricting to sold lots with a positive high estimate first:

sold = df[(df["status"] == "sold") & (df["sale_estimates_high_usd_price"] > 0)].copy()
sold["price_ratio"] = sold["usd_price_decimal"] / sold["sale_estimates_high_usd_price"]
pricing_power_median = sold["price_ratio"].median()
print(round(pricing_power_median, 2))

Bucket sold lots by quarter with pandas, mirroring PY_06 but with fewer lines:

sold["sale_date"] = pd.to_datetime(sold["sale_date"])
sold["sale_quarter"] = sold["sale_date"].dt.to_period("Q")
quarterly = sold.groupby("sale_quarter").agg(
    lots_sold=("price_ratio", "count"),
    median_price_usd=("usd_price_decimal", "median"),
)
print(quarterly)

Expected result

df is a pandas DataFrame with one row per lot and columns matching the requested fields. sell_through_rate is a float between 0 and 1. pricing_power_median is a float typically in the range of roughly 0.5 to 1.5, with a strong brand like Van Cleef & Arpels tending above 1.0. quarterly is a small DataFrame indexed by quarter with lots_sold and median_price_usd columns; as in PY_06, treat the most recent one to two quarters as provisional and under-ingested rather than as evidence of a real recent decline.

Stretch challenge

Use df.groupby("designer") on a DataFrame built from a stock_ticker eq filter (as in PY_07) to compute pricing power per brand within a single listed parent group in one pandas pipeline, without writing a separate loop per designer.