{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 21398/21398 [00:16<00:00, 1308.22it/s]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pyspark\n",
    "from tqdm import tqdm\n",
    "\n",
    "basic_folder = ''\n",
    "file_name2 = basic_folder + '2019_students_num.csv'\n",
    "df2 =pd.read_csv(file_name2)\n",
    "\n",
    "count_row=df2.shape[0] #number of rows\n",
    "elementary=[]\n",
    "middle=[]\n",
    "high=[]\n",
    "\n",
    "def type_of_school():\n",
    "    for x in tqdm(range(count_row)):\n",
    "        type=df2.loc[x]['학교급']\n",
    "        if(type=='고등학교'):\n",
    "            row=[df2.loc[x]['학교명'],df2.loc[x]['1학년_학생수_계'],df2.loc[x]['2학년_학생수_계'],df2.loc[x]['3학년_학생수_계']]\n",
    "            high.append(row)\n",
    "        elif(type=='중학교'):\n",
    "            row=[df2.loc[x]['학교명'],df2.loc[x]['1학년_학생수_계'],df2.loc[x]['2학년_학생수_계'],df2.loc[x]['3학년_학생수_계']]\n",
    "            middle.append(row)\n",
    "        elif(type=='초등학교'):\n",
    "            row=[df2.loc[x]['학교명'],df2.loc[x]['1학년_학생수_계'],df2.loc[x]['2학년_학생수_계'],df2.loc[x]['3학년_학생수_계'],df2.loc[x]['4학년_학생수_계'],df2.loc[x]['5학년_학생수_계'],df2.loc[x]['6학년_학생수_계']]\n",
    "            elementary.append(row)\n",
    "\n",
    "type_of_school()\n",
    "            \n",
    "columns=['school_name','1_stu_num','2_stu_num','3_stu_num']\n",
    "high_df=pd.DataFrame(high,columns=columns)\n",
    "high_df.to_csv(r'high_school_stu_num.csv')\n",
    "\n",
    "middle_df=pd.DataFrame(middle,columns=columns)\n",
    "middle_df.to_csv(r'middle_school_stu_num.csv')\n",
    "\n",
    "columns1=['school_name','1_stu_num','2_stu_num','3_stu_num','4_stu_num','5_stu_num','6_stu_num']\n",
    "ele_df=pd.DataFrame(elementary,columns=columns1)\n",
    "ele_df.to_csv(r'elementary_school_stu_num.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 11873/11873 [00:08<00:00, 1379.25it/s]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pyspark\n",
    "from tqdm import tqdm\n",
    "\n",
    "basic_folder = ''\n",
    "file_name = basic_folder + 'elementary_middle_schools_location.csv'\n",
    "\n",
    "df2 =pd.read_csv(file_name)\n",
    "count_row=df2.shape[0]\n",
    "\n",
    "elementary=[]\n",
    "middle=[]\n",
    "\n",
    "def type_of_school():\n",
    "    for x in tqdm(range(count_row)):\n",
    "        type=df2.loc[x]['학교급구분']\n",
    "        if(type=='중학교'):\n",
    "            row=[df2.loc[x]['학교명'],df2.loc[x]['소재지도로명주소'],df2.loc[x]['위도'],df2.loc[x]['경도']]\n",
    "            middle.append(row)\n",
    "        elif(type=='초등학교'):\n",
    "            row=[df2.loc[x]['학교명'],df2.loc[x]['소재지도로명주소'],df2.loc[x]['위도'],df2.loc[x]['경도']]\n",
    "            elementary.append(row)\n",
    "\n",
    "type_of_school()\n",
    "columns=['school_name','school_addr','latitude','longitude']\n",
    "middle_df=pd.DataFrame(middle,columns=columns)\n",
    "middle_df.to_csv(r'middle_school.csv')\n",
    "\n",
    "elem_df=pd.DataFrame(elementary,columns=columns)\n",
    "elem_df.to_csv(r'elementary_school.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 3240/3240 [26:47<00:00,  2.02it/s]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pyspark\n",
    "from tqdm import tqdm\n",
    "\n",
    "basic_folder = ''\n",
    "file_name = basic_folder + 'middle_school.csv'\n",
    "file_name2 = basic_folder + 'middle_school_stu_num.csv'\n",
    "\n",
    "middle_df =pd.read_csv(file_name)\n",
    "middle_stu_num_df=pd.read_csv(file_name2)\n",
    "\n",
    "count_row=middle_df.shape[0]\n",
    "count_row2=middle_stu_num_df.shape[0]\n",
    "\n",
    "middle_arr=[]\n",
    "\n",
    "def find_middle_student_num():\n",
    "    for x in tqdm(range(count_row)):\n",
    "        name=middle_df.loc[x]['school_name']\n",
    "        for y in range(count_row2):\n",
    "            if name == middle_stu_num_df.loc[y]['school_name']:\n",
    "                row=[middle_df.loc[x]['school_name'],middle_df.loc[x]['school_addr'],middle_df.loc[x]['latitude'],middle_df.loc[x]['longitude'],\n",
    "                   middle_stu_num_df.loc[y]['1_stu_num'],middle_stu_num_df.loc[y]['2_stu_num'],middle_stu_num_df.loc[y]['3_stu_num']]\n",
    "                middle_arr.append(row)\n",
    "                    \n",
    "\n",
    "find_middle_student_num()\n",
    "columns=['school_name','school_addr','latitude','longitude','1_stu_num','2_stu_num','3_stu_num']\n",
    "final_middle_df=pd.DataFrame(middle_arr,columns=columns)\n",
    "final_middle_df.to_csv(r'final_middle_school.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 6278/6278 [1:38:36<00:00,  1.06it/s]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pyspark\n",
    "from tqdm import tqdm\n",
    "\n",
    "basic_folder = ''\n",
    "file_name = basic_folder + 'elementary_school.csv'\n",
    "file_name2 = basic_folder + 'elementary_school_stu_num.csv'\n",
    "\n",
    "ele_df =pd.read_csv(file_name)\n",
    "ele_stu_num_df=pd.read_csv(file_name2)\n",
    "\n",
    "count_row=ele_df.shape[0]\n",
    "count_row2=ele_stu_num_df.shape[0]\n",
    "\n",
    "ele_arr=[]\n",
    "\n",
    "def find_ele_student_num():\n",
    "    for x in tqdm(range(count_row)):\n",
    "        name=ele_df.loc[x]['school_name']\n",
    "        for y in range(count_row2):\n",
    "            if name == ele_stu_num_df.loc[y]['school_name']:\n",
    "                row=[ele_df.loc[x]['school_name'],ele_df.loc[x]['school_addr'],ele_df.loc[x]['latitude'],ele_df.loc[x]['longitude'],\n",
    "                   ele_stu_num_df.loc[y]['1_stu_num'],ele_stu_num_df.loc[y]['2_stu_num'],ele_stu_num_df.loc[y]['3_stu_num'],\n",
    "                    ele_stu_num_df.loc[y]['4_stu_num'],ele_stu_num_df.loc[y]['5_stu_num'],ele_stu_num_df.loc[y]['6_stu_num']]\n",
    "                ele_arr.append(row)\n",
    "                    \n",
    "find_ele_student_num()\n",
    "columns=['school_name','school_addr','latitude','longitude','1_stu_num','2_stu_num','3_stu_num','4_stu_num','5_stu_num','6_stu_num']\n",
    "final_ele_df=pd.DataFrame(ele_arr,columns=columns)\n",
    "final_ele_df.to_csv(r'final_ele_school.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pyspark\n",
    "from tqdm import tqdm\n",
    "\n",
    "basic_folder = ''\n",
    "file_name = basic_folder + 'high_school.csv'\n",
    "file_name2 = basic_folder + 'high_school_stu_num.csv'\n",
    "\n",
    "middle_df =pd.read_csv(file_name)\n",
    "middle_stu_num_df=pd.read_csv(file_name2)\n",
    "\n",
    "count_row=middle_df.shape[0]\n",
    "count_row2=middle_stu_num_df.shape[0]\n",
    "\n",
    "high_arr=[]\n",
    "\n",
    "def find_high_student_num():\n",
    "    for x in tqdm(range(count_row)):\n",
    "        name=middle_df.loc[x]['school_name']\n",
    "        for y in range(count_row2):\n",
    "            if name == middle_stu_num_df.loc[y]['school_name']:\n",
    "                row=[middle_df.loc[x]['school_name'],middle_df.loc[x]['school_addr'],middle_df.loc[x]['latitude'],middle_df.loc[x]['longitude'],\n",
    "                   middle_stu_num_df.loc[y]['1_stu_num'],middle_stu_num_df.loc[y]['2_stu_num'],middle_stu_num_df.loc[y]['3_stu_num']]\n",
    "                high_arr.append(row)\n",
    "                    \n",
    "find_high_student_num()\n",
    "columns=['school_name','school_addr','latitude','longitude','1_stu_num','2_stu_num','3_stu_num']\n",
    "final_high_df=pd.DataFrame(middle_arr,columns=columns)\n",
    "final_high_df.to_csv(r'final_high_school.csv')"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}