Весь курс мы пользовались готовой таблицей shop_orders.csv. Теперь соберём её сами — из трёх таблиц,
как они лежат в базе магазина: заказы, справочник товаров, справочник клиентов. А заодно получим то, чего
в готовой таблице не было: себестоимость, а значит, и прибыль. Новых методов в проекте нет.
import pandas as pd
orders = pd.read_csv("data/orders.csv")
products = pd.read_csv("data/products.csv")
customers = pd.read_csv("data/customers.csv")
print(orders.shape, products.shape, customers.shape)
orders.head(3)(2448, 6) (20, 5) (240, 5)
| order_id | date | customer_id | product_id | quantity | channel | |
|---|---|---|---|---|---|---|
| 0 | 10001 | 2025-01-01 | C095 | P18 | 2 | маркетплейс |
| 1 | 10001 | 2025-01-01 | C095 | P09 | 1 | маркетплейс |
| 2 | 10002 | 2025-01-01 | C059 | P10 | 1 | приложение |
Шаг 1. Проверить ключи
Прежде чем соединять, убедимся, что в справочниках ключ не повторяется: иначе строки размножатся.
Сохраните в product_key_ok, уникальны ли значения product_id в таблице products; в customer_key_ok —
уникальны ли customer_id в customers; в order_key_ok — уникальны ли order_id в orders.
is_unique возвращает True или False: products["product_id"].is_unique.Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
product_key_ok = ...
customer_key_ok = ...
order_key_ok = ...Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
product_key_ok = products["product_id"].is_unique
customer_key_ok = customers["customer_id"].is_unique
order_key_ok = orders["order_id"].is_uniqueВ справочниках ключи уникальны — так и должно быть. В заказах order_id повторяется, и это тоже нормально:
заказ состоит из нескольких строк. Значит, оба соединения будут «многие к одному».
Шаг 2. Заказы и товары
Соедините orders с products по product_id и сохраните в lines; попросите pandas проверить, что
соединение «многие к одному». Добавьте столбцы revenue (цена × количество) и profit — прибыль:
(цена − себестоимость) × количество.
orders.merge(products, on="product_id", validate="many_to_one").(lines["price"] - lines["cost"]) * lines["quantity"].Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
lines = ...
# добавьте в lines столбцы revenue и profitУпражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
lines = orders.merge(products, on="product_id", validate="many_to_one")
lines["revenue"] = lines["price"] * lines["quantity"]
lines["profit"] = (lines["price"] - lines["cost"]) * lines["quantity"]Шаг 3. Добавить покупателей
Добавьте к lines город и сегмент покупателя и сохраните в full. Из справочника customers возьмите только
три столбца: customer_id, city, segment.
lines.merge(customers[["customer_id", "city", "segment"]], on="customer_id", validate="many_to_one").Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
full = ...Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
full = lines.merge(customers[["customer_id", "city", "segment"]], on="customer_id", validate="many_to_one")Шаг 4. Сверка
Собранную таблицу стоит сверить с чем-нибудь известным. У нас есть готовая shop_orders.csv — сравним
выручку по городам:
shop = pd.read_csv("data/shop_orders.csv")
shop["revenue"] = shop["price"] * shop["quantity"]
ours = full.groupby("city")["revenue"].sum()
theirs = shop.groupby("city")["revenue"].sum()
print((ours == theirs).all())
print(ours.sum(), theirs.sum())True 3301420 3301420
Выручка каждого города совпала, общий итог тоже. У обоих Series одинаковые метки в одинаковом порядке
(группировка сортирует города по алфавиту), поэтому их можно сравнить через ==; all() подтверждает, что
совпало всё.
Шаг 5. Прибыль по городам
Маржа — доля прибыли в выручке: сколько копеек с каждого рубля остаётся магазину.
Сохраните в city_table таблицу по городам (город в индексе): revenue — выручка, profit — прибыль,
margin — маржа, округлённая до трёх знаков. Отсортируйте по убыванию прибыли.
full.groupby("city").agg(revenue=("revenue", "sum"), profit=("profit", "sum")).profit / revenue; затем sort_values("profit", ascending=False).Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
city_table = ...Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
city_table = full.groupby("city").agg(revenue=("revenue", "sum"), profit=("profit", "sum"))
city_table["margin"] = (city_table["profit"] / city_table["revenue"]).round(3)
city_table = city_table.sort_values("profit", ascending=False)Шаг 6. Прибыль по товарам
Сохраните в product_table плоскую таблицу по товарам со столбцами category, name, revenue, profit,
margin (три знака). В top_profit сохраните три товара с наибольшей прибылью, в low_margin — три товара
с наименьшей маржой.
["category", "name"] с as_index=False.product_table.nlargest(3, "profit") и product_table.nsmallest(3, "margin").Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
product_table = ...
top_profit = ...
low_margin = ...Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
product_table = full.groupby(["category", "name"], as_index=False).agg(revenue=("revenue", "sum"), profit=("profit", "sum"))
product_table["margin"] = (product_table["profit"] / product_table["revenue"]).round(3)
top_profit = product_table.nlargest(3, "profit")
low_margin = product_table.nsmallest(3, "margin")Шаг 7. Доставка по каналам
Данные о доставке — по строке на заказ, а наша таблица — по строке на позицию. Если соединить их напрямую, срок и оценка заказа повторятся в каждой его позиции, и заказы из трёх позиций будут весить втрое больше. Сначала свернём таблицу до заказов.
Склейте файлы data/delivery_h1.csv и data/delivery_h2.csv в таблицу delivery. Соберите order_table:
сверните full до заказов — плоская таблица order_id, channel, total (сумма revenue), — и присоедините
к ней delivery по order_id с проверкой «один к одному». В channel_delivery сохраните средний срок (days)
и среднюю оценку (rating) по каналам, округлённые до двух знаков.
pd.concat([pd.read_csv(...), pd.read_csv(...)], ignore_index=True).full.groupby(["order_id", "channel"], as_index=False).agg(total=("revenue", "sum")), затем .merge(delivery, on="order_id", validate="one_to_one").order_table.groupby("channel").agg(days=("days", "mean"), rating=("rating", "mean")).round(2).Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
delivery = ...
order_table = ...
channel_delivery = ...Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
delivery = pd.concat([pd.read_csv("data/delivery_h1.csv"), pd.read_csv("data/delivery_h2.csv")], ignore_index=True)
order_table = full.groupby(["order_id", "channel"], as_index=False).agg(total=("revenue", "sum"))
order_table = order_table.merge(delivery, on="order_id", validate="one_to_one")
channel_delivery = order_table.groupby("channel").agg(days=("days", "mean"), rating=("rating", "mean")).round(2)Итог
print("Выручка:", full["revenue"].sum(), "· прибыль:", full["profit"].sum(), "· маржа:", round(full["profit"].sum() / full["revenue"].sum(), 3))
print(city_table)
print(channel_delivery)Выручка: 3301420 · прибыль: 1542150 · маржа: 0.467
revenue profit margin
city
Москва 1310110 612070 0.467
Санкт-Петербург 803720 375160 0.467
Казань 440150 204610 0.465
Екатеринбург 423350 198470 0.469
Новосибирск 324090 151840 0.469
days rating
channel
маркетплейс 5.25 3.33
приложение 3.99 3.78
сайт 3.95 3.76Выводы
- За год магазин заработал 1.54 млн ₽ прибыли с 3.3 млн ₽ выручки — маржа 47 %.
- Маржа по городам одинакова: 46.5–46.9 %. Города отличаются объёмом, а не выгодностью.
- Больше всего прибыли приносят кофе в пачках по 250 г. Килограммовые пачки продаются на те же суммы, но маржа у них самая низкая — около 40 %.
- Маркетплейс везёт заказы на день с лишним дольше (5.3 дня против 4.0), и оценки у него ниже: 3.3 против 3.8. Возможно, поэтому на маркетплейс реже возвращаются.
Три таблицы, два соединения, одна склейка — и каждое действие проверено: ключи уникальны, число строк не изменилось, итог сошёлся с известным.