02_team_hak_19_10_data_gathering.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "02_team_hak_19_10_data_gathering.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "XICXLjFhL5Fu",
        "2_2X5s_UMnjd",
        "09XOPXOvM1KN",
        "QJuBsM6MNZGN",
        "-7l27Ju6OAah",
        "G-RmnpqOOLQF",
        "-JbCm-JtOoLx"
      ],
      "authorship_tag": "ABX9TyOfvOWz9Dj5lHYmVlQoMSHx",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Egozit/ds_master_dataton/blob/main/02_team_hak_19_10_data_gathering.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v6zUvl9HLxkH"
      },
      "source": [
        "## Imports"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ynb-AM3sLYAB"
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import requests \n",
        "import re\n",
        "from geopy.geocoders import Nominatim \n",
        "import json\n",
        "from lxml import etree, objectify\n",
        "from bs4 import BeautifulSoup\n",
        "from pandas.io.json import json_normalize \n",
        "import statistics\n",
        "from geopy.distance import distance"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XICXLjFhL5Fu"
      },
      "source": [
        "## Functions"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MRFcaSffL3ad"
      },
      "source": [
        "def html_table_read(html_table, to_erase=None):\n",
        "    \"\"\"\n",
        "    This funtion transform BeautifulSoup tag object with table\n",
        "    into list of columns. This version removes references from\n",
        "    column names only.\n",
        "    TODO: 1. Add references check\n",
        "    TODO: 2. Refactoring is needed\n",
        "    :param bs4.tag html_table:\n",
        "    :param list to_erase: [optional] list of symbols which must\n",
        "                                     be removed from the results\n",
        "                                     (Default = [])\n",
        "    :return: list (Content of the table),\n",
        "             list (Content of the column row)\n",
        "    \"\"\"\n",
        "\n",
        "    if to_erase is None:\n",
        "        to_erase = []\n",
        "    column_names = []\n",
        "    table_content = []\n",
        "\n",
        "    html_rows = html_table.find_all('tr')\n",
        "    raw_column_names = html_rows[0].find_all('th')\n",
        "\n",
        "    for element in raw_column_names:\n",
        "        if element.find('sup') is not None:\n",
        "            element.sup.decompose()\n",
        "        name = re.sub(\"<.*?>\", \"\", str(element))\n",
        "        column_names.append(name)\n",
        "\n",
        "    for row in html_rows:\n",
        "        if row.td is not None:\n",
        "            current_row = []\n",
        "            row_elements = row.find_all('td')\n",
        "            for element in range(0, len(row_elements)):\n",
        "                value = re.sub(\"<.*?>\", \"\", str(row_elements[element]))\n",
        "                for symbol in to_erase:\n",
        "                    value = value.replace(symbol, \"\")\n",
        "                current_row.append(value)\n",
        "            table_content.append(current_row)\n",
        "    return (table_content, column_names)\n",
        "\n",
        "\n",
        "def get_cov_area_tag_df(html_table):\n",
        "    \"\"\"\n",
        "    This function parses data from html file into two-dimensional list.\n",
        "    \"\"\"\n",
        "    table = []\n",
        "    tag_list = html_table.find(\n",
        "        'div', style=\"text-align: justify;\").find_all('td')\n",
        "\n",
        "    for tag in tag_list:\n",
        "        table.append(tag.span.text)\n",
        "\n",
        "    num_el = 7\n",
        "    table = [table[i:i + num_el] for i in range(0, len(table), num_el)]\n",
        "    return table\n",
        "\n",
        "\n",
        "def transliterate(name):\n",
        "    \"\"\"\n",
        "    Author: LarsKort\n",
        "    https://gist.github.com/ledovsky/6398962\n",
        "    Date: 16/07/2011; 1:05 GMT-4;\n",
        "    This function replaces Cyrillic characters with the corresponding \n",
        "    Latin characters.\n",
        "    \"\"\"\n",
        "    # Dictionary with replacements\n",
        "    replacement = {'а': 'a', 'б': 'b', 'в': 'v', 'г': 'g', 'д': 'd', 'е': 'e',\n",
        "              'ё': 'e', 'ж': 'zh', 'з': 'z', 'и': 'i', 'й': 'y', 'к': 'k',\n",
        "              'л': 'l', 'м': 'm', 'н': 'n', 'о': 'o', 'п': 'p', 'р': 'r',\n",
        "              'с': 's', 'т': 't', 'у': 'u', 'ф': 'f', 'х': 'h', 'ц': 'c',\n",
        "              'ч': 'cz', 'ш': 'sh', 'щ': 'scz', 'ъ': '', 'ы': 'y', 'ь': '',\n",
        "              'э': 'e', 'ю': 'u', 'я': 'ja', 'А': 'A', 'Б': 'B', 'В': 'V',\n",
        "              'Г': 'G', 'Д': 'D', 'Е': 'E', 'Ё': 'E', 'Ж': 'ZH', 'З': 'Z',\n",
        "              'И': 'I', 'Й': 'I', 'К': 'K', 'Л': 'L', 'М': 'M', 'Н': 'N',\n",
        "              'О': 'O', 'П': 'P', 'Р': 'R', 'С': 'S', 'Т': 'T', 'У': 'U',\n",
        "              'Ф': 'F', 'Х': 'H', 'Ц': 'C', 'Ч': 'CZ', 'Ш': 'SH', 'Щ': 'SCH',\n",
        "              'Ъ': '', 'Ы': 'y', 'Ь': '', 'Э': 'E', 'Ю': 'U', 'Я': 'YA',\n",
        "              'ґ': '', 'ї': '', 'є': '', 'Ґ': 'g', 'Ї': 'i', 'Є': 'e', \n",
        "              '«':'\"','»':'\"'}\n",
        "\n",
        "    # Replacing every character from the dictionary\n",
        "    for key in replacement:\n",
        "        name = name.replace(key, replacement[key])\n",
        "    return name\n",
        "\n",
        "\n",
        "def replace_symbol_in_columns(dataframe, lst_columns, find_sym, new_sym):\n",
        "    \"\"\"\n",
        "    This function replaces symbol find_sym with new_sym in dataframe \n",
        "    in columns specified in lst_columns \n",
        "    \"\"\"\n",
        "    for col in lst_columns:\n",
        "        dataframe[col] = dataframe[col].apply(\n",
        "            lambda x: re.sub(find_sym, new_sym, str(x)))\n",
        "\n",
        "def contat_unique(x, sep=', ', lowercase=False):\n",
        "    if lowercase == True:\n",
        "        x = x.str.lower()\n",
        "    return set(sep.join(x).split(sep))\n",
        "\n",
        "def set_to_str(x):\n",
        "    return str(x).replace(\"'\",'').strip('{}')\n",
        "\n",
        "def series_to_list(df):\n",
        "    return df.values.tolist()\n",
        "\n",
        "def add_labels(x, st_type=False):\n",
        "    if st_type == True:\n",
        "        labels = temp_tpu_data.iloc[x.index]['Station_type']\n",
        "        return dict(zip(x, labels))\n",
        "    else:\n",
        "        labels = temp_tpu_data.iloc[x.index]['global_id']\n",
        "        return dict(zip(labels, x))\n",
        "\n",
        "def find_mean_coordinate(x):\n",
        "    x['center'] = np.mean(list(x.values()))\n",
        "    return x\n",
        "\n",
        "def add_new_status(x):\n",
        "    if len(x)>1:\n",
        "        if 'построен' in list(x.values()):\n",
        "            x = 'частично построен'\n",
        "        else:\n",
        "            x = 'строится'\n",
        "    else:\n",
        "        x = list(x.values())[0]\n",
        "    return x\n",
        "\n",
        "def return_max_year(x):\n",
        "    x = max(list(x.values()))\n",
        "    return x\n",
        "\n",
        "def add_global_id(df, col):\n",
        "    temp_col = df[col].apply(lambda x : abs(hash(x)))\n",
        "    if len(df[col]) == len(temp_col.unique()):\n",
        "        return temp_col\n",
        "    else:\n",
        "        return add_random(col)\n",
        "    \n",
        "def add_random(col):\n",
        "    length = len(col)\n",
        "    new_col = col + np.random.randint(0, length**2, length)\n",
        "    if len(col) == len(new_col.unique()):\n",
        "        return new_col\n",
        "    else:\n",
        "        return add_random(new_col)\n",
        "\n",
        "def boundaries(arr, val, rad):\n",
        "    arr = np.array(arr)\n",
        "    idx_min = np.argmin(abs(arr-(val-rad)))\n",
        "    idx_max = np.argmin(abs(arr-(val+rad)))\n",
        "    return idx_min, idx_max\n",
        "\n",
        "\n",
        "def count_dist(x_arr, p_lat, p_lon):\n",
        "    dist_l = []\n",
        "    for i in x_arr:\n",
        "        dist = distance([i[1], i[2]], [p_lat, p_lon]).m\n",
        "        if dist == 0:\n",
        "            return i[0], dist\n",
        "        else:\n",
        "            dist_l.append([i[0], dist])\n",
        "    dist_l.sort(key=lambda x: x[1])\n",
        "    return dist_l[0]\n",
        "\n",
        "def get_linesname(html_table):\n",
        "    \"\"\"\n",
        "    This function parses Moscow metro lines numbers\n",
        "    from html file into a list.\n",
        "    \"\"\"\n",
        "    table = []\n",
        "    for tag in soup.find_all('td'):\n",
        "        if tag.find('div', {'class': 'oneline'}):\n",
        "            table.append([tag.div.div.get('id').replace('line', ''), \n",
        "                          tag.div.text])\n",
        " \n",
        "    return table\n",
        "\n",
        "\n",
        "def parse_passenger_turnover(url_part):\n",
        "    \"\"\"\n",
        "    This function parses passenger turnover for each \n",
        "    station for every line: url_part.\n",
        "    \"\"\"\n",
        "    main_domain_url = 'https://www.metro-msk.ru/stancii-metro/liniya-' + url_part + '/'\n",
        "    headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:81.0) Gecko/20100101 Firefox/81.0'}\n",
        "\n",
        "    coverage_area_response = requests.get(main_domain_url, headers=headers).text\n",
        "    soup = BeautifulSoup(coverage_area_response, 'html.parser')\n",
        "\n",
        "    passenger_tr = []\n",
        "\n",
        "    for tag in soup.find_all('td'):\n",
        "        found = tag.find('div', id='linesname')\n",
        "        if found:   \n",
        "            key = found.parent.find_next('td').find('a')\n",
        "            if not key:\n",
        "                continue\n",
        "            key = key.text\n",
        "            value = found.parent.find_next('td').find('font')\n",
        "            try:\n",
        "                value = value.text\n",
        "            except AttributeError:\n",
        "                value = ''\n",
        "            passenger_tr.append([key, value])\n",
        "\n",
        "    return passenger_tr\n",
        "\n",
        "\n",
        "def clean_passenger_tr_df(raw_passenger_turnover):\n",
        "    '''\n",
        "    This function cleans parsed passenger turnover data\n",
        "    and returns a tidy dataframe \n",
        "    '''\n",
        "    station_list, turnover_list, line_list = [], [], []\n",
        "    for el in raw_passenger_turnover:\n",
        "        for line, stations in el.items():\n",
        "            for station in stations:\n",
        "                station_list.append(station[0])\n",
        "                turnover_list.append(station[1])\n",
        "                line_list.append(line)\n",
        "     \n",
        "    passenger_turnover = pd.DataFrame(list(zip(line_list, station_list, \n",
        "                                               turnover_list)), \n",
        "                                               columns=['line','station', \n",
        "                                               'passengers_per_day'])\n",
        "\n",
        "    symbol_to_replace = {\n",
        "        ' чел. в сутки' : '',\n",
        "        'тыс.' : '',\n",
        "        ' ' : '',\n",
        "        '\\.' : '',\n",
        "        '\\t' : '',\n",
        "    }\n",
        "\n",
        "    for key, value in symbol_to_replace.items():\n",
        "        replace_symbol_in_columns(passenger_turnover, ['passengers_per_day'], \n",
        "                                  key, value)\n",
        "\n",
        "    passenger_turnover['passengers_per_day'] = pd.to_numeric(\n",
        "        passenger_turnover['passengers_per_day'], errors='coerce'\n",
        "        )\n",
        "    passenger_turnover.drop(passenger_turnover.loc[passenger_turnover['line'] ==\n",
        "                                                   'МиниметроВыставочная, ' + \n",
        "                                                   'Международная'].index, \n",
        "                                                    inplace=True)\n",
        "                                                   \n",
        "    return passenger_turnover         "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gFt6d9GdMd9F"
      },
      "source": [
        "# Data preparation\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2_2X5s_UMnjd"
      },
      "source": [
        "## Moscow districts data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IZByMQJXMkx2"
      },
      "source": [
        "# Load Moscow districts table from Wikipedia\n",
        "target_url = ('https://ru.wikipedia.org/wiki/%D0%A1%D0%BF%D0%B8%D1%' +\n",
        "               '81%D0%BE%D0%BA_%D1%80%D0%B0%D0%B9%D0%BE%D0%BD%D0%BE%' +\n",
        "               'D0%B2_%D0%B8_%D0%BF%D0%BE%D1%81%D0%B5%D0%BB%D0%B5%D0' +\n",
        "               '%BD%D0%B8%D0%B9_%D0%9C%D0%BE%D1%81%D0%BA%D0%B2%D1%8B')\n",
        "\n",
        "to_drop = [' ', '\\t', '\\n', '\\xa0']\n",
        "\n",
        "districts_response = requests.get(target_url).content.decode(encoding='utf-8')\n",
        "\n",
        "soup = BeautifulSoup(districts_response)\n",
        "\n",
        "dist_html_table = soup.find('table')\n",
        "\n",
        "table_content, column_names = html_table_read(dist_html_table, to_drop)\n",
        "\n",
        "\n",
        "raw_districts_df = pd.DataFrame(table_content, columns=column_names)\n",
        "raw_districts_df.drop(columns=['№',\n",
        "                               'Флаг',\n",
        "                               'Герб',\n",
        "                               'Название района/поселения[3][4]',\n",
        "                               'Адми-нистра-тивныйокруг'],\n",
        "                      inplace=True,\n",
        "                      errors='ignore')\n",
        "raw_districts_df.columns = ['object_district_name',\n",
        "                            'object_district_square_m2',\n",
        "                            'object_district_population',\n",
        "                            'object_district_population_density',\n",
        "                            'object_district_living_space_m2',\n",
        "                            'object_district_living_space_m2_per_person']\n",
        "\n",
        "# Cast data types \n",
        "raw_districts_df['object_district_population_density'] = raw_districts_df.\\\n",
        "                                        object_district_population_density.\\\n",
        "                                        astype('float64')\n",
        "\n",
        "# Replace symbols\n",
        "columns_list = ['object_district_square_m2',\n",
        "                'object_district_living_space_m2',\n",
        "                'object_district_living_space_m2_per_person']\n",
        "for col in columns_list:\n",
        "    raw_districts_df[col] = pd.to_numeric(\n",
        "        raw_districts_df[col].apply(\n",
        "            lambda x: re.sub(',', '.', str(x))))\n",
        "\n",
        "raw_districts_df['object_district_population'] = pd.to_numeric(\n",
        "    raw_districts_df['object_district_population'].apply(\n",
        "        lambda x: re.sub('↗', ' ', str(x))))\n",
        "\n",
        "# Convert kilometers to meters\n",
        "columns_list = ['object_district_square_m2', 'object_district_living_space_m2']\n",
        "raw_districts_df[columns_list] = raw_districts_df[columns_list]*1000\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "X9maZY8pMwZi",
        "outputId": "684f3c97-c527-4eb1-84e5-7409cc7279f1"
      },
      "source": [
        "raw_districts_df.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 146 entries, 0 to 145\n",
            "Data columns (total 6 columns):\n",
            " #   Column                                      Non-Null Count  Dtype  \n",
            "---  ------                                      --------------  -----  \n",
            " 0   object_district_name                        146 non-null    object \n",
            " 1   object_district_square_m2                   146 non-null    float64\n",
            " 2   object_district_population                  146 non-null    int64  \n",
            " 3   object_district_population_density          146 non-null    float64\n",
            " 4   object_district_living_space_m2             125 non-null    float64\n",
            " 5   object_district_living_space_m2_per_person  125 non-null    float64\n",
            "dtypes: float64(4), int64(1), object(1)\n",
            "memory usage: 7.0+ KB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 218
        },
        "id": "j_qfG7qMMyj9",
        "outputId": "c56658f8-fd11-4163-90f4-840f529e0e8e"
      },
      "source": [
        "raw_districts_df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>object_district_name</th>\n",
              "      <th>object_district_square_m2</th>\n",
              "      <th>object_district_population</th>\n",
              "      <th>object_district_population_density</th>\n",
              "      <th>object_district_living_space_m2</th>\n",
              "      <th>object_district_living_space_m2_per_person</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Академический</td>\n",
              "      <td>5830.0</td>\n",
              "      <td>110038</td>\n",
              "      <td>18874.44</td>\n",
              "      <td>2467000.0</td>\n",
              "      <td>22.7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Алексеевский</td>\n",
              "      <td>5290.0</td>\n",
              "      <td>80634</td>\n",
              "      <td>15242.72</td>\n",
              "      <td>1607900.0</td>\n",
              "      <td>20.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Алтуфьевский</td>\n",
              "      <td>3250.0</td>\n",
              "      <td>57697</td>\n",
              "      <td>17752.92</td>\n",
              "      <td>839300.0</td>\n",
              "      <td>15.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Арбат</td>\n",
              "      <td>2110.0</td>\n",
              "      <td>36308</td>\n",
              "      <td>17207.58</td>\n",
              "      <td>731000.0</td>\n",
              "      <td>26.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Аэропорт</td>\n",
              "      <td>4580.0</td>\n",
              "      <td>79541</td>\n",
              "      <td>17367.03</td>\n",
              "      <td>1939700.0</td>\n",
              "      <td>25.9</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  object_district_name  ...  object_district_living_space_m2_per_person\n",
              "0        Академический  ...                                        22.7\n",
              "1         Алексеевский  ...                                        20.5\n",
              "2         Алтуфьевский  ...                                        15.5\n",
              "3                Арбат  ...                                        26.0\n",
              "4             Аэропорт  ...                                        25.9\n",
              "\n",
              "[5 rows x 6 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "09XOPXOvM1KN"
      },
      "source": [
        "## Store coverage areas data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5mvZv5zzM49R"
      },
      "source": [
        "# Load store coverage areas table\n",
        "target_url = 'http://www.arhitrade.com/education.php?Id=43'\n",
        " \n",
        "headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:81.0)' +\n",
        "                         'Gecko/20100101 Firefox/81.0'}\n",
        " \n",
        "coverage_area_response = requests.get(target_url, headers=headers).text\n",
        "soup = BeautifulSoup(coverage_area_response, 'html.parser')\n",
        "\n",
        "raw_cov_area_df = get_cov_area_tag_df(soup)\n",
        "cov_area_df = pd.DataFrame(raw_cov_area_df[1:], columns=raw_cov_area_df[0])\n",
        " \n",
        "# Rename columns\n",
        "cov_area_df.columns = ['shop_type', 'shop_square', 'access_time_on_foot',\n",
        "                       'access_time_transport', 'max_reach_distance',\n",
        "                       'real_reach_distance', 'coverage_radius']\n",
        " \n",
        "# Replace symbols\n",
        "symbol_to_replace = {\n",
        "    '[~км]': ['max_reach_distance', 'real_reach_distance',\n",
        "              'coverage_radius'],\n",
        "    '[докв.минутблее]': ['shop_square', 'access_time_on_foot',\n",
        "                         'access_time_transport'],\n",
        "    '[ \\xa0]': ['shop_square', 'access_time_on_foot',\n",
        "                'access_time_transport', 'max_reach_distance',\n",
        "                'real_reach_distance', 'coverage_radius']}\n",
        " \n",
        "for key, value in symbol_to_replace.items():\n",
        "    replace_symbol_in_columns(cov_area_df, value, key, '')\n",
        " \n",
        "replace_symbol_in_columns(\n",
        "    cov_area_df, ['max_reach_distance', 'coverage_radius'], ',', '.')\n",
        " \n",
        "# Averaging of intervals  \n",
        "for idx, el in enumerate(cov_area_df.shop_square):\n",
        "    cov_area_df.shop_square[idx] = statistics.mean([*map(int, el.split('–'))])\n",
        " \n",
        "# Cast data types \n",
        "columns_list = ['access_time_on_foot', 'access_time_transport', 'shop_square',\n",
        "                'max_reach_distance', 'real_reach_distance', 'coverage_radius']\n",
        " \n",
        "for column in columns_list:\n",
        "    cov_area_df[column] = pd.to_numeric(cov_area_df[column], errors='coerce',\n",
        "                                        downcast='float')\n",
        " \n",
        "# Convert kilometers to meters\n",
        "columns_list = ['max_reach_distance', 'real_reach_distance', 'coverage_radius']\n",
        "cov_area_df[columns_list] = cov_area_df[columns_list] * 1000\n",
        " \n",
        "# Add store class information\n",
        "cov_area_df['shop_class'] = [0, 1, 2, 3]\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3GrMIuMgM_S0",
        "outputId": "f539a9c7-4faf-4de2-8a42-5241ecba16db"
      },
      "source": [
        "cov_area_df.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 4 entries, 0 to 3\n",
            "Data columns (total 8 columns):\n",
            " #   Column                 Non-Null Count  Dtype  \n",
            "---  ------                 --------------  -----  \n",
            " 0   shop_type              4 non-null      object \n",
            " 1   shop_square            4 non-null      float32\n",
            " 2   access_time_on_foot    4 non-null      float32\n",
            " 3   access_time_transport  3 non-null      float32\n",
            " 4   max_reach_distance     4 non-null      float32\n",
            " 5   real_reach_distance    4 non-null      float32\n",
            " 6   coverage_radius        4 non-null      float32\n",
            " 7   shop_class             4 non-null      int64  \n",
            "dtypes: float32(6), int64(1), object(1)\n",
            "memory usage: 288.0+ bytes\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gKMFXUcWNDJ9",
        "outputId": "c263c4fe-a280-4b77-b516-595aee9e781e"
      },
      "source": [
        "cov_area_df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>shop_type</th>\n",
              "      <th>shop_square</th>\n",
              "      <th>access_time_on_foot</th>\n",
              "      <th>access_time_transport</th>\n",
              "      <th>max_reach_distance</th>\n",
              "      <th>real_reach_distance</th>\n",
              "      <th>coverage_radius</th>\n",
              "      <th>shop_class</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Магазин шаговой доступности   (мини-маркет)</td>\n",
              "      <td>300.0</td>\n",
              "      <td>15.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1500.0</td>\n",
              "      <td>1000.0</td>\n",
              "      <td>1000.0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Универсальный   магазин (миди-маркет, универма...</td>\n",
              "      <td>450.0</td>\n",
              "      <td>30.0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>3700.0</td>\n",
              "      <td>2000.0</td>\n",
              "      <td>2500.0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Средние   специализированные и универсальные м...</td>\n",
              "      <td>1300.0</td>\n",
              "      <td>60.0</td>\n",
              "      <td>15.0</td>\n",
              "      <td>7700.0</td>\n",
              "      <td>4000.0</td>\n",
              "      <td>5000.0</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Крупные   специализированные и универсальные м...</td>\n",
              "      <td>2000.0</td>\n",
              "      <td>120.0</td>\n",
              "      <td>30.0</td>\n",
              "      <td>15000.0</td>\n",
              "      <td>10000.0</td>\n",
              "      <td>12000.0</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                           shop_type  ...  shop_class\n",
              "0        Магазин шаговой доступности   (мини-маркет)  ...           0\n",
              "1  Универсальный   магазин (миди-маркет, универма...  ...           1\n",
              "2  Средние   специализированные и универсальные м...  ...           2\n",
              "3  Крупные   специализированные и универсальные м...  ...           3\n",
              "\n",
              "[4 rows x 8 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D9uB-gNONLzE"
      },
      "source": [
        "## Transport hubs and stores data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mTYOe8oENMyM"
      },
      "source": [
        "# Load transports hubs table and stores tabels \n",
        "target_url = 'https://op.mos.ru/EHDWSREST/catalog/export/get?id=872986'\n",
        "trade_place_data = pd.read_json(target_url, compression='zip',\n",
        "                                encoding='windows-1251')\n",
        "\n",
        "target_url = 'https://op.mos.ru/EHDWSREST/catalog/export/get?id=955944'\n",
        "tpu_data = pd.read_csv(target_url, compression='zip',\n",
        "                       encoding='windows-1251', sep=';').drop(\n",
        "                           ['Unnamed: 11'], axis=1)\n",
        "target_url = 'https://op.mos.ru/EHDWSREST/catalog/export/get?id=976939'\n",
        "service_place_data = pd.read_json(target_url, compression='zip',\n",
        "                                encoding='windows-1251').drop(\n",
        "                                    columns=['Latitude_WGS84', \n",
        "                                             'Longitude_WGS84'])\n",
        "\n",
        "# Merge stores tables\n",
        "trade_place_data = trade_place_data.append(service_place_data,\n",
        "                                           ignore_index=True)\n",
        "\n",
        "# Extract phone number\n",
        "trade_place_data['PublicPhone'] = trade_place_data['PublicPhone'].apply(\n",
        "    lambda x: x[0]['PublicPhone'])\n",
        "\n",
        "# Split geoData column into two: longitude and latitude\n",
        "trade_place_data['geoData'] = trade_place_data['geoData'].astype(str)\n",
        "trade_place_data['longitude'], trade_place_data['latitude'] = zip(\n",
        "    *trade_place_data.geoData.str.strip(\"{'type': 'Point', 'coordinates': []}\")\n",
        "    .str.split(', '))\n",
        "\n",
        "columns_list = ['latitude', 'longitude']\n",
        "trade_place_data[columns_list] = trade_place_data[columns_list].astype('float')\n",
        "\n",
        "tpu_data['tpu_longitude'], tpu_data['tpu_latitude'] = zip(\n",
        "    *tpu_data.geoData.str.strip('{type=Point, coordinates=[]}')\n",
        "    .str.split(', '))\n",
        "\n",
        "columns_list = ['tpu_latitude', 'tpu_longitude']\n",
        "tpu_data[columns_list] = tpu_data[columns_list].astype('float')\n",
        "\n",
        "# Fixing errors with mixed latitude and longtitude data in TPU dataframe\n",
        "geolocator = Nominatim(user_agent=\"demo\")\n",
        "address = 'Moscow, Russia'\n",
        "location = geolocator.geocode(address)\n",
        "mos_lat = location.latitude\n",
        "mos_lng = location.longitude\n",
        "for name, lat, lng in zip(tpu_data.TPUName, \n",
        "                          tpu_data.tpu_latitude, \n",
        "                          tpu_data.tpu_longitude):\n",
        "    if distance([lat,lng],[mos_lat, mos_lng]).m > 200000:\n",
        "        real_lat, real_lng = tpu_data[tpu_data.TPUName==name]\\\n",
        "                             [['tpu_longitude',\n",
        "                               'tpu_latitude']].iloc[0][0],\\\n",
        "                             tpu_data[tpu_data.TPUName==name]\\\n",
        "                             [['tpu_longitude',\n",
        "                               'tpu_latitude']].iloc[0][1]\n",
        "        tpu_data.loc[tpu_data.TPUName==name,['tpu_longitude']] = real_lng\n",
        "        tpu_data.loc[tpu_data.TPUName==name,['tpu_latitude']] = real_lat\n",
        "\n",
        "# IsNetObject column label encoding: 0/1 - no/yes\n",
        "trade_place_data.loc[trade_place_data.IsNetObject == 'нет', ['IsNetObject']] = 0\n",
        "trade_place_data.loc[trade_place_data.IsNetObject == 'да', ['IsNetObject']] = 1\n",
        "\n",
        "\n",
        "# Add store size information (1 - small, 2 - medium, 3 - large)\n",
        "trade_place_data['PlaceSize'] = 1\n",
        "\n",
        "trade_place_data.loc[trade_place_data.TypeObject.str.contains(\n",
        "    'Супер',  case=False), 'PlaceSize'] = 2\n",
        "trade_place_data.loc[trade_place_data.Name.str.contains(\n",
        "    'Супермаркет', case=False), 'PlaceSize'] = 2\n",
        "\n",
        "large_objects = ['Гипер', \n",
        "                 'Торговые центры', \n",
        "                 'Торговый Дом', \n",
        "                 'ТД ']\n",
        "for obj_name in large_objects:\n",
        "    trade_place_data.loc[trade_place_data.TypeObject.str.contains(\n",
        "        obj_name, case=False), 'PlaceSize'] = 3\n",
        "    trade_place_data.loc[trade_place_data.Name.str.contains(\n",
        "        obj_name, case=False), 'PlaceSize'] = 3\n",
        "\n",
        "# Convert District column to lowercase\n",
        "tpu_data['District'] = tpu_data.District.str.replace(\n",
        "    'район', '').str.strip(' ')\n",
        "trade_place_data['District'] = trade_place_data.District.str.replace(\n",
        "    'район', '').str.strip(' ')\n",
        "\n",
        "# Drop excessive columns\n",
        "trade_place_data.drop(columns=['ID', 'geoData'], inplace=True)\n",
        "tpu_data.drop(columns=['geoData'], inplace=True)\n",
        "\n",
        "# Delete outliers\n",
        "name_list = ['Транспортно-пересадочный узел «Волоколамская» (1)',\n",
        "             'Транспортно-пересадочный узел «Саларьево» (к)']\n",
        "idx = np.where(tpu_data.TPUName.isin(name_list))[0]\n",
        "tpu_data.drop(idx, inplace=True)\n",
        "\n",
        "tpu_data.loc[tpu_data.TPUName =='Транспортно-пересадочный узел «Планерная» (2)',\n",
        "            'TPUName'] = 'Транспортно-пересадочный узел «Планерная Химки» (2)'\n",
        "tpu_data.reset_index(drop=True, inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZRXujXkdNP0d",
        "outputId": "5168d5f4-bde3-47a8-8351-b99f04fa1bcf"
      },
      "source": [
        "trade_place_data.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 78086 entries, 0 to 78085\n",
            "Data columns (total 15 columns):\n",
            " #   Column                       Non-Null Count  Dtype  \n",
            "---  ------                       --------------  -----  \n",
            " 0   global_id                    78086 non-null  int64  \n",
            " 1   WorkingHours                 78086 non-null  object \n",
            " 2   Name                         78086 non-null  object \n",
            " 3   IsNetObject                  78086 non-null  object \n",
            " 4   TypeService                  78086 non-null  object \n",
            " 5   TypeObject                   78086 non-null  object \n",
            " 6   AdmArea                      78086 non-null  object \n",
            " 7   District                     78086 non-null  object \n",
            " 8   Address                      78086 non-null  object \n",
            " 9   PublicPhone                  78086 non-null  object \n",
            " 10  OperatingCompany             19735 non-null  object \n",
            " 11  ClarificationOfWorkingHours  90 non-null     object \n",
            " 12  longitude                    78086 non-null  float64\n",
            " 13  latitude                     78086 non-null  float64\n",
            " 14  PlaceSize                    78086 non-null  int64  \n",
            "dtypes: float64(2), int64(2), object(11)\n",
            "memory usage: 8.9+ MB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 477
        },
        "id": "HQH0pab0NRq0",
        "outputId": "a9c51d23-2579-4917-a2da-c43fcef0dcba"
      },
      "source": [
        "trade_place_data.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>global_id</th>\n",
              "      <th>WorkingHours</th>\n",
              "      <th>Name</th>\n",
              "      <th>IsNetObject</th>\n",
              "      <th>TypeService</th>\n",
              "      <th>TypeObject</th>\n",
              "      <th>AdmArea</th>\n",
              "      <th>District</th>\n",
              "      <th>Address</th>\n",
              "      <th>PublicPhone</th>\n",
              "      <th>OperatingCompany</th>\n",
              "      <th>ClarificationOfWorkingHours</th>\n",
              "      <th>longitude</th>\n",
              "      <th>latitude</th>\n",
              "      <th>PlaceSize</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>637390942</td>\n",
              "      <td>[{'Hours': '09:30-21:30', 'DayOfWeek': 'понеде...</td>\n",
              "      <td>Одежда. Обувь. Промтовары</td>\n",
              "      <td>0</td>\n",
              "      <td>реализация непродовольственных товаров</td>\n",
              "      <td>Магазин «Промтовары»</td>\n",
              "      <td>Северный административный округ</td>\n",
              "      <td>Беговой</td>\n",
              "      <td>город Москва, Беговая аллея, дом 3А</td>\n",
              "      <td>(977) 757-40-37</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.564782</td>\n",
              "      <td>55.783361</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>637391109</td>\n",
              "      <td>[{'Hours': '10:00-21:00', 'DayOfWeek': 'понеде...</td>\n",
              "      <td>АЙКОВЕР</td>\n",
              "      <td>0</td>\n",
              "      <td>реализация непродовольственных товаров</td>\n",
              "      <td>Магазин-салон</td>\n",
              "      <td>Центральный административный округ</td>\n",
              "      <td>Тверской</td>\n",
              "      <td>город Москва, Новослободская улица, дом 50/1, ...</td>\n",
              "      <td>(499) 250-12-40</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.594849</td>\n",
              "      <td>55.786559</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>637391684</td>\n",
              "      <td>[{'Hours': '10:00-22:00', 'DayOfWeek': 'понеде...</td>\n",
              "      <td>Магазин «Радуга»</td>\n",
              "      <td>0</td>\n",
              "      <td>реализация продовольственных товаров</td>\n",
              "      <td>Магазин «Продукты»</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Марьино</td>\n",
              "      <td>город Москва, Марьинский бульвар, дом 4</td>\n",
              "      <td>(909) 977-22-02</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.749819</td>\n",
              "      <td>55.647955</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>637391714</td>\n",
              "      <td>[{'Hours': '10:00-22:00', 'DayOfWeek': 'понеде...</td>\n",
              "      <td>Точка любви</td>\n",
              "      <td>1</td>\n",
              "      <td>реализация непродовольственных товаров</td>\n",
              "      <td>Прочие специализированные непродовольственные ...</td>\n",
              "      <td>Северный административный округ</td>\n",
              "      <td>Беговой</td>\n",
              "      <td>город Москва, Ленинградский проспект, дом 12</td>\n",
              "      <td>(985) 780-51-78</td>\n",
              "      <td>Точка любви</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.578783</td>\n",
              "      <td>55.780188</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>637391844</td>\n",
              "      <td>[{'Hours': 'круглосуточно', 'DayOfWeek': 'поне...</td>\n",
              "      <td>ПРОДУКТЫ</td>\n",
              "      <td>0</td>\n",
              "      <td>реализация продовольственных товаров</td>\n",
              "      <td>Магазин «Продукты»</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Лефортово</td>\n",
              "      <td>город Москва, Волочаевская улица, дом 40Б</td>\n",
              "      <td>(495) 361-17-35</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.678140</td>\n",
              "      <td>55.752482</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   global_id  ... PlaceSize\n",
              "0  637390942  ...         1\n",
              "1  637391109  ...         1\n",
              "2  637391684  ...         1\n",
              "3  637391714  ...         1\n",
              "4  637391844  ...         1\n",
              "\n",
              "[5 rows x 15 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WrchNlJkNTv8",
        "outputId": "b038bc78-5560-4e0d-bca0-d2bf7e05bf0f"
      },
      "source": [
        "tpu_data.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 276 entries, 0 to 275\n",
            "Data columns (total 12 columns):\n",
            " #   Column              Non-Null Count  Dtype  \n",
            "---  ------              --------------  -----  \n",
            " 0   ID                  276 non-null    int64  \n",
            " 1   TPUName             276 non-null    object \n",
            " 2   global_id           276 non-null    int64  \n",
            " 3   AdmArea             276 non-null    object \n",
            " 4   District            276 non-null    object \n",
            " 5   NearStation         276 non-null    object \n",
            " 6   YearOfComissioning  276 non-null    int64  \n",
            " 7   Status              276 non-null    object \n",
            " 8   AvailableTransfer   276 non-null    object \n",
            " 9   CarCapacity         107 non-null    float64\n",
            " 10  tpu_longitude       276 non-null    float64\n",
            " 11  tpu_latitude        276 non-null    float64\n",
            "dtypes: float64(3), int64(3), object(6)\n",
            "memory usage: 26.0+ KB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 512
        },
        "id": "w59eUcvtNVc8",
        "outputId": "c50b92fe-94a0-4a07-9799-163a6ab8e17f"
      },
      "source": [
        "tpu_data.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>ID</th>\n",
              "      <th>TPUName</th>\n",
              "      <th>global_id</th>\n",
              "      <th>AdmArea</th>\n",
              "      <th>District</th>\n",
              "      <th>NearStation</th>\n",
              "      <th>YearOfComissioning</th>\n",
              "      <th>Status</th>\n",
              "      <th>AvailableTransfer</th>\n",
              "      <th>CarCapacity</th>\n",
              "      <th>tpu_longitude</th>\n",
              "      <th>tpu_latitude</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>96</td>\n",
              "      <td>Транспортно-пересадочный узел «Сортировочная» (п)</td>\n",
              "      <td>1769845</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Лефортово</td>\n",
              "      <td>платформа «Сортировочная»</td>\n",
              "      <td>2014</td>\n",
              "      <td>проект</td>\n",
              "      <td>пригородная железная дорога</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.720353</td>\n",
              "      <td>55.764080</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>97</td>\n",
              "      <td>Транспортно-пересадочный узел «Фрезер» (п)</td>\n",
              "      <td>1769846</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Нижегородский</td>\n",
              "      <td>платформа «Фрезер»</td>\n",
              "      <td>2014</td>\n",
              "      <td>построен</td>\n",
              "      <td>пригородная железная дорога</td>\n",
              "      <td>45.0</td>\n",
              "      <td>37.738276</td>\n",
              "      <td>55.745111</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>98</td>\n",
              "      <td>Транспортно-пересадочный узел «Чухлинка» (п)</td>\n",
              "      <td>1769847</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Рязанский</td>\n",
              "      <td>платформа «Чухлинка»</td>\n",
              "      <td>2014</td>\n",
              "      <td>построен</td>\n",
              "      <td>пригородная железная дорога</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.764119</td>\n",
              "      <td>55.733620</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>99</td>\n",
              "      <td>Транспортно-пересадочный узел «Братиславская» (п)</td>\n",
              "      <td>1769848</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Марьино</td>\n",
              "      <td>станция метро «Братиславская»</td>\n",
              "      <td>2015</td>\n",
              "      <td>построен</td>\n",
              "      <td>метро; перехватывающая парковка</td>\n",
              "      <td>320.0</td>\n",
              "      <td>37.750696</td>\n",
              "      <td>55.659641</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100</td>\n",
              "      <td>Транспортно-пересадочный узел «Волжская» (п)</td>\n",
              "      <td>1769849</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Люблино</td>\n",
              "      <td>станция метро «Волжская»</td>\n",
              "      <td>2014</td>\n",
              "      <td>построен</td>\n",
              "      <td>метро; перехватывающая парковка</td>\n",
              "      <td>240.0</td>\n",
              "      <td>37.755894</td>\n",
              "      <td>55.690307</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    ID  ... tpu_latitude\n",
              "0   96  ...    55.764080\n",
              "1   97  ...    55.745111\n",
              "2   98  ...    55.733620\n",
              "3   99  ...    55.659641\n",
              "4  100  ...    55.690307\n",
              "\n",
              "[5 rows x 12 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QJuBsM6MNZGN"
      },
      "source": [
        "## Passenger turnover data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TJKSiqtPNXD9"
      },
      "source": [
        "linename_url = 'https://www.metro-msk.ru/stat/2019/'\n",
        "headers = {'User-Agent': \n",
        "           'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:81.0)' + \n",
        "           'Gecko/20100101 Firefox/81.0'}\n",
        "\n",
        "coverage_area_response = requests.get(linename_url, headers=headers).text\n",
        "soup = BeautifulSoup(coverage_area_response, 'html.parser')\n",
        "\n",
        "raw_linesname = get_linesname(soup)\n",
        "\n",
        "raw_passenger_turnover = []\n",
        "for url_part, line in raw_linesname:\n",
        "    raw_passenger_turnover.append({line : parse_passenger_turnover(url_part)})\n",
        "\n",
        "passenger_turnover = clean_passenger_tr_df(raw_passenger_turnover)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tqWPZCh_Nmod",
        "outputId": "7ad66742-a039-4922-8483-f9696551a6b3"
      },
      "source": [
        "passenger_turnover.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 229 entries, 0 to 228\n",
            "Data columns (total 3 columns):\n",
            " #   Column              Non-Null Count  Dtype  \n",
            "---  ------              --------------  -----  \n",
            " 0   line                229 non-null    object \n",
            " 1   station             229 non-null    object \n",
            " 2   passengers_per_day  215 non-null    float64\n",
            "dtypes: float64(1), object(2)\n",
            "memory usage: 7.2+ KB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8pEXaEQ7NoOE",
        "outputId": "4025c70f-de70-432f-e2f3-5b84e094e400"
      },
      "source": [
        "passenger_turnover.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>line</th>\n",
              "      <th>station</th>\n",
              "      <th>passengers_per_day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Таганско-Краснопресненская</td>\n",
              "      <td>Планерная</td>\n",
              "      <td>56200.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Таганско-Краснопресненская</td>\n",
              "      <td>Сходненская</td>\n",
              "      <td>83200.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Таганско-Краснопресненская</td>\n",
              "      <td>Тушинская</td>\n",
              "      <td>42400.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Таганско-Краснопресненская</td>\n",
              "      <td>Спартак</td>\n",
              "      <td>21000.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Таганско-Краснопресненская</td>\n",
              "      <td>Щукинская</td>\n",
              "      <td>90700.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                         line      station  passengers_per_day\n",
              "0  Таганско-Краснопресненская    Планерная             56200.0\n",
              "1  Таганско-Краснопресненская  Сходненская             83200.0\n",
              "2  Таганско-Краснопресненская    Тушинская             42400.0\n",
              "3  Таганско-Краснопресненская      Спартак             21000.0\n",
              "4  Таганско-Краснопресненская    Щукинская             90700.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rX76l7lvNrws"
      },
      "source": [
        "## Transport hub & Passenger turnover data merging"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Rn5jSdNMNrZE"
      },
      "source": [
        "temp_df = tpu_data.TPUName.str.extract(r'(.*\\«.*?») \\((.*?)\\)')\n",
        "temp_df = temp_df.rename(columns = \n",
        "                         {0:'Name',\n",
        "                          1:'Station_type'}\n",
        "                        )\n",
        "tpu_data_copy = tpu_data.copy()\n",
        "temp_tpu_data = tpu_data.join(temp_df)\n",
        "\n",
        "\n",
        "tpu_data = temp_tpu_data.groupby('Name', as_index=False).agg({\n",
        "                              'global_id': lambda x: add_labels(x,\n",
        "                                                                st_type=True),\n",
        "                              'AdmArea': lambda x: add_labels(x),\n",
        "                              'District': lambda x: add_labels(x),\n",
        "                              'NearStation': lambda x: set_to_str(\n",
        "                                  contat_unique(x)),\n",
        "                              'YearOfComissioning':lambda x: add_labels(x),\n",
        "                              'Status': lambda x: add_labels(x),\n",
        "                              'AvailableTransfer': lambda x: set_to_str(\n",
        "                                  contat_unique(x, sep='; ', lowercase=True )),\n",
        "                              'CarCapacity': sum,\n",
        "                              'tpu_longitude': lambda x: add_labels(x),\n",
        "                              'tpu_latitude': lambda x: add_labels(x)})\n",
        "tpu_data.CarCapacity.replace(0.0, np.nan, inplace=True)\n",
        "\n",
        "tpu_data.tpu_longitude = tpu_data.tpu_longitude.apply(\n",
        "    lambda x: find_mean_coordinate(x))\n",
        "tpu_data.tpu_latitude = tpu_data.tpu_latitude.apply(\n",
        "    lambda x: find_mean_coordinate(x))\n",
        "\n",
        "tpu_data.YearOfComissioning = tpu_data.YearOfComissioning.apply(\n",
        "    lambda x: return_max_year(x))\n",
        "tpu_data.Status = tpu_data.Status.apply(lambda x: add_new_status(x))\n",
        "\n",
        "substr = r'станция метро \\«(.*?)\\»'\n",
        "tpu_data['station'] = tpu_data['NearStation'].str.extract(substr)\n",
        "new_passenger_turnover  = passenger_turnover.groupby(\n",
        "    'station', as_index=False).agg({\n",
        "        'line' : lambda x: set_to_str(contat_unique(x)),\n",
        "        'passengers_per_day' : sum })\n",
        "tpu_data = pd.merge(tpu_data, new_passenger_turnover,  \n",
        "                   on='station', \n",
        "                   how='left')\n",
        "tpu_data.rename(columns = {'global_id' : 'tpu_content',\n",
        "                          'station' : 'subway_station',\n",
        "                          'Name' : 'TPUName'}, inplace=True)\n",
        "\n",
        "tpu_data.insert(2, 'tpu_content_count', tpu_data.tpu_content.apply(len))\n",
        "tpu_data.insert(1, 'global_id', add_global_id(tpu_data, 'TPUName'))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kBm9R0fwqWH_",
        "outputId": "bcde36c8-e658-47f7-b9ea-b5ccaa471d6c"
      },
      "source": [
        "tpu_data.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 207 entries, 0 to 206\n",
            "Data columns (total 16 columns):\n",
            " #   Column              Non-Null Count  Dtype  \n",
            "---  ------              --------------  -----  \n",
            " 0   TPUName             207 non-null    object \n",
            " 1   global_id           207 non-null    int64  \n",
            " 2   tpu_content         207 non-null    object \n",
            " 3   tpu_content_count   207 non-null    int64  \n",
            " 4   AdmArea             207 non-null    object \n",
            " 5   District            207 non-null    object \n",
            " 6   NearStation         207 non-null    object \n",
            " 7   YearOfComissioning  207 non-null    int64  \n",
            " 8   Status              207 non-null    object \n",
            " 9   AvailableTransfer   207 non-null    object \n",
            " 10  CarCapacity         97 non-null     float64\n",
            " 11  tpu_longitude       207 non-null    object \n",
            " 12  tpu_latitude        207 non-null    object \n",
            " 13  subway_station      159 non-null    object \n",
            " 14  line                131 non-null    object \n",
            " 15  passengers_per_day  131 non-null    float64\n",
            "dtypes: float64(2), int64(3), object(11)\n",
            "memory usage: 27.5+ KB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 477
        },
        "id": "Cv2B0OtUi-bM",
        "outputId": "fb38e506-48ec-413f-b546-6c8a7ffe328d"
      },
      "source": [
        "tpu_data.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>TPUName</th>\n",
              "      <th>global_id</th>\n",
              "      <th>tpu_content</th>\n",
              "      <th>tpu_content_count</th>\n",
              "      <th>AdmArea</th>\n",
              "      <th>District</th>\n",
              "      <th>NearStation</th>\n",
              "      <th>YearOfComissioning</th>\n",
              "      <th>Status</th>\n",
              "      <th>AvailableTransfer</th>\n",
              "      <th>CarCapacity</th>\n",
              "      <th>tpu_longitude</th>\n",
              "      <th>tpu_latitude</th>\n",
              "      <th>subway_station</th>\n",
              "      <th>line</th>\n",
              "      <th>passengers_per_day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Транспортно-пересадочный узел «Авиамоторная»</td>\n",
              "      <td>7423989657863085847</td>\n",
              "      <td>{1770036: 'к'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{1770036: 'Юго-Восточный административный округ'}</td>\n",
              "      <td>{1770036: 'Лефортово'}</td>\n",
              "      <td>станция метро «Авиамоторная»</td>\n",
              "      <td>2019</td>\n",
              "      <td>проект</td>\n",
              "      <td>перехватывающая парковка, станция железной дор...</td>\n",
              "      <td>290.0</td>\n",
              "      <td>{1770036: 37.717212999987, 'center': 37.717212...</td>\n",
              "      <td>{1770036: 55.751630999933, 'center': 55.751630...</td>\n",
              "      <td>Авиамоторная</td>\n",
              "      <td>Калининская</td>\n",
              "      <td>399000.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Транспортно-пересадочный узел «Автозаводская»</td>\n",
              "      <td>3851741139439695697</td>\n",
              "      <td>{1769892: 'п'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{1769892: 'Южный административный округ'}</td>\n",
              "      <td>{1769892: 'Даниловский'}</td>\n",
              "      <td>станция метро «Автозаводская»</td>\n",
              "      <td>2014</td>\n",
              "      <td>построен</td>\n",
              "      <td>московская кольцевая железная дорога, метро</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{1769892: 37.657222151552, 'center': 37.657222...</td>\n",
              "      <td>{1769892: 55.706949489906, 'center': 55.706949...</td>\n",
              "      <td>Автозаводская</td>\n",
              "      <td>Замоскворецкая</td>\n",
              "      <td>86600.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Транспортно-пересадочный узел «Академическая»</td>\n",
              "      <td>3704362173907811625</td>\n",
              "      <td>{1769865: 'п', 170429968: 'к'}</td>\n",
              "      <td>2</td>\n",
              "      <td>{1769865: 'Юго-Западный административный округ...</td>\n",
              "      <td>{1769865: 'Академический', 170429968: 'Академи...</td>\n",
              "      <td>станция метро «Академическая»</td>\n",
              "      <td>2018</td>\n",
              "      <td>частично построен</td>\n",
              "      <td>перехватывающая парковка, метро</td>\n",
              "      <td>260.0</td>\n",
              "      <td>{1769865: 37.573083937002, 170429968: 37.57341...</td>\n",
              "      <td>{1769865: 55.687492147998, 170429968: 55.68773...</td>\n",
              "      <td>Академическая</td>\n",
              "      <td>Калужско-Рижская</td>\n",
              "      <td>47700.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Транспортно-пересадочный узел «Алабушево»</td>\n",
              "      <td>4475050148001869354</td>\n",
              "      <td>{1769942: 'п'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{1769942: 'Зеленоградский административный окр...</td>\n",
              "      <td>{1769942: 'Силино'}</td>\n",
              "      <td>платформа «Алабушево»</td>\n",
              "      <td>2014</td>\n",
              "      <td>проект</td>\n",
              "      <td>пригородная железная дорога</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{1769942: 37.1399527786, 'center': 37.1399527786}</td>\n",
              "      <td>{1769942: 56.008723123693, 'center': 56.008723...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Транспортно-пересадочный узел «Алексеевская»</td>\n",
              "      <td>5531869237941398190</td>\n",
              "      <td>{1770013: 'п'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{1770013: 'Северо-Восточный административный о...</td>\n",
              "      <td>{1770013: 'Останкинский'}</td>\n",
              "      <td>станция метро «Алексеевская»</td>\n",
              "      <td>2014</td>\n",
              "      <td>построен</td>\n",
              "      <td>перехватывающая парковка, метро</td>\n",
              "      <td>390.0</td>\n",
              "      <td>{1770013: 37.636681795289, 'center': 37.636681...</td>\n",
              "      <td>{1770013: 55.80891609238, 'center': 55.8089160...</td>\n",
              "      <td>Алексеевская</td>\n",
              "      <td>Калужско-Рижская</td>\n",
              "      <td>29200.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                         TPUName  ...  passengers_per_day\n",
              "0   Транспортно-пересадочный узел «Авиамоторная»  ...            399000.0\n",
              "1  Транспортно-пересадочный узел «Автозаводская»  ...             86600.0\n",
              "2  Транспортно-пересадочный узел «Академическая»  ...             47700.0\n",
              "3      Транспортно-пересадочный узел «Алабушево»  ...                 NaN\n",
              "4   Транспортно-пересадочный узел «Алексеевская»  ...             29200.0\n",
              "\n",
              "[5 rows x 16 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-7l27Ju6OAah"
      },
      "source": [
        "## Table: Distance between store and transport hub"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mUVoSqQzOEO5"
      },
      "source": [
        "# Calculate distance from store to the nearest transport hub\n",
        "tpu_tradeplace_dist_list = []\n",
        "rad = 0.05\n",
        "\n",
        "lat_col = tpu_data['tpu_latitude'].apply(lambda x: x['center'])\n",
        "lon_col = tpu_data['tpu_longitude'].apply(lambda x: x['center'])\n",
        "\n",
        "temp_tpu_lat = list(zip(tpu_data['global_id'],\n",
        "                        lat_col,\n",
        "                        lon_col))\n",
        "temp_tpu_lat.sort(key=lambda x: x[1])\n",
        "\n",
        "for row in range(len(trade_place_data)):\n",
        "    place_id = trade_place_data.iloc[row]['global_id']\n",
        "    p_lat, p_lon = trade_place_data.iloc[row][['latitude', 'longitude']]\n",
        "    min_l, max_l = boundaries([x[1] for x in temp_tpu_lat], p_lat, rad)\n",
        "    tp_id = trade_place_data.iloc[row]['global_id']\n",
        "    if min_l != max_l:\n",
        "        temp_tpu_lon = temp_tpu_lat[min_l:max_l]\n",
        "        temp_tpu_lon.sort(key=lambda x: x[2])\n",
        "        min_l, max_l = boundaries([x[2] for x in temp_tpu_lon], p_lon, rad)\n",
        "        if min_l != max_l:\n",
        "            temp_tpu_lon = temp_tpu_lon[min_l:max_l]\n",
        "            tpu_tradeplace_dist_list.append([place_id] +\n",
        "                                            count_dist(\n",
        "                                                 temp_tpu_lon, p_lat, p_lon))\n",
        "        else:\n",
        "            tpu_tradeplace_dist_list.append([place_id, temp_tpu_lon[min_l][0],\n",
        "                                             distance([temp_tpu_lon[min_l][1],\n",
        "                                                       temp_tpu_lon[min_l][2]],\n",
        "                                                      [p_lat, p_lon]).m])\n",
        "    else:\n",
        "        tpu_tradeplace_dist_list.append([place_id, temp_tpu_lat[min_l][0],\n",
        "                                        distance([temp_tpu_lat[min_l][1],\n",
        "                                                  temp_tpu_lat[min_l][2]],\n",
        "                                                 [p_lat, p_lon]).m])\n",
        "columns_list = ['trade_place_global_id', 'tpu_global_id', 'distance']\n",
        "tpu_tradeplace_dist = pd.DataFrame(tpu_tradeplace_dist_list,\n",
        "                                   columns=columns_list)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-hwl_jiEOGTT",
        "outputId": "1148521b-25e3-472f-a527-a73cf8708442"
      },
      "source": [
        "tpu_tradeplace_dist.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 78086 entries, 0 to 78085\n",
            "Data columns (total 3 columns):\n",
            " #   Column                 Non-Null Count  Dtype  \n",
            "---  ------                 --------------  -----  \n",
            " 0   trade_place_global_id  78086 non-null  int64  \n",
            " 1   tpu_global_id          78086 non-null  int64  \n",
            " 2   distance               78086 non-null  float64\n",
            "dtypes: float64(1), int64(2)\n",
            "memory usage: 1.8 MB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DCAa1RW8OITr",
        "outputId": "206ac46b-6411-4a7b-ae88-9571e90d1639"
      },
      "source": [
        "tpu_tradeplace_dist.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>trade_place_global_id</th>\n",
              "      <th>tpu_global_id</th>\n",
              "      <th>distance</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>637390942</td>\n",
              "      <td>5970351464247905946</td>\n",
              "      <td>947.329127</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>637391109</td>\n",
              "      <td>1297575275508927000</td>\n",
              "      <td>794.746640</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>637391684</td>\n",
              "      <td>8720156415891978423</td>\n",
              "      <td>447.344507</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>637391714</td>\n",
              "      <td>6044955882543811283</td>\n",
              "      <td>418.756267</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>637391844</td>\n",
              "      <td>9061517819343448243</td>\n",
              "      <td>676.612092</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   trade_place_global_id        tpu_global_id    distance\n",
              "0              637390942  5970351464247905946  947.329127\n",
              "1              637391109  1297575275508927000  794.746640\n",
              "2              637391684  8720156415891978423  447.344507\n",
              "3              637391714  6044955882543811283  418.756267\n",
              "4              637391844  9061517819343448243  676.612092"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "G-RmnpqOOLQF"
      },
      "source": [
        "# Final dataset merging"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dGjHrskTOKbb"
      },
      "source": [
        "rent_cost_df = pd.read_excel('https://github.com/Egozit/ds_master_dataton' +\n",
        "                             '/raw/main/data/realty_cost.xlsx', header=1)\n",
        "\n",
        "main_df = pd.merge(trade_place_data, tpu_tradeplace_dist, \n",
        "                   left_on = 'global_id', right_on = 'trade_place_global_id')\n",
        "\n",
        "copy_tpu_data = tpu_data.copy()        \n",
        "copy_tpu_data.columns = ['tpu_' + str(col) if col!='TPUName' else col for col \\\n",
        "                         in copy_tpu_data.columns]\n",
        "\n",
        "# Data merging\n",
        "main_df = pd.merge(main_df, copy_tpu_data,  \n",
        "                   on='tpu_global_id', \n",
        "                   how='left', suffixes=['', '_y'])\n",
        "main_df = pd.merge(main_df, cov_area_df[['shop_class','real_reach_distance']],  \n",
        "                   left_on='PlaceSize', right_on='shop_class', \n",
        "                   how='left', suffixes=['', '_y'])\n",
        "main_df = pd.merge(main_df, raw_districts_df,  \n",
        "                   left_on='District', right_on='object_district_name', \n",
        "                   how='left', suffixes=['', '_y'])\n",
        "main_df = pd.merge(main_df, rent_cost_df,  \n",
        "                   left_on='District', right_on='Район', \n",
        "                   how='left', suffixes=['', '_y'])\n",
        "\n",
        "# Dropping technical columns after merging\n",
        "main_df = main_df.drop(columns=['trade_place_global_id', 'tpu_AdmArea',\n",
        "                                'shop_class', 'object_district_name', 'Район'])\n",
        "\n",
        "# Addind indicator to show if shop is in reach distance of transport hub \n",
        "main_df.insert(loc=3, column='IsInRange', \n",
        "               value=[0 for x in range(main_df.shape[0])])\n",
        "main_df.loc[main_df.distance <= main_df.real_reach_distance, \\\n",
        "            ['IsInRange']] = 1\n",
        "\n",
        "# Adjusting column names\n",
        "main_df.rename(columns={\n",
        "      'global_id': 'object_global_id',\n",
        "      'Name':'object_name',\n",
        "      'IsNetObject':'is_network_object',\n",
        "      'IsInRange':'is_tpu_in_coverage',\n",
        "      'OperatingCompany':'object_operating_company',\n",
        "      'TypeService':'object_service_type',\n",
        "      'TypeObject':'object_type',\n",
        "      'AdmArea':'object_area',\n",
        "      'District':'object_district',\n",
        "      'Address':'object_address',\n",
        "      'PublicPhone':'object_phone',\n",
        "      'WorkingHours':'object_working_hours',\n",
        "      'ClarificationOfWorkingHours':'object_working_hours_clarification',\n",
        "      'PlaceSize':'object_size',\n",
        "      'longitude':'object_longitude',\n",
        "      'latitude':'object_latitude',\n",
        "      'TPUName':'tpu_name',\n",
        "      'tpu_District':'tpu_district',\n",
        "      'tpu_NearStation':'tpu_near_station',\n",
        "      'tpu_YearOfComissioning':'tpu_comissioning_year',\n",
        "      'tpu_Status':'tpu_status',\n",
        "      'tpu_AvailableTransfer':'tpu_available_transfer',\n",
        "      'tpu_CarCapacity':'tpu_car_capacity',\n",
        "      'real_reach_distance':'object_real_reach_distance_t',\n",
        "      'distance': 'distance_to_tpu',\n",
        "      'tpu_tpu_latitude': 'tpu_latitude', \n",
        "      'tpu_line' : 'subway_line',\n",
        "      'tpu_subway_station' : 'subway_station',\n",
        "      'tpu_passengers_per_day' : 'subway_passengers_per_day',\n",
        "      'tpu_tpu_longitude': 'tpu_longitude',\n",
        "      'tpu_tpu_content' : 'tpu_content',\n",
        "      'tpu_tpu_content_count' : 'tpu_content_count',\n",
        "      'Отдельно стоящие здания (м2)': 'object_district_building_property_' +\n",
        "                                      'price_per_m2',\n",
        "      'Торговые помещения (м2)': 'object_district_tradeplace_property_' + \n",
        "                                'price_per_m2',\n",
        "      'Помещения свободного назначения (м2)': 'object_district_generalplace_' +\n",
        "                                              'property_price_per_m2',\n",
        "      'Отдельно стоящие здания (м2).1': 'object_district_building_rent_' + \n",
        "                                        'price_per_m2',\n",
        "      'Торговые помещения (м2).1': 'object_district_tradeplace_rent_' + \n",
        "                                   'price_per_m2',\n",
        "      'Помещения свободного назначения (м2).1': 'object_district_' + \n",
        "                                                'generalplace_' +\n",
        "                                                'rent_price_per_m2'}, \n",
        "      inplace=True)\n",
        "\n",
        "coordinates = ['object_latitude',\n",
        "               'object_longitude']\n",
        "for coordinate in coordinates:\n",
        "    main_df[coordinates] = main_df[coordinates].astype('float64')\n",
        "\n",
        "main_df.insert(loc=16, column='object_real_reach_distance', \n",
        "               value=main_df.object_real_reach_distance_t)\n",
        "main_df = main_df.drop(columns=['object_real_reach_distance_t'])  \n",
        "\n",
        "# Casting tpu_available_transfer column to lower case\n",
        "main_df['tpu_available_transfer'] = main_df.tpu_available_transfer.str.lower()\n",
        "\n",
        "# Fixing some types to int32\n",
        "main_df.is_network_object = main_df.is_network_object.astype('int32')\n",
        "main_df.distance_to_tpu = main_df.distance_to_tpu.astype('int32')\n",
        "\n",
        "mask = main_df['subway_passengers_per_day']==0.0, ['subway_passengers_per_day']\n",
        "main_df.loc[mask] = np.nan\n",
        "\n",
        "# Changing columns position\n",
        "col_names = ['object_working_hours', 'is_tpu_in_coverage',\n",
        "             'object_operating_company', 'object_size',\n",
        "             'distance_to_tpu', 'tpu_name']\n",
        "new_position = [11, 3, 4, 13, 17, 18]\n",
        "\n",
        "for col_name, pos in zip(col_names, new_position):\n",
        "    col = main_df.pop(col_name)\n",
        "    main_df.insert(pos, col.name, col)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qZIeRVrxOkFZ",
        "outputId": "31360610-11bb-4ce2-b810-c9df11f434ca"
      },
      "source": [
        "main_df.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 78086 entries, 0 to 78085\n",
            "Data columns (total 44 columns):\n",
            " #   Column                                              Non-Null Count  Dtype  \n",
            "---  ------                                              --------------  -----  \n",
            " 0   object_global_id                                    78086 non-null  int64  \n",
            " 1   object_name                                         78086 non-null  object \n",
            " 2   is_network_object                                   78086 non-null  int32  \n",
            " 3   is_tpu_in_coverage                                  78086 non-null  int64  \n",
            " 4   object_operating_company                            19735 non-null  object \n",
            " 5   object_service_type                                 78086 non-null  object \n",
            " 6   object_type                                         78086 non-null  object \n",
            " 7   object_area                                         78086 non-null  object \n",
            " 8   object_district                                     78086 non-null  object \n",
            " 9   object_address                                      78086 non-null  object \n",
            " 10  object_phone                                        78086 non-null  object \n",
            " 11  object_working_hours                                78086 non-null  object \n",
            " 12  object_working_hours_clarification                  90 non-null     object \n",
            " 13  object_size                                         78086 non-null  int64  \n",
            " 14  object_longitude                                    78086 non-null  float64\n",
            " 15  object_latitude                                     78086 non-null  float64\n",
            " 16  object_real_reach_distance                          78086 non-null  float32\n",
            " 17  distance_to_tpu                                     78086 non-null  int32  \n",
            " 18  tpu_name                                            78086 non-null  object \n",
            " 19  tpu_global_id                                       78086 non-null  int64  \n",
            " 20  tpu_content                                         78086 non-null  object \n",
            " 21  tpu_content_count                                   78086 non-null  int64  \n",
            " 22  tpu_district                                        78086 non-null  object \n",
            " 23  tpu_near_station                                    78086 non-null  object \n",
            " 24  tpu_comissioning_year                               78086 non-null  int64  \n",
            " 25  tpu_status                                          78086 non-null  object \n",
            " 26  tpu_available_transfer                              78086 non-null  object \n",
            " 27  tpu_car_capacity                                    36604 non-null  float64\n",
            " 28  tpu_longitude                                       78086 non-null  object \n",
            " 29  tpu_latitude                                        78086 non-null  object \n",
            " 30  subway_station                                      66972 non-null  object \n",
            " 31  subway_line                                         58410 non-null  object \n",
            " 32  subway_passengers_per_day                           57297 non-null  float64\n",
            " 33  object_district_square_m2                           58833 non-null  float64\n",
            " 34  object_district_population                          58833 non-null  float64\n",
            " 35  object_district_population_density                  58833 non-null  float64\n",
            " 36  object_district_living_space_m2                     58833 non-null  float64\n",
            " 37  object_district_living_space_m2_per_person          58833 non-null  float64\n",
            " 38  object_district_building_property_price_per_m2      10303 non-null  float64\n",
            " 39  object_district_tradeplace_property_price_per_m2    44479 non-null  float64\n",
            " 40  object_district_generalplace_property_price_per_m2  48173 non-null  float64\n",
            " 41  object_district_building_rent_price_per_m2          12784 non-null  float64\n",
            " 42  object_district_tradeplace_rent_price_per_m2        54603 non-null  float64\n",
            " 43  object_district_generalplace_rent_price_per_m2      58418 non-null  float64\n",
            "dtypes: float32(1), float64(15), int32(2), int64(6), object(20)\n",
            "memory usage: 25.9+ MB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 477
        },
        "id": "RO43axQiOgS1",
        "outputId": "286d5922-734a-4561-ef82-648490cf5822"
      },
      "source": [
        "main_df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>object_global_id</th>\n",
              "      <th>object_working_hours</th>\n",
              "      <th>object_name</th>\n",
              "      <th>is_tpu_in_coverage</th>\n",
              "      <th>is_network_object</th>\n",
              "      <th>object_service_type</th>\n",
              "      <th>object_type</th>\n",
              "      <th>object_area</th>\n",
              "      <th>object_district</th>\n",
              "      <th>object_address</th>\n",
              "      <th>object_phone</th>\n",
              "      <th>object_operating_company</th>\n",
              "      <th>object_working_hours_clarification</th>\n",
              "      <th>object_longitude</th>\n",
              "      <th>object_latitude</th>\n",
              "      <th>object_size</th>\n",
              "      <th>object_real_reach_distance</th>\n",
              "      <th>tpu_global_id</th>\n",
              "      <th>distance_to_tpu</th>\n",
              "      <th>tpu_name</th>\n",
              "      <th>tpu_content</th>\n",
              "      <th>tpu_content_count</th>\n",
              "      <th>tpu_district</th>\n",
              "      <th>tpu_near_station</th>\n",
              "      <th>tpu_comissioning_year</th>\n",
              "      <th>tpu_status</th>\n",
              "      <th>tpu_available_transfer</th>\n",
              "      <th>tpu_car_capacity</th>\n",
              "      <th>tpu_longitude</th>\n",
              "      <th>tpu_latitude</th>\n",
              "      <th>subway_station</th>\n",
              "      <th>subway_line</th>\n",
              "      <th>subway_passengers_per_day</th>\n",
              "      <th>object_district_square_m2</th>\n",
              "      <th>object_district_population</th>\n",
              "      <th>object_district_population_density</th>\n",
              "      <th>object_district_living_space_m2</th>\n",
              "      <th>object_district_living_space_m2_per_person</th>\n",
              "      <th>object_district_building_property_price_per_m2</th>\n",
              "      <th>object_district_tradeplace_property_price_per_m2</th>\n",
              "      <th>object_district_generalplace_property_price_per_m2</th>\n",
              "      <th>object_district_building_rent_price_per_m2</th>\n",
              "      <th>object_district_tradeplace_rent_price_per_m2</th>\n",
              "      <th>object_district_generalplace_rent_price_per_m2</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>637390942</td>\n",
              "      <td>[{'Hours': '09:30-21:30', 'DayOfWeek': 'понеде...</td>\n",
              "      <td>Одежда. Обувь. Промтовары</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>реализация непродовольственных товаров</td>\n",
              "      <td>Магазин «Промтовары»</td>\n",
              "      <td>Северный административный округ</td>\n",
              "      <td>Беговой</td>\n",
              "      <td>город Москва, Беговая аллея, дом 3А</td>\n",
              "      <td>(977) 757-40-37</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.564782</td>\n",
              "      <td>55.783361</td>\n",
              "      <td>1</td>\n",
              "      <td>2000.0</td>\n",
              "      <td>5970351464247905946</td>\n",
              "      <td>947</td>\n",
              "      <td>Транспортно-пересадочный узел «Петровский парк»</td>\n",
              "      <td>{1770010: 'п'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{1770010: 'Аэропорт'}</td>\n",
              "      <td>станция метро «Петровский парк»</td>\n",
              "      <td>2015</td>\n",
              "      <td>проект</td>\n",
              "      <td>перехватывающая парковка, метро</td>\n",
              "      <td>300.0</td>\n",
              "      <td>{1770010: 37.556888576359, 'center': 37.556888...</td>\n",
              "      <td>{1770010: 55.790614556397, 'center': 55.790614...</td>\n",
              "      <td>Петровский парк</td>\n",
              "      <td>Солнцевская, Каховская</td>\n",
              "      <td>NaN</td>\n",
              "      <td>5560.0</td>\n",
              "      <td>42908.0</td>\n",
              "      <td>7717.27</td>\n",
              "      <td>791100.0</td>\n",
              "      <td>18.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>426341.0</td>\n",
              "      <td>378271.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3031.0</td>\n",
              "      <td>2500.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>637391109</td>\n",
              "      <td>[{'Hours': '10:00-21:00', 'DayOfWeek': 'понеде...</td>\n",
              "      <td>АЙКОВЕР</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>реализация непродовольственных товаров</td>\n",
              "      <td>Магазин-салон</td>\n",
              "      <td>Центральный административный округ</td>\n",
              "      <td>Тверской</td>\n",
              "      <td>город Москва, Новослободская улица, дом 50/1, ...</td>\n",
              "      <td>(499) 250-12-40</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.594849</td>\n",
              "      <td>55.786559</td>\n",
              "      <td>1</td>\n",
              "      <td>2000.0</td>\n",
              "      <td>1297575275508927000</td>\n",
              "      <td>794</td>\n",
              "      <td>Транспортно-пересадочный узел «Новослободская»</td>\n",
              "      <td>{1769880: 'п'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{1769880: 'Тверской'}</td>\n",
              "      <td>станция метро «Новослободская»</td>\n",
              "      <td>2014</td>\n",
              "      <td>построен</td>\n",
              "      <td>метро</td>\n",
              "      <td>39.0</td>\n",
              "      <td>{1769880: 37.600518160589, 'center': 37.600518...</td>\n",
              "      <td>{1769880: 55.780175380754, 'center': 55.780175...</td>\n",
              "      <td>Новослободская</td>\n",
              "      <td>Кольцевая</td>\n",
              "      <td>49000.0</td>\n",
              "      <td>7270.0</td>\n",
              "      <td>77947.0</td>\n",
              "      <td>10721.73</td>\n",
              "      <td>1253300.0</td>\n",
              "      <td>16.4</td>\n",
              "      <td>285469.0</td>\n",
              "      <td>987610.0</td>\n",
              "      <td>664251.0</td>\n",
              "      <td>2019.0</td>\n",
              "      <td>4923.0</td>\n",
              "      <td>4050.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>637391684</td>\n",
              "      <td>[{'Hours': '10:00-22:00', 'DayOfWeek': 'понеде...</td>\n",
              "      <td>Магазин «Радуга»</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>реализация продовольственных товаров</td>\n",
              "      <td>Магазин «Продукты»</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Марьино</td>\n",
              "      <td>город Москва, Марьинский бульвар, дом 4</td>\n",
              "      <td>(909) 977-22-02</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.749819</td>\n",
              "      <td>55.647955</td>\n",
              "      <td>1</td>\n",
              "      <td>2000.0</td>\n",
              "      <td>8720156415891978423</td>\n",
              "      <td>447</td>\n",
              "      <td>Транспортно-пересадочный узел «Марьино»</td>\n",
              "      <td>{1769856: 'к'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{1769856: 'Марьино'}</td>\n",
              "      <td>станция метро «Марьино»</td>\n",
              "      <td>2018</td>\n",
              "      <td>проект</td>\n",
              "      <td>перехватывающая парковка, метро</td>\n",
              "      <td>230.0</td>\n",
              "      <td>{1769856: 37.744333999899, 'center': 37.744333...</td>\n",
              "      <td>{1769856: 55.650510000027, 'center': 55.650510...</td>\n",
              "      <td>Марьино</td>\n",
              "      <td>Люблинская</td>\n",
              "      <td>181900.0</td>\n",
              "      <td>11910.0</td>\n",
              "      <td>254142.0</td>\n",
              "      <td>21338.54</td>\n",
              "      <td>4523000.0</td>\n",
              "      <td>18.6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>416755.0</td>\n",
              "      <td>110717.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3493.0</td>\n",
              "      <td>1580.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>637391714</td>\n",
              "      <td>[{'Hours': '10:00-22:00', 'DayOfWeek': 'понеде...</td>\n",
              "      <td>Точка любви</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>реализация непродовольственных товаров</td>\n",
              "      <td>Прочие специализированные непродовольственные ...</td>\n",
              "      <td>Северный административный округ</td>\n",
              "      <td>Беговой</td>\n",
              "      <td>город Москва, Ленинградский проспект, дом 12</td>\n",
              "      <td>(985) 780-51-78</td>\n",
              "      <td>Точка любви</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.578783</td>\n",
              "      <td>55.780188</td>\n",
              "      <td>1</td>\n",
              "      <td>2000.0</td>\n",
              "      <td>6044955882543811283</td>\n",
              "      <td>418</td>\n",
              "      <td>Транспортно-пересадочный узел «Белорусский»</td>\n",
              "      <td>{170430036: 'к'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{170430036: 'Тверской'}</td>\n",
              "      <td>Белорусский вокзал</td>\n",
              "      <td>2020</td>\n",
              "      <td>проект</td>\n",
              "      <td>перехватывающая парковка, станция железной дороги</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{170430036: 37.58183100001, 'center': 37.58183...</td>\n",
              "      <td>{170430036: 55.776841999905, 'center': 55.7768...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>5560.0</td>\n",
              "      <td>42908.0</td>\n",
              "      <td>7717.27</td>\n",
              "      <td>791100.0</td>\n",
              "      <td>18.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>426341.0</td>\n",
              "      <td>378271.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3031.0</td>\n",
              "      <td>2500.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>637391844</td>\n",
              "      <td>[{'Hours': 'круглосуточно', 'DayOfWeek': 'поне...</td>\n",
              "      <td>ПРОДУКТЫ</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>реализация продовольственных товаров</td>\n",
              "      <td>Магазин «Продукты»</td>\n",
              "      <td>Юго-Восточный административный округ</td>\n",
              "      <td>Лефортово</td>\n",
              "      <td>город Москва, Волочаевская улица, дом 40Б</td>\n",
              "      <td>(495) 361-17-35</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.678140</td>\n",
              "      <td>55.752482</td>\n",
              "      <td>1</td>\n",
              "      <td>2000.0</td>\n",
              "      <td>9061517819343448243</td>\n",
              "      <td>676</td>\n",
              "      <td>Транспортно-пересадочный узел «Площадь Ильича»</td>\n",
              "      <td>{1770035: 'п'}</td>\n",
              "      <td>1</td>\n",
              "      <td>{1770035: 'Таганский'}</td>\n",
              "      <td>платформа «Площадь Ильича», станция метро «Пло...</td>\n",
              "      <td>2020</td>\n",
              "      <td>проект</td>\n",
              "      <td>пригородная железная дорога, метро</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{1770035: 37.680626999899, 'center': 37.680626...</td>\n",
              "      <td>{1770035: 55.746568999972, 'center': 55.746568...</td>\n",
              "      <td>Площадь Ильича</td>\n",
              "      <td>Калининская</td>\n",
              "      <td>32700.0</td>\n",
              "      <td>9060.0</td>\n",
              "      <td>95467.0</td>\n",
              "      <td>10537.20</td>\n",
              "      <td>1384200.0</td>\n",
              "      <td>15.2</td>\n",
              "      <td>NaN</td>\n",
              "      <td>370549.0</td>\n",
              "      <td>204294.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1943.0</td>\n",
              "      <td>2084.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   object_global_id  ... object_district_generalplace_rent_price_per_m2\n",
              "0         637390942  ...                                         2500.0\n",
              "1         637391109  ...                                         4050.0\n",
              "2         637391684  ...                                         1580.0\n",
              "3         637391714  ...                                         2500.0\n",
              "4         637391844  ...                                         2084.0\n",
              "\n",
              "[5 rows x 44 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-JbCm-JtOoLx"
      },
      "source": [
        "# Saving dataset example in JSON format"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SGBHqLbUOiMU"
      },
      "source": [
        "# Top 5 rows to show dataset structure\n",
        "result = main_df.head(5).to_json(orient='index', \n",
        "                                 force_ascii=False).encode('utf8')\n",
        "parsed = json.loads(result)\n",
        "with open('data_example.json', 'w', encoding='utf8') as json_file:\n",
        "    json.dump(parsed, json_file, ensure_ascii=False, indent=4)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6MTnrriuOrqh"
      },
      "source": [
        "# Saving the final dataset"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_LSJdFA-Ov3K"
      },
      "source": [
        "#compression_opts = dict(method='zip', archive_name='final_dataset.csv') \n",
        "#main_df.to_csv('final_dataset.zip', compression=compression_opts)\n",
        "main_df.to_csv('final_dataset.csv', sep=';', encoding='cp1251')\n",
        "tpu_data.to_csv('transport_hub.csv', sep=';', encoding='cp1251')"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}