Раздел 1 · 4 балла

Данные, pandas и SQL

Читаем три файла и отвечаем на два вопроса. Каждый вопрос решаем дважды: командами pandas и запросом SQL. Склеивать таблицы (join) здесь запрещено.

Что за инструменты

Таблица (DataFrame)

Строки это клиенты, столбцы это их свойства. В pandas таблица называется DataFrame.

pandas

Библиотека для таблиц. Сокращённо pd.

SQL

Язык вопросов к базе данных, читается почти как английский: «SELECT MIN(income) FROM people» = «выбери минимальный доход из people».

sqlite3

Крошечная база данных, встроенная в Python. Ноутбук создаёт её в памяти (':memory:') и кладёт туда наши три таблицы.

CSV

Текстовый файл-таблица: одна строка = один клиент, значения разделены каким-то символом.

Пропуск (NaN)

Пустая ячейка. NaN значит «не число», то есть значения нет.

Три таблицы

Таблицы связаны столбцом id (номер клиента): у одного человека один и тот же id во всех трёх.

Ячейка 1: читаем данные

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns  # sns нужен ниже, в ячейке с PCA / t-SNE / UMAP

# у файлов разные разделители: people - ";", products - табуляция, purchases - ","
people = pd.read_csv('people.csv', sep=';')
products = pd.read_csv('products.csv', sep='\t')
purchases = pd.read_csv('purchases.csv')

for name, table in [('people', people), ('products', products), ('purchases', purchases)]:
    missing = table.isna().sum()
    print(f'{name}: {table.shape[0]} строк, {table.shape[1]} столбцов')
    print('  пропуски:', missing[missing > 0].to_dict() if missing.sum() > 0 else 'нет')

1. import pandas as pd подключаем pandas под коротким именем pd.
2. import seaborn as sns подключаем библиотеку графиков. В исходном ноутбуке её забыли, а ниже она используется.
3. pd.read_csv('people.csv', sep=';') читаем файл в таблицу. sep говорит, каким символом разделены значения.
4. sep='\t' у products разделитель табуляция (невидимый «большой пробел»).
5. pd.read_csv('purchases.csv') без sep: по умолчанию pandas ждёт запятую, она тут и стоит.
6. for name, table in [...] повторяем одно и то же для трёх таблиц по очереди.
7. table.isna().sum() isna() помечает пустые ячейки как True, sum() считает пометки в каждом столбце. Получаем число пропусков по столбцам.
8. table.shape размер таблицы: пара (строки, столбцы). [0] строки, [1] столбцы.
9. missing[missing > 0] оставляем только столбцы, где пропуски есть, и печатаем.

Результат:

people: 2240 строк, 10 столбцов
  пропуски: {'income': 24}
products: 2240 строк, 7 столбцов
  пропуски: нет
purchases: 2240 строк, 6 столбцов
  пропуски: нет

Задание 1: разница доходов

Вопрос: абсолютная разница между минимальным доходом клиентов с детьми и максимальным доходом клиентов без детей. Учитываем только kidhome.

• Группа «с детьми»: kidhome > 0. Самый маленький доход в ней: 2447.
• Группа «без детей»: kidhome == 0. Самый большой доход в ней: 160803.
• Разница по модулю: |2447 − 160803| = 158356.

Модуль, abs

Число без знака минус: abs(−5) = 5. «Абсолютная разница» = разница по модулю.

Маска

Столбец из True / False: True там, где условие выполнено. По маске выбирают нужные строки.

pandas

min_income_with_kids = people.loc[people['kidhome'] > 0, 'income'].min()
max_income_without_kids = people.loc[people['kidhome'] == 0, 'income'].max()

abs(min_income_with_kids - max_income_without_kids)

1. people['kidhome'] > 0 маска: True у тех, у кого есть дети.
2. people.loc[маска, 'income'] loc берёт строки по маске и столбец income. Получаем доходы только семей с детьми.
3. .min() наименьший из них, 2447. Пропуски (NaN) min сам пропускает.
4. == 0 двойное «равно» это вопрос «равно ли?». Одинарное «=» значит «положить значение в переменную».
5. abs(...) модуль разности. Последнюю строку ячейки Colab выводит сам: 158356.0.

SQL

query = """
SELECT ABS(
    (SELECT MIN(income) FROM people WHERE kidhome > 0)
  - (SELECT MAX(income) FROM people WHERE kidhome = 0)
) AS income_diff;
"""
pd.read_sql_query(query, conn)

1. """...""" тройные кавычки: текст запроса можно писать на нескольких строках.
2. (SELECT MIN(income) FROM people WHERE kidhome > 0) подзапрос, то есть маленький запрос внутри большого. Возвращает одно число, 2447.
3. (SELECT MAX(income) ... WHERE kidhome = 0) второй подзапрос, 160803. В SQL одно «=» это сравнение.
4. ABS(...) AS income_diff модуль разности, а AS даёт столбцу ответа имя.
5. pd.read_sql_query(query, conn) отправляет запрос в базу conn и возвращает ответ таблицей: 158356.0.

Задание 2: доля PhD среди любителей сладкого

Вопрос: делим клиентов на тех, кто покупает сладкое, и тех, кто не покупает. В каждой группе считаем, какая часть имеет PhD, а какая другое образование. Сумма по строке = 1.

PhD

Учёная степень (кандидат наук). Other = все остальные: Graduation, Master, 2n Cycle, Basic.

«Покупает сладкое»

За 2 года потратил на сладости больше нуля: mntsweetproducts > 0.

Доля

Часть от группы: (сколько PhD в группе) / (сколько всего людей в группе). Доля 0.18 = 18%.

Сложность: доход и образование лежат в people, а сладости в products. Склеивать нельзя, поэтому сначала достаём список id сладкоежек, а потом спрашиваем у каждого человека из people, есть ли он в этом списке.

pandas

# id клиентов, которые хоть раз тратили деньги на сладкое
sweet_ids = products.loc[products['mntsweetproducts'] > 0, 'id']

sweets_group = people['id'].isin(sweet_ids).map({True: 'Sweets', False: 'No Sweets'})
education_group = (people['education'] == 'PhD').map({True: 'PhD', False: 'Other'})

# normalize='index' - делим на сумму по строке, поэтому строка в сумме даёт 1
answer = pd.crosstab(sweets_group, education_group, normalize='index')
answer = answer.loc[['Sweets', 'No Sweets'], ['PhD', 'Other']]
answer.index.name = None
answer.columns.name = None
answer

1. sweet_ids = products.loc[...] список номеров клиентов, у которых траты на сладкое больше нуля.
2. people['id'].isin(sweet_ids) для каждого человека: есть ли его номер в списке (True / False). Это замена join.
3. .map({True: 'Sweets', False: 'No Sweets'}) переименовываем True / False в понятные слова по словарю.
4. (people['education'] == 'PhD').map(...) то же для образования: «PhD» или «Other».
5. pd.crosstab(a, b) таблица-счётчик: сколько людей в каждой паре групп (Sweets + PhD и т.д.).
6. normalize='index' делит каждую строку на её сумму: из количеств получаются доли.
7. answer.loc[['Sweets', 'No Sweets'], ['PhD', 'Other']] ставим строки и столбцы в порядке как в задании (сам crosstab сортирует по алфавиту).
8. index.name = None убираем лишние подписи над таблицей.

SQL

query = """
SELECT
    CASE WHEN id IN (SELECT id FROM products WHERE mntsweetproducts > 0)
         THEN 'Sweets' ELSE 'No Sweets' END AS sweets,
    AVG(CASE WHEN education = 'PhD' THEN 1.0 ELSE 0.0 END) AS PhD,
    AVG(CASE WHEN education <> 'PhD' THEN 1.0 ELSE 0.0 END) AS Other
FROM people
GROUP BY sweets
ORDER BY sweets DESC;
"""
pd.read_sql_query(query, conn).set_index('sweets').rename_axis(None)

1. id IN (SELECT id FROM products WHERE ...) «есть ли номер человека среди сладкоежек». Аналог isin.
2. CASE WHEN ... THEN 'Sweets' ELSE 'No Sweets' END это «если ..., то ..., иначе ...». Каждому человеку пишем его группу.
3. CASE WHEN education = 'PhD' THEN 1.0 ELSE 0.0 END у PhD ставим 1, у остальных 0.
4. AVG(...) среднее из единиц и нулей это и есть доля единиц. Пример: 1, 0, 0, 0 → среднее 0.25 → 25% PhD.
5. <> в SQL значит «не равно».
6. GROUP BY sweets считаем средние отдельно для группы Sweets и отдельно для No Sweets.
7. ORDER BY sweets DESC сортируем по убыванию: «S» по алфавиту дальше «N», поэтому Sweets встаёт первой строкой.
8. .set_index('sweets') делаем названия групп подписями строк, чтобы таблица выглядела как в задании.

Ответ (pandas и SQL совпали)

Проверка: 0.182867 + 0.817133 = 1. Среди тех, кто сладкое не покупает, доля PhD 36.5%, это вдвое больше, чем у сладкоежек (18.3%).

Вопросы на защите