«Зачем мне 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 -- группировка по первому столбцу в select
SQLite с помощью оконной ранжирующей функции 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.
Используемый датасет является коммерческим и отсутствует в открытом доступе, поэтому выложен быть не может.
