schools_merger.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from collections import defaultdict\n",
    "from difflib import SequenceMatcher\n",
    "import re\n",
    "\n",
    "import warnings\n",
    "warnings.simplefilter('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel('FINAL_DATASET_777(Q_edu_4).xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop_duplicates(subset='school_name', keep='first',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1=pd.read_excel('output_final.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.rename(index=str, columns={'Область': \"region\", 'Район': 'area','Населенный пункт':'location','Наименование школы':'school', 'Тип организации образования':'type'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.drop_duplicates(subset='school_name', keep='first',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "common_areas=list(set(df_1.area)&set(df.area))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Аккольский район'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.area[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dict=dict(zip(df.school_name,df.area))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "keys=list(df_dict.keys())\n",
    "values=list(df_dict.values())\n",
    "uniq_val=list(set(values))\n",
    "dict_df=defaultdict(list)\n",
    "for val in uniq_val:\n",
    "    for ind,i in enumerate(values):\n",
    "        if val==i:\n",
    "            dict_df[val].append(keys[ind])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1dict=dict(zip(df_1.school_name,df_1.area))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "keys_=list(df_1dict.keys())\n",
    "values_=list(df_1dict.values())\n",
    "uniq_val_=list(set(values_))\n",
    "dict_df_1=defaultdict(list)\n",
    "for val in uniq_val_:\n",
    "    for ind,i in enumerate(values_):\n",
    "        if val==i:\n",
    "            dict_df_1[val].append(keys_[ind])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "del df_1dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "def proccessing(x):\n",
    "    \n",
    "    for i in x:\n",
    "        ind=x.index(i)\n",
    "        x[ind]=x[ind].lower()\n",
    "        x[ind]=re.sub(r'\\W+', ' ',x[ind])\n",
    "        x[ind]=x[ind].replace('кгу','').replace('сш','').replace('гку','')\n",
    "        x[ind]=x[ind].replace('коммунальное' ,'').replace('государственное учреждение','')\n",
    "        x[ind]=x[ind].replace('средняя школа', '').replace('имени','').replace('им.','')\n",
    "        x[ind]=re.sub(' +', ' ',x[ind])\n",
    "        num=re.findall(r'\\d+',x[ind])\n",
    "        if len(num)>0:\n",
    "            x[ind]=num[0]\n",
    "    return x\n",
    "\n",
    "def get_alt_name(school0,school1,com_ar,proc_func):\n",
    "    \n",
    "    result={}\n",
    "    for area in common_areas:\n",
    "        sc0=school0[area].copy()\n",
    "        sc0_=school0[area].copy()\n",
    "        sc1=school1[area].copy()\n",
    "        sc1_=school1[area].copy()\n",
    "        #preproc\n",
    "        sc0=proc_func(sc0)  \n",
    "        sc1=proc_func(sc1)\n",
    "        for s0 in sc0:\n",
    "            score=[]\n",
    "            names=[]\n",
    "            ind0=sc0.index(s0)\n",
    "            for s1 in sc1:\n",
    "                ind1=sc1.index(s1)\n",
    "                f=SequenceMatcher(None, s1,s0).ratio()\n",
    "                if f>=0.8:\n",
    "                    score.append(f)\n",
    "                    names.append(s1)\n",
    "            if len(score)>0:\n",
    "                result[sc0_[ind0]]=sc1_[sc1.index(names[np.array(score).argmax()])]\n",
    "            else:\n",
    "                result[sc0_[ind0]]=sc0_[ind0]+'_old'\n",
    "            \n",
    "    alt_names=list(result.keys())\n",
    "    my_names=list(result.values())\n",
    "    result=dict(zip(my_names,alt_names))\n",
    "\n",
    "    return result      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "total_trans=get_alt_name(dict_df_1,dict_df,common_areas,proccessing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "common_schools_0={}\n",
    "common_schools_1={}\n",
    "for name in total_trans:\n",
    "    if not name.endswith('_old'):\n",
    "        common_schools_0[name]=total_trans[name]\n",
    "        common_schools_1[total_trans[name]]=name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Переведено: 1318 из 2053\n"
     ]
    }
   ],
   "source": [
    "tt=len(total_trans)\n",
    "cs=len(common_schools_0)\n",
    "print('Переведено: '+str(cs)+' из '+str(tt))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['alt_names']=df.school_name.map(common_schools_0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(df[df.alt_names!=df.alt_names].index, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1['alt_names']=df_1.school_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df=pd.merge(df,df_1,on='alt_names',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# writing \n",
    "\n",
    "writer = pd.ExcelWriter(r\"final_dataset_w_q_edu_somnitelno.xlsx\", engine='xlsxwriter', options={'strings_to_urls': False})\n",
    "result.to_excel(writer)\n",
    "writer.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_excel(\"final_dataset_w_q_edu.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.merge(df, df1, on='ID', how = 'left')"
   ]
  }
 ],
 "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
}