Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "funded-praise",
   "metadata": {},
   "source": [
    "# Data\n",
    "\n",
    "> In this part, data collection, preparation, cleaning and improvement are performed."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "interpreted-madonna",
   "metadata": {},
   "source": [
    "## 1. Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "significant-purple",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "import numpy as np\n",
    "import re\n",
    "\n",
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "\n",
    "from geopy.geocoders import Nominatim\n",
    "from functools import partial"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "spiritual-resident",
   "metadata": {},
   "source": [
    "___\n",
    "## 2. Prepping Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "excited-grace",
   "metadata": {},
   "source": [
    "First, let's process the data from the **cian_Penza.txt** that contains information from ads, in order to obtain and transform the data into a pandas dataframe. In this step, we will use the library **BeautifulSoup**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "infrared-automation",
   "metadata": {},
   "source": [
    "### 2.1. Read Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "criminal-playlist",
   "metadata": {},
   "source": [
    "Read Data from cian_Penza.txt."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "angry-whole",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('Data/cian_Penza.txt', 'r') as file:\n",
    "    list_html = file.readlines()\n",
    "    html = ''.join(list_html)\n",
    "    soup = BeautifulSoup(html, 'html5lib')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hungry-expert",
   "metadata": {},
   "source": [
    "___\n",
    "### 2.2. Find Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "respiratory-cedar",
   "metadata": {},
   "source": [
    "The data we need is stored in tags **'article'**. Therefore, we will find them all and save them to a variable **articles**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "afraid-patient",
   "metadata": {},
   "outputs": [],
   "source": [
    "articles = soup.find_all('article')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "signed-batch",
   "metadata": {},
   "source": [
    "___\n",
    "### 2.3. Clearing data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "incident-china",
   "metadata": {},
   "source": [
    "#### 2.3.1. Create Function for removing side and extra lines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "successful-turkish",
   "metadata": {},
   "outputs": [],
   "source": [
    "def dellete(data):\n",
    "    if 'Ещё объекты' in pre_data[5]:\n",
    "        del data[5]\n",
    "    if ('Подробнее' in pre_data[6]) or ('owner' in pre_data[6]):\n",
    "        del data[6]\n",
    "    if 'Сдаем' in pre_data[7]:\n",
    "        del data[7]\n",
    "    if '' == data[7]:\n",
    "        data[7] = data[8]\n",
    "        data[8] = '0 ₽/мес.'\n",
    "    if '+7 9' not in data[9]:\n",
    "        del data[9]\n",
    "    if ('Собственник' in data[10]) or ('Ещё объекты' in data[10]):\n",
    "        del data[10]\n",
    "    if 'Ещё объекты' in data[11]:\n",
    "        del data[11]\n",
    "    \n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cheap-breach",
   "metadata": {},
   "source": [
    "#### 2.3.2. Create a loop"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fundamental-stable",
   "metadata": {},
   "source": [
    "Create a loop that will iterate over each line from the **articles**, get the necessary data and write it to the variable **data**. Also, we will exclude announcements of apartments for daily rent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "governing-homeless",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = []\n",
    "for i in range(0, len(articles)):\n",
    "    \n",
    "    a = (articles[i].find_all('a'))\n",
    "    pre_data = []\n",
    "    for j in range(0, len(a)):\n",
    "        if j in [0, 7]:\n",
    "            continue\n",
    "        elif a[j].string is None:\n",
    "            pre_data.append('owner')\n",
    "            continue\n",
    "        pre_data.append(a[j].string)\n",
    "    \n",
    "    pre_data.append(articles[i].find_all('img')[0].get('src'))\n",
    "    \n",
    "    span = (articles[i].find_all('span'))\n",
    "    for j in range(1, len(span)):\n",
    "        if span[j].string is None:\n",
    "            pre_data.append('')\n",
    "            continue\n",
    "        pre_data.append(span[j].string)\n",
    "        \n",
    "    p = (articles[i].find_all('p'))\n",
    "    for j in range(0, len(p)):\n",
    "        if p[j].string is None:\n",
    "            pre_data.append('')\n",
    "            continue\n",
    "        pre_data.append(p[j].string)\n",
    "    \n",
    "    \n",
    "    if len(pre_data) > 13:\n",
    "        prepre_data = dellete(pre_data)\n",
    "        pre_data = dellete(prepre_data)\n",
    "    \n",
    "    if len(pre_data) < 13:\n",
    "        print(pre_data)\n",
    "        n = input()\n",
    "        pre_data[n] = ''\n",
    "    if 'Посуточно' in pre_data[12]:\n",
    "        continue\n",
    "    else:\n",
    "        data.append(pre_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "warming-bundle",
   "metadata": {},
   "source": [
    "An example of a received ad:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "minimal-proportion",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Пензенская область',\n",
       " 'Пенза',\n",
       " 'р-н Октябрьский',\n",
       " 'проспект Строителей',\n",
       " '152',\n",
       " 'student city',\n",
       " 'https://cdn-p.cian.site/images/43/309/101/kvartira-penza-prospekt-stroiteley-1019033481-4.jpg',\n",
       " '3-комн. кв., 64 м², 4/10 этаж',\n",
       " '11 000 ₽/мес.',\n",
       " '+7 960 325-16-...',\n",
       " '2 недели назад',\n",
       " '12 фев, 11:35',\n",
       " 'От года, 11 000 ₽ + 2 000 ₽ комм. платежи (без счётчиков), комиссия 50%, без залога',\n",
       " 'Сдам 3-х ком. квартиру на длительный срок по пр.Строителей 152 квартира чистая, теплая, мебелированная, имеется вся бытовая техника']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "engaged-taylor",
   "metadata": {},
   "source": [
    "___\n",
    "### 2.4. Saving data "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "spare-partner",
   "metadata": {},
   "source": [
    "Save the data to a DataFrame - **data_Penza**. Use regular expressions from the library **re** to highlight specific information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "verbal-friday",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza = pd.DataFrame(columns=['FullAdress', 'District', 'Address',\n",
    "                                   'NumHouse', 'NumRooms', 'Area', 'Floor', \n",
    "                                   'RentPrice', 'CommunalPayments',\n",
    "                                   'Meters', 'Commission', 'Pledge', 'Description'])\n",
    "for i,flat in enumerate(data):\n",
    "    try:\n",
    "        District = flat[2]\n",
    "        if 'проезд' in flat[3]:\n",
    "            Address = flat[3][10:] + ' ' + flat[3][:6]\n",
    "        else:\n",
    "            Address = flat[3]\n",
    "        NumHouse = flat[4]\n",
    "        try:\n",
    "            about_flat = flat[7].split(', ')\n",
    "            try:\n",
    "                if 'Студия' in about_flat[0]:\n",
    "                    NumRooms = '1' + about_flat[0]\n",
    "                else:\n",
    "                    NumRooms = about_flat[0]\n",
    "            except:\n",
    "                NumRooms = about_flat[0]\n",
    "            Area = about_flat[1][:-2]\n",
    "            Floor = about_flat[2][:2].replace('/', '')\n",
    "        except IndexError:\n",
    "            NumRooms = None\n",
    "            Area = None\n",
    "            Floor = None\n",
    "        RentPrice = flat[8][:-7].replace(' ', '')\n",
    "        info = flat[12].split(', ')\n",
    "        try:\n",
    "            CommunalPayments = re.findall(r'\\+ (\\d+ \\d+)', info[1])[0].replace(' ', '')\n",
    "        except IndexError:\n",
    "            CommunalPayments = '0'\n",
    "        try:\n",
    "            Meters = re.findall(r'платежи \\((.+)\\)', info[1])[0]\n",
    "        except IndexError:\n",
    "            Meters = 'не включены'\n",
    "        Commission = info[2]\n",
    "        Pledge = info[3]\n",
    "        Description = flat[13]\n",
    "        FullAdress = Address + ', ' + NumHouse\n",
    "    \n",
    "        data_Penza = data_Penza.append({'FullAdress': FullAdress, 'District': District, 'Address': Address,\n",
    "                                       'NumHouse': NumHouse, 'NumRooms': NumRooms, 'Area': Area, 'Floor': Floor, \n",
    "                                        'RentPrice': RentPrice, 'CommunalPayments': CommunalPayments,\n",
    "                                       'Meters': Meters, 'Commission': Commission, 'Pledge': Pledge, \n",
    "                                        'Description': Description}, ignore_index=True)\n",
    "    except IndexError:\n",
    "        print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "normal-ethics",
   "metadata": {},
   "source": [
    "Check the resulting df:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "finished-helen",
   "metadata": {},
   "outputs": [
    {
     "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>FullAdress</th>\n",
       "      <th>District</th>\n",
       "      <th>Address</th>\n",
       "      <th>NumHouse</th>\n",
       "      <th>NumRooms</th>\n",
       "      <th>Area</th>\n",
       "      <th>Floor</th>\n",
       "      <th>RentPrice</th>\n",
       "      <th>CommunalPayments</th>\n",
       "      <th>Meters</th>\n",
       "      <th>Commission</th>\n",
       "      <th>Pledge</th>\n",
       "      <th>Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>проспект Строителей, 152</td>\n",
       "      <td>р-н Октябрьский</td>\n",
       "      <td>проспект Строителей</td>\n",
       "      <td>152</td>\n",
       "      <td>3-комн. кв.</td>\n",
       "      <td>64</td>\n",
       "      <td>4</td>\n",
       "      <td>11000</td>\n",
       "      <td>2000</td>\n",
       "      <td>без счётчиков</td>\n",
       "      <td>комиссия 50%</td>\n",
       "      <td>без залога</td>\n",
       "      <td>Сдам 3-х ком. квартиру на длительный срок по п...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>улица Ленина, 12</td>\n",
       "      <td>р-н Октябрьский</td>\n",
       "      <td>улица Ленина</td>\n",
       "      <td>12</td>\n",
       "      <td>3-комн. кв.</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>3000</td>\n",
       "      <td>без счётчиков</td>\n",
       "      <td>комиссия 50%</td>\n",
       "      <td>без залога</td>\n",
       "      <td>Большая 3х комнатная квартира 70м2,в шикарном ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Рахманинова проезд, 5</td>\n",
       "      <td>р-н Октябрьский</td>\n",
       "      <td>Рахманинова проезд</td>\n",
       "      <td>5</td>\n",
       "      <td>1-комн. кв.</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>11000</td>\n",
       "      <td>2000</td>\n",
       "      <td>без счётчиков</td>\n",
       "      <td>комиссия 50%</td>\n",
       "      <td>без залога</td>\n",
       "      <td>Сдается современная 1 ком квартира в развитом ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>улица Терновского, 158Б</td>\n",
       "      <td>р-н Первомайский</td>\n",
       "      <td>улица Терновского</td>\n",
       "      <td>158Б</td>\n",
       "      <td>1-комн. кв.</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "      <td>11000</td>\n",
       "      <td>2000</td>\n",
       "      <td>без счётчиков</td>\n",
       "      <td>комиссия 50%</td>\n",
       "      <td>залог 3 000 ₽</td>\n",
       "      <td>11 000 + ком.плат + 600 руб Инет и Тв (подключ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>улица Володарского, 75</td>\n",
       "      <td>р-н Железнодорожный</td>\n",
       "      <td>улица Володарского</td>\n",
       "      <td>75</td>\n",
       "      <td>2-комн. кв.</td>\n",
       "      <td>62</td>\n",
       "      <td>2</td>\n",
       "      <td>11000</td>\n",
       "      <td>2000</td>\n",
       "      <td>без счётчиков</td>\n",
       "      <td>комиссия 50%</td>\n",
       "      <td>без залога</td>\n",
       "      <td>Сдается 2 ком кв на Володарского 75. Сдаётся д...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 FullAdress             District              Address  \\\n",
       "0  проспект Строителей, 152      р-н Октябрьский  проспект Строителей   \n",
       "1          улица Ленина, 12      р-н Октябрьский         улица Ленина   \n",
       "2     Рахманинова проезд, 5      р-н Октябрьский   Рахманинова проезд   \n",
       "3   улица Терновского, 158Б     р-н Первомайский    улица Терновского   \n",
       "4    улица Володарского, 75  р-н Железнодорожный   улица Володарского   \n",
       "\n",
       "  NumHouse     NumRooms Area Floor RentPrice CommunalPayments         Meters  \\\n",
       "0      152  3-комн. кв.  64      4     11000             2000  без счётчиков   \n",
       "1       12  3-комн. кв.  70      1     10000             3000  без счётчиков   \n",
       "2        5  1-комн. кв.  30      3     11000             2000  без счётчиков   \n",
       "3     158Б  1-комн. кв.  40      9     11000             2000  без счётчиков   \n",
       "4       75  2-комн. кв.  62      2     11000             2000  без счётчиков   \n",
       "\n",
       "     Commission         Pledge  \\\n",
       "0  комиссия 50%     без залога   \n",
       "1  комиссия 50%     без залога   \n",
       "2  комиссия 50%     без залога   \n",
       "3  комиссия 50%  залог 3 000 ₽   \n",
       "4  комиссия 50%     без залога   \n",
       "\n",
       "                                         Description  \n",
       "0  Сдам 3-х ком. квартиру на длительный срок по п...  \n",
       "1  Большая 3х комнатная квартира 70м2,в шикарном ...  \n",
       "2  Сдается современная 1 ком квартира в развитом ...  \n",
       "3  11 000 + ком.плат + 600 руб Инет и Тв (подключ...  \n",
       "4  Сдается 2 ком кв на Володарского 75. Сдаётся д...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_Penza.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "incomplete-tooth",
   "metadata": {},
   "source": [
    "- **FullAdress** - Street name and house number. We'll need it to get latitude and longitude data.\n",
    "- **District** - The name of the area of the city. There are 4 of them.\n",
    "- **Address** - Street name.\n",
    "- **NumHouse** - House number.\n",
    "- **NumRooms** - Number of rooms.\n",
    "- **Area** - The area of the apartment.\n",
    "- **Floor** - Floor in the house.\n",
    "- **RentPrice** - The cost of renting housing per month.\n",
    "- **CommunalPayments** - Communal payments. Payment for light, gas, water and other meters.\n",
    "- **Meters** - Are meters included in the payment?\n",
    "- **Commission** - Commission to the agent, in case of a deal.\n",
    "- **Pledge** - Security deposit paid for the apartment to guarantee security. Upon leaving the apartment, the deposit is refunded.\n",
    "- **Description** - Description in the ad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "dimensional-medline",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame has 1411 lines and 13 columns.\n"
     ]
    }
   ],
   "source": [
    "print('DataFrame has {} lines and {} columns.'.format(data_Penza.shape[0], data_Penza.shape[1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "regional-berlin",
   "metadata": {},
   "source": [
    "___\n",
    "### 2.5. Freeing data from incorrect information"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "velvet-atlantic",
   "metadata": {},
   "source": [
    "At this stage, we remove the incorrect or empty information from the columns. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "classified-fisher",
   "metadata": {},
   "source": [
    "#### 2.5.1. Wrong city districts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "aboriginal-gentleman",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Old Values----\n",
      "['р-н Октябрьский', 'р-н Первомайский', 'р-н Железнодорожный', 'р-н Ленинский', 'с. Засечное', 'Ладожская улица']\n"
     ]
    }
   ],
   "source": [
    "print('----Old Values----')\n",
    "print(data_Penza.District.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "binding-inquiry",
   "metadata": {},
   "source": [
    "We see that we have 6 unique districts, but in fact there are 4 of them in Penza. Indeed, the last two meanings do not have the \"р-н\" subscript at the beginning, which means a city district."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "competent-winner",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza = data_Penza.loc[data_Penza['District'] != 'с. Засечное']\n",
    "data_Penza = data_Penza.loc[data_Penza['District'] != 'Ладожская улица']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "educated-request",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----New Values----\n",
      "['р-н Октябрьский', 'р-н Первомайский', 'р-н Железнодорожный', 'р-н Ленинский']\n"
     ]
    }
   ],
   "source": [
    "print('----New Values----')\n",
    "print(data_Penza.District.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "central-acrobat",
   "metadata": {},
   "source": [
    "#### 2.5.2. Remove missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "trying-operation",
   "metadata": {},
   "source": [
    "As mentioned above, \"True\" represents a missing value, \"False\" means the value is present in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "portable-consortium",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FullAdress\n",
      "False    1406\n",
      "Name: FullAdress, dtype: int64\n",
      "\n",
      "District\n",
      "False    1406\n",
      "Name: District, dtype: int64\n",
      "\n",
      "Address\n",
      "False    1406\n",
      "Name: Address, dtype: int64\n",
      "\n",
      "NumHouse\n",
      "False    1406\n",
      "Name: NumHouse, dtype: int64\n",
      "\n",
      "NumRooms\n",
      "False    1394\n",
      "True       12\n",
      "Name: NumRooms, dtype: int64\n",
      "\n",
      "Area\n",
      "False    1394\n",
      "True       12\n",
      "Name: Area, dtype: int64\n",
      "\n",
      "Floor\n",
      "False    1394\n",
      "True       12\n",
      "Name: Floor, dtype: int64\n",
      "\n",
      "RentPrice\n",
      "False    1406\n",
      "Name: RentPrice, dtype: int64\n",
      "\n",
      "CommunalPayments\n",
      "False    1406\n",
      "Name: CommunalPayments, dtype: int64\n",
      "\n",
      "Meters\n",
      "False    1406\n",
      "Name: Meters, dtype: int64\n",
      "\n",
      "Commission\n",
      "False    1406\n",
      "Name: Commission, dtype: int64\n",
      "\n",
      "Pledge\n",
      "False    1406\n",
      "Name: Pledge, dtype: int64\n",
      "\n",
      "Description\n",
      "False    1406\n",
      "Name: Description, dtype: int64\n",
      "\n"
     ]
    }
   ],
   "source": [
    "missing_data = data_Penza.isnull()\n",
    "\n",
    "for column in missing_data.columns.values.tolist():\n",
    "    print(column)\n",
    "    print (missing_data[column].value_counts())\n",
    "    print(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "sensitive-guarantee",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza.dropna(axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "yellow-living",
   "metadata": {},
   "source": [
    "#### 2.5.3. Remove incorrect values in house numbers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "trained-overview",
   "metadata": {},
   "source": [
    "The house number can be a number or a number with a letter. If instead the value is 'Этажи Пенза', we delete these lines, since they are not suitable for further analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "prime-globe",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza = data_Penza.loc[data_Penza['NumHouse'] != 'Этажи Пенза']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "specialized-lightweight",
   "metadata": {},
   "source": [
    "We used the values from this column to form the values in the **FullAdress** column, therefore, having removed the incorrect values in one place, we removed them everywhere. Now we can delete the entire column, since the information in it is duplicated and does not carry any usefulness."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "distant-enforcement",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza.drop(['NumHouse'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "electric-chicago",
   "metadata": {},
   "source": [
    "#### 2.5.4. Remove duplicates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "described-accordance",
   "metadata": {},
   "source": [
    "In particular, you should **pay attention to duplicate data**. We will also get rid of them to improve the accuracy of the analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "contemporary-livestock",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza = data_Penza.drop_duplicates(keep='first')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "facial-madison",
   "metadata": {},
   "source": [
    "Drop indexes after removing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "international-auction",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "statutory-shift",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame has 427 lines and 12 columns.\n"
     ]
    }
   ],
   "source": [
    "print('DataFrame has {} lines and {} columns.'.format(data_Penza.shape[0], data_Penza.shape[1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sought-context",
   "metadata": {},
   "source": [
    "As you can see, the number of rows dropped from 1411 to 427 after the cleanup. This is almost 3 times less than it was, but now we are confident that we will get more accurate analysis results."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "insured-reynolds",
   "metadata": {},
   "source": [
    "___\n",
    "### 2.6. Improving data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "pressed-separate",
   "metadata": {},
   "source": [
    "At this stage, we are improving the data, making it more processable."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "smoking-detection",
   "metadata": {},
   "source": [
    "#### 2.6.1. Number of rooms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "primary-germany",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Old Values----\n",
      "['3-комн. кв.', '1-комн. кв.', '2-комн. кв.', '1Студия']\n"
     ]
    }
   ],
   "source": [
    "print('----Old Values----')\n",
    "print(data_Penza.NumRooms.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "foreign-access",
   "metadata": {},
   "source": [
    "It can be seen that each copy has a number at the beginning that shows the number of rooms in the apartment. The rest of the information is useless to us."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "cleared-pharmacy",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----New Values----\n",
      "[3.0, 1.0, 2.0]\n"
     ]
    }
   ],
   "source": [
    "data_Penza['NumRooms'] = data_Penza['NumRooms'].str[0]\n",
    "data_Penza.NumRooms = data_Penza.NumRooms.astype('float')\n",
    "print('----New Values----')\n",
    "print(data_Penza.NumRooms.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "finite-president",
   "metadata": {},
   "source": [
    "#### 2.6.2. Cost of utilities"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cultural-biotechnology",
   "metadata": {},
   "source": [
    "The high cost of utilities is one of the main problems in Penza. This is due to the inflated tariffs of management companies and a complex heat supply calculation scheme. Gas prices in Penza are higher than in Moscow (5692 rubles / cubic meter versus 4836 rubles / cubic meter). At the same time, the real estate market in the city is experiencing stagnation. Read more on [RBC](https://realty.rbc.ru/news/5cdc5f189a794735ab002b69)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "serial-mississippi",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Old Values----\n",
      "['без счётчиков', 'счётчики включены', 'не включены']\n"
     ]
    }
   ],
   "source": [
    "print('----Old Values----')\n",
    "print(data_Penza.Meters.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "yellow-strategy",
   "metadata": {},
   "source": [
    "If utility bills are not included in the rental price, we will consider them equal to 4000 rubles. If included, except for counters, then 2000 rubles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "sharing-hamburg",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----New Values----\n",
      "[2000, 0, 4000]\n"
     ]
    }
   ],
   "source": [
    "values_meters = {'без счётчиков': 2000,'не включены': 4000,'счётчики включены': 0}\n",
    "data_Penza['Meters'] = data_Penza['Meters'].map(values_meters)\n",
    "print('----New Values----')\n",
    "print(data_Penza.Meters.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "stuffed-ordinance",
   "metadata": {},
   "source": [
    "#### 2.6.3. Rental price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "apparent-contribution",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza.RentPrice = data_Penza.RentPrice.astype('float')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "irish-microphone",
   "metadata": {},
   "source": [
    "#### 2.6.4. Commission"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "stopped-fabric",
   "metadata": {},
   "source": [
    "We calculate the commission as a percentage of the rental price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "referenced-hampshire",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Old Values----\n",
      "['комиссия 50%', 'комиссия 40%', 'без комиссии', 'комиссия 30%', 'комиссия 67%', 'комиссия 60%']\n"
     ]
    }
   ],
   "source": [
    "print('----Old Values----')\n",
    "print(data_Penza.Commission.unique().tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "signal-arabic",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza['Commission'].replace({'без комиссии': 'комиссия 0%'}, regex=True, inplace=True)\n",
    "data_Penza['Commission'] = data_Penza['Commission'].str.replace('комиссия ', '').str.replace('%', '')\n",
    "data_Penza['Commission'] = data_Penza['Commission'].astype('float') / 100 * data_Penza['RentPrice']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "disturbed-bleeding",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----New Values----\n",
      "[5500.0, 5000.0, 4000.0, 6000.0, 7500.0, 4500.0, 0.0, 4750.0, 3500.0, 7000.0, 11500.0, 6500.0, 3750.0, 3600.0, 3000.0, 3250.0, 4250.0, 3300.0, 8500.0, 6250.0, 2400.0, 2750.0, 2500.0, 9000.0, 8000.0, 10000.0, 4800.0, 3200.0, 15000.0, 6030.0, 5750.0]\n"
     ]
    }
   ],
   "source": [
    "print('----New Values----')\n",
    "print(data_Penza.Commission.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "educated-inspector",
   "metadata": {},
   "source": [
    "#### 2.6.5. Pledge"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "balanced-consultancy",
   "metadata": {},
   "source": [
    "We only need a number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "planned-running",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Old Values----\n",
      "['без залога', 'залог 3 000\\xa0₽', 'залог 10 000\\xa0₽', 'залог 5 000\\xa0₽', 'залог 7 500\\xa0₽', 'залог 7 000\\xa0₽', 'залог 12 500\\xa0₽', 'залог 6 000\\xa0₽', 'залог 2 000\\xa0₽', 'залог 500\\xa0₽', 'залог 2 500\\xa0₽', 'залог 13 000\\xa0₽', 'залог 18 000\\xa0₽', 'залог 1 000\\xa0₽', 'залог 12 000\\xa0₽', 'залог 20 000\\xa0₽', 'залог 4 000\\xa0₽', 'залог 30 000\\xa0₽']\n"
     ]
    }
   ],
   "source": [
    "print('----Old Values----')\n",
    "print(data_Penza.Pledge.unique().tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "entertaining-thirty",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza['Pledge'].replace({'без залога': 'залог 0 ₽'}, regex=True, inplace=True)\n",
    "data_Penza['Pledge'] = data_Penza['Pledge'].str.replace('залог ', '').str.replace('₽', '').str.replace('\\xa0', '').str.replace(' ', '')\n",
    "data_Penza['Pledge'] = data_Penza['Pledge'].astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "bearing-guidance",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----New Values----\n",
      "[0.0, 3000.0, 10000.0, 5000.0, 7500.0, 7000.0, 12500.0, 6000.0, 2000.0, 500.0, 2500.0, 13000.0, 18000.0, 1000.0, 12000.0, 20000.0, 4000.0, 30000.0]\n"
     ]
    }
   ],
   "source": [
    "print('----New Values----')\n",
    "print(data_Penza.Pledge.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "exclusive-formation",
   "metadata": {},
   "source": [
    "#### 2.6.6. Communal Payments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "liked-emerald",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Old Values----\n",
      "['2000', '3000', '1000', '3500', '0', '1500', '2500', '1200', '1700', '1800', '1100', '2200', '6000', '1300', '4000', '1600', '5000']\n"
     ]
    }
   ],
   "source": [
    "print('----Old Values----')\n",
    "print(data_Penza.CommunalPayments.unique().tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "described-repeat",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza['CommunalPayments'] = data_Penza['CommunalPayments'].astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "cardiovascular-pride",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----New Values----\n",
      "[2000.0, 3000.0, 1000.0, 3500.0, 0.0, 1500.0, 2500.0, 1200.0, 1700.0, 1800.0, 1100.0, 2200.0, 6000.0, 1300.0, 4000.0, 1600.0, 5000.0]\n"
     ]
    }
   ],
   "source": [
    "print('----New Values----')\n",
    "print(data_Penza.CommunalPayments.unique().tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "split-pixel",
   "metadata": {},
   "source": [
    "#### 2.6.7. Floor and Area"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "informed-groove",
   "metadata": {},
   "source": [
    "Change data type for apartment area and floors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "civil-divide",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza.Area = data_Penza.Area.str.replace(\",\", \".\")\n",
    "data_Penza[['Area', 'Floor']] = data_Penza[['Area', 'Floor']].astype('float')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "rural-publicity",
   "metadata": {},
   "source": [
    "#### 2.6.6. Payments"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "postal-federal",
   "metadata": {},
   "source": [
    "Let's collect all the payment data in two separate columns. \n",
    "1. **MonthlyPayments** - payments that must be made every month in case of renting an apartment. They include **RentPrice, Meters, CommunalPayments**.\n",
    "2. **OneTimeFee** - payment that must be paid once at the very beginning of renting an apartment. They include **Commission, Pledge**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "legislative-twist",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza['MonthlyPayments'] = data_Penza.RentPrice + data_Penza.Meters + data_Penza.CommunalPayments\n",
    "data_Penza['OneTimeFee'] = data_Penza.Commission + data_Penza.Pledge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "hollywood-vertex",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza.drop(['RentPrice', 'Meters', 'CommunalPayments', 'Commission', 'Pledge'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "after-niger",
   "metadata": {},
   "source": [
    "For the convenience of counting, divide the resulting columns by 1000. This is how they will express the amount in thousands of rubles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "interesting-wings",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Penza['MonthlyPayments'] = data_Penza['MonthlyPayments'] / 1000\n",
    "data_Penza['OneTimeFee'] = data_Penza['OneTimeFee'] / 1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "chemical-project",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 427 entries, 0 to 426\n",
      "Data columns (total 9 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   FullAdress       427 non-null    object \n",
      " 1   District         427 non-null    object \n",
      " 2   Address          427 non-null    object \n",
      " 3   NumRooms         427 non-null    float64\n",
      " 4   Area             427 non-null    float64\n",
      " 5   Floor            427 non-null    float64\n",
      " 6   Description      427 non-null    object \n",
      " 7   MonthlyPayments  427 non-null    float64\n",
      " 8   OneTimeFee       427 non-null    float64\n",
      "dtypes: float64(5), object(4)\n",
      "memory usage: 30.1+ KB\n"
     ]
    }
   ],
   "source": [
    "data_Penza.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "express-school",
   "metadata": {},
   "source": [
    "____\n",
    "### 2.7. Add GeoData"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "brief-detail",
   "metadata": {},
   "source": [
    "Geodata means **latitude** and **longitude** for each address in our Dataframe. We'll use the **geopy** library to get the data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "polish-consolidation",
   "metadata": {},
   "source": [
    "#### 2.7.1. Сreate a df for Location."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "convertible-thesis",
   "metadata": {},
   "source": [
    "We will save the data in this Dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "sudden-classics",
   "metadata": {},
   "outputs": [],
   "source": [
    "Location = pd.DataFrame(columns=['microDistrict', 'ALatitude', 'ALongitude', 'Latitude', 'Longitude'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "united-foundation",
   "metadata": {},
   "source": [
    "#### 2.7.2. Getting location data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "opposed-disabled",
   "metadata": {},
   "source": [
    "Function to check for a number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "incoming-trailer",
   "metadata": {},
   "outputs": [],
   "source": [
    "def is_not_number(s):\n",
    "    try:\n",
    "        number = int(s)\n",
    "        return False\n",
    "    except ValueError:\n",
    "        return True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fifth-roberts",
   "metadata": {},
   "source": [
    "In this loop, we go through each address and district in our Dataframe. If we have not received the data, we try to send the request again. We print every unsuccessful attempt. If the request is successful, then add data to Dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "demonstrated-hartford",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "73 None\n",
      "126 None\n",
      "198 None\n",
      "201 None\n",
      "216 None\n",
      "276 None\n",
      "318 None\n",
      "323 None\n",
      "353 None\n",
      "355 None\n",
      "365 None\n",
      "410 3, улица Лермонтова, Нижний Ломов, городское поселение Нижний Ломов, Нижнеломовский район, Пензенская область, Приволжский федеральный округ, 442150, Россия\n"
     ]
    }
   ],
   "source": [
    "geolocator = Nominatim(user_agent=\"to_explorer\")\n",
    "geocode = partial(geolocator.geocode, language=\"ru\")\n",
    "for i in range(len(data_Penza)):\n",
    "    address = data_Penza.FullAdress[i] + ', Пенза'\n",
    "    district = data_Penza.District[i] + ', Пенза'\n",
    "    try:\n",
    "        location = geolocator.geocode(district)\n",
    "        latitude = location.latitude\n",
    "        longitude = location.longitude\n",
    "        alocation = geolocator.geocode(address)\n",
    "        alatitude = alocation.latitude\n",
    "        alongitude = alocation.longitude\n",
    "        row = alocation.address.split(', ')\n",
    "        if is_not_number(row[0][0]):\n",
    "            if is_not_number(row[1][0]):\n",
    "                row.insert(0, '')\n",
    "            else:\n",
    "                del row[0]\n",
    "        \n",
    "        if ('район' not in row[3].split()) and ('Пензенская' not in row[3].split()) and ('Пенза' not in row[3].split()):\n",
    "            del row[2]\n",
    "        \n",
    "        if ('Ленинский район' == row[2]) or ('Первомайский район' == row[2]):\n",
    "            naibourhood = 'Западная поляна'\n",
    "        elif 'кордон Октябрьский Сад' == row[2]:\n",
    "            naibourhood = 'Окружная'\n",
    "        elif 'городское поселение Нижний Ломов' == row[2]:\n",
    "            raise AttributeError\n",
    "        else:\n",
    "            naibourhood = row[2]\n",
    "        Location = Location.append({'microDistrict': naibourhood, 'ALatitude': alatitude, 'ALongitude': alongitude, 'Latitude': latitude, 'Longitude': longitude}, ignore_index=True)\n",
    "    except AttributeError:\n",
    "        print(i, alocation)\n",
    "        Location = Location.append({'microDistrict': np.NaN, 'ALatitude': np.NaN, 'ALongitude': np.NaN, 'Latitude': latitude, 'Longitude': longitude}, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "stable-house",
   "metadata": {},
   "source": [
    "We were unable to receive data for 12 addresses. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "general-triangle",
   "metadata": {},
   "outputs": [
    {
     "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>microDistrict</th>\n",
       "      <th>ALatitude</th>\n",
       "      <th>ALongitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Запрудный</td>\n",
       "      <td>53.220538</td>\n",
       "      <td>44.888742</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Заводской</td>\n",
       "      <td>53.215607</td>\n",
       "      <td>45.000883</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Арбеково</td>\n",
       "      <td>53.225680</td>\n",
       "      <td>44.929247</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Терновка</td>\n",
       "      <td>53.144821</td>\n",
       "      <td>45.024579</td>\n",
       "      <td>53.162396</td>\n",
       "      <td>45.029846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Центр</td>\n",
       "      <td>53.200063</td>\n",
       "      <td>45.016249</td>\n",
       "      <td>53.203561</td>\n",
       "      <td>45.012268</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  microDistrict  ALatitude  ALongitude   Latitude  Longitude\n",
       "0     Запрудный  53.220538   44.888742  53.172801  44.973169\n",
       "1     Заводской  53.215607   45.000883  53.172801  44.973169\n",
       "2      Арбеково  53.225680   44.929247  53.172801  44.973169\n",
       "3      Терновка  53.144821   45.024579  53.162396  45.029846\n",
       "4         Центр  53.200063   45.016249  53.203561  45.012268"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Location.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "illegal-serve",
   "metadata": {},
   "source": [
    "#### 2.7.3. Concatenate Dataframes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "nasty-rochester",
   "metadata": {},
   "source": [
    "Concatenate our two df's into one new one and remove the lines with empty data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "based-swaziland",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_Penza = pd.concat([data_Penza, Location], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abstract-defensive",
   "metadata": {},
   "source": [
    "#### 2.7.4. Delete lines with None values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "armed-channel",
   "metadata": {},
   "source": [
    "Delete lines with empty values in latitude and longitude."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "nonprofit-rental",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_Penza.dropna(subset=['Latitude'], axis=0, inplace=True)\n",
    "df_Penza.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "delayed-thesaurus",
   "metadata": {},
   "source": [
    "Checking Data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "developed-advice",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame has 427 lines and 14 columns.\n"
     ]
    }
   ],
   "source": [
    "print('DataFrame has {} lines and {} columns.'.format(df_Penza.shape[0], df_Penza.shape[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "affecting-prairie",
   "metadata": {},
   "outputs": [
    {
     "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>FullAdress</th>\n",
       "      <th>District</th>\n",
       "      <th>Address</th>\n",
       "      <th>NumRooms</th>\n",
       "      <th>Area</th>\n",
       "      <th>Floor</th>\n",
       "      <th>Description</th>\n",
       "      <th>MonthlyPayments</th>\n",
       "      <th>OneTimeFee</th>\n",
       "      <th>microDistrict</th>\n",
       "      <th>ALatitude</th>\n",
       "      <th>ALongitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>проспект Строителей, 152</td>\n",
       "      <td>р-н Октябрьский</td>\n",
       "      <td>проспект Строителей</td>\n",
       "      <td>3.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Сдам 3-х ком. квартиру на длительный срок по п...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>Запрудный</td>\n",
       "      <td>53.220538</td>\n",
       "      <td>44.888742</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>улица Ленина, 12</td>\n",
       "      <td>р-н Октябрьский</td>\n",
       "      <td>улица Ленина</td>\n",
       "      <td>3.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Большая 3х комнатная квартира 70м2,в шикарном ...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Заводской</td>\n",
       "      <td>53.215607</td>\n",
       "      <td>45.000883</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Рахманинова проезд, 5</td>\n",
       "      <td>р-н Октябрьский</td>\n",
       "      <td>Рахманинова проезд</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Сдается современная 1 ком квартира в развитом ...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>Арбеково</td>\n",
       "      <td>53.225680</td>\n",
       "      <td>44.929247</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>улица Терновского, 158Б</td>\n",
       "      <td>р-н Первомайский</td>\n",
       "      <td>улица Терновского</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11 000 + ком.плат + 600 руб Инет и Тв (подключ...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>Терновка</td>\n",
       "      <td>53.144821</td>\n",
       "      <td>45.024579</td>\n",
       "      <td>53.162396</td>\n",
       "      <td>45.029846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>улица Володарского, 75</td>\n",
       "      <td>р-н Железнодорожный</td>\n",
       "      <td>улица Володарского</td>\n",
       "      <td>2.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Сдается 2 ком кв на Володарского 75. Сдаётся д...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>Центр</td>\n",
       "      <td>53.200063</td>\n",
       "      <td>45.016249</td>\n",
       "      <td>53.203561</td>\n",
       "      <td>45.012268</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 FullAdress             District              Address  \\\n",
       "0  проспект Строителей, 152      р-н Октябрьский  проспект Строителей   \n",
       "1          улица Ленина, 12      р-н Октябрьский         улица Ленина   \n",
       "2     Рахманинова проезд, 5      р-н Октябрьский   Рахманинова проезд   \n",
       "3   улица Терновского, 158Б     р-н Первомайский    улица Терновского   \n",
       "4    улица Володарского, 75  р-н Железнодорожный   улица Володарского   \n",
       "\n",
       "   NumRooms  Area  Floor                                        Description  \\\n",
       "0       3.0  64.0    4.0  Сдам 3-х ком. квартиру на длительный срок по п...   \n",
       "1       3.0  70.0    1.0  Большая 3х комнатная квартира 70м2,в шикарном ...   \n",
       "2       1.0  30.0    3.0  Сдается современная 1 ком квартира в развитом ...   \n",
       "3       1.0  40.0    9.0  11 000 + ком.плат + 600 руб Инет и Тв (подключ...   \n",
       "4       2.0  62.0    2.0  Сдается 2 ком кв на Володарского 75. Сдаётся д...   \n",
       "\n",
       "   MonthlyPayments  OneTimeFee microDistrict  ALatitude  ALongitude  \\\n",
       "0             15.0         5.5     Запрудный  53.220538   44.888742   \n",
       "1             15.0         5.0     Заводской  53.215607   45.000883   \n",
       "2             15.0         5.5      Арбеково  53.225680   44.929247   \n",
       "3             15.0         8.5      Терновка  53.144821   45.024579   \n",
       "4             15.0         5.5         Центр  53.200063   45.016249   \n",
       "\n",
       "    Latitude  Longitude  \n",
       "0  53.172801  44.973169  \n",
       "1  53.172801  44.973169  \n",
       "2  53.172801  44.973169  \n",
       "3  53.162396  45.029846  \n",
       "4  53.203561  45.012268  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Penza.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "stock-jacket",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Глобус                      59\n",
       "Центр                       47\n",
       "Запрудный                   44\n",
       "Арбеково                    41\n",
       "ГПЗ                         39\n",
       "Западная поляна             37\n",
       "Южная поляна                37\n",
       "ЖК «Арбековская застава»    20\n",
       "Сосновка                    19\n",
       "Новая Западная поляна       18\n",
       "остров Пески                17\n",
       "Стрела                      12\n",
       "Заводской                    9\n",
       "Северная поляна              3\n",
       "Шуист                        2\n",
       "Гидрострой                   2\n",
       "Терновка                     2\n",
       "Цыганский посёлок            2\n",
       "пос.Ахуны                    1\n",
       "Железнодорожный район        1\n",
       "Бугровка                     1\n",
       "Окружная                     1\n",
       "Заречный                     1\n",
       "Name: microDistrict, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Penza['microDistrict'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "other-mechanism",
   "metadata": {},
   "source": [
    "___\n",
    "### 2.8. Utilizing the Foursquare API"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "received-outdoors",
   "metadata": {},
   "source": [
    "At this stage, we will use Foursquare API to get venues for each address in Dataframe.\n",
    "\n",
    "The Places API offers real-time access to Foursquare’s global database of rich venue data and user content to power location-based experiences in app or website."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "appropriate-medium",
   "metadata": {},
   "source": [
    "#### 2.8.1. Preparing to connect\n",
    "\n",
    "To connect, we have to enter the following 4 parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "cognitive-terrace",
   "metadata": {},
   "outputs": [],
   "source": [
    "CLIENT_ID = 'LBI0N4QMTJUEELDUQPYBEEDT1V5S3IPO3J4N1R05LJIK0YHF' # Foursquare ID\n",
    "CLIENT_SECRET = 'NX2AS300KTMMU21U5NZAHSDXH4CVOJGTO0FPLIE1WCL2NVUS' # Foursquare Secret\n",
    "VERSION = '20210131' # Foursquare API version\n",
    "LIMIT = 500 # A default Foursquare API limit value"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "noticed-niger",
   "metadata": {},
   "source": [
    "#### 2.8.2. Receiving data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ceramic-farmer",
   "metadata": {},
   "source": [
    "Create a function that, using the connection data, will receive all venues for each micro districts within a radius of 1000 meters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "loving-grant",
   "metadata": {},
   "outputs": [],
   "source": [
    "def getNearbyVenues(names, latitudes, longitudes, radius):\n",
    "    \n",
    "    venues_list=[]\n",
    "    for name, lat, lng in zip(names, latitudes, longitudes):\n",
    "            \n",
    "        # create the API request URL\n",
    "        url = 'https://api.foursquare.com/v2/venues/explore?&client_id={}&client_secret={}&v={}&ll={},{}&radius={}&limit={}'.format(\n",
    "            CLIENT_ID, \n",
    "            CLIENT_SECRET, \n",
    "            VERSION, \n",
    "            lat, \n",
    "            lng, \n",
    "            radius, \n",
    "            LIMIT)\n",
    "            \n",
    "        # make the GET request\n",
    "        results = requests.get(url).json()[\"response\"]['groups'][0]['items']\n",
    "        \n",
    "        # return only relevant information for each nearby venue\n",
    "        venues_list.append([(\n",
    "            name,\n",
    "            lat, \n",
    "            lng, \n",
    "            v['venue']['name'], \n",
    "            v['venue']['location']['lat'], \n",
    "            v['venue']['location']['lng'],  \n",
    "            v['venue']['categories'][0]['name']) for v in results])\n",
    "\n",
    "    nearby_venues = pd.DataFrame([item for venue_list in venues_list for item in venue_list])\n",
    "    nearby_venues.columns = ['microDistrict', \n",
    "                             'microDistrict Latitude', \n",
    "                             'microDistrict Longitude',\n",
    "                             'Venue', \n",
    "                             'Venue Latitude', \n",
    "                             'Venue Longitude', \n",
    "                             'Venue Category']\n",
    "    \n",
    "    return(nearby_venues)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "saved-fossil",
   "metadata": {},
   "outputs": [],
   "source": [
    "penza_microdistrics_venues = getNearbyVenues(names=df_Penza['microDistrict'],\n",
    "                                             latitudes=df_Penza['Latitude'],\n",
    "                                             longitudes=df_Penza['Longitude'],\n",
    "                                             radius=1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "convertible-creator",
   "metadata": {},
   "source": [
    "Checking Data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "charitable-festival",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame with coordinates has 16955 lines and 7 columns.\n"
     ]
    },
    {
     "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>microDistrict</th>\n",
       "      <th>microDistrict Latitude</th>\n",
       "      <th>microDistrict Longitude</th>\n",
       "      <th>Venue</th>\n",
       "      <th>Venue Latitude</th>\n",
       "      <th>Venue Longitude</th>\n",
       "      <th>Venue Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Запрудный</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "      <td>Spar</td>\n",
       "      <td>53.165857</td>\n",
       "      <td>44.979529</td>\n",
       "      <td>Grocery Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Запрудный</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "      <td>Мой</td>\n",
       "      <td>53.167134</td>\n",
       "      <td>44.982601</td>\n",
       "      <td>Shopping Mall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Запрудный</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "      <td>Моя Родня</td>\n",
       "      <td>53.168849</td>\n",
       "      <td>44.968008</td>\n",
       "      <td>Shopping Mall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Запрудный</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "      <td>Стрелец</td>\n",
       "      <td>53.168782</td>\n",
       "      <td>44.984803</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Запрудный</td>\n",
       "      <td>53.172801</td>\n",
       "      <td>44.973169</td>\n",
       "      <td>Duty Free</td>\n",
       "      <td>53.167948</td>\n",
       "      <td>44.984070</td>\n",
       "      <td>Liquor Store</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  microDistrict  microDistrict Latitude  microDistrict Longitude      Venue  \\\n",
       "0     Запрудный               53.172801                44.973169       Spar   \n",
       "1     Запрудный               53.172801                44.973169        Мой   \n",
       "2     Запрудный               53.172801                44.973169  Моя Родня   \n",
       "3     Запрудный               53.172801                44.973169    Стрелец   \n",
       "4     Запрудный               53.172801                44.973169  Duty Free   \n",
       "\n",
       "   Venue Latitude  Venue Longitude     Venue Category  \n",
       "0       53.165857        44.979529      Grocery Store  \n",
       "1       53.167134        44.982601      Shopping Mall  \n",
       "2       53.168849        44.968008      Shopping Mall  \n",
       "3       53.168782        44.984803  Food & Drink Shop  \n",
       "4       53.167948        44.984070       Liquor Store  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('DataFrame with coordinates has {} lines and {} columns.'.format(penza_microdistrics_venues.shape[0], penza_microdistrics_venues.shape[1]))\n",
    "penza_microdistrics_venues.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "public-protection",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----Top 10 venues by quantity----\n",
      "Автосуши               376\n",
      "M.Видео                376\n",
      "Duty Free              321\n",
      "Перекресток            268\n",
      "Спорт-бар \"Колизей\"    188\n",
      "Fellini                188\n",
      "Пассаж                 188\n",
      "Rush                   188\n",
      "Спортмастер            188\n",
      "Чарли Gold             188\n",
      "Name: Venue, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print('----Top 10 venues by quantity----')\n",
    "print(penza_microdistrics_venues.Venue.value_counts()[:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "extreme-formation",
   "metadata": {},
   "source": [
    "#### 2.8.3. One hot encoding"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "attended-ability",
   "metadata": {},
   "source": [
    "One hot Encoding is a type of vector representation in which all of the elements in a vector are 0, except for one, which has 1 as its value, where 1 represents a boolean specifying a category of the element."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "canadian-registration",
   "metadata": {},
   "outputs": [],
   "source": [
    "penza_onehot = pd.get_dummies(penza_microdistrics_venues[['Venue Category']], prefix=\"\", prefix_sep=\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "outdoor-watts",
   "metadata": {},
   "source": [
    "Add neighborhood column back to dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "noble-rainbow",
   "metadata": {},
   "outputs": [],
   "source": [
    "penza_onehot['microDistrict'] = penza_microdistrics_venues['microDistrict'] "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "statistical-trademark",
   "metadata": {},
   "source": [
    "Move neighborhood column to the first column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "bibliographic-barbados",
   "metadata": {},
   "outputs": [],
   "source": [
    "fixed_columns = [penza_onehot.columns[-1]] + list(penza_onehot.columns[:-1])\n",
    "penza_onehot = penza_onehot[fixed_columns]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "signal-notebook",
   "metadata": {},
   "source": [
    "Group rows by neighborhood and by taking the mean of the frequency of occurrence of each category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "solid-consequence",
   "metadata": {},
   "outputs": [],
   "source": [
    "penza_grouped_by_microDistrict = penza_onehot.groupby('microDistrict').mean().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dated-cricket",
   "metadata": {},
   "source": [
    "Checking new Dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "overhead-violin",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "New DataFrame with sorted venues has 23 lines and 62 columns.\n"
     ]
    }
   ],
   "source": [
    "print('New DataFrame with sorted venues has {} lines and {} columns.'.format(penza_grouped_by_microDistrict.shape[0], penza_grouped_by_microDistrict.shape[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "pressed-class",
   "metadata": {},
   "outputs": [
    {
     "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>microDistrict</th>\n",
       "      <th>ATM</th>\n",
       "      <th>American Restaurant</th>\n",
       "      <th>Asian Restaurant</th>\n",
       "      <th>BBQ Joint</th>\n",
       "      <th>Bar</th>\n",
       "      <th>Bookstore</th>\n",
       "      <th>Brewery</th>\n",
       "      <th>Burger Joint</th>\n",
       "      <th>Café</th>\n",
       "      <th>Camera Store</th>\n",
       "      <th>Clothing Store</th>\n",
       "      <th>Cocktail Bar</th>\n",
       "      <th>Coffee Shop</th>\n",
       "      <th>Convenience Store</th>\n",
       "      <th>Cosmetics Shop</th>\n",
       "      <th>Deli / Bodega</th>\n",
       "      <th>Dessert Shop</th>\n",
       "      <th>Donut Shop</th>\n",
       "      <th>Eastern European Restaurant</th>\n",
       "      <th>Electronics Store</th>\n",
       "      <th>Fast Food Restaurant</th>\n",
       "      <th>Food &amp; Drink Shop</th>\n",
       "      <th>French Restaurant</th>\n",
       "      <th>Gaming Cafe</th>\n",
       "      <th>German Restaurant</th>\n",
       "      <th>Grocery Store</th>\n",
       "      <th>Gym / Fitness Center</th>\n",
       "      <th>Hookah Bar</th>\n",
       "      <th>Hotel</th>\n",
       "      <th>Ice Cream Shop</th>\n",
       "      <th>Italian Restaurant</th>\n",
       "      <th>Japanese Restaurant</th>\n",
       "      <th>Jewelry Store</th>\n",
       "      <th>Karaoke Bar</th>\n",
       "      <th>Liquor Store</th>\n",
       "      <th>Middle Eastern Restaurant</th>\n",
       "      <th>Miscellaneous Shop</th>\n",
       "      <th>Mobile Phone Shop</th>\n",
       "      <th>Modern European Restaurant</th>\n",
       "      <th>Movie Theater</th>\n",
       "      <th>Nightclub</th>\n",
       "      <th>Park</th>\n",
       "      <th>Pharmacy</th>\n",
       "      <th>Pizza Place</th>\n",
       "      <th>Plaza</th>\n",
       "      <th>Pool</th>\n",
       "      <th>Pub</th>\n",
       "      <th>Restaurant</th>\n",
       "      <th>Shoe Store</th>\n",
       "      <th>Shopping Mall</th>\n",
       "      <th>Skating Rink</th>\n",
       "      <th>Spa</th>\n",
       "      <th>Sporting Goods Shop</th>\n",
       "      <th>Sports Bar</th>\n",
       "      <th>Steakhouse</th>\n",
       "      <th>Supermarket</th>\n",
       "      <th>Sushi Restaurant</th>\n",
       "      <th>Theater</th>\n",
       "      <th>Toy / Game Store</th>\n",
       "      <th>Wine Bar</th>\n",
       "      <th>Wine Shop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Арбеково</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Бугровка</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.040000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.070000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.030000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.030000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.030000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.070000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.060000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.020000</td>\n",
       "      <td>0.030000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.040000</td>\n",
       "      <td>0.030000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.010000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ГПЗ</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.032258</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.080645</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.048387</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.032258</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.032258</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.048387</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.080645</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.032258</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.032258</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.032258</td>\n",
       "      <td>0.064516</td>\n",
       "      <td>0.032258</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Гидрострой</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Глобус</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.039643</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.069376</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.029732</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.029732</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.021606</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.011695</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.011695</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.029732</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.069376</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.063033</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.019822</td>\n",
       "      <td>0.029732</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.009911</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.039643</td>\n",
       "      <td>0.029732</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.009911</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  microDistrict       ATM  American Restaurant  Asian Restaurant  BBQ Joint  \\\n",
       "0      Арбеково  0.000000             0.000000          0.000000   0.000000   \n",
       "1      Бугровка  0.010000             0.010000          0.010000   0.010000   \n",
       "2           ГПЗ  0.000000             0.016129          0.000000   0.000000   \n",
       "3    Гидрострой  0.000000             0.000000          0.000000   0.000000   \n",
       "4        Глобус  0.009911             0.009911          0.009911   0.009911   \n",
       "\n",
       "        Bar  Bookstore   Brewery  Burger Joint      Café  Camera Store  \\\n",
       "0  0.000000   0.000000  0.000000      0.000000  0.000000      0.000000   \n",
       "1  0.040000   0.020000  0.010000      0.010000  0.070000      0.010000   \n",
       "2  0.032258   0.016129  0.000000      0.000000  0.080645      0.016129   \n",
       "3  0.000000   0.000000  0.000000      0.000000  0.000000      0.000000   \n",
       "4  0.039643   0.019822  0.009911      0.009911  0.069376      0.009911   \n",
       "\n",
       "   Clothing Store  Cocktail Bar  Coffee Shop  Convenience Store  \\\n",
       "0        0.000000      0.000000     0.000000                0.0   \n",
       "1        0.010000      0.020000     0.030000                0.0   \n",
       "2        0.000000      0.000000     0.016129                0.0   \n",
       "3        0.000000      0.000000     0.000000                0.2   \n",
       "4        0.009911      0.019822     0.029732                0.0   \n",
       "\n",
       "   Cosmetics Shop  Deli / Bodega  Dessert Shop  Donut Shop  \\\n",
       "0        0.000000       0.000000      0.000000    0.000000   \n",
       "1        0.010000       0.010000      0.010000    0.000000   \n",
       "2        0.016129       0.016129      0.016129    0.016129   \n",
       "3        0.000000       0.000000      0.000000    0.000000   \n",
       "4        0.009911       0.009911      0.009911    0.000000   \n",
       "\n",
       "   Eastern European Restaurant  Electronics Store  Fast Food Restaurant  \\\n",
       "0                     0.000000           0.000000              0.000000   \n",
       "1                     0.010000           0.030000              0.020000   \n",
       "2                     0.016129           0.048387              0.016129   \n",
       "3                     0.200000           0.000000              0.000000   \n",
       "4                     0.009911           0.029732              0.019822   \n",
       "\n",
       "   Food & Drink Shop  French Restaurant  Gaming Cafe  German Restaurant  \\\n",
       "0           0.200000           0.000000     0.000000           0.000000   \n",
       "1           0.020000           0.010000     0.010000           0.010000   \n",
       "2           0.032258           0.016129     0.016129           0.016129   \n",
       "3           0.200000           0.000000     0.000000           0.000000   \n",
       "4           0.021606           0.009911     0.009911           0.009911   \n",
       "\n",
       "   Grocery Store  Gym / Fitness Center  Hookah Bar     Hotel  Ice Cream Shop  \\\n",
       "0       0.200000              0.000000    0.000000  0.000000        0.000000   \n",
       "1       0.010000              0.010000    0.020000  0.020000        0.010000   \n",
       "2       0.032258              0.016129    0.000000  0.000000        0.016129   \n",
       "3       0.000000              0.000000    0.000000  0.200000        0.000000   \n",
       "4       0.011695              0.009911    0.019822  0.019822        0.009911   \n",
       "\n",
       "   Italian Restaurant  Japanese Restaurant  Jewelry Store  Karaoke Bar  \\\n",
       "0            0.000000             0.000000       0.000000     0.000000   \n",
       "1            0.010000             0.000000       0.010000     0.010000   \n",
       "2            0.016129             0.016129       0.000000     0.016129   \n",
       "3            0.000000             0.000000       0.000000     0.000000   \n",
       "4            0.009911             0.000000       0.009911     0.009911   \n",
       "\n",
       "   Liquor Store  Middle Eastern Restaurant  Miscellaneous Shop  \\\n",
       "0      0.200000                   0.000000            0.000000   \n",
       "1      0.010000                   0.020000            0.010000   \n",
       "2      0.016129                   0.000000            0.000000   \n",
       "3      0.000000                   0.000000            0.000000   \n",
       "4      0.011695                   0.019822            0.009911   \n",
       "\n",
       "   Mobile Phone Shop  Modern European Restaurant  Movie Theater  Nightclub  \\\n",
       "0           0.000000                    0.000000       0.000000   0.000000   \n",
       "1           0.010000                    0.010000       0.020000   0.010000   \n",
       "2           0.000000                    0.000000       0.016129   0.048387   \n",
       "3           0.000000                    0.000000       0.000000   0.000000   \n",
       "4           0.009911                    0.009911       0.019822   0.009911   \n",
       "\n",
       "       Park  Pharmacy  Pizza Place     Plaza      Pool       Pub  Restaurant  \\\n",
       "0  0.000000  0.000000     0.000000  0.000000  0.000000  0.000000    0.000000   \n",
       "1  0.020000  0.010000     0.020000  0.030000  0.010000  0.020000    0.070000   \n",
       "2  0.016129  0.016129     0.016129  0.016129  0.000000  0.016129    0.080645   \n",
       "3  0.000000  0.000000     0.000000  0.000000  0.000000  0.000000    0.200000   \n",
       "4  0.019822  0.009911     0.019822  0.029732  0.009911  0.019822    0.069376   \n",
       "\n",
       "   Shoe Store  Shopping Mall  Skating Rink       Spa  Sporting Goods Shop  \\\n",
       "0    0.000000       0.400000      0.000000  0.000000             0.000000   \n",
       "1    0.010000       0.060000      0.010000  0.020000             0.030000   \n",
       "2    0.000000       0.032258      0.000000  0.000000             0.032258   \n",
       "3    0.000000       0.000000      0.000000  0.000000             0.000000   \n",
       "4    0.009911       0.063033      0.009911  0.019822             0.029732   \n",
       "\n",
       "   Sports Bar  Steakhouse  Supermarket  Sushi Restaurant   Theater  \\\n",
       "0    0.000000    0.000000     0.000000          0.000000  0.000000   \n",
       "1    0.010000    0.010000     0.000000          0.040000  0.030000   \n",
       "2    0.016129    0.000000     0.032258          0.064516  0.032258   \n",
       "3    0.000000    0.000000     0.000000          0.000000  0.000000   \n",
       "4    0.009911    0.009911     0.000000          0.039643  0.029732   \n",
       "\n",
       "   Toy / Game Store  Wine Bar  Wine Shop  \n",
       "0          0.000000  0.000000   0.000000  \n",
       "1          0.000000  0.000000   0.010000  \n",
       "2          0.016129  0.016129   0.000000  \n",
       "3          0.000000  0.000000   0.000000  \n",
       "4          0.000000  0.000000   0.009911  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "penza_grouped_by_microDistrict.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "asian-picture",
   "metadata": {},
   "source": [
    "#### 2.8.4. Sort the venues"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "documented-africa",
   "metadata": {},
   "source": [
    "Function to sort the venues in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "informal-symbol",
   "metadata": {},
   "outputs": [],
   "source": [
    "def return_most_common_venues(row, num_top_venues):\n",
    "    row_categories = row.iloc[1:]\n",
    "    row_categories_sorted = row_categories.sort_values(ascending=False)\n",
    "    \n",
    "    return row_categories_sorted.index.values[0:num_top_venues]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sudden-landscape",
   "metadata": {},
   "source": [
    "Create columns according to number of 10 top venues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "reverse-strand",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_top_venues = 10\n",
    "indicators = ['st', 'nd', 'rd']\n",
    "\n",
    "columns = ['microDistrict']\n",
    "for ind in np.arange(num_top_venues):\n",
    "    try:\n",
    "        columns.append('{}{} Most Common Venue'.format(ind+1, indicators[ind]))\n",
    "    except:\n",
    "        columns.append('{}th Most Common Venue'.format(ind+1))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "human-omega",
   "metadata": {},
   "source": [
    "Create a new Dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "above-injury",
   "metadata": {},
   "outputs": [],
   "source": [
    "neighborhoods_venues_sorted_microDistrict = pd.DataFrame(columns=columns)\n",
    "neighborhoods_venues_sorted_microDistrict['microDistrict'] = penza_grouped_by_microDistrict['microDistrict']\n",
    "\n",
    "for ind in np.arange(penza_grouped_by_microDistrict.shape[0]):\n",
    "    neighborhoods_venues_sorted_microDistrict.iloc[ind, 1:] = return_most_common_venues(penza_grouped_by_microDistrict.iloc[ind, :], num_top_venues)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "provincial-sculpture",
   "metadata": {},
   "source": [
    "Checking new Dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "mysterious-ebony",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "New DataFrame with sorted venues has 23 lines and 11 columns.\n"
     ]
    }
   ],
   "source": [
    "print('New DataFrame with sorted venues has {} lines and {} columns.'.format(neighborhoods_venues_sorted_microDistrict.shape[0], neighborhoods_venues_sorted_microDistrict.shape[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "surprised-beginning",
   "metadata": {},
   "outputs": [
    {
     "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>microDistrict</th>\n",
       "      <th>1st Most Common Venue</th>\n",
       "      <th>2nd Most Common Venue</th>\n",
       "      <th>3rd Most Common Venue</th>\n",
       "      <th>4th Most Common Venue</th>\n",
       "      <th>5th Most Common Venue</th>\n",
       "      <th>6th Most Common Venue</th>\n",
       "      <th>7th Most Common Venue</th>\n",
       "      <th>8th Most Common Venue</th>\n",
       "      <th>9th Most Common Venue</th>\n",
       "      <th>10th Most Common Venue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Арбеково</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Grocery Store</td>\n",
       "      <td>Liquor Store</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Wine Shop</td>\n",
       "      <td>Cosmetics Shop</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>German Restaurant</td>\n",
       "      <td>Gaming Cafe</td>\n",
       "      <td>French Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Бугровка</td>\n",
       "      <td>Café</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Coffee Shop</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Plaza</td>\n",
       "      <td>Theater</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ГПЗ</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Café</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Nightclub</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Supermarket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Гидрострой</td>\n",
       "      <td>Convenience Store</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Eastern European Restaurant</td>\n",
       "      <td>Hotel</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Bookstore</td>\n",
       "      <td>Hookah Bar</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>Grocery Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Глобус</td>\n",
       "      <td>Café</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Theater</td>\n",
       "      <td>Coffee Shop</td>\n",
       "      <td>Plaza</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ЖК «Арбековская застава»</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Grocery Store</td>\n",
       "      <td>Liquor Store</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Wine Shop</td>\n",
       "      <td>Cosmetics Shop</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>German Restaurant</td>\n",
       "      <td>Gaming Cafe</td>\n",
       "      <td>French Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Железнодорожный район</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Café</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Nightclub</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Supermarket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Заводской</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Grocery Store</td>\n",
       "      <td>Liquor Store</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Wine Shop</td>\n",
       "      <td>Cosmetics Shop</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>German Restaurant</td>\n",
       "      <td>Gaming Cafe</td>\n",
       "      <td>French Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Западная поляна</td>\n",
       "      <td>Convenience Store</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Eastern European Restaurant</td>\n",
       "      <td>Hotel</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Bookstore</td>\n",
       "      <td>Hookah Bar</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>Grocery Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Запрудный</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Grocery Store</td>\n",
       "      <td>Liquor Store</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Wine Shop</td>\n",
       "      <td>Cosmetics Shop</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>German Restaurant</td>\n",
       "      <td>Gaming Cafe</td>\n",
       "      <td>French Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Заречный</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Café</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Nightclub</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Supermarket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Новая Западная поляна</td>\n",
       "      <td>Convenience Store</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Eastern European Restaurant</td>\n",
       "      <td>Hotel</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Bookstore</td>\n",
       "      <td>Hookah Bar</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>Grocery Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Окружная</td>\n",
       "      <td>Convenience Store</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Eastern European Restaurant</td>\n",
       "      <td>Hotel</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Bookstore</td>\n",
       "      <td>Hookah Bar</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>Grocery Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Северная поляна</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Grocery Store</td>\n",
       "      <td>Liquor Store</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Wine Shop</td>\n",
       "      <td>Cosmetics Shop</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>German Restaurant</td>\n",
       "      <td>Gaming Cafe</td>\n",
       "      <td>French Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Сосновка</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Café</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Nightclub</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Supermarket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Стрела</td>\n",
       "      <td>Café</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Nightclub</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Theater</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Терновка</td>\n",
       "      <td>Convenience Store</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Eastern European Restaurant</td>\n",
       "      <td>Hotel</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Bookstore</td>\n",
       "      <td>Hookah Bar</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>Grocery Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Центр</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Café</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Theater</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Coffee Shop</td>\n",
       "      <td>Plaza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Цыганский посёлок</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Grocery Store</td>\n",
       "      <td>Liquor Store</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Wine Shop</td>\n",
       "      <td>Cosmetics Shop</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>German Restaurant</td>\n",
       "      <td>Gaming Cafe</td>\n",
       "      <td>French Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Шуист</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Café</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Nightclub</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Supermarket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Южная поляна</td>\n",
       "      <td>Convenience Store</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Eastern European Restaurant</td>\n",
       "      <td>Hotel</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Bookstore</td>\n",
       "      <td>Hookah Bar</td>\n",
       "      <td>Gym / Fitness Center</td>\n",
       "      <td>Grocery Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>остров Пески</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Café</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Plaza</td>\n",
       "      <td>Coffee Shop</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Theater</td>\n",
       "      <td>Electronics Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>пос.Ахуны</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>Café</td>\n",
       "      <td>Sushi Restaurant</td>\n",
       "      <td>Electronics Store</td>\n",
       "      <td>Nightclub</td>\n",
       "      <td>Food &amp; Drink Shop</td>\n",
       "      <td>Bar</td>\n",
       "      <td>Shopping Mall</td>\n",
       "      <td>Sporting Goods Shop</td>\n",
       "      <td>Supermarket</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               microDistrict 1st Most Common Venue 2nd Most Common Venue  \\\n",
       "0                   Арбеково         Shopping Mall         Grocery Store   \n",
       "1                   Бугровка                  Café            Restaurant   \n",
       "2                        ГПЗ            Restaurant                  Café   \n",
       "3                 Гидрострой     Convenience Store            Restaurant   \n",
       "4                     Глобус                  Café            Restaurant   \n",
       "5   ЖК «Арбековская застава»         Shopping Mall         Grocery Store   \n",
       "6      Железнодорожный район            Restaurant                  Café   \n",
       "7                  Заводской         Shopping Mall         Grocery Store   \n",
       "8            Западная поляна     Convenience Store            Restaurant   \n",
       "9                  Запрудный         Shopping Mall         Grocery Store   \n",
       "10                  Заречный            Restaurant                  Café   \n",
       "11     Новая Западная поляна     Convenience Store            Restaurant   \n",
       "12                  Окружная     Convenience Store            Restaurant   \n",
       "13           Северная поляна         Shopping Mall         Grocery Store   \n",
       "14                  Сосновка            Restaurant                  Café   \n",
       "15                    Стрела                  Café            Restaurant   \n",
       "16                  Терновка     Convenience Store            Restaurant   \n",
       "17                     Центр            Restaurant                  Café   \n",
       "18         Цыганский посёлок         Shopping Mall         Grocery Store   \n",
       "19                     Шуист            Restaurant                  Café   \n",
       "20              Южная поляна     Convenience Store            Restaurant   \n",
       "21              остров Пески            Restaurant                  Café   \n",
       "22                 пос.Ахуны            Restaurant                  Café   \n",
       "\n",
       "   3rd Most Common Venue        4th Most Common Venue 5th Most Common Venue  \\\n",
       "0           Liquor Store            Food & Drink Shop             Wine Shop   \n",
       "1          Shopping Mall             Sushi Restaurant                   Bar   \n",
       "2       Sushi Restaurant            Electronics Store             Nightclub   \n",
       "3      Food & Drink Shop  Eastern European Restaurant                 Hotel   \n",
       "4          Shopping Mall             Sushi Restaurant                   Bar   \n",
       "5           Liquor Store            Food & Drink Shop             Wine Shop   \n",
       "6       Sushi Restaurant            Electronics Store             Nightclub   \n",
       "7           Liquor Store            Food & Drink Shop             Wine Shop   \n",
       "8      Food & Drink Shop  Eastern European Restaurant                 Hotel   \n",
       "9           Liquor Store            Food & Drink Shop             Wine Shop   \n",
       "10      Sushi Restaurant            Electronics Store             Nightclub   \n",
       "11     Food & Drink Shop  Eastern European Restaurant                 Hotel   \n",
       "12     Food & Drink Shop  Eastern European Restaurant                 Hotel   \n",
       "13          Liquor Store            Food & Drink Shop             Wine Shop   \n",
       "14      Sushi Restaurant            Electronics Store             Nightclub   \n",
       "15      Sushi Restaurant            Electronics Store             Nightclub   \n",
       "16     Food & Drink Shop  Eastern European Restaurant                 Hotel   \n",
       "17         Shopping Mall             Sushi Restaurant                   Bar   \n",
       "18          Liquor Store            Food & Drink Shop             Wine Shop   \n",
       "19      Sushi Restaurant            Electronics Store             Nightclub   \n",
       "20     Food & Drink Shop  Eastern European Restaurant                 Hotel   \n",
       "21         Shopping Mall             Sushi Restaurant                   Bar   \n",
       "22      Sushi Restaurant            Electronics Store             Nightclub   \n",
       "\n",
       "   6th Most Common Venue 7th Most Common Venue 8th Most Common Venue  \\\n",
       "0         Cosmetics Shop  Gym / Fitness Center     German Restaurant   \n",
       "1      Electronics Store           Coffee Shop   Sporting Goods Shop   \n",
       "2      Food & Drink Shop                   Bar         Shopping Mall   \n",
       "3                    Bar             Bookstore            Hookah Bar   \n",
       "4      Electronics Store               Theater           Coffee Shop   \n",
       "5         Cosmetics Shop  Gym / Fitness Center     German Restaurant   \n",
       "6      Food & Drink Shop                   Bar         Shopping Mall   \n",
       "7         Cosmetics Shop  Gym / Fitness Center     German Restaurant   \n",
       "8                    Bar             Bookstore            Hookah Bar   \n",
       "9         Cosmetics Shop  Gym / Fitness Center     German Restaurant   \n",
       "10     Food & Drink Shop                   Bar         Shopping Mall   \n",
       "11                   Bar             Bookstore            Hookah Bar   \n",
       "12                   Bar             Bookstore            Hookah Bar   \n",
       "13        Cosmetics Shop  Gym / Fitness Center     German Restaurant   \n",
       "14     Food & Drink Shop                   Bar         Shopping Mall   \n",
       "15         Shopping Mall                   Bar               Theater   \n",
       "16                   Bar             Bookstore            Hookah Bar   \n",
       "17     Electronics Store               Theater   Sporting Goods Shop   \n",
       "18        Cosmetics Shop  Gym / Fitness Center     German Restaurant   \n",
       "19     Food & Drink Shop                   Bar         Shopping Mall   \n",
       "20                   Bar             Bookstore            Hookah Bar   \n",
       "21                 Plaza           Coffee Shop   Sporting Goods Shop   \n",
       "22     Food & Drink Shop                   Bar         Shopping Mall   \n",
       "\n",
       "   9th Most Common Venue 10th Most Common Venue  \n",
       "0            Gaming Cafe      French Restaurant  \n",
       "1                  Plaza                Theater  \n",
       "2    Sporting Goods Shop            Supermarket  \n",
       "3   Gym / Fitness Center          Grocery Store  \n",
       "4                  Plaza    Sporting Goods Shop  \n",
       "5            Gaming Cafe      French Restaurant  \n",
       "6    Sporting Goods Shop            Supermarket  \n",
       "7            Gaming Cafe      French Restaurant  \n",
       "8   Gym / Fitness Center          Grocery Store  \n",
       "9            Gaming Cafe      French Restaurant  \n",
       "10   Sporting Goods Shop            Supermarket  \n",
       "11  Gym / Fitness Center          Grocery Store  \n",
       "12  Gym / Fitness Center          Grocery Store  \n",
       "13           Gaming Cafe      French Restaurant  \n",
       "14   Sporting Goods Shop            Supermarket  \n",
       "15   Sporting Goods Shop      Food & Drink Shop  \n",
       "16  Gym / Fitness Center          Grocery Store  \n",
       "17           Coffee Shop                  Plaza  \n",
       "18           Gaming Cafe      French Restaurant  \n",
       "19   Sporting Goods Shop            Supermarket  \n",
       "20  Gym / Fitness Center          Grocery Store  \n",
       "21               Theater      Electronics Store  \n",
       "22   Sporting Goods Shop            Supermarket  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neighborhoods_venues_sorted_microDistrict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "shaped-bumper",
   "metadata": {},
   "source": [
    "___\n",
    "### 2.9. Saving the resulting tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "killing-america",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_Penza.to_csv('df_Penza.csv', encoding='utf-8', index=False)\n",
    "penza_grouped_by_microDistrict.to_csv('penza_grouped_by_microDistrict.csv', encoding='utf-8', index=False)\n",
    "neighborhoods_venues_sorted_microDistrict.to_csv('neighborhoods_venues_sorted_microDistrict.csv', encoding='utf-8', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}