В продолжение рассказов о Big Data для простых смертных предлагаем попробовать себя в решении жилищного вопроса: как отличаются цены за квадратный метр на разных этажах. Житейская польза от этой заметки заключается в получении без особых усилий и без специальных навыков достоверных знаний об ожиданиях на рынке недвижимости. Государство скрывает цены реальных сделок, но иметь адекватное представление о настроениях продавцов можно и без знакомого опытного риелтора, сына маминой подруги.
Статья представляет собой пошаговую иллюстрированную инструкцию по решению задачи анализа этажности городской застройки, в т.ч. расчет скидок за первый и последний этажи. В качестве модельного города принят Брянск. Расчетный файл прилагается и его листы пронумерованы в последовательности выполненных операций. Шаблон расчета легко адаптировать для другого города и вида недвижимости.
Изложенный подход призван аргументировать представление о том, что Big Data не является прерогативой дата сатанистов, но в известной степени доступен неспециалистам. Материал может быть интересен всем, кроме работающих с данными профессионально. Приведенные приемы будут востребованы в работе с данными для риелторов и оценщиков без навыков программирования. Технохардкор в данном случае заключается не в инсайтах применения фреймворков типа MapReduce, а в реализации всего пайплайна средствами общеизвестного офисного приложения.
Инструкция состоит из двух частей. В первой части изложен порядок подготовки, первичного ознакомления с данными и уточнения цели исследования. Во второй части будет сделан расчет скидки за этаж.
Содержание материала не претендует на академичность. Основной целью является популяризация работы с данными для тех профессий, где это было бы полезно.
1. System requirements
Для построения инфраструктуры работы с большими данными в данном случае потребуется только Excel. Excel соберет данные с досок объявлений, Excel их подготовит для использования, Excel их визуализирует и преобразует в знания. Вы знакомы с Excel? → если да, то вы уже не офисный планктон, а опытный дата-инженер и дата-аналитик!
Настройка инфраструктуры для нашего кейса заключается в конфигурировании Excel двумя надстройками. Надстройка Power Query предоставляет средства ETL. Надстройка Робастик предоставляет средства автоматизации.
2. Dataset
Исходный набор данных представляет собой данные объявлений с одной из общеизвестных досок объявлений. Датасет состоит из 15 файлов в одной папке, в каждом из которых находятся собранные в течение календарного месяца объявления (ежемесячный срез рынка). Инструкция для парсинга объявлений недвижимости изложена в одной из предыдущих публикаций.
3. Extract
Для извлечения данных из датасета используем Power Query. В разных версиях Excel меню Power Query выглядит по-разному. В версиях 2010 и 2013 ее надо устанавливать дополнительно, а с 2016 она идет в составе Excel.
Для объединения данных из 15 файлов и вставки в лист новой книги нужно сделать несколько кликов.
В версии 2019 это меню находится здесь:
В версии 2013 это меню находится здесь:
Затем объединяем и загружаем:
После чего остается выбрать лист с данными:
Поскольку парсер автоматически формирует названия листов, названия листов с данными по квартирам в Брянске будут одинаковыми во всех файлах. В результате на одном листе №1 будут объединены в одну таблицу все имеющиеся данные из всех файлов в папке с листов, имеющих одинаковое название.
Всего таким образом извлечено порядка 150 тысяч записей. Для масштаба enterprise это ничто. Для локального МСБ это вполне Big Data.
4. Transform
Удаление лишних колонок
Исходные данные расположены в 49 колонках. Данные из большинства колонок не потребуются. Удаляем лишние колонки данных.
В версии 2019 это выглядит так:
В версии 2013 это выглядит так:
И затем либо удаляем лишние столбы, либо выбираем те, которые надо оставить.
В завершение размещаем результат на новый лист №2:
Теперь переходим к работе со строками.
Удаление дубликатов
Поскольку в задаче не предполагается сопоставление помесячных срезов, дубликаты нам не потребуются.
Для удаления дубликатов делаем активным лист №2 → из таблицы → выделить колонку itemID →
удалить строки → удалить дубликаты:
itemID - это номера объявлений. Удаление строк с дублирующимися номерами объявлений означает, что будут удалены записи одного и того же объявления, опубликованного в течение разных месяцев.
В завершение размещаем результат на новый лист №3 → закрыть и загрузить.
Удаление лишних категорий
В исходных данных собрана продажа, покупка и аренда. Ограничимся данными о продаже.
Также не станем рассматривать специфику новостроек, оставим только вторичный рынок.
Для этой операции делаем активным лист №3 → из таблицы → открыть фильтр колонки Офер → оставить галку Продам:
Аналогично в колонке Рынок снять галку Новостройка:
В завершение размещаем результат на новый лист №4: закрыть и загрузить.
На этом данные считаем подготовленными для анализа.
Отметим, что для подготовки данных потребовалось только немного пощелкать мышкой. Формулы не использовались совсем.
5. Анализ
Группы этажности
Для общего понимания города в аспекте этажности рассмотрим распределение зданий по этажности и размещение этажности на территории.
Делаем активным лист №4 → из таблицы → выделяем столбец Этажность → группировать:
→ В таблице после группировки выбираем столбец Этажность → сортировать:
В завершение размещаем результат на новый лист №5: закрыть и загрузить.
На листе №5 теперь расположена таблица:
Для наглядного представления построим на этих данных график:
Как видим из графика, город преимущественно пятиэтажный. Помня о том, что данные парсились по запросу для Брянской области, можно предположить, что малоэтажки в значительной степени размазаны по территории всей области.
Следующую по численности группу составляют 9 – 10 –этажки. Последняя по количеству группа состоит из 14 – 17 –этажек.
Из визуальной оценки можно отметить, что каждой группе этажности соответствует свой исторический период. Каждый период характеризуется не только преобладающими нормами и технологией строительства, но и отношениями между властью и обществом (насколько можно уплотнить граждан на квадратном метре застройки), и преобладающим контингентом (пенсионеры, потомки тружеников промышленности, баловни периода «до», гетто времен «после», и т.д.).
Исходя из первичной оценки групп этажности сформируем внутри них территориальные кластеры. Благодаря этому будут получены однородные группы объектов с минимальным разбросом характеристик. Это позволит лучше выделить влияние фактора первого/последнего этажа благодаря низкому уровню шума от факторов местонахождения и этажности.
Разделим данные на группы этажности:
На графике:
Кластеры по территории
Подготовим данные для построения графика кластера для группы этажности 1 - 3.
Делаем активным лист №4 → из таблицы → выбрать столбцы → (удерживая Ctrl) широта, долгота, этажность.
В завершение размещаем результат на новый лист №6: закрыть и загрузить.
Делаем активным лист №6 → из таблицы → открыть фильтр колонки Этажность → оставить галки 1,2 и 3 → закрыть и загрузить в лист 7.1-3:
Аналогично загружаем данные для группы 4 – 7 на лист 7.4-7, группы 8 – 12 на лист 7.8-12 и группы 13 – 19 на лист 7.13-19.
Далее создаем лист №8.1-3 и добавляем в него шаблон карты. Работа с картой подробно разобрана в предыдущей статье.
В шаблон карты вставляем ссылку на столбцы А:В в листе №7.1-3, синий цвет RGB имеет код 0,0,255.
В результате получаем пустую карту где-то в Белоруссии:
Это произошло оттого, что не стали чистить данные в колонках Широта и Долгота. Пустая карта не является проблемой → нужно позумить карту колесом мышки до тех пор, пока на ней не появятся отметки:
Затем перетащить мышкой Брянск в центр карты и отзумить масштаб обратно:
Как и предполагалось, малоэтажные здания не формируют кварталы сплошной застройки.
Аналогично построим график на карте для остальных групп этажности.
Этажность 4 – 7 за пределами города сконцентрирована в районных центрах:
В городе этажность 4 - 7 расположена кварталами сплошной застройки:
Определим территориальный кластер на перекрестке улиц Литейная и Ново-Советская:
Откроем это место на сайте 2gis.ru и определим координаты центра кластера:
Там же воспользуемся инструментом Линейка и измерим размер кластера (радиус окружности, вмещающей жилые строения):
С помощью этих данных можно определить окружность, включающую в себя объекты территориального кластера. Окружность используется для отбора входящих в кластер данных.
Аналогично повторим с территориальным кластером между улицами Костычева и Красноармейская – Авиационная:
Поскольку кластер очень вытянутый, для его определения используем две окружности: 1) с центром 53.261924° 34.32897° и радиусом 700 метров, 2) с центром 53.253313° 34.334564° и радиусом 650 метров.
Построим выбранные территориальные кластеры на карте.
Воспользуемся функцией Дистанция:
В кластер войдут те точки, дистанция от которых до центра кластера меньше радиуса окружности.
На листе №9.4-7 запишем сводку определяющих кластеры окружностей:
По этим данным добавим на листе №7.4-7 новые столбцы с формулой для расчета. Если дистанция от точки до центра окружности меньше радиуса, то точка входит в кластер:
Для формирования выборок точек, входящих и не входящих в территориальные кластеры делаем активным лист №7.4-7 → из таблицы → в фильтре колонки Кластер 1 снять галку FALSE → удалить все столбцы, кроме широты и долготы → закрыть и загрузить → назвать новый лист 10.4-7.1.
Аналогично из кластера 2-1 загружаем в лист №10.4-7.21 и из кластера 2-2 загружаем в лист №10.4-7.22. Затем объединяем их в лист №10.4-7.2.
Чтобы получить выборку данных, не вошедших в территориальные кластеры, из таблицы на листе 7.4-7 вычитаем таблицы 10.4-7.1, 10.4-7.21 и 10.4-7.22 и загружаем в лист 10.4-7.0.
Заполняем шаблон карты:
Отображаем результат:
Аналогично для этажности 8 – 12:
И для 13 – 19:
В заключение – все кластеры вместе:
Все использованные данные:
Стоит обратить внимание на то, что для подготовки данных потребовалась только одна формула.
Подготовленные данные далее будут использованы для расчета скидки на этаж во второй части инструкции.
Файл с данными и с расчетами (в следующей части также скидки для первого и последнего этажа) можно скачать здесь.