«Зачем мне SQL и python?» — задают резонный вопрос маркетологи или менеджеры по продукту, особенно в сфере недвижимости, оптовой торговли, услуг для бизнеса: «У нас нет миллионов строк данных, нет логов, мы успешно работаем с несколькими таблицами в excel».
Да действительно, у вас может не быть корпоративного хранилища данных в компании, и основой автоматизации работы с данными является Power query (что сейчас в РФ делать все труднее и труднее). Но у вас точно есть данные, которые вы получаете от смежных отделов, из CRM/CDP, MES, АСУ ТП. Эти данные приходят регулярно в виде файлов, и вы сопоставляете эти данные друг с другом с помощью ВПР, фильтруете воронкой, чистите с помощью "Найти или заменить", делаете сводники с помощью функции Pivot table.
Какие есть серьезные проблемы при работе в Excel файлами, кроме очевидных преимуществ.
Нужно писать на языке VBA, чтобы автоматизировать рутину: например, очищать данные, или приводить их к нужному виду. Или вам приходится заказывать эти скрипты в it отделе, а они почему то не спешат вам помочь.
Трудно работать с версиями, делать бэкапы. Код макроса может внезапно перестать работать, а на наладку может уйти много времени.
Нужна лицензия на полноценную работу, есть привязка к Windows, что сейчас крайне актуально для многих компаний в РФ с переходом на Linux. А python и СУБД (Например SQLite или PostgreSQL) имеют открытый исходный код и распространяются свободно.
Код макроса может перестать работать после обновления Excel.
Ограничение excel по размеру в миллион строк, и зависание при работе на больших массивах (более 200 000 строк на средних офисных машинах)
Все эти проблемы решает простая СУБД. Вам даже не нужно поднимать никакой сервер. А вся работа будет построена на простом языке запросов к этой СУБД — SQL, которым можно овладеть на серьезном уровне за 2-3 месяца тренировок. Python для нас будет вспомогательным инструментом, для обработки данных.
В этой первой моей статье на Хабре, я буду делать минимум прямых сопоставлений SQL vs Excel и просто покажу как это может работать. К делу!
Задача
Имея данные от поставщика, о продаже квартир в новостройках в формате csv, маркетологу на их основе, нужно ответить на вопросы о том, в каком районе СПб лучше строить новый ЖК, где был наибольший рост цен и что на него влияет? Как росла или падала недвижимость относительно других активов?
Кроме этого, каждый месяц будут приходить новые данные о сделках, и нужно будет обновлять наш отчет. Что мы сделаем для решения задачи сейчас, и ускорения работы в будущем:
Загрузим файлы в Python, загрузим нужные для работы модули.
Очистим данные от мусора (запятые, неверный тип данных, некорректные даты)
Посчитаем расстояние до центра Питера до каждого дома за 30 секунд
За 1 минуту создадим СУБД и подключимся к ней
Создадим расчетные поля в SQL
Соединим 5 таблиц в одну.
Сделаем график и сводную таблицу
Где мы будем работать? Самый простой способ – это начать работать в VSCode, предварительно установив anaconda. При запуске VSСode выберите "New file", а затем "Jupiter Notebook".
В начале нам нужно импортировать модули, также их еще называют библиотеки. Это уже созданные другими программистами "куски" готового кода. Модуль Geopy требует установки через командную строку pip install geopyв магазине с открытым исходным кодом conda/anaconda его нет, поэтому устанавливаем через через pip.
import pandas as pd #работа с данными
import sqlite3 #СУБД для создания локальной БД. Скоро это будет duckBD:)
from geopy.distance import geodesic #вычисление расстояний по координатам
import matplotlib as plt #графики
import seaborn as sns #графикиЗагружаем файлы для работы
Работа в Jupyter Notebook выглядит как ввод команды в ячейке на языке Python и получение результата на выводе или в output
Загрузить файлы в документ можно командой read_csv, указав адрес, где хранится этот файл. Вы можете указывать и ссылку в интернете или сетевое хранилище. Pandas может читать другие типы файлов, например дамп sql, json, spss, sas.
df = pd.read_csv("/Users/andrew/Documents/DataSets/Dataflat_Oct_2023_LO/realty_sold_07112023_LO.csv", sep = ';')
df #выводим результат
Для наглядности в дальнейшем таблица будет крупнее, и мы не будем выводить все колонки. Пока вы можете видеть объект DataFrame библиотеки Pandas. Это многомерный массив, или проще привычная нам таблица со строками или столбцами.
Введем команду для получения количества строк в DataFrame
Когда колонок в таблице очень много, вы увидите только 20 колонок. В опциях Pandas вы можете изменить параметры отображения, чтобы у вас появился скролл влево, и отобразились все столбцы.
pd.options.display.float_format = '{:.2f}'.format
# Максимальное число отображаемых колонок.
pd.set_option('display.max_columns', 500)
# Максимальное число отображаемых строк.
pd.set_option('display.max_rows', 500) Преобразование и очистка данных
Мы загружали данные из csv файла, довольно распространенного формата в корпоративной среде, и наверное вас не обрадует, что в отличии от excel, Pandas не старается угадать тип данных при загрузке файла (строка, число, дата). Типы данных нужно указывать явно, но этого того стоит, так сделаем мы это однажды, и код будет делать нужные манипуляции многократно, освобождая ценное время.
Проверим какие типы данных присвоились столбцам при загрузке.
В первую очередь нас интересуют даты, все они прочитались как object. Для преобразования типов применим метод to_datetime.
df['Дата ДДУ'] = pd.to_datetime(df['Дата ДДУ'], format='%d/%m/%y')
df['Дата регистрации'] = pd.to_datetime(df['Дата регистрации'], format='%d/%m/%y')
df['Дата регистрации модель'] = pd.to_datetime(df['Дата регистрации модель'], format='%d/%m/%y')
df['Дата обременения'] = pd.to_datetime(df['Дата обременения'], format='%d/%m/%y')Задать формат даты при импорте файла
Кроме описанных выше способов, есть еще один: задать дату сразу при импорте. Нужно указать параметр parse_dates
pd.read_csv('file_name.csv', parse_dates=['Deal_date'])Далее мы видим, что некоторые числа, также прочитались программой, как строки или object. Все это произошло потому, что целую часть от дробной части, у нас разделяет запятая, а не точка. Напишем короткий код, который меняет запятую на точку и переводит строку в числовой тип.
df['Площадь'] = df['Площадь'].str.replace(',', '.').astype(float)
df['lat'] = df['lat'].str.replace(',', '.').astype(float)
df['lng'] = df['lng'].str.replace(',', '.').astype(float)
df['Цена кв. м'] = df['Цена кв. м'].str.replace(',', '.').astype(float)В анализе данных не обязательно использовать принцип DRY (Не повторяй свой код). По желанию вы можете использовать конструкции языка Python, циклы, функции, но в данной статье используются простые и понятные примеры встроенных методов.
Обрабатываем "Срок сдачи". В исходной таблице было "2 кв 2017", извлекаем цифру с 4 знаками - год с помощью регулярного выражения.
df['Год сдачи'] = df['Срок сдачи'].str.extract(r'(\d{4})') #Выделяем год из строки
df['Год сдачи'] = pd.to_datetime(df['Год сдачи']) #присваевам тип датыОбрабатываем дату старта продаж. В исходной таблице было "2020.2" тип float. Делим до точки беря первую часть числа. Предварительно приведя число float к строке.
df['Старт продаж К'] = df['Старт продаж К'].astype(str)
df['Старт продаж К'] = df['Старт продаж К'].astype(str).str.split('.').str[0]В наборе данных полная цена сделки у нас оказалась пустой, поэтому мы решили вычислить ее из площади и цены квадратного метра.
#создаем расчетный столбец и добавляем его в датасет
df['Цена производная'] = df['Цена кв. м'] * df['Площадь'] В наборе данных, с котором нам нужно работать названия столбцов на русском, что будет не так удобно при написании SQL запросов. Это не обязательно, но мы переназовем столбцы на латинице.
Скрытый код
new_column_names = {
'ID ЖК': 'id_zhk',
'ЖК рус': 'zhk_rus',
'ЖК англ': 'zhk_angl',
'Район Город': 'rayon_gorod',
'Округ Направление': 'okrug_napravleniye',
'Регион': 'region',
'АТД': 'atd',
'Застройщик ЖК': 'zastroishchik_zhk',
'Площадь': 'ploshchad',
'Комнатность': 'komnatnost',
'Тип Комнатности': 'tip_komnatnosti',
'Этаж': 'etazh',
'Номер': 'nomer',
'Тип помещения': 'tip_pomeshcheniya',
'Корпус': 'korpus',
'Дата регистрации': 'data_registratsii',
'Условия обременения': 'usloviya_obremeneniya',
'Номер обременения': 'nomer_obremeneniya',
'Дата обременения': 'data_obremeneniya',
'Залогодержатель': 'zalogkhozhatel',
'Длительность обременения': 'dlitel_obremeneniya',
'Тип обременения': 'tip_obremeneniya',
'Оценка цены': 'otsenka_tseny',
'Дата ДДУ': 'data_ddu',
'Уступка': 'ustupka',
'Купил лотов в ЖК': 'kupil_lotov_v_zhk',
'ID Корпус': 'id_korpus',
'класс': 'klass',
'Срок сдачи': 'srok_sdachi',
'Стадия строительства': 'stadiya_stroitelstva',
'lat': 'lat',
'lng': 'lng',
'Ипотека': 'ipoteka',
'Секция': 'sektsiya',
'Отделка': 'otdelka',
'Старт продаж К': 'start_prodazh_k',
'Продавец ЮЛ': 'prodavets_yul',
'Зона': 'zona',
'Купил кв и ап в ЖК': 'kupil_kv_i_ap_v_zhk',
'Стадия строительства в дату ДДУ': 'stadiya_stroitelstva_v_data_ddu',
'Цена ДДУ': 'tsena_ddu',
'Цена со скидкой': 'tsena_so_skidkoy',
'Тип сделки': 'tip_sdelki',
'Разница дат': 'raznitsa_dat',
'Дата регистрации модель': 'data_registratsii_model',
'version': 'version',
'Участок': 'uchastok',
'ФИО': 'fio',
'Цена кв. м': 'price_m2',
'ID дом.рф': 'id_dom_rf',
'Оценка по ЕИСЖС': 'otsenka_po_eiszhzs',
'Unnamed: 51': 'unnamed_51',
'Год сдачи': 'god_sdachi',
'Цена производная': 'price'
}
df = df.rename(columns=new_column_names)Вычислим расстояние для Эрмитажа
Это нужно для понимания того, как цена меняется в зависимости от центра притяжения в туристическом городе. В нашем файле есть 2 столбца с широтой и долготой объекта недвижимости. Этот показатель правильно применить только к данным в таблице ЖК, а не к сделкам в них (чтобы не занимать постоянную память), но для упрощения сделаем это и там и там.
# координаты Эрмитажа
a = (59.939442, 30.314131)
#применение лямбда функции, которая считает расстояние по координатам
df.apply(lambda row: geodesic(a, (row['lat'], row['lng'])).km, axis=1)
Импорт исторических данных о курсе доллара, золота, Мосбиржи и нефти.
Я повторил все те же операции и с другими файлами с финансовыми данными. Для экономии места в статье, и вашего времени, покажу лишь скриншот загрузки, так как весь код аналогичен заливке данных по сделкам. Была та же самая очистка данных и приведение к нужным типам.
Итак, мы загрузили 6 таблиц, запомните переменные, ниже они будут нужны для названия таблиц в базе данных:
Таблица сделок в переменной
dfТаблица жилых комплексов, где продавались эти квартиры. Переменная
zkТаблицы с финансовыми данными из 4 источников в соответствующих переменных, например
gold
Создаем базу данных в три строчки
На этом весь почти весь python закончился, он был нужен для того, чтобы получить данные из csv, очистить их, переименовать и добавить туда расстояние до Эрмитажа. Вы можете обогащать свои данные с помощью платных библиотек на подобие dadata, применять свои модели машинного обучения для классификации клиентов или прогноза оттока клиентов.
Наша цель сейчас – начать писать запросы к данным на языке SQL, который в отличии от питона, проще в освоении диджитал специалистами, финансовыми или торговыми менеджерами.
В данном примере, мы будем создавать очень удобную безсерверную базу данных SQlite. Однако вы можете написать в этом же файле и подключение к любой другой баз данных, которая есть в вашей компании.
con = sqlite3.connect('spb_estate')
cur = con.cursor()to_sql отправляет dataframe в таблицы базы данных:
zk.to_sql('zk',con,index=False,if_exists='replace') #таблица ЖК
df.to_sql('df',con,index=False,if_exists='replace') #таблица сделок
ind.to_sql('ind',con,index=False,if_exists='replace') #индексы мосбиржи с 2013
q.to_sql('q',con,index=False,if_exists='replace') #курсы доллара с 2013
oil.to_sql('oil',con,index=False,if_exists='replace') #курсы на brent
metals.to_sql('metals', con, index=False, if_exists='replace') #курсы на золотоJupyter notebook или DBeaver для работы SQL
Где и как работать с базой данных – вопрос удобства и привычки. Выше мы в директории (папке проекта где лежит скрипт Python) создали фаил базы данных SQLite. Мы можем получить доступ к таблицам из программы-клиента DBeaver.
Скачайте DBeaver
Нажмите на кнопку подключения и выберете SQLite
Для работы вам потребуются драйвера, "бобер" скачает их автоматически
Нажмите на "SQL script" и выполните с
Слева у нас будут наши таблицы, которые мы создали выше. Справа - интерфейс для написания и исполнения SQL команд.
SQL в Python
Если вы не хотите из коробки подсветку синтаксиса, схемы отношений таблиц, подсказок, а хотите хардкора, то можете продолжить анализ данных в интерактивной среде Python, используя SQL. Мы уже создали базу данных, остается только передавать ей команды.
Для удобства аналитика, создается функция. На вход она принимает запрос в виде строки и нам возвращается результат запроса к базе данных.
def select(sql):
return pd.read_sql(sql,con)Никакой магии в этом нет, просто как я писал выше Pandas может читать и файлы sql. В переменно sql "зашита" строка запроса на языке SQL, а переменной conn присвоен адрес подключения. (см выше con = sqlite3.connect('spb_estate') )
#Пандас, прочитай sql фаил, по запросу, по подключению в переменной con
pd.read_sql('SELECT avg(price) FROM df', con)
pd.read_sql('Запрос к базе данных в строке', Подключение, которое мы делали ранее)Посмотрим, как залились таблицы БД. Делаем запрос к базе данных, получаем список таблиц которые есть в нашей базе данных. Вот как это выглядит:
sql = '''select name from sqlite_master where type = 'table';''' #запрос
select(sql) #вывод результата
И вот они наши 6 таблиц. Можно спокойно работать над анализом данных при помощи sql
Пример анализа данных с помощью языка SQL
Все примеры SQL запросов, будут приведены с подстветкой синтаксиса.
Сделаем первый select , который поможет нам понять временные границы данных. Получим самые минимальные и максимальные значения.
select --достань мне следующие данные:
max(data_registratsii), -- максимальная дата
min(data_registratsii), -- минимальная дата
min(price), max(price),
min(price_m2),
max(price_m2),
min(ploshchad),
max(ploshchad)
from df -- из какой таблицы?
where data_registratsii not null -- где дата не пустая
and price not null -- и цена не пустая
and -- и
tip_pomeshcheniya = 'квартира'
and price != 0.00 -- цена на кватиры не 0
Построим сводную таблицу, сгруппируем все сделки по годам от 2014 до 2023 в одном запросе, в колонках выведем нужные показатели.
Select --достань мне следующие данные:
-- приводим дату к началу года
date(data_registratsii, 'start of year') as date_year
, count(*) -- считаем количество строк
, avg(price)
, avg(price_m2) -- считаем среднее по площади сделки
, avg(ipoteka)
, avg(ploshchad)
, min(ploshchad)
, avg(dlitel_obremeneniya)
from df -- указываем откуда брать данные
where data_registratsii not null
and price not null
and tip_pomeshcheniya = 'квартира'
and price != 0.00 -- фильруем запрос
-- группируем по дате все данные
group by date(data_registratsii, 'start of year') 
Мы увидели, что до 2016 года сделок крайне мало и брать их в анализ не стоит. Анализируем только данные о сделках на первичном рынке с 2016 по 2023 год.
У нас очень много данных, более 200 000 строк сделок, и нам нужно понять как внутри этого набора распределены цены. Давайте разобъем их на процентили и посмотрим на средние цены внутри этих групп. Создадим "корзины" с помощью оконной функции ntile, которая каждой строке в датасете вернет значение от 1 до 10.
with cte as (
-- В начале нашли процентиль для каждой сделки и другие параметры
SELECT
price,
price_m2,
ploshchad,
data_registratsii,
NTILE(10) OVER (ORDER BY ploshchad) as percentile
FROM df
where data_registratsii not null
and price not null and tip_pomeshcheniya = 'квартира'
)
-- Потом на основе первой таблицы, сделали группировку по percentile
select
percentile,
min(ploshchad),
max(ploshchad),
avg(price),
avg(price_m2),
count(*) from cte
group by 1 -- группировка по первому столбцу в selectSQLite с помощью оконной ранжирующей функции ntile разбил весь набор данных на 10 равных частей по площади. Это можно использовать для сегментации рынка, при сравнении своих цен с ценами конкурентов.
Соединяем таблицы с помощью join
А теперь добавим финансовые данные. Какой был курс доллара на дату регистрации сделки? Мы в основной таблице сделок добавим по ключу даты другие таблицы. В excel этому есть аналог – функция ВПР.
select
id_zhk,
data_registratsii,
zhk_rus,
price,
ipoteka,
ploshchad,
price_m2 as rub_m2_price,
price_m2 / Quote as m2_dollar_price,
price_m2 / Quote / oil.close as m2_oil_price,
price_m2 / Quote / gold as m2_gold_price,
price_m2 / OPEN as mosex_m2_pice
from df
-- добавим данные по доллару на дату
join q on q.date = df.data_registratsii
-- добавим данные по нефти на дату
join oil on oil.date = df.data_registratsii
-- добавим данные по цене золота
join metals on metals.date = df.data_registratsii
-- добавим данные по цене индексов
join ind on ind.TRADEDATE = df.data_registratsii
where data_registratsii not null
and price not null
and tip_pomeshcheniya = 'квартира'
and price != 0.00
and data_registratsii > '2015.01.01'
and name = 'Индекс МосБиржи'
order by date(data_registratsii)
Давайте узнаем какой процент квартир был продан в ипотеку в том или ином году, проследим тенденцию на рынке.
select
date(data_registratsii, 'start of month') as date_m,
avg(ipoteka) as ipoteka
from df
join q on q.date = df.data_registratsii
join oil on oil.date = df.data_registratsii
join metals on metals.date = df.data_registratsii
where data_registratsii not null
and price not null
and tip_pomeshcheniya = 'квартира'
and price != 0.00
and data_registratsii > '2015.01.01'
group by 1
order by 1 ascИз полученной таблицы уже в python сделаем график с помощью библиотеки seaborn и matplotlib
plt.figure(figsize=(20, 6)) #размер графика
sns.lineplot(x='date_m', y='ipoteka', data=select(sql)) #оси и dataframe
sns.set_style("darkgrid") #стиль
plt.title('Доля сделок в ипотеку') #заголовок
plt.xticks(rotation=90) #наклон дат
plt.show() #показать график
А теперь сгруппируем наши данные по расстоянию от Эрмитажа в километрах. Также мы в столбцах хотим видеть и динамику в годах, для этого нужно будет делать условные столбцы, с помощью логической конструкции case when, которая дословно говорит "Если дата регистрации между 2017 и 2018 годом, то создай столбец с значениями этого года". Это одна из моих любимы и частых конструкций.
select
round(distance_hermitage, -2),
count(case when data_registratsii between '2016-01-01' and '2017-01-01' then data_registratsii end) as s2016,
count(case when data_registratsii between '2017-01-01' and '2018-01-01' then data_registratsii end) as s2017,
count(case when data_registratsii between '2018-01-01' and '2019-01-01' then data_registratsii end) as s2018,
count(case when data_registratsii between '2019-01-01' and '2020-01-01' then data_registratsii end) as s2019,
count(case when data_registratsii between '2020-01-01' and '2021-01-01' then data_registratsii end) as s2020,
count(case when data_registratsii between '2021-01-01' and '2022-01-01' then data_registratsii end) as s2021,
count(case when data_registratsii between '2022-01-01' and '2023-01-01' then data_registratsii end) as s2022,
count(case when data_registratsii between '2023-01-01' and '2024-01-01' then data_registratsii end) as s2023
from df
join q on q.date = df.data_registratsii
join oil on oil.date = df.data_registratsii
join metals on metals.date = df.data_registratsii
where data_registratsii not null and price not null and tip_pomeshcheniya = 'квартира' and price != 0.00 and data_registratsii > '2015.01.01'
group by 1
order by 1 asc
limit 20Для полученной таблицы сделаем тепловую карту dataframe(как условное форматирование в excel), применим условное форматирование ячеек в dataframe. Возможно застройщику, такие таблицы помогут определиться с эффективной ценой и удаленностью локации застройки от исторического центра СПб.
select(sql).style.background_gradient(axis=None, vmin=1, vmax=10000) 
В каком ЖК больше всего сделок в период с 1 января 2020 года? Вы уже сами можете без труда прочитать запрос.
select
df.zhk_rus,
okrug_napravleniye,
rayon_gorod,
count(data_registratsii) as cnt_deal,
avg(price_m2) as rub_m2_price,
avg(price_m2) / Quote as m2_dollar_price,
avg(ipoteka) as ipoteka_precent,
max(date(god_sdachi)) as max_god_sdachi,
min(distance_hermitage)
from df
join q on q.date = df.data_registratsii
where data_registratsii not null and price not null
and tip_pomeshcheniya = 'квартира' and price != 0.00
and data_registratsii > '2020.01.01'
group by 1,2 -- групируем по 1 и 2 столбцу в select
order by 4 desc -- сортируем по убывани по 4 стобцу в select
Какая тройка лидеров в ипотечном кредитовании с 2016 года по количеству сделок?
select
zalogkhozhatel,
count(data_registratsii) as cnt_deal,
sum(price),
avg(dlitel_obremeneniya),
avg(price_m2) as rub_m2_price
from df
join q on q.date = df.data_registratsii
join oil on oil.date = df.data_registratsii
join metals on metals.date = df.data_registratsii
where data_registratsii not null and price not null and tip_pomeshcheniya = 'квартира' and price != 0.00 and data_registratsii > '2016.01.01'
group by 1
order by 2 desc
limit 10
plt.figure(figsize=(20, 8))
sns.barplot(x='zalogkhozhatel', y='cnt_deal', data=select(sql))
sns.set_style("darkgrid")
plt.title('Количество сделок с обременением на первичном рынке с 2016 года по третий квартал 2023 года в Спб')
plt.xticks(rotation=90)
plt.show()
Вместо заключения
Если у вас в компании есть типовой процесс работы с данными, например анализ цен конкурентов, товарных запасов и продаж, то запросто сами или с помощью аналитика сможете организовать такой процесс обработки и визуализации данных с помощью кода.
Вам не придется возиться с excel-таблицами и каждый раз переделывать одни и те же манипуляции, вы просто можете пропустить данные через подобный скрипт, который готовит нужные вам отчеты!
P.S.
Используемый датасет является коммерческим и отсутствует в открытом доступе, поэтому выложен быть не может.
