Projects_03_Valeeva.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Project Week 3 Valeeva"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Houses in emergency condition in Sverdlovsk region, Russia\n",
    "\n",
    "\"In the comfort of your home\" sounds like a sad joke for thousands of people in Russia who are living in the emergency conditions because their houses are too old, in physical deterioration or even have survived a fire. \n",
    "\n",
    "There is a government program for the resettlement, but does it work as planned?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.get_option(\"display.max_columns\", 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"Alarm_Sverd_Clean.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Questions to data:\n",
    "- How many houses in emergency condition are in Sverdlovsk region? Is it a lot?\n",
    "- How many people live in them? Is it a lot? \n",
    "- additional question: which is the worst municipal district by the % of emergency houses?\n",
    "    \n",
    "- How many years does it take for a house to get into the emergency condition?\n",
    "- What are the most popular reasons?\n",
    "- How much time does it take for resettlement?\n",
    "- Does resettlement happen according to the plan?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total number of houses in emergency condition in the region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3398, 74)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#I want to see how many houses are there, where the actual resettlment date is not filled yet\n",
    "#df['actual_resettlement_date'] != 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a total of 3398 houses in emergency condition in Sverdlovsk region, which is right in the middle of Russia. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### % of houses in the region that are in emergency condition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#save to a new var\n",
    "round((len(df.index)/42210)*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### total number of people living in these conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45607.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.residents_count.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### % of people in the region that live in emergency condition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(df.residents_count.sum()/4290067*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.info(verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    5/17/2018\n",
       "1    9/28/2017\n",
       "2    1/12/2004\n",
       "3    1/12/2004\n",
       "4    4/29/1980\n",
       "Name: determined_date, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.determined_date.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1911.0\n",
       "1    1951.0\n",
       "2    1965.0\n",
       "3    1955.0\n",
       "4    1930.0\n",
       "Name: exploitation_start_year, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.exploitation_start_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.determined_date.str.split(pat=\"/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "new = df.determined_date.str.split(\"/\", n = 2, expand = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>17</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>28</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>29</td>\n",
       "      <td>1980</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0   1     2\n",
       "0  5  17  2018\n",
       "1  9  28  2017\n",
       "2  1  12  2004\n",
       "3  1  12  2004\n",
       "4  4  29  1980"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"determined_year\"] = new[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2018\n",
       "1    2017\n",
       "2    2004\n",
       "3    2004\n",
       "4    1980\n",
       "Name: determined_year, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.determined_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna(subset=['determined_year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.determined_year = df.determined_year.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       1911.0\n",
       "1       1951.0\n",
       "2       1965.0\n",
       "3       1955.0\n",
       "4       1930.0\n",
       "         ...  \n",
       "3393    1958.0\n",
       "3394    1975.0\n",
       "3395    1960.0\n",
       "3396    1962.0\n",
       "3397    1960.0\n",
       "Name: exploitation_start_year, Length: 1755, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.exploitation_start_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['exploitation']=(df.determined_year-df.exploitation_start_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## On average, houses have been in use for 61 years before they are determined as emergency housing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "61.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['exploitation'].median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### there are, however, some outliers, such as the oldest house which is more than 200 years old..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "204.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['exploitation'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>house_id</th>\n",
       "      <th>region_id</th>\n",
       "      <th>area_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>street_id</th>\n",
       "      <th>shortname_region</th>\n",
       "      <th>formalname_region</th>\n",
       "      <th>shortname_area</th>\n",
       "      <th>formalname_area</th>\n",
       "      <th>shortname_city</th>\n",
       "      <th>formalname_city</th>\n",
       "      <th>shortname_street</th>\n",
       "      <th>formalname_street</th>\n",
       "      <th>house_number</th>\n",
       "      <th>building</th>\n",
       "      <th>block</th>\n",
       "      <th>letter</th>\n",
       "      <th>address</th>\n",
       "      <th>houseguid</th>\n",
       "      <th>cadastral_number</th>\n",
       "      <th>cadastral_registration_date</th>\n",
       "      <th>stage</th>\n",
       "      <th>built_year</th>\n",
       "      <th>exploitation_start_year</th>\n",
       "      <th>project_type</th>\n",
       "      <th>house_type</th>\n",
       "      <th>floor_count_max</th>\n",
       "      <th>floor_count_min</th>\n",
       "      <th>entrance_count</th>\n",
       "      <th>residents_count</th>\n",
       "      <th>area_total</th>\n",
       "      <th>area_residential</th>\n",
       "      <th>area_non_residential</th>\n",
       "      <th>area_common_property</th>\n",
       "      <th>area_land</th>\n",
       "      <th>monument_status</th>\n",
       "      <th>monument_document_date</th>\n",
       "      <th>monument_document_number</th>\n",
       "      <th>deterioration_total</th>\n",
       "      <th>determined_date</th>\n",
       "      <th>floor_type</th>\n",
       "      <th>wall_material</th>\n",
       "      <th>alarm_reason</th>\n",
       "      <th>alarm_reason_other</th>\n",
       "      <th>alarm_document_number</th>\n",
       "      <th>alarm_document_date</th>\n",
       "      <th>alarm_has_file</th>\n",
       "      <th>exp_name</th>\n",
       "      <th>exp_document_number</th>\n",
       "      <th>exp_document_date</th>\n",
       "      <th>exp_has_file</th>\n",
       "      <th>commission_document_number</th>\n",
       "      <th>commission_document_date</th>\n",
       "      <th>commission_has_file</th>\n",
       "      <th>further_use</th>\n",
       "      <th>decision_deadline_date</th>\n",
       "      <th>planned_resettlement_date</th>\n",
       "      <th>actual_resettlement_date</th>\n",
       "      <th>planned_demolition_date</th>\n",
       "      <th>planned_rooms_count</th>\n",
       "      <th>planned_rooms_square</th>\n",
       "      <th>planned_ppl_count</th>\n",
       "      <th>resettled_rooms_count</th>\n",
       "      <th>resettled_rooms_square</th>\n",
       "      <th>resettled_ppl_count</th>\n",
       "      <th>empty_rooms_count</th>\n",
       "      <th>empty_rooms_square</th>\n",
       "      <th>redemption_amount</th>\n",
       "      <th>planned_providing_area_square</th>\n",
       "      <th>address2</th>\n",
       "      <th>rest</th>\n",
       "      <th>rest.1</th>\n",
       "      <th>Unnamed: 72</th>\n",
       "      <th>Unnamed: 73</th>\n",
       "      <th>determined_year</th>\n",
       "      <th>exploitation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1152</th>\n",
       "      <td>8927972</td>\n",
       "      <td>92b30014-4d52-4e2e-892d-928142b924bf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4a90bd67-7682-4012-a0c5-09865b9d08a0</td>\n",
       "      <td>f34a5290-2b18-4649-a8ec-acf63bca8948</td>\n",
       "      <td>обл</td>\n",
       "      <td>Свердловская</td>\n",
       "      <td>NaN</td>\n",
       "      <td>г. Ирбит</td>\n",
       "      <td>г</td>\n",
       "      <td>Ирбит</td>\n",
       "      <td>ул</td>\n",
       "      <td>Ленина</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>обл. Свердловская, г. Ирбит, ул. Ленина, д. 19</td>\n",
       "      <td>32d0e7de-079b-6e0a-b2cc-159ec5fa7b93</td>\n",
       "      <td>66:44:0101005:93</td>\n",
       "      <td>10/5/2005</td>\n",
       "      <td>Эксплуатируемый</td>\n",
       "      <td>1808.0</td>\n",
       "      <td>1808.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Многоквартирный дом</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>1730,5</td>\n",
       "      <td>1244,7</td>\n",
       "      <td>0</td>\n",
       "      <td>485,8</td>\n",
       "      <td>1601</td>\n",
       "      <td>Не имеется</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77</td>\n",
       "      <td>12/10/2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Физический износ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1196-а</td>\n",
       "      <td>5/23/2013</td>\n",
       "      <td>Имеется</td>\n",
       "      <td>ООО \"Актив Проект\"</td>\n",
       "      <td>0362300228312000053-0198328-01</td>\n",
       "      <td>12/10/2012</td>\n",
       "      <td>Имеется</td>\n",
       "      <td>29</td>\n",
       "      <td>5/23/2013</td>\n",
       "      <td>Имеется</td>\n",
       "      <td>Снос</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9/1/2025</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2025</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.0</td>\n",
       "      <td>845,7</td>\n",
       "      <td>87.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>36,6</td>\n",
       "      <td>38080,36</td>\n",
       "      <td>875,11</td>\n",
       "      <td>обл. Свердловская</td>\n",
       "      <td>ул. Ленина</td>\n",
       "      <td>д. 19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012</td>\n",
       "      <td>204.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      house_id                             region_id area_id  \\\n",
       "1152   8927972  92b30014-4d52-4e2e-892d-928142b924bf     NaN   \n",
       "\n",
       "                                   city_id  \\\n",
       "1152  4a90bd67-7682-4012-a0c5-09865b9d08a0   \n",
       "\n",
       "                                 street_id shortname_region formalname_region  \\\n",
       "1152  f34a5290-2b18-4649-a8ec-acf63bca8948              обл      Свердловская   \n",
       "\n",
       "     shortname_area formalname_area shortname_city formalname_city  \\\n",
       "1152            NaN        г. Ирбит              г           Ирбит   \n",
       "\n",
       "     shortname_street formalname_street house_number building block letter  \\\n",
       "1152               ул            Ленина           19      NaN   NaN    NaN   \n",
       "\n",
       "                                             address  \\\n",
       "1152  обл. Свердловская, г. Ирбит, ул. Ленина, д. 19   \n",
       "\n",
       "                                 houseguid  cadastral_number  \\\n",
       "1152  32d0e7de-079b-6e0a-b2cc-159ec5fa7b93  66:44:0101005:93   \n",
       "\n",
       "     cadastral_registration_date            stage  built_year  \\\n",
       "1152                   10/5/2005  Эксплуатируемый      1808.0   \n",
       "\n",
       "      exploitation_start_year project_type           house_type  \\\n",
       "1152                   1808.0          NaN  Многоквартирный дом   \n",
       "\n",
       "      floor_count_max  floor_count_min  entrance_count  residents_count  \\\n",
       "1152              2.0              2.0             2.0             87.0   \n",
       "\n",
       "     area_total area_residential area_non_residential area_common_property  \\\n",
       "1152     1730,5           1244,7                    0                485,8   \n",
       "\n",
       "     area_land monument_status monument_document_date  \\\n",
       "1152      1601      Не имеется                    NaN   \n",
       "\n",
       "     monument_document_number deterioration_total determined_date floor_type  \\\n",
       "1152                      NaN                  77      12/10/2012        NaN   \n",
       "\n",
       "     wall_material      alarm_reason alarm_reason_other alarm_document_number  \\\n",
       "1152           NaN  Физический износ                NaN                1196-а   \n",
       "\n",
       "     alarm_document_date alarm_has_file            exp_name  \\\n",
       "1152           5/23/2013        Имеется  ООО \"Актив Проект\"   \n",
       "\n",
       "                 exp_document_number exp_document_date exp_has_file  \\\n",
       "1152  0362300228312000053-0198328-01        12/10/2012      Имеется   \n",
       "\n",
       "     commission_document_number commission_document_date commission_has_file  \\\n",
       "1152                         29                5/23/2013             Имеется   \n",
       "\n",
       "     further_use decision_deadline_date planned_resettlement_date  \\\n",
       "1152        Снос                    NaN                  9/1/2025   \n",
       "\n",
       "     actual_resettlement_date planned_demolition_date  planned_rooms_count  \\\n",
       "1152                      NaN              12/31/2025                  NaN   \n",
       "\n",
       "     planned_rooms_square  planned_ppl_count  resettled_rooms_count  \\\n",
       "1152                  NaN                NaN                   45.0   \n",
       "\n",
       "     resettled_rooms_square  resettled_ppl_count  empty_rooms_count  \\\n",
       "1152                  845,7                 87.0                2.0   \n",
       "\n",
       "     empty_rooms_square redemption_amount planned_providing_area_square  \\\n",
       "1152               36,6          38080,36                        875,11   \n",
       "\n",
       "               address2        rest rest.1 Unnamed: 72 Unnamed: 73  \\\n",
       "1152  обл. Свердловская  ул. Ленина  д. 19         NaN         NaN   \n",
       "\n",
       "      determined_year  exploitation  \n",
       "1152             2012         204.0  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['exploitation'] == 204]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### or baby houses like this one which is just 1 year old but is already in emergency condition!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['exploitation'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>house_id</th>\n",
       "      <th>region_id</th>\n",
       "      <th>area_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>street_id</th>\n",
       "      <th>shortname_region</th>\n",
       "      <th>formalname_region</th>\n",
       "      <th>shortname_area</th>\n",
       "      <th>formalname_area</th>\n",
       "      <th>shortname_city</th>\n",
       "      <th>formalname_city</th>\n",
       "      <th>shortname_street</th>\n",
       "      <th>formalname_street</th>\n",
       "      <th>house_number</th>\n",
       "      <th>building</th>\n",
       "      <th>block</th>\n",
       "      <th>letter</th>\n",
       "      <th>address</th>\n",
       "      <th>houseguid</th>\n",
       "      <th>cadastral_number</th>\n",
       "      <th>cadastral_registration_date</th>\n",
       "      <th>stage</th>\n",
       "      <th>built_year</th>\n",
       "      <th>exploitation_start_year</th>\n",
       "      <th>project_type</th>\n",
       "      <th>house_type</th>\n",
       "      <th>floor_count_max</th>\n",
       "      <th>floor_count_min</th>\n",
       "      <th>entrance_count</th>\n",
       "      <th>residents_count</th>\n",
       "      <th>area_total</th>\n",
       "      <th>area_residential</th>\n",
       "      <th>area_non_residential</th>\n",
       "      <th>area_common_property</th>\n",
       "      <th>area_land</th>\n",
       "      <th>monument_status</th>\n",
       "      <th>monument_document_date</th>\n",
       "      <th>monument_document_number</th>\n",
       "      <th>deterioration_total</th>\n",
       "      <th>determined_date</th>\n",
       "      <th>floor_type</th>\n",
       "      <th>wall_material</th>\n",
       "      <th>alarm_reason</th>\n",
       "      <th>alarm_reason_other</th>\n",
       "      <th>alarm_document_number</th>\n",
       "      <th>alarm_document_date</th>\n",
       "      <th>alarm_has_file</th>\n",
       "      <th>exp_name</th>\n",
       "      <th>exp_document_number</th>\n",
       "      <th>exp_document_date</th>\n",
       "      <th>exp_has_file</th>\n",
       "      <th>commission_document_number</th>\n",
       "      <th>commission_document_date</th>\n",
       "      <th>commission_has_file</th>\n",
       "      <th>further_use</th>\n",
       "      <th>decision_deadline_date</th>\n",
       "      <th>planned_resettlement_date</th>\n",
       "      <th>actual_resettlement_date</th>\n",
       "      <th>planned_demolition_date</th>\n",
       "      <th>planned_rooms_count</th>\n",
       "      <th>planned_rooms_square</th>\n",
       "      <th>planned_ppl_count</th>\n",
       "      <th>resettled_rooms_count</th>\n",
       "      <th>resettled_rooms_square</th>\n",
       "      <th>resettled_ppl_count</th>\n",
       "      <th>empty_rooms_count</th>\n",
       "      <th>empty_rooms_square</th>\n",
       "      <th>redemption_amount</th>\n",
       "      <th>planned_providing_area_square</th>\n",
       "      <th>address2</th>\n",
       "      <th>rest</th>\n",
       "      <th>rest.1</th>\n",
       "      <th>Unnamed: 72</th>\n",
       "      <th>Unnamed: 73</th>\n",
       "      <th>determined_year</th>\n",
       "      <th>exploitation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1074</th>\n",
       "      <td>8596386</td>\n",
       "      <td>92b30014-4d52-4e2e-892d-928142b924bf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c32d06c4-cca2-42f4-a3b3-b2fee48b9585</td>\n",
       "      <td>14dc9681-edcf-4307-bdbe-d52ad1daad47</td>\n",
       "      <td>обл</td>\n",
       "      <td>Свердловская</td>\n",
       "      <td>NaN</td>\n",
       "      <td>г. Ивдель</td>\n",
       "      <td>г</td>\n",
       "      <td>Ивдель</td>\n",
       "      <td>ул</td>\n",
       "      <td>Клубная</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>обл. Свердловская, г. Ивдель, ул. Клубная, д. 1</td>\n",
       "      <td>a0121391-3a02-454e-a2d7-4d2e30c1ad88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Эксплуатируемый</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>деревянный</td>\n",
       "      <td>Многоквартирный дом</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>353,4</td>\n",
       "      <td>327,4</td>\n",
       "      <td>0</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Не заполнено</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>11/16/1971</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Физический износ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>297</td>\n",
       "      <td>4/23/2015</td>\n",
       "      <td>Имеется</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Не имеется</td>\n",
       "      <td>1</td>\n",
       "      <td>3/23/2015</td>\n",
       "      <td>Имеется</td>\n",
       "      <td>Снос</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2024</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31/2025</td>\n",
       "      <td>18.0</td>\n",
       "      <td>327,4</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>обл. Свердловская</td>\n",
       "      <td>ул. Клубная</td>\n",
       "      <td>д. 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1971</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      house_id                             region_id area_id  \\\n",
       "1074   8596386  92b30014-4d52-4e2e-892d-928142b924bf     NaN   \n",
       "\n",
       "                                   city_id  \\\n",
       "1074  c32d06c4-cca2-42f4-a3b3-b2fee48b9585   \n",
       "\n",
       "                                 street_id shortname_region formalname_region  \\\n",
       "1074  14dc9681-edcf-4307-bdbe-d52ad1daad47              обл      Свердловская   \n",
       "\n",
       "     shortname_area formalname_area shortname_city formalname_city  \\\n",
       "1074            NaN       г. Ивдель              г          Ивдель   \n",
       "\n",
       "     shortname_street formalname_street house_number building block letter  \\\n",
       "1074               ул           Клубная            1      NaN   NaN    NaN   \n",
       "\n",
       "                                              address  \\\n",
       "1074  обл. Свердловская, г. Ивдель, ул. Клубная, д. 1   \n",
       "\n",
       "                                 houseguid cadastral_number  \\\n",
       "1074  a0121391-3a02-454e-a2d7-4d2e30c1ad88              NaN   \n",
       "\n",
       "     cadastral_registration_date            stage  built_year  \\\n",
       "1074                         NaN  Эксплуатируемый      1970.0   \n",
       "\n",
       "      exploitation_start_year project_type           house_type  \\\n",
       "1074                   1970.0   деревянный  Многоквартирный дом   \n",
       "\n",
       "      floor_count_max  floor_count_min  entrance_count  residents_count  \\\n",
       "1074              2.0              2.0             1.0             19.0   \n",
       "\n",
       "     area_total area_residential area_non_residential area_common_property  \\\n",
       "1074      353,4            327,4                    0                   26   \n",
       "\n",
       "     area_land monument_status monument_document_date  \\\n",
       "1074       NaN    Не заполнено                    NaN   \n",
       "\n",
       "     monument_document_number deterioration_total determined_date floor_type  \\\n",
       "1074                      NaN                  31      11/16/1971        NaN   \n",
       "\n",
       "     wall_material      alarm_reason alarm_reason_other alarm_document_number  \\\n",
       "1074           NaN  Физический износ                NaN                   297   \n",
       "\n",
       "     alarm_document_date alarm_has_file exp_name exp_document_number  \\\n",
       "1074           4/23/2015        Имеется      NaN                 NaN   \n",
       "\n",
       "     exp_document_date exp_has_file commission_document_number  \\\n",
       "1074               NaN   Не имеется                          1   \n",
       "\n",
       "     commission_document_date commission_has_file further_use  \\\n",
       "1074                3/23/2015             Имеется        Снос   \n",
       "\n",
       "     decision_deadline_date planned_resettlement_date  \\\n",
       "1074                    NaN                12/31/2024   \n",
       "\n",
       "     actual_resettlement_date planned_demolition_date  planned_rooms_count  \\\n",
       "1074                      NaN              12/31/2025                 18.0   \n",
       "\n",
       "     planned_rooms_square  planned_ppl_count  resettled_rooms_count  \\\n",
       "1074                327,4               19.0                    NaN   \n",
       "\n",
       "     resettled_rooms_square  resettled_ppl_count  empty_rooms_count  \\\n",
       "1074                    NaN                  NaN                NaN   \n",
       "\n",
       "     empty_rooms_square redemption_amount planned_providing_area_square  \\\n",
       "1074                NaN               NaN                           NaN   \n",
       "\n",
       "               address2         rest rest.1 Unnamed: 72 Unnamed: 73  \\\n",
       "1074  обл. Свердловская  ул. Клубная   д. 1         NaN         NaN   \n",
       "\n",
       "      determined_year  exploitation  \n",
       "1074             1971           1.0  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['exploitation'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Here is a distribution of the age of houses that are in emergency condition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x118566820>"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYUAAAD5CAYAAADItClGAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAVKklEQVR4nO3da7BdZ33f8e8PGWRMTG3HsqpKciRnFBM5E7B7cNLh0oBCfAuWScZUTNrRpG6UTJUWJk2LBBlCX2jGtA25TOsQEWgEAYwMMVYCSRBqgOlMQZaNjS3LqgQW9kGKpJhmzG3kSPn3xV5nZVs6ly35rL2Pfb6fmTNrrWc/a6//efbW+Wnd9k5VIUkSwAtGXYAkae4wFCRJLUNBktQyFCRJLUNBktQyFCRJrfO6euIkVwIf72u6AngX8KGmfQVwCHhzVf2/Zp3NwG3AKeDfV9VfTreNSy+9tFasWDHbpUvS89p99933N1W1aLLHMoz7FJIsAL4J/ASwEfhWVd2eZBNwcVW9Pclq4GPAtcA/AT4H/EhVnZrqecfGxmrPnj2d1y9JzydJ7quqsckeG9bhozXA16rqG8BaYFvTvg24pZlfC9xZVSeq6jHgIL2AkCQNybBCYR29vQCAxVV1BKCZXta0LwWe6FtnvGmTJA1J56GQ5EXAzcBdM3WdpO2MY1tJNiTZk2TP8ePHZ6NESVJjGHsKNwD3V9XRZvlokiUAzfRY0z4OLO9bbxlw+PQnq6qtVTVWVWOLFk16nkSSdI6GEQpv4R8OHQHsANY38+uBe/ra1yVZmGQlsArYPYT6JEmNzi5JBUhyAfAG4Jf7mm8Htie5DXgcuBWgqvYm2Q48ApwENk535ZEkafZ1GgpV9T3gB09re5Le1UiT9d8CbOmyJknS1LyjWZLUMhQkSa1ODx9pblmx6dMj2/ah228a2bYlDc49BUlSy1CQJLUMBUlSy1CQJLUMBUlSy1CQJLUMBUlSy1CQJLUMBUlSy1CQJLUMBUlSy1CQJLUMBUlSy1CQJLUMBUlSy1CQJLUMBUlSy1CQJLUMBUlSq9NQSHJRkk8keTTJviT/LMklSXYmOdBML+7rvznJwST7k1zXZW2SpDN1vafwu8BfVNXLgJcD+4BNwK6qWgXsapZJshpYB1wFXA/ckWRBx/VJkvp0FgpJXgq8FvgAQFU9XVV/C6wFtjXdtgG3NPNrgTur6kRVPQYcBK7tqj5J0pm63FO4AjgO/M8kX0nyh0leAiyuqiMAzfSypv9S4Im+9cebNknSkHQZCucB1wC/X1VXA9+lOVQ0hUzSVmd0SjYk2ZNkz/Hjx2enUkkS0G0ojAPjVfXlZvkT9ELiaJIlAM30WF//5X3rLwMOn/6kVbW1qsaqamzRokWdFS9J81FnoVBVfw08keTKpmkN8AiwA1jftK0H7mnmdwDrkixMshJYBezuqj5J0pnO6/j5/x3wkSQvAr4O/CK9INqe5DbgceBWgKram2Q7veA4CWysqlMd1ydJ6tNpKFTVA8DYJA+tmaL/FmBLlzVJkqbmHc2SpJahIElqGQqSpJahIElqGQqSpJahIElqGQqSpJahIElqGQqSpJahIElqGQqSpJahIElqGQqSpJahIElqGQqSpJahIElqGQqSpFbXX8cpAbBi06dHst1Dt980ku1Kz1XuKUiSWoaCJKllKEiSWp2GQpJDSR5K8kCSPU3bJUl2JjnQTC/u6785ycEk+5Nc12VtkqQzDWNP4XVV9YqqGmuWNwG7qmoVsKtZJslqYB1wFXA9cEeSBUOoT5LUGMXho7XAtmZ+G3BLX/udVXWiqh4DDgLXjqA+SZq3ug6FAj6b5L4kG5q2xVV1BKCZXta0LwWe6Ft3vGmTJA1J1/cpvKqqDie5DNiZ5NFp+maStjqjUy9cNgBcfvnls1OlJAnoeE+hqg4302PA3fQOBx1NsgSgmR5ruo8Dy/tWXwYcnuQ5t1bVWFWNLVq0qMvyJWne6SwUkrwkyYUT88DPAA8DO4D1Tbf1wD3N/A5gXZKFSVYCq4DdXdUnSTpTl4ePFgN3J5nYzker6i+S3AtsT3Ib8DhwK0BV7U2yHXgEOAlsrKpTHdYnSTpNZ6FQVV8HXj5J+5PAminW2QJs6aomSdL0vKNZktQyFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQaKBSS/FjXhUiSRm/QPYX3Jdmd5N8muajTiiRJIzNQKFTVq4FfAJYDe5J8NMkbOq1MkjR0A59TqKoDwG8Abwf+OfB7SR5N8nNdFSdJGq5Bzyn8eJLfBvYBrwfeWFU/2sz/dof1SZKG6LwB+/134P3AO6rq+xONVXU4yW90UpkkaegGDYUbge9X1SmAJC8Azq+q71XVhzurTpI0VIOeU/gc8OK+5QuathklWZDkK0n+rFm+JMnOJAea6cV9fTcnOZhkf5LrBv0lJEmzY9BQOL+qvjOx0MxfMOC6b6V3LmLCJmBXVa0CdjXLJFkNrAOuAq4H7kiyYMBtSJJmwaCh8N0k10wsJPmnwPen6T/RbxlwE/CHfc1rgW3N/Dbglr72O6vqRFU9BhwErh2wPknSLBj0nMLbgLuSHG6WlwD/YoD1fgf4T8CFfW2Lq+oIQFUdSXJZ074U+FJfv/GmTZI0JAOFQlXdm+RlwJVAgEer6u+mWyfJzwLHquq+JD81wGYy2aYned4NwAaAyy+/fICnlSQNatA9BYBXAiuada5OQlV9aJr+rwJuTnIjcD7w0iR/DBxNsqTZS1gCHGv6j9O7Y3rCMuAwp6mqrcBWgLGxsTNCQ5J07ga9ee3DwH8DXk0vHF4JjE23TlVtrqplVbWC3gnk/1VV/xLYAaxvuq0H7mnmdwDrkixMshJYBew+u19HkvRsDLqnMAasrqrZ+J/57cD2JLcBjwO3AlTV3iTbgUeAk8DGifsiJEnDMWgoPAz8Y+DIuWykqj4PfL6ZfxJYM0W/LcCWc9mGJOnZGzQULgUeSbIbODHRWFU3d1KVJGkkBg2Fd3dZhCRpbhj0ktQvJPkhYFVVfS7JBYB3G0vS88ygVx/9EvAJ4A+apqXAp7oqSpI0GoN+zMVGevcdPAXtF+5cNu0akqTnnEFD4URVPT2xkOQ8JrnbWJL03DZoKHwhyTuAFzffzXwX8KfdlSVJGoVBQ2ETcBx4CPhl4DP0vq9ZkvQ8MujVR39P7+s4399tOZKkURooFJI8xiTnEKrqilmvSJI0Mmfz2UcTzqf3eUWXzH45kqRRGuicQlU92ffzzar6HeD1HdcmSRqyQQ8fXdO3+AJ6ew4XTtFdkvQcNejho9/qmz8JHALePOvVSJJGatCrj17XdSGSpNEb9PDRr033eFW9d3bKkSSN0tlcffRKel+ZCfBG4IvAE10UJUkajbP5kp1rqurbAEneDdxVVf+mq8IkScM36MdcXA483bf8NLBi1quRJI3UoHsKHwZ2J7mb3p3NbwI+1FlVkqSRGPTqoy1J/hx4TdP0i1X1le7KkiSNwqCHjwAuAJ6qqt8FxpOsnK5zkvOT7E7yYJK9Sf5z035Jkp1JDjTTi/vW2ZzkYJL9Sa47p99IknTOBv06zt8E3g5sbppeCPzxDKudAF5fVS8HXgFcn+Qn6X0M966qWgXsapZJshpYB1wFXA/ckcTvgZakIRp0T+FNwM3AdwGq6jAzfMxF9XynWXxh81PAWmBb074NuKWZXwvcWVUnquox4CBw7YD1SZJmwaCh8HRVFc3HZyd5ySArJVmQ5AHgGLCzqr4MLK6qIwDNdOK7npfyzPsexps2SdKQDBoK25P8AXBRkl8CPscAX7hTVaeq6hXAMuDaJD82TfdM9hRndEo2JNmTZM/x48cHLF+SNIgZrz5KEuDjwMuAp4ArgXdV1c5BN1JVf5vk8/TOFRxNsqSqjiRZQm8vAnp7Bsv7VlsGHJ7kubYCWwHGxsbOCA1J0rmbcU+hOWz0qaraWVX/sap+fZBASLIoyUXN/IuBnwYepfdRGeubbuuBe5r5HcC6JAubK5tWAbvP+jeSJJ2zQW9e+1KSV1bVvWfx3EuAbc0VRC8AtlfVnyX5P/QOR90GPE7vW9yoqr1JtgOP0Pt47o1VdeostidJepYGDYXXAb+S5BC9K5BCbyfix6daoaq+Clw9SfuTwJop1tkCbBmwJknSLJs2FJJcXlWPAzcMqR5J0gjNtKfwKXqfjvqNJJ+sqp8fRlGSpNGY6URz/2WiV3RZiCRp9GYKhZpiXpL0PDTT4aOXJ3mK3h7Di5t5+IcTzS/ttDpJ0lBNGwpV5QfSSdI8cjYfnS1Jep4zFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQyFCRJLUNBktQyFCRJrZm+T0F6Tlux6dMj2/ah228a2balc+WegiSp1VkoJFme5K+S7EuyN8lbm/ZLkuxMcqCZXty3zuYkB5PsT3JdV7VJkibX5Z7CSeA/VNWPAj8JbEyyGtgE7KqqVcCuZpnmsXXAVcD1wB1J/OY3SRqizkKhqo5U1f3N/LeBfcBSYC2wrem2DbilmV8L3FlVJ6rqMeAgcG1X9UmSzjSUcwpJVgBXA18GFlfVEegFB3BZ020p8ETfauNNmyRpSDoPhSQ/AHwSeFtVPTVd10naapLn25BkT5I9x48fn60yJUl0HApJXkgvED5SVX/SNB9NsqR5fAlwrGkfB5b3rb4MOHz6c1bV1qoaq6qxRYsWdVe8JM1DXV59FOADwL6qem/fQzuA9c38euCevvZ1SRYmWQmsAnZ3VZ8k6Uxd3rz2KuBfAQ8leaBpewdwO7A9yW3A48CtAFW1N8l24BF6Vy5trKpTHdYnSTpNZ6FQVf+byc8TAKyZYp0twJauapIkTc87miVJLUNBktQyFCRJLUNBktQyFCRJLUNBktTyS3ZGYJRf/CJJ03FPQZLUMhQkSS1DQZLUMhQkSS1DQZLUMhQkSS1DQZLUMhQkSS1DQZLUMhQkSS1DQZLUMhQkSS1DQZLUMhQkSa3OQiHJB5McS/JwX9slSXYmOdBML+57bHOSg0n2J7muq7okSVPrck/hj4DrT2vbBOyqqlXArmaZJKuBdcBVzTp3JFnQYW2SpEl0FgpV9UXgW6c1rwW2NfPbgFv62u+sqhNV9RhwELi2q9okSZMb9jmFxVV1BKCZXta0LwWe6Os33rRJkoZorpxoziRtNWnHZEOSPUn2HD9+vOOyJGl+GXYoHE2yBKCZHmvax4Hlff2WAYcne4Kq2lpVY1U1tmjRok6LlaT5ZtihsANY38yvB+7pa1+XZGGSlcAqYPeQa5Okee+8rp44yceAnwIuTTIO/CZwO7A9yW3A48CtAFW1N8l24BHgJLCxqk51VZskaXKdhUJVvWWKh9ZM0X8LsKWreiRJM5srJ5olSXOAoSBJahkKkqSWoSBJahkKkqSWoSBJahkKkqSWoSBJahkKkqSWoSBJahkKkqSWoSBJahkKkqSWoSBJahkKkqSWoSBJahkKkqSWoSBJahkKkqRWZ9/RLM13KzZ9eiTbPXT7TSPZrp4f3FOQJLUMBUlSa86FQpLrk+xPcjDJplHXI0nzyZw6p5BkAfA/gDcA48C9SXZU1SNdbG9Ux3ylLo3yfe35jOe+ubancC1wsKq+XlVPA3cCa0dckyTNG3NqTwFYCjzRtzwO/MSIapF0lubbFVfPx72yuRYKmaStntEh2QBsaBa/k2T/OWznUuBvzmG9rs3FuqxpcHOxrrlYE8xyXXnPrDzNc2qsnuXv/ENTPTDXQmEcWN63vAw43N+hqrYCW5/NRpLsqaqxZ/McXZiLdVnT4OZiXXOxJpibdc3FmmD4dc21cwr3AquSrEzyImAdsGPENUnSvDGn9hSq6mSSXwX+ElgAfLCq9o64LEmaN+ZUKABU1WeAz3S8mWd1+KlDc7EuaxrcXKxrLtYEc7OuuVgTDLmuVNXMvSRJ88JcO6cgSRqheRUKc+UjNJIsT/JXSfYl2ZvkrU37u5N8M8kDzc+NQ67rUJKHmm3vadouSbIzyYFmevGQa7qybzweSPJUkreNYqySfDDJsSQP97VNOT5JNjfvtf1JrhtiTf81yaNJvprk7iQXNe0rkny/b8ze10VN09Q15Ws2wrH6eF89h5I80LQPZaym+VswuvdVVc2LH3onrr8GXAG8CHgQWD2iWpYA1zTzFwL/F1gNvBv49RGO0SHg0tPa/guwqZnfBLxnxK/hX9O7xnroYwW8FrgGeHim8WlezweBhcDK5r23YEg1/QxwXjP/nr6aVvT3G8FYTfqajXKsTnv8t4B3DXOspvlbMLL31XzaU5gzH6FRVUeq6v5m/tvAPnp3c89Fa4Ftzfw24JYR1rIG+FpVfWMUG6+qLwLfOq15qvFZC9xZVSeq6jHgIL33YOc1VdVnq+pks/glevf7DNUUYzWVkY3VhCQB3gx8bLa3O0NNU/0tGNn7aj6FwmQfoTHyP8RJVgBXA19umn612e3/4LAP1dC7e/yzSe5r7hwHWFxVR6D3BgYuG3JN/dbxzH+0oxyrCVONz1x5v/1r4M/7llcm+UqSLyR5zQjqmew1mwtj9RrgaFUd6Gsb6lid9rdgZO+r+RQKM36ExrAl+QHgk8Dbquop4PeBHwZeARyhtzs7TK+qqmuAG4CNSV475O1PKb2bGW8G7mqaRj1WMxn5+y3JO4GTwEeapiPA5VV1NfBrwEeTvHSIJU31mo18rIC38Mz/cAx1rCb5WzBl10naZnWs5lMozPgRGsOU5IX03gQfqao/Aaiqo1V1qqr+Hng/HexCT6eqDjfTY8DdzfaPJlnS1LwEODbMmvrcANxfVUebGkc6Vn2mGp+Rvt+SrAd+FviFag5GN4ccnmzm76N3PPpHhlXTNK/ZqMfqPODngI/31Tq0sZrsbwEjfF/Np1CYMx+h0Ry//ACwr6re29e+pK/bm4CHT1+3w5pekuTCiXl6JysfpjdG65tu64F7hlXTaZ7xP7lRjtVpphqfHcC6JAuTrARWAbuHUVCS64G3AzdX1ff62hel950lJLmiqenrw6ip2eZUr9nIxqrx08CjVTU+0TCssZrqbwGjfF91fXZ9Lv0AN9I7u/814J0jrOPV9Hb5vgo80PzcCHwYeKhp3wEsGWJNV9C7quFBYO/E+AA/COwCDjTTS0YwXhcATwL/qK9t6GNFL5SOAH9H739st003PsA7m/fafuCGIdZ0kN5x54n31vuavj/fvLYPAvcDbxzyWE35mo1qrJr2PwJ+5bS+Qxmraf4WjOx95R3NkqTWfDp8JEmagaEgSWoZCpKklqEgSWoZCpKklqEgSWoZCpKklqEgSWr9fzvuq5repHs9AAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['exploitation'].plot(kind='hist')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## As for the reason of the house being in emergency condition, it's mostly physical deterioration. However, fires are also a reason for 2% of the cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Физический износ    1725\n",
       "Пожар                 29\n",
       "Иная                   1\n",
       "Name: alarm_reason, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.alarm_reason.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Физический износ    98.0\n",
       "Пожар                2.0\n",
       "Иная                 0.0\n",
       "Name: alarm_reason, dtype: float64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(df.alarm_reason.value_counts(normalize=True)*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Resettlement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    9/17/2023\n",
       "1    9/30/2024\n",
       "2          NaN\n",
       "3          NaN\n",
       "4     9/1/2025\n",
       "Name: planned_resettlement_date, dtype: object"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.planned_resettlement_date.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna(subset=['planned_resettlement_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "new = df.planned_resettlement_date.str.split(\"/\", n = 2, expand = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    0   1     2\n",
       "0   9  17  2023\n",
       "1   9  30  2024\n",
       "4   9   1  2025\n",
       "5   9   1  2025\n",
       "6  12  31  2022"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"resettlement_year\"] = new[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2023\n",
       "1    2024\n",
       "4    2025\n",
       "5    2025\n",
       "6    2022\n",
       "Name: resettlement_year, dtype: object"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.resettlement_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.resettlement_year = df.resettlement_year.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['wait_years']=(df.resettlement_year-df.determined_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## On average, people wait for the resettlement for 10 years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10.0"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['wait_years'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x118673820>"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df.loc[df['alarm_reason'] == 'Пожар'].groupby(by=\"resettlement_year\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fire = df[df['alarm_reason']=='Пожар']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#ok so our of 29 houses that were on fire I only have 12 with the filled resettlement year\n",
    "len(df_fire)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we could use 0 as a replacement if this is not used throughout the dataset "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## And there are at least 6 houses that have been on fire but still waiting to be resettled!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_fire[df_fire['resettlement_year']>2021])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Needs to be done: For those houses that have a resettlement date and it's already passed, calculate how many % have actually been resettled."
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}