Scrools-RS-Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dropping libs, df and connecting to GoogleMaps\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "# import googlemaps\n",
    "# gmaps = googlemaps.Client(key='AIzaSyDgRZqdfREOUJsen8JwfgkVNhNSuWO0V9g') \n",
    "\n",
    "df = pd.read_excel(\"Final_DataSet_1(afterVar).xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating functions\n",
    "\n",
    "def qual_ind_finder(pattern_word, from_ind = 0, to_ind = len(df.columns) - 1):\n",
    "    \"\"\"finds column by pattern word, need for future rearranging\"\"\"\n",
    "    return [i for i in df.columns[from_ind:to_ind] if i.find(pattern_word) != -1]\n",
    "\n",
    "\n",
    "def safe_div(x, y):\n",
    "    \"\"\"divides x on y, if y == 0, returns 0\"\"\"\n",
    "    return x/y if y!=0 else 0\n",
    "\n",
    "\n",
    "def relate_field_by_field(df, field_nom, field_denom, field_target=None):\n",
    "    \"\"\"\n",
    "    by default replaces all values of field_nom, to result of save_div function\n",
    "    if we got field_target argument, creates new column and inserts result of save_div\n",
    "        \n",
    "    \"\"\"\n",
    "    if not field_target:\n",
    "        field_target = field_nom\n",
    "    df[field_target] = [safe_div(df.loc[i, field_nom], df.loc[i, field_denom]) for i in df.index] \n",
    "\n",
    "def handle_computer_need_repair(df):\n",
    "    \"\"\"\n",
    "    replaces specified column values to result of math operations\n",
    "    \"\"\"\n",
    "    df['computer_w_internet_2017'] = \\\n",
    "            [df.loc[i, \"computer_w_internet_2017\"] - \n",
    "             df.loc[i, \"comp_count_need_repair_2017\"] * \n",
    "            (safe_div(df.loc[i, \"computer_w_internet_2017\"], df.loc[i, \"computer_overall_2017\"])) for i in df.index]\n",
    "\n",
    "    df['computers_education'] = \\\n",
    "            [df.loc[i, \"computers_education\"] - \n",
    "            df.loc[i, \"comp_count_need_repair_2017\"] * \n",
    "            (safe_div(df.loc[i, \"computers_education\"], df.loc[i, \"computer_overall_2017\"])) for i in df.index]\n",
    "\n",
    "    df['computer_for_teacher_2017'] = \\\n",
    "            [df.loc[i, \"computers_education\"] - \n",
    "            df.loc[i, \"comp_count_need_repair_2017\"] * \n",
    "            (safe_div(df.loc[i, \"computer_for_teacher_2017\"], df.loc[i, \"computer_overall_2017\"])) for i in df.index]\n",
    "\n",
    "    df['computer_overall_2017'] = \\\n",
    "            [df.loc[i, \"computer_overall_2017\"] - df.loc[i, \"comp_count_need_repair_2017\"] for i in df.index]\n",
    "    \n",
    "\n",
    "def handle_special_cabs(df):\n",
    "    \"\"\"\n",
    "    creates new columns with boolean values of presence spec cab from s_dict in current school\n",
    "    \"\"\"\n",
    "    s = set()\n",
    "    for i in df.index:\n",
    "        for cab in df.loc[i, \"spec_cab_2017\"].split(\",\"):\n",
    "            s.add(cab)\n",
    "    s_dict = {\n",
    "        \"кабинет сурдолога\": \"audiologist_cab\",\n",
    "        \"кабинет невропатолога\": \"neuropatologist_cab\",\n",
    "        \"кабинет психолога\" : \"psychologist_cab\",\n",
    "        \"стоматологический кабинет\" : \"dentist_cab\",\n",
    "        \"лечебно-физкультурный кабинет\" : \"medical_physical_cab\",\n",
    "        \"не имеется\" : \"no_special_cabs\",\n",
    "        \"кабинет логопеда\" : \"speech_terapist_cab\",\n",
    "        \"кабинет офтальмолога\" : \"ophtalmologist_cab\",\n",
    "        \"кабинет психиатра\" : \"psychiatrist_cab\",\n",
    "        \"участкового инспектора полиции\" : \"police_inspector_cab\",\n",
    "        \"медицинский кабинет (пункт)\" : \"medical_cab\",\n",
    "        \"кабинет диагностики\" : \"diagnostic_cab\",\n",
    "        \"кабинет педагога-психолога\" : \"pedagog_psychologist_cab\"\n",
    "    }\n",
    "    for cab in s:\n",
    "        df[s_dict[cab] if cab in s_dict else cab] = [cab in df.loc[i, \"spec_cab_2017\"] for i in df.index]\n",
    "        \n",
    "def handle_coordinates(df):\n",
    "    \"\"\"\n",
    "    creates two columns with latitude and longtitude\n",
    "    prints non formatted coordinates number\n",
    "    \"\"\"\n",
    "    nones = 0\n",
    "    for ind in df.index:\n",
    "        c = df.loc[ind, \"coordinates\"]\n",
    "        try:\n",
    "            if \"(\" in c:\n",
    "                lat = float(c.split(\"(\")[1].split(\")\")[0])\n",
    "                lon = float(c.split(\"(\")[2].split(\")\")[0])\n",
    "            elif \"с\" in c:\n",
    "                c = \"\".join([i for i in c if not i.isalpha() and not i in \"/.()\"])\n",
    "    #             print(c)\n",
    "                lat = 1\n",
    "                lon = 1\n",
    "                n2 += 1\n",
    "                raise Exception()\n",
    "            elif \"N\" in c:\n",
    "                lat = 0\n",
    "                lon = 0\n",
    "                n3 += 1\n",
    "                raise Exception()\n",
    "            elif \",\" in c:\n",
    "                lat = float(c.split(\",\")[0])\n",
    "                lon = float(c.split(\",\")[1])\n",
    "            else:\n",
    "                lat = float(c.split()[0])\n",
    "                lon = float(c.split()[1])\n",
    "        except:\n",
    "            lat = None\n",
    "            lon = None\n",
    "            nones += 1\n",
    "        df.loc[ind, 'lat'] = lat\n",
    "        df.loc[ind, 'lon'] = lon\n",
    "    print(\"Coordinates not formatted: {nones}\".format(nones=nones))\n",
    "    \n",
    "def diff_between_lat_lon(df):\n",
    "    for ind in df.index:\n",
    "        try:\n",
    "            if float(df.loc[ind, 'lat']) - float(df.loc[ind, 'lon']) >= 5:\n",
    "                df.loc[ind,'lat'], df.loc[ind, 'lon'] = df.loc[ind, 'lon'], df.loc[ind, 'lat']\n",
    "        except:\n",
    "            continue\n",
    "\n",
    "def handle_dist_school_city(origin_latitude, origin_longitude, destination_latitude, destination_longitude): \n",
    "    \"\"\"\n",
    "    finds distance between 2 objects with GoogleMaps API\n",
    "    \"\"\"\n",
    "    distance = gmaps.distance_matrix([str(origin_latitude) + \" \" + str(origin_longitude)], [str(destination_latitude) + \" \" + str(destination_longitude)])['rows'][0]['elements'][0]\n",
    "    return round(distance['distance']['value'] / 1000)\n",
    "\n",
    "def haversine(lat1, lon1, lat2, lon2):\n",
    "    \"\"\"\n",
    "    finds distance between 2 objects\n",
    "    \"\"\"\n",
    "    lon1, lat1, lon2, lat2 = map(np.radians, [lon1, lat1, lon2, lat2])\n",
    "    dlon = lon2 - lon1 \n",
    "    dlat = lat2 - lat1 \n",
    "    a = np.sin(dlat/2)**2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon/2)**2\n",
    "    c = 2 * np.arcsin(np.sqrt(a)) \n",
    "    r = 6371\n",
    "    return c * r\n",
    "\n",
    "def calc_optimal_dist(l):\n",
    "    \"\"\"\n",
    "    calculates distance for every element(school) to nearest city (from 'cities' dictionary)\n",
    "    \"\"\"\n",
    "    res = []\n",
    "    for i in l:\n",
    "        buff = []\n",
    "        for key, value in cities.items():\n",
    "            try:\n",
    "                buff.append(handle_dist_school_city(i[0],i[1], value[0], value[1]))\n",
    "            except KeyError:\n",
    "                if str(e) == 'distance':\n",
    "            buff.append(haversine(i[0], i[1], value[0], value[1]) * np.pi / 2)\n",
    "        res.append(min(buff))\n",
    "    return res\n",
    "\n",
    "def schools_density(l, needed_dist):\n",
    "    \"\"\"\n",
    "    returns array with density to every school in l,with the necessary accuracy\n",
    "    \"\"\"\n",
    "    res = []\n",
    "    cnt = 0\n",
    "    for i in l:\n",
    "        for j in l:\n",
    "            if haversine(i[0], i[1], j[0], j[1]) <= needed_dist:\n",
    "                cnt += 1\n",
    "        res.append(cnt - 1)\n",
    "        cnt = 0\n",
    "    return res\n",
    "            \n",
    "def handle_rare_categoricals(df):    \n",
    "    df['cameras__2017'] = df['cameras__2017'].\\\n",
    "                 apply(lambda x: 'отсутствует' if x == '0' or x =='...' else(\"комбинированное\" if x != 'внутреннее' and x != \"наружнее\" \\\n",
    "                                                                             else ('внутренне' if x == 'внутренне' else 'внешнее')))\n",
    "    df['building_ownership_2017'] = df['building_ownership_2017'].\\\n",
    "                     apply(lambda x: 'Не указано' if x == '0' or x =='нет' else(\"Частично собственное\" if x != \"Собственное\" and x != 'Арендуемое' \\\n",
    "                                                                                else ('Собственное' if x == 'Собственное' else 'Арендуемое')))\n",
    "    df['landscaping'] = df['landscaping'].\\\n",
    "    apply(lambda x: 'не благоустроены' if x == 0 else(\"частично благоустроены\" if x != 'не благоустроены' and x != 'наличие всех видов благоустройств' \\\n",
    "                                                      else ('не благоустроены' if x == 'не благоустроены' else 'наличие всех видов благоустройств')))\n",
    "\n",
    "    heating_types = [\n",
    "        \"на твердом топливе\",\n",
    "        \"центральное\",\n",
    "        \"на газовом топливе\",\n",
    "        \"на жидком топливе\",\n",
    "        \"электрокотельное\",\n",
    "        \"с другим видом отопления\",\n",
    "        \"0\"\n",
    "    ]\n",
    "    df['heating_type_2017'] = df['heating_type_2017'].apply(lambda x: x if x.strip() in heating_types else \"с другим видом отопления\")\n",
    "    \n",
    "    df['spec_edu_from_class_num_2017'] = \\\n",
    "        df['spec_edu_from_class_num_2017'].apply(\n",
    "            lambda x: \"9-12\" if x.startswith(\"12\") or x.startswith(\"11\") or x.startswith(\"10\") or x.startswith(\"9\") else \\\n",
    "                      \"5-8\" if x.startswith(\"8\") or x.startswith(\"7\") or x.startswith(\"6\") or x.startswith(\"5\") else \\\n",
    "                      \"1-4\" if x != \"нет\" and x != \"0\" else \"0\"\n",
    "        )\n",
    "    \n",
    "    spec_schools = [\n",
    "        \"0\",\n",
    "        \"естественно-математическое\",\n",
    "        \"естественно-математическое,общественно-гуманитарное\"\n",
    "    ]\n",
    "    df['spec_school_2017'] = df['spec_school_2017'].apply(lambda x: x if x.strip() in spec_schools else \"другие\")\n",
    "    \n",
    "    schools_langs = [\n",
    "        \"казахский\",\n",
    "        \"казахский,русский\",\n",
    "        \"русский\"\n",
    "    ]\n",
    "    df['edu_lang_2017'] = df['edu_lang_2017'].apply(lambda x: x if x.strip() in schools_langs else \"другой\")\n",
    "    \n",
    "    building_types = [\n",
    "        \"кирпичное\",\n",
    "        \"блочное\",\n",
    "        \"другое\",\n",
    "        \"саманное\",\n",
    "        \"деревянное\",\n",
    "        \"каркасное\",\n",
    "        \"монолитное\"\n",
    "    ]\n",
    "    df['building_type_2017'] = df['building_type_2017'].apply(lambda x: x if x.strip() in building_types else \"другой\")\n",
    "    \n",
    "    gym_types = [\n",
    "        \"спортивный зал, оснащенный стандартным оборудованием,спортивные площадки,спортивные секции\",\n",
    "        \"спортивные площадки,спортивные секции\",\n",
    "        \"спортивные площадки\",\n",
    "        \"спортивный зал, оснащенный стандартным оборудованием,спортивные площадки,гимнастические городки,спортивные секции\",\n",
    "        \"спортивный зал, оснащенный стандартным оборудованием,спортивные площадки\",\n",
    "        \"не имеется\",        \n",
    "    ]\n",
    "    df['gym_equipment_2017'] = df['gym_equipment_2017'].apply(lambda x: x if x.strip() in gym_types else \"другой\")\n",
    "\n",
    "    df['building_condition_2017'] = df['building_condition_2017'].apply(lambda x: \"аварийные\" if \"авари\" in x else \"требует\" if \"требу\" in x else \"проведён\" if \"прове\" in x else \"требует\")\n",
    "\n",
    "    future_langs = [\n",
    "        \"казахский\",\n",
    "        \"русский\",\n",
    "        \"английский\"\n",
    "    ]\n",
    "    df['future_edu_lang_2017'] = df['future_edu_lang_2017'].apply(lambda x: x if x.strip() in future_langs else \"другой\")\n",
    "\n",
    "    df['edu_from_class_num_2017'] = \\\n",
    "        df['edu_from_class_num_2017'].apply(\n",
    "            lambda x: \"9-12\" if x.startswith(\"12\") or x.startswith(\"11\") or x.startswith(\"10\") or x.startswith(\"9\") else \\\n",
    "                      \"5-8\" if x.startswith(\"8\") or x.startswith(\"7\") or x.startswith(\"6\") or x.startswith(\"5\") else \\\n",
    "                      \"1-4\" if x != \"нет\" and not x.startswith(\"пред\") else \"предшкольное\"\n",
    "        )\n",
    "    toilet_types = [\n",
    "        \"наличие теплых и надворных туалетов\",\n",
    "        \"наличие только надворных туалетов\",\n",
    "        \"наличие только теплых туалетов\",\n",
    "        \"нет\"\n",
    "    ]\n",
    "    \n",
    "    df['toilet_2017'] = df['toilet_2017'].apply(lambda x: \"нет\" if x == \"0\" else x)\n",
    "    df['toilet_2017'] = df['toilet_2017'].apply(lambda x: \"наличие теплых и надворных туалетов\" if x == \"наличие только надворных туалетов,наличие теплых и надворных туалетов\" else x)\n",
    "    df['toilet_2017'] = df['toilet_2017'].apply(lambda x: x if x.strip() in toilet_types else \"нет\")\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# main body of code, where we normalazing, droping and creating our columns\n",
    "\n",
    "relate_field_by_field(df, \"library_reading_room_sitting_places\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"library_num_of_readers\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"library_num_of_visits\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"given_books\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"library_books_overall\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"canteen_places\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"buffet_places\", \"total_students_2017\")\n",
    "handle_computer_need_repair(df)\n",
    "relate_field_by_field(df, \"computer_overall_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"computer_w_internet_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"computers_education\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"computer_for_teacher_2017\", \"total_teachers_2017\")\n",
    "\n",
    "df['internet_speed_mbit_2017'] = df['internet_speed_mbit_2017'].\\\n",
    "        apply(lambda x: str(x).split()[0].strip()).apply(lambda x: 0 if x==\"nan\" else x).apply(lambda x: 20 if x==\">10\" else x).\\\n",
    "        apply(lambda x: 0.5 if x==\"512\" else float(x))\n",
    "\n",
    "df['school_first_contrstuction_year_2017'] = df['school_contrstuction_year_2017'].apply(lambda x: int(float(str(x).split(\",\")[0].strip())))\n",
    "df['school_last_contrstuction_year_2017'] = df['school_contrstuction_year_2017'].apply(lambda x: int(float(str(x).split(\",\")[-1].strip())))\n",
    "df = df.drop([\"school_contrstuction_year_2017\"], axis=1)\n",
    "\n",
    "relate_field_by_field(df, \"classrooms_num\", \"total_students_2017\", \"classrooms_per_student\")\n",
    "relate_field_by_field(df, \"classrooms_area\", \"classrooms_num\", \"classroom_average_area\")\n",
    "\n",
    "relate_field_by_field(df, \"building_working_area_2017\", \"building_area_2017\", \"working_area_ratio\")\n",
    "\n",
    "handle_special_cabs(df)\n",
    "df = df.drop([\"spec_cab_2017\"], axis=1)\n",
    "\n",
    "relate_field_by_field(df, \"deep_spec_edu_classes_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"clubs_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"total_students_2017\", \"class_count_2017\", \"average_students_per_class\")\n",
    "relate_field_by_field(df, \"class_count_1_shift_2017\", \"class_count_2017\")\n",
    "relate_field_by_field(df, \"class_count_2_shift_2017\", \"class_count_2017\")\n",
    "relate_field_by_field(df, \"class_count_3_shift_2017\", \"class_count_2017\")\n",
    "relate_field_by_field(df, \"class_count_4_shift_2017\", \"class_count_2017\")\n",
    "\n",
    "\n",
    "relate_field_by_field(df, \"college_after_9_2017\", \"total_after_9_2017\")\n",
    "relate_field_by_field(df, \"applied_10_after_9_2017\", \"total_after_9_2017\")\n",
    "relate_field_by_field(df, \"employed_after_9\", \"total_after_9_2017\")\n",
    "relate_field_by_field(df, \"total_after_9_2017\", \"total_students_2017\")\n",
    "\n",
    "relate_field_by_field(df, \"total_after_11_continue_edu_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"total_after_11_college_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"total_after_11_uni_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"total_after_11_uni_2017\", \"total_after_11_continue_edu_2017\", \n",
    "                      \"students_11_successfull_uni_ratio\")\n",
    "relate_field_by_field(df, \"total_employed_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_agriculture_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_industry_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_building_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_trade_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_transport_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_housing_utility_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_edu_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_medical_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_domestic_service_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"employed_other_after_11_2017\", \"total_after_11_2017\")\n",
    "relate_field_by_field(df, \"total_after_11_2017\", \"total_students_2017\")\n",
    "\n",
    "relate_field_by_field(df, \"total_students_2017\", \"total_teachers_2017\", \"average_students_per_teacher\")\n",
    "relate_field_by_field(df, \"academic_degree_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"qualified_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"eng_lang_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"out_of_system_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"high_category_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"teachers_1_category_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"teachers_2_category_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"teachers_no_category_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"experience_teachers_under_3_years_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"experience_teachers_between_3_5_years_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"experience_teachers_between_6_10_years_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"experience_teachers_between_11_15_years_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"experience_teachers_between_16_20_years_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"experience_teachers_20+_years_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"higher_edu_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"tech_spec_edu_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"middle_edu_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_under_25_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_between_25_29_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_between_30_34_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_between_35_39_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_between_40_44_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_between_45_49_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_between_50_54_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_between_55_59_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"age_teachers_59+_years_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"retired_age_man_teachers_2017\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"retired_age_woman_teachers_2017\", \"total_teachers_2017\")\n",
    "\n",
    "relate_field_by_field(df, \"staff_num\", \"building_area_2017\", \"average_staff_per_area\")\n",
    "relate_field_by_field(df, \"staff_num\", \"total_teachers_2017\")\n",
    "relate_field_by_field(df, \"zavuch_num\", \"total_teachers_2017\")\n",
    "\n",
    "relate_field_by_field(df, \"boys_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"girls_2017\", \"total_students_2017\")\n",
    "df['sex_diversity_index'] = [1 - (max(0.5 - df.loc[i, \"boys_2017\"], 0) * 2 + \\\n",
    "                              max(0.5 - df.loc[i, \"girls_2017\"], 0) * 2) ** 2 \n",
    "                             for i in df.index]\n",
    "\n",
    "relate_field_by_field(df, \"using_clubs_students_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"student_with_financial_aid\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"student_books_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"students_without_parents_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"disabled_students\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"students_without_parent_help_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"hot_food_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"free_hot_food_2017\", \"total_students_2017\")\n",
    "\n",
    "relate_field_by_field(df, \"student_edu_lang_rus_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"student_edu_lang_kaz_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"student_edu_lang_uyghur_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"student_edu_lang_uzbek_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"student_edu_lang_tadjik_2017\", \"total_students_2017\")\n",
    "\n",
    "relate_field_by_field(df, \"olympiad_district_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"olympiad_city_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"olympiad_region_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"olympiad_republic_2017\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"olympiad_international_2017\", \"total_students_2017\")\n",
    "\n",
    "\n",
    "for i in range(1,12):\n",
    "    df['total_students_' + str(i)] = df[\"exclnt_grades_\" + str(i) + \"_class_2017\"] + df[\"good_grades_\" + str(i) + \"_class_2017\"] + \\\n",
    "                                     df[\"well_grades_\" + str(i) + \"_class_2017\"] + df[\"bad_grades_\" + str(i) + \"_class_2017\"]\n",
    "\n",
    "df[\"exclnt_grades_total\"] = sum([df[\"exclnt_grades_\" + str(i) + \"_class_2017\"] for i in range(1, 12)])\n",
    "relate_field_by_field(df, \"exclnt_grades_total\", \"total_students_2017\")\n",
    "\n",
    "df[\"good_grades_total\"] = sum([df[\"good_grades_\" + str(i) + \"_class_2017\"] for i in range(1, 12)])\n",
    "relate_field_by_field(df, \"good_grades_total\", \"total_students_2017\")\n",
    "\n",
    "df[\"well_grades_total\"] = sum([df[\"well_grades_\" + str(i) + \"_class_2017\"] for i in range(1, 12)])\n",
    "relate_field_by_field(df, \"well_grades_total\", \"total_students_2017\")\n",
    "\n",
    "df[\"bad_grades_total\"] = sum([df[\"bad_grades_\" + str(i) + \"_class_2017\"] for i in range(1, 12)])\n",
    "relate_field_by_field(df, \"bad_grades_total\", \"total_students_2017\")\n",
    "\n",
    "for i in range(1, 12):\n",
    "    relate_field_by_field(df, \"exclnt_grades_\" + str(i) + \"_class_2017\", \"total_students_\" + str(i))\n",
    "    relate_field_by_field(df, \"good_grades_\" + str(i) + \"_class_2017\", \"total_students_\" + str(i))\n",
    "    relate_field_by_field(df, \"well_grades_\" + str(i) + \"_class_2017\", \"total_students_\" + str(i))\n",
    "    relate_field_by_field(df, \"bad_grades_\" + str(i) + \"_class_2017\", \"total_students_\" + str(i))\n",
    "    relate_field_by_field(df, \"total_students_\" + str(i), \"total_students_2017\")\n",
    "\n",
    "relate_field_by_field(df, \"russian_students\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"kazakh_students\", \"total_students_2017\")\n",
    "#df[\"other_nationality_students\"] = sum([df[\"nationality\" + str(i)] for i in range(1, 117)])\n",
    "relate_field_by_field(df, \"other_nationality_students\", \"total_students_2017\")\n",
    "\n",
    "# droppin nationalities after union\n",
    "#for i in range(1,117):\n",
    "#    df = df.drop([\"nationality\" + str(i)], axis = 1)\n",
    "        \n",
    "df[\"nationality_diversity_index\"] = [1 - (max(0.7 - df.loc[i, \"kazakh_students\"], 0) * (5 / 7) + \\\n",
    "                                          max(0.2 - df.loc[i, \"russian_students\"], 0) * (5 / 2) + \\\n",
    "                                          max(0.1 - df.loc[i, \"other_nationality_students\"], 0) * (5 / 1)) ** 2 for i in df.index]\n",
    "\n",
    "relate_field_by_field(df, \"exclnt_good_2018\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"altyn_want_2018\", \"total_after_11_2018\")\n",
    "relate_field_by_field(df, \"got_altyn_2018\", \"total_after_11_2018\")\n",
    "relate_field_by_field(df, \"got_altyn_2018\", \"altyn_want_2018\", \"altyn_confirm_ratio\")\n",
    "relate_field_by_field(df, \"total_students_2017\", \"building_capacity_2017\", \"load_ratio\")\n",
    "relate_field_by_field(df, \"grant_2018\", \"participate_ent_2018\")\n",
    "relate_field_by_field(df, \"got_min_for_uni_2018\", \"participate_ent_2018\")\n",
    "relate_field_by_field(df, \"participate_ent_2018\", \"total_after_11_2018\")\n",
    "relate_field_by_field(df, \"9_abroad_uni_2018\", \"total_after_9_2018\")\n",
    "relate_field_by_field(df, \"9_not_educated_2018\", \"total_after_9_2018\")\n",
    "relate_field_by_field(df, \"11_abroad_uni_2018\", \"total_after_11_2018\")\n",
    "relate_field_by_field(df, \"11_not_educated_2018\", \"total_after_11_2018\")\n",
    "\n",
    "relate_field_by_field(df, \"poor_fams_2018\", \"total_students_2017\")\n",
    "relate_field_by_field(df, \"poor_fams_food_2018\", \"total_students_2017\")\n",
    "\n",
    "df['open_date'] =  pd.to_datetime(df['open_date'], format='%Y-%m-%d', errors=\"ignore\")\n",
    "\n",
    "df['drinking_water_presence'] = df['Наличие питьевой воды']\n",
    "df = df.drop(['Наличие питьевой воды'], axis=1)\n",
    "cols = ['canteen', \n",
    "'buffet', \n",
    "'nutrition_in_apapted_premises', \n",
    "'computer_availability_2017', \n",
    "'gym_2017', \n",
    "'civil_protection_alert_system_2017',\n",
    "'panic_button_2017',\n",
    "'tourniquet_2017',\n",
    "'museum',\n",
    "'camp_2017',\n",
    "'hot_water_2017',\n",
    "'import_water',\n",
    "'drinking_water_presence',\n",
    "'med_cab_2017']\n",
    "for i in cols:\n",
    "    df[i] = df[i].apply(lambda x: 1 if x == \"да\" or x == 'Да'or x =='ДА' else 0)\n",
    "\n",
    "df['magnet_school'] = df['magnet_school'].apply(lambda x: 0 if x==\"нет\" or x==0 else 1)\n",
    "df['student_delivery'] = df['student_delivery'].apply(lambda x: 0 if x =='нет' or x == 0 else 1)\n",
    "\n",
    "handle_rare_categoricals(df)\n",
    "#handle_coordinates(df)\n",
    "#df = df.drop(['lat'], axis = 1)\n",
    "#df = df.drop(['lon'], axis = 1)\n",
    "\n",
    "cities = {\"Almaty\":[43.25, 76.9],\n",
    "\"Astana\":[51.133333, 71.433333],\n",
    "\"Shymkent\":[42.3, 69.6],\n",
    "\"Karaganda\":[49.8, 73.116667],\n",
    "\"Aktobe\":[50.3, 57.166667],\n",
    "\"Taraz\":[42.883333, 71.366667],\n",
    "\"Pavlodar\":[52.315556, 76.956389],\n",
    "\"UstKaman\":[49.95, 82.616667],\n",
    "\"Semey\":[50.411111, 80.2275],\n",
    "\"Kostanay\":[53.214167, 63.624444],\n",
    "\"Atyrau\":[47.116667, 51.883333],\n",
    "\"Kyzylorda\":[44.85, 65.516667],\n",
    "\"Uralsk\":[51.233333, 51.366667],\n",
    "\"Petropavlovsk\":[54.862222, 69.140833],\n",
    "\"Aktau\":[43.65, 51.15],\n",
    "\"Temirtau\":[50.066667, 72.966667],\n",
    "\"Turkistan\":[43.3, 68.243611],\n",
    "\"Kokshetau\":[53.291667, 69.391667],\n",
    "\"Taldykorgan\":[45.016667, 78.366667],\n",
    "\"Ekibastuz\":[51.723611, 75.322778],\n",
    "\"Rudniy\":[52.966667, 63.116667]}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# inserting three density columns \n",
    "\n",
    "l = (np.array([df.loc[i, 'coordinates'].split(',') for i in df.index]).astype('float'))\n",
    "#df.insert(51, 'school_density_in_10km',schools_density(l, 10))\n",
    "#df.insert(52, 'school_density_in_3km', schools_density(l, 3))\n",
    "#df.insert(53, 'school_density_in1.5km', schools_density(l, 1.5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# here we parallellizing our calculations between 10 jupiterNotebooks, and creating a new feature\n",
    "# GOOGLE API\n",
    "res = calc_optimal_dist(l)\n",
    "df.insert(50, 'school_city_dist', res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rearranging dataset, by quality column types: 'ent', 'voud', 'olympiad', 'grade', 'altyn', 'grant' etc\n",
    "\n",
    "overall_qual_list = sum([qual_ind_finder(*i) for i in [\n",
    "            (\"ent_\", 0,30), (\"voud\", ), (\"olympiad\", ), (\"grade\", ), (\"grant\", ), (\"altyn\", ), (\"exclnt_good_2018\",),\n",
    "            (\"got_min_for_uni_2018\",), (\"9_abroad_uni_2018\",), (\"9_not_educated_2018\",), (\"11_abroad_uni_2018\", ),\n",
    "            (\"11_not_educated_2018\", ), (\"students_11_successfull_uni_ratio\",), (\"employed_\", ),\n",
    "            (\"total_after_11_continue_edu_2017\", ), (\"total_after_11_college_2017\", ), (\"total_after_11_uni_2017\", ),\n",
    "        ]], [])\n",
    "\n",
    "columns = list(df.columns)\n",
    "\n",
    "for i in overall_qual_list:\n",
    "    columns.remove(i)\n",
    "    \n",
    "df = df[columns + overall_qual_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# writing \n",
    "\n",
    "writer = pd.ExcelWriter(r\"FINAL_DATASET_666.xlsx\", engine='xlsxwriter', options={'strings_to_urls': False})\n",
    "df.to_excel(writer)\n",
    "writer.close()"
   ]
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}