Итоговый проект курса — в двух частях. В этой части вы соберёте данные, в следующей — напишете отчёт.
Год закончился, и владелец «Кофейной лавки» хочет знать, сколько магазин на самом деле заработал: с учётом себестоимости и возвратов. Готовой таблицы для этого нет. Есть четыре файла, как они лежат в разных системах магазина:
| Файл | Что в нём | Состояние |
|---|---|---|
orders.csv |
строки заказов: заказ, дата, покупатель, товар, количество, канал | чистый |
products.csv |
справочник товаров: название, категория, цена, себестоимость | чистый |
customers_raw.csv |
выгрузка клиентов из CRM | грязный: пробелы, регистр, дубли |
returns_raw.csv |
журнал возвратов | грязный: дубли |
Новых методов нет — только то, что вы уже умеете. Подсказок меньше, чем обычно: это проверка себя.
Шаг 1. Загрузка
Загрузите файлы в таблицы orders, products, customers_raw, returns_raw. Столбец date в orders
должен быть настоящей датой.
data/. Даты при чтении — параметр parse_dates=["date"].Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
import pandas as pd
orders = ...
products = ...
customers_raw = ...
returns_raw = ...Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
import pandas as pd
orders = pd.read_csv("data/orders.csv", parse_dates=["date"])
products = pd.read_csv("data/products.csv")
customers_raw = pd.read_csv("data/customers_raw.csv")
returns_raw = pd.read_csv("data/returns_raw.csv")print(customers_raw.head(4))
print(returns_raw.head(4)) customer_id name city signup_date segment \
0 C001 Сергей Ю. Екб 2025-06-12 постоянный
1 C002 Сергей В. екатеринбург 2025-01-14 новый
2 C003 Максим С. Екб 2025-05-19 новый
3 C004 Андрей Д. москва 2023-09-15 НОВЫЙ
email bonus
0 client001@lavka.example 120
1 client002@lavka.example 2 400
2 client003@lavka.example 50
3 client004@lavka.example 1900
return_id order_id product_id return_date quantity reason
0 R001 10001 P18 17.01.2025 2 брак
1 R002 10010 P16 2025-01-19 1 повреждена упаковка
2 R003 10012 P01 21.01.2025 2 передумал
3 R004 10018 P03 2025-01-15 5 не понравился вкусШаг 2. Клиенты
Из выгрузки клиентов для отчёта нужны три столбца: код, город и сегмент. Все три нужно почистить — вы уже делали это в модуле об очистке.
Соберите таблицу customers из трёх столбцов — customer_id, city, segment: коды без пробелов
и с заглавной буквой; города — пять названий без сокращений (словарь short уже есть); сегменты — три значения
строчными буквами. Один клиент — одна строка; индекс с нуля.
.str.strip(), затем .str.upper() для кода, .str.title().replace(short) для города, .str.lower() для сегмента..drop_duplicates().reset_index(drop=True).Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
short = {"Спб": "Санкт-Петербург", "С.-Петербург": "Санкт-Петербург", "Екб": "Екатеринбург"}
customers = ...Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
short = {"Спб": "Санкт-Петербург", "С.-Петербург": "Санкт-Петербург", "Екб": "Екатеринбург"}
customers = customers_raw.assign(
customer_id=lambda d: d["customer_id"].str.strip().str.upper(),
city=lambda d: d["city"].str.strip().str.title().replace(short),
segment=lambda d: d["segment"].str.strip().str.lower(),
)
customers = customers[["customer_id", "city", "segment"]].drop_duplicates().reset_index(drop=True)Шаг 3. Возвраты
В журнале возвратов два вида дублей: записи, сохранённые дважды, и возвраты, оформленные повторно под новым номером. Возврат — это позиция заказа: пара «заказ, товар».
Соберите таблицу returns: по одной строке на возвращённую позицию, столбцы order_id, product_id
и returned (число возвращённых штук — переименованный столбец quantity); индекс с нуля.
.drop_duplicates() и .drop_duplicates(subset=["order_id", "product_id"])..rename(columns={"quantity": "returned"}) и .reset_index(drop=True).Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
returns = (
returns_raw
# допишите шаги цепочки
)Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
returns = (
returns_raw
.drop_duplicates()
.drop_duplicates(subset=["order_id", "product_id"])
[["order_id", "product_id", "quantity"]]
.rename(columns={"quantity": "returned"})
.reset_index(drop=True)
)Шаг 4. Соединение
Три соединения — и каждое стоит проверить параметром validate: заказы с товарами и с клиентами — «многие
к одному», с возвратами — «один к одному» по двум ключам. Возвраты есть не у всех позиций.
Соедините orders с products, customers и returns и сохраните в data. Все 2448 строк заказов должны
остаться.
.merge(products, on="product_id", validate="many_to_one"), так же customers по customer_id..merge(returns, on=["order_id", "product_id"], how="left", validate="one_to_one").Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
data = (
orders
# допишите три соединения
)Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
data = (
orders
.merge(products, on="product_id", validate="many_to_one")
.merge(customers, on="customer_id", validate="many_to_one")
.merge(returns, on=["order_id", "product_id"], how="left", validate="one_to_one")
)Если бы в справочнике клиентов остались дубли, validate остановил бы соединение ошибкой — и это лучше, чем
размноженные строки и завышенная выручка.
Шаг 5. Деньги
В столбце returned замените пропуски нулями и сделайте его целым. Добавьте в data три столбца:
revenue— выручка: цена × количество;net— выручка за вычетом возвратов: цена × (количество − возвращено);profit— прибыль с проданного и не возвращённого: (цена − себестоимость) × (количество − возвращено).
data["returned"] = data["returned"].fillna(0).astype("int64").data["price"] * (data["quantity"] - data["returned"]).Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
# заполните пропуски в data["returned"] и добавьте столбцы revenue, net, profitУпражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
data["returned"] = data["returned"].fillna(0).astype("int64")
data["revenue"] = data["price"] * data["quantity"]
data["net"] = data["price"] * (data["quantity"] - data["returned"])
data["profit"] = (data["price"] - data["cost"]) * (data["quantity"] - data["returned"])Шаг 6. Проверка
Собранной таблице нельзя верить на слово. Три проверки: сверка с известным итогом, отсутствие пропусков и здравый смысл.
У нас есть готовая таблица shop — сверим с ней выручку по городам. Сохраните в ours выручку (revenue)
по городам в data, в theirs — в shop. В cities_match — одно значение True или False: совпали ли
все города. В no_gaps — True, если во всей таблице data нет ни одного пропуска. В lost — сколько
рублей выручки ушло в возвраты.
(ours == theirs).all() — одно значение вместо пяти.data.isna().sum().sum() — сумма по столбцам и ещё раз сумма.Заготовка упражнения обновилась с тех пор, как вы сохранили свой код.
Вернуть заготовку? Ваш код в этом упражнении будет удалён.
shop = pd.read_csv("data/shop_orders.csv")
shop["revenue"] = shop["price"] * shop["quantity"]
ours = ...
theirs = ...
cities_match = ...
no_gaps = ...
lost = ...Упражнение пока не решено, поэтому для ячеек ниже выполнено эталонное решение — ваш код не изменён.
shop = pd.read_csv("data/shop_orders.csv")
shop["revenue"] = shop["price"] * shop["quantity"]
ours = data.groupby("city")["revenue"].sum()
theirs = shop.groupby("city")["revenue"].sum()
cities_match = (ours == theirs).all()
no_gaps = data.isna().sum().sum() == 0
lost = data["revenue"].sum() - data["net"].sum()print(data.shape)
print(list(data.columns))
data[["date", "name", "city", "segment", "quantity", "returned", "revenue", "net", "profit"]].head(4)(2448, 16) ['order_id', 'date', 'customer_id', 'product_id', 'quantity', 'channel', 'name', 'category', 'price', 'cost', 'city', 'segment', 'returned', 'revenue', 'net', 'profit']
| date | name | city | segment | quantity | returned | revenue | net | profit | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2025-01-01 | Кофемолка ручная | Москва | новый | 2 | 2 | 6400 | 0 | 0 |
| 1 | 2025-01-01 | Травяной сбор 50 г | Москва | новый | 1 | 0 | 260 | 260 | 160 |
| 2 | 2025-01-01 | Шоколад горький | Москва | постоянный | 1 | 0 | 180 | 180 | 90 |
| 3 | 2025-01-01 | Чёрный чай 100 г | Москва | постоянный | 1 | 0 | 350 | 350 | 200 |
Что получилось
Из четырёх файлов — одна таблица: 2448 строк, 16 столбцов, ни одного пропуска. Выручка по городам совпала с известной. Возвраты уменьшили выручку на 142 тысячи рублей — 4.3 %.
По дороге были сделаны все шаги, которые отличают аккуратную работу от «как-нибудь»:
- грязные таблицы почищены до соединения;
- дубли удалены по правильному ключу;
- каждое соединение проверено: тип связи и число строк;
- пропуски после соединения заполнены осмысленно;
- итог сверен с независимым источником.
В следующем уроке эта таблица станет отчётом.