Independent assessment: Campus Café

order_data = {
    "Order_ID": ["P001", "P002", "P003"],
    "Item": ["Tea", "Coffee", "Sandwich"],
    "Quantity": [2, 1, 3],
    "Unit_Price": [5, 10, 15],
}
result = pd.DataFrame(order_data)
print(result)
print("Shape:", result.shape)
print("Total quantity:", result["Quantity"].sum())

The dictionary maps column names to equal-length lists. Each row is one fictional order with one item type. The table has three rows, four columns, and six units ordered. Columns must line up by list position.

df = cafe_orders()
print("Shape:", df.shape)
print(df.dtypes)
mask = (df["Campus"] == "RAK") & (df["Quantity"] >= 2)
result = df.loc[mask, ["Order_ID", "Item", "Quantity"]].sort_values(["Quantity", "Order_ID"], ascending=[False, True])
print(result)
print("First two rows:")
print(result.iloc[:2])

The original table has eight rows and six columns. Four RAK orders meet the quantity rule. C007 comes first with four units, followed by C003 with three. C001 precedes C005 because their quantities tie and IDs are sorted ascending.

df = raw_cafe_orders().drop_duplicates().reset_index(drop=True)
df["Item"] = df["Item"].astype("string").str.strip().str.title()
campus_text = df["Campus"].astype("string").str.strip().replace("", pd.NA)
df["Campus_was_missing"] = campus_text.isna()
df["Campus"] = campus_text.str.lower().map({"rak": "RAK", "dubai": "Dubai"}).fillna("Unknown")
df["Quantity_raw"] = df["Quantity"]
df["Unit_Price_raw"] = df["Unit_Price"]
df["Quantity"] = pd.to_numeric(df["Quantity"], errors="coerce")
df["Unit_Price"] = pd.to_numeric(df["Unit_Price"], errors="coerce")
df["Quantity_unparsed"] = df["Quantity"].isna()
df["Quantity_not_positive_integer"] = df["Quantity"].notna() & ((df["Quantity"] <= 0) | (df["Quantity"] % 1 != 0))
df["Price_missing_or_invalid"] = df["Unit_Price"].isna() | (df["Unit_Price"] <= 0)
bad = df[["Quantity_unparsed", "Quantity_not_positive_integer", "Price_missing_or_invalid"]].any(axis=1)
audit = df.loc[bad, ["Order_ID", "Quantity_raw", "Unit_Price_raw", "Quantity_unparsed", "Quantity_not_positive_integer", "Price_missing_or_invalid"]].copy()
result = df.loc[~bad].copy()
print("Accepted orders:")
print(result[["Order_ID", "Item", "Campus", "Campus_was_missing", "Quantity", "Unit_Price"]])
print("Excluded orders and reasons:")
print(audit)

Nine raw rows become eight after removing one exact duplicate. C003 has an unparseable quantity, C005 lacks a price, and C006 has zero quantity under the stated positive-quantity rule. Five orders remain. Their known revenue would total AED 75. C007 remains because its quantity and price are valid; its unknown campus must stay visible in any campus report. "Unknown" is a label, not a recovered location.

df = cafe_orders()
df["Revenue"] = df["Quantity"] * df["Unit_Price"]
result = df.groupby("Campus", as_index=False).agg(Orders=("Order_ID", "size"), Units=("Quantity", "sum"), Revenue_AED=("Revenue", "sum"))
print(result)
print("Original total:", df["Revenue"].sum())
print("Grouped total:", result["Revenue_AED"].sum())
assert result["Revenue_AED"].sum() == df["Revenue"].sum()

RAK has four orders, eleven units, and AED 95 revenue. Dubai has four orders, six units, and AED 60 revenue. Total revenue is AED 155 from seventeen units. These are revenues before considering any costs; they are not profits.

orders = cafe_orders()
menu = cafe_menu()
result = orders.merge(menu, on="Item", how="outer", validate="many_to_one", indicator=True)
audit = result.loc[result["_merge"] != "both", ["Order_ID", "Item", "Category", "_merge"]]
print(result[["Order_ID", "Item", "Category", "_merge"]])
print("Unmatched rows:")
print(audit)
print("Merge counts:")
print(result["_merge"].value_counts())
print("Rows representing orders:", result["Order_ID"].notna().sum())

Six order rows match the catalog. Two Sandwich orders are left_only because the catalog lacks Sandwich. Juice is one right_only catalog row. The outer merge has nine rows but still represents eight orders; the extra catalog-only row is not a transaction. The menu must be unique on Item for many_to_one validation.

df = cafe_orders()
df["Revenue"] = df["Quantity"] * df["Unit_Price"]
result = df.pivot_table(index="Campus", columns="Item", values="Revenue", aggfunc="sum", fill_value=0)
tidy = result.reset_index().melt(id_vars="Campus", var_name="Item", value_name="Revenue_AED")
print("Wide:")
print(result)
print("Long:")
print(tidy)
assert tidy["Revenue_AED"].sum() == df["Revenue"].sum()

The wide table has two campus rows and three item columns. RAK totals are Coffee 20, Sandwich 45, Tea 30; Dubai totals are Coffee 40, Sandwich 15, Tea 5. Melting yields six Campus-Item rows with the same AED 155 total. Ordinary pivot cannot aggregate repeated Campus-Item keys. Filling absent combinations with zero is appropriate here only under the exercise assumption that the provided order list is complete.

df = cafe_orders()
df["Ordered_At"] = pd.to_datetime(df["Ordered_At"], format="%Y-%m-%d %H:%M")
df["Revenue"] = df["Quantity"] * df["Unit_Price"]
result = df.set_index("Ordered_At").resample("D").agg(Orders=("Order_ID", "size"), Revenue_AED=("Revenue", "sum"))
best_days = result.loc[result["Revenue_AED"] == result["Revenue_AED"].max()]
print(result)
print("Highest-revenue days:")
print(best_days)

Daily revenue is AED 20, 50, 35, and 50 on October 1 through 4. Each day has two orders. October 2 and October 4 tie for the maximum. A filter for the complete period through October 4 can use timestamps >= October 1 and < October 5, preserving all October 4 times.

df = cafe_orders()
df["Revenue"] = df["Quantity"] * df["Unit_Price"]
result = df.groupby("Campus", as_index=False).agg(Revenue_AED=("Revenue", "sum"))
fig, ax = plt.subplots(figsize=(6, 4))
result.plot.bar(x="Campus", y="Revenue_AED", ax=ax, legend=False, color="#286b69", rot=0)
ax.set_title("Fictional café revenue by campus")
ax.set_xlabel("Campus")
ax.set_ylabel("Revenue (AED)")
ax.set_ylim(bottom=0)
fig.tight_layout()
csv_path = OUTPUT_DIR / "cafe_revenue_by_campus.csv"
plot_path = OUTPUT_DIR / "cafe_revenue_by_campus.png"
result.to_csv(csv_path, index=False)
fig.savefig(plot_path, dpi=160)
round_trip = pd.read_csv(csv_path)
pd.testing.assert_frame_equal(round_trip, result)
print(result)
print("RAK revenue exceeds Dubai revenue by AED 35 in these eight fictional orders.")
print("Costs are unavailable, so the table does not establish profit or predict future sales.")
plt.show()

The chart compares AED 60 for Dubai with AED 95 for RAK. RAK exceeds Dubai by AED 35 in the supplied records. CSV export with index=False keeps only the intended two columns. The result describes fictional revenue, not profit, demand, or a forecast. Re-running the task replaces only its two named output files.