bus_gu.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>ROUTE_ID</th>\n",
       "      <th>노선명</th>\n",
       "      <th>순번</th>\n",
       "      <th>NODE_ID</th>\n",
       "      <th>ARS_ID</th>\n",
       "      <th>정류소명</th>\n",
       "      <th>X좌표</th>\n",
       "      <th>Y좌표</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>122000006</td>\n",
       "      <td>N6703</td>\n",
       "      <td>1</td>\n",
       "      <td>161000612</td>\n",
       "      <td>92702</td>\n",
       "      <td>인천공항T2-B1층</td>\n",
       "      <td>126.434018</td>\n",
       "      <td>37.466505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>122000004</td>\n",
       "      <td>N6701</td>\n",
       "      <td>1</td>\n",
       "      <td>161000612</td>\n",
       "      <td>92702</td>\n",
       "      <td>인천공항T2-B1층</td>\n",
       "      <td>126.434018</td>\n",
       "      <td>37.466505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100100573</td>\n",
       "      <td>6702</td>\n",
       "      <td>1</td>\n",
       "      <td>161000612</td>\n",
       "      <td>92702</td>\n",
       "      <td>인천공항T2-B1층</td>\n",
       "      <td>126.434018</td>\n",
       "      <td>37.466505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100100413</td>\n",
       "      <td>6002</td>\n",
       "      <td>1</td>\n",
       "      <td>161000612</td>\n",
       "      <td>92702</td>\n",
       "      <td>인천공항T2-B1층</td>\n",
       "      <td>126.434018</td>\n",
       "      <td>37.466505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100100412</td>\n",
       "      <td>6001</td>\n",
       "      <td>1</td>\n",
       "      <td>161000612</td>\n",
       "      <td>92702</td>\n",
       "      <td>인천공항T2-B1층</td>\n",
       "      <td>126.434018</td>\n",
       "      <td>37.466505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47056</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>183</td>\n",
       "      <td>116000142</td>\n",
       "      <td>17233</td>\n",
       "      <td>개봉역</td>\n",
       "      <td>126.856720</td>\n",
       "      <td>37.493978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47057</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>184</td>\n",
       "      <td>116000144</td>\n",
       "      <td>17235</td>\n",
       "      <td>개봉1동사거리.개봉푸르지오아파트</td>\n",
       "      <td>126.846791</td>\n",
       "      <td>37.501413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47058</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>185</td>\n",
       "      <td>114000125</td>\n",
       "      <td>15228</td>\n",
       "      <td>신정학마을아파트</td>\n",
       "      <td>126.843963</td>\n",
       "      <td>37.507421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47059</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>186</td>\n",
       "      <td>114000257</td>\n",
       "      <td>15362</td>\n",
       "      <td>신정현대아파트.서부트럭터미널</td>\n",
       "      <td>126.842007</td>\n",
       "      <td>37.509011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47060</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>187</td>\n",
       "      <td>114000267</td>\n",
       "      <td>15373</td>\n",
       "      <td>양천공영차고지</td>\n",
       "      <td>126.837219</td>\n",
       "      <td>37.508493</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>47061 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ROUTE_ID    노선명   순번    NODE_ID  ARS_ID               정류소명  \\\n",
       "0      122000006  N6703    1  161000612   92702         인천공항T2-B1층   \n",
       "1      122000004  N6701    1  161000612   92702         인천공항T2-B1층   \n",
       "2      100100573   6702    1  161000612   92702         인천공항T2-B1층   \n",
       "3      100100413   6002    1  161000612   92702         인천공항T2-B1층   \n",
       "4      100100412   6001    1  161000612   92702         인천공항T2-B1층   \n",
       "...          ...    ...  ...        ...     ...                ...   \n",
       "47056  100100589    N61  183  116000142   17233                개봉역   \n",
       "47057  100100589    N61  184  116000144   17235  개봉1동사거리.개봉푸르지오아파트   \n",
       "47058  100100589    N61  185  114000125   15228           신정학마을아파트   \n",
       "47059  100100589    N61  186  114000257   15362    신정현대아파트.서부트럭터미널   \n",
       "47060  100100589    N61  187  114000267   15373            양천공영차고지   \n",
       "\n",
       "              X좌표        Y좌표  \n",
       "0      126.434018  37.466505  \n",
       "1      126.434018  37.466505  \n",
       "2      126.434018  37.466505  \n",
       "3      126.434018  37.466505  \n",
       "4      126.434018  37.466505  \n",
       "...           ...        ...  \n",
       "47056  126.856720  37.493978  \n",
       "47057  126.846791  37.501413  \n",
       "47058  126.843963  37.507421  \n",
       "47059  126.842007  37.509011  \n",
       "47060  126.837219  37.508493  \n",
       "\n",
       "[47061 rows x 8 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the Excel file\n",
    "excel_path = \"data/서울시버스노선별정류소정보(20240507).xlsx\"\n",
    "df = pd.read_excel(excel_path)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/minseokpark/Library/Python/3.9/lib/python/site-packages/IPython/core/interactiveshell.py:3490: FutureWarning: The `op` parameter is deprecated and will be removed in a future release. Please use the `predicate` parameter instead.\n",
      "  if await self.run_code(code, result, async_=asy):\n"
     ]
    }
   ],
   "source": [
    "import geopandas as gpd\n",
    "import pandas as pd\n",
    "from shapely.geometry import Point\n",
    "\n",
    "# Load the shapefile\n",
    "shp_path = \"data/시군구/sig.shp\"\n",
    "gdf_shp = gpd.read_file(shp_path, encoding='euc-kr')\n",
    "\n",
    "# Load the Excel file\n",
    "excel_path = \"data/서울시버스노선별정류소정보(20240507).xlsx\"\n",
    "df = pd.read_excel(excel_path)\n",
    "\n",
    "# Convert the DataFrame to a GeoDataFrame\n",
    "geometry = [Point(xy) for xy in zip(df['X좌표'], df['Y좌표'])]\n",
    "gdf_points = gpd.GeoDataFrame(df, geometry=geometry)\n",
    "\n",
    "# Set the CRS (Coordinate Reference System)\n",
    "gdf_points = gdf_points.set_crs(\"EPSG:4326\")\n",
    "gdf_shp = gdf_shp.to_crs(\"EPSG:4326\")\n",
    "\n",
    "# Perform spatial join to determine the district\n",
    "gdf_joined = gpd.sjoin(gdf_points, gdf_shp, how='left', op='within')\n",
    "\n",
    "# Extract necessary columns\n",
    "result = gdf_joined[['ROUTE_ID', '노선명', '순번', 'NODE_ID', 'ARS_ID', '정류소명', 'X좌표', 'Y좌표', 'SIG_KOR_NM', 'SIG_CD']]\n",
    "\n",
    "# Save the result to a new Excel file\n",
    "result_excel_path = \"data/서울시버스노선별정류소정보_with_district.xlsx\"\n",
    "result.to_excel(result_excel_path, index=False)\n",
    "\n",
    "filtered_result = result[result['SIG_CD'].astype(str).str.startswith('11')]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>ROUTE_ID</th>\n",
       "      <th>노선명</th>\n",
       "      <th>순번</th>\n",
       "      <th>NODE_ID</th>\n",
       "      <th>ARS_ID</th>\n",
       "      <th>정류소명</th>\n",
       "      <th>X좌표</th>\n",
       "      <th>Y좌표</th>\n",
       "      <th>SIG_KOR_NM</th>\n",
       "      <th>SIG_CD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>100000020</td>\n",
       "      <td>청와대A01(자율주행)</td>\n",
       "      <td>1</td>\n",
       "      <td>100000419</td>\n",
       "      <td>1280</td>\n",
       "      <td>경복궁역(효자로입구)</td>\n",
       "      <td>126.974305</td>\n",
       "      <td>37.576715</td>\n",
       "      <td>종로구</td>\n",
       "      <td>11110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>123900001</td>\n",
       "      <td>송파01</td>\n",
       "      <td>1</td>\n",
       "      <td>123900007</td>\n",
       "      <td>24511</td>\n",
       "      <td>송파호반베르디움더퍼스트</td>\n",
       "      <td>127.140918</td>\n",
       "      <td>37.506850</td>\n",
       "      <td>송파구</td>\n",
       "      <td>11710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>103900012</td>\n",
       "      <td>성동03-2</td>\n",
       "      <td>1</td>\n",
       "      <td>103900269</td>\n",
       "      <td>4884</td>\n",
       "      <td>왕십리KCC스위첸아파트</td>\n",
       "      <td>127.026824</td>\n",
       "      <td>37.560369</td>\n",
       "      <td>성동구</td>\n",
       "      <td>11200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>112900002</td>\n",
       "      <td>서대문12</td>\n",
       "      <td>1</td>\n",
       "      <td>112900261</td>\n",
       "      <td>13946</td>\n",
       "      <td>동원베네스트주차장.두산위브입구</td>\n",
       "      <td>126.944217</td>\n",
       "      <td>37.599114</td>\n",
       "      <td>서대문구</td>\n",
       "      <td>11410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>110900005</td>\n",
       "      <td>노원03</td>\n",
       "      <td>1</td>\n",
       "      <td>110000183</td>\n",
       "      <td>11283</td>\n",
       "      <td>석계역1번출구.A</td>\n",
       "      <td>127.065197</td>\n",
       "      <td>37.614930</td>\n",
       "      <td>노원구</td>\n",
       "      <td>11350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47056</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>183</td>\n",
       "      <td>116000142</td>\n",
       "      <td>17233</td>\n",
       "      <td>개봉역</td>\n",
       "      <td>126.856720</td>\n",
       "      <td>37.493978</td>\n",
       "      <td>구로구</td>\n",
       "      <td>11530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47057</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>184</td>\n",
       "      <td>116000144</td>\n",
       "      <td>17235</td>\n",
       "      <td>개봉1동사거리.개봉푸르지오아파트</td>\n",
       "      <td>126.846791</td>\n",
       "      <td>37.501413</td>\n",
       "      <td>구로구</td>\n",
       "      <td>11530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47058</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>185</td>\n",
       "      <td>114000125</td>\n",
       "      <td>15228</td>\n",
       "      <td>신정학마을아파트</td>\n",
       "      <td>126.843963</td>\n",
       "      <td>37.507421</td>\n",
       "      <td>양천구</td>\n",
       "      <td>11470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47059</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>186</td>\n",
       "      <td>114000257</td>\n",
       "      <td>15362</td>\n",
       "      <td>신정현대아파트.서부트럭터미널</td>\n",
       "      <td>126.842007</td>\n",
       "      <td>37.509011</td>\n",
       "      <td>양천구</td>\n",
       "      <td>11470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47060</th>\n",
       "      <td>100100589</td>\n",
       "      <td>N61</td>\n",
       "      <td>187</td>\n",
       "      <td>114000267</td>\n",
       "      <td>15373</td>\n",
       "      <td>양천공영차고지</td>\n",
       "      <td>126.837219</td>\n",
       "      <td>37.508493</td>\n",
       "      <td>양천구</td>\n",
       "      <td>11470</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41745 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ROUTE_ID           노선명   순번    NODE_ID  ARS_ID               정류소명  \\\n",
       "14     100000020  청와대A01(자율주행)    1  100000419    1280        경복궁역(효자로입구)   \n",
       "15     123900001          송파01    1  123900007   24511       송파호반베르디움더퍼스트   \n",
       "16     103900012        성동03-2    1  103900269    4884       왕십리KCC스위첸아파트   \n",
       "17     112900002         서대문12    1  112900261   13946   동원베네스트주차장.두산위브입구   \n",
       "18     110900005          노원03    1  110000183   11283          석계역1번출구.A   \n",
       "...          ...           ...  ...        ...     ...                ...   \n",
       "47056  100100589           N61  183  116000142   17233                개봉역   \n",
       "47057  100100589           N61  184  116000144   17235  개봉1동사거리.개봉푸르지오아파트   \n",
       "47058  100100589           N61  185  114000125   15228           신정학마을아파트   \n",
       "47059  100100589           N61  186  114000257   15362    신정현대아파트.서부트럭터미널   \n",
       "47060  100100589           N61  187  114000267   15373            양천공영차고지   \n",
       "\n",
       "              X좌표        Y좌표 SIG_KOR_NM SIG_CD  \n",
       "14     126.974305  37.576715        종로구  11110  \n",
       "15     127.140918  37.506850        송파구  11710  \n",
       "16     127.026824  37.560369        성동구  11200  \n",
       "17     126.944217  37.599114       서대문구  11410  \n",
       "18     127.065197  37.614930        노원구  11350  \n",
       "...           ...        ...        ...    ...  \n",
       "47056  126.856720  37.493978        구로구  11530  \n",
       "47057  126.846791  37.501413        구로구  11530  \n",
       "47058  126.843963  37.507421        양천구  11470  \n",
       "47059  126.842007  37.509011        양천구  11470  \n",
       "47060  126.837219  37.508493        양천구  11470  \n",
       "\n",
       "[41745 rows x 10 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_result.to_excel(\"data/서울시버스노선별정류소정보_seoul_filtered.xlsx\", index=False)\n",
    "\n",
    "filtered_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>SIG_CD</th>\n",
       "      <th>SIG_ENG_NM</th>\n",
       "      <th>SIG_KOR_NM</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11110</td>\n",
       "      <td>Jongno-gu</td>\n",
       "      <td>종로구</td>\n",
       "      <td>POLYGON ((127.00864 37.58047, 127.00871 37.580...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11140</td>\n",
       "      <td>Jung-gu</td>\n",
       "      <td>중구</td>\n",
       "      <td>POLYGON ((127.02314 37.57196, 127.02336 37.571...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11170</td>\n",
       "      <td>Yongsan-gu</td>\n",
       "      <td>용산구</td>\n",
       "      <td>POLYGON ((126.96918 37.55566, 126.96917 37.554...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11200</td>\n",
       "      <td>Seongdong-gu</td>\n",
       "      <td>성동구</td>\n",
       "      <td>POLYGON ((127.04341 37.57234, 127.04524 37.571...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11215</td>\n",
       "      <td>Gwangjin-gu</td>\n",
       "      <td>광진구</td>\n",
       "      <td>POLYGON ((127.10166 37.57240, 127.10224 37.572...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>52740</td>\n",
       "      <td>Jangsu-gun</td>\n",
       "      <td>장수군</td>\n",
       "      <td>POLYGON ((127.61996 35.83894, 127.61996 35.838...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>52750</td>\n",
       "      <td>Imsil-gun</td>\n",
       "      <td>임실군</td>\n",
       "      <td>POLYGON ((127.10477 35.61910, 127.10510 35.619...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>52770</td>\n",
       "      <td>Sunchang-gun</td>\n",
       "      <td>순창군</td>\n",
       "      <td>POLYGON ((126.97726 35.55564, 126.97780 35.555...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>52790</td>\n",
       "      <td>Gochang-gun</td>\n",
       "      <td>고창군</td>\n",
       "      <td>MULTIPOLYGON (((126.42760 35.44847, 126.42764 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>52800</td>\n",
       "      <td>Buan-gun</td>\n",
       "      <td>부안군</td>\n",
       "      <td>MULTIPOLYGON (((126.35718 35.55838, 126.35702 ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    SIG_CD    SIG_ENG_NM SIG_KOR_NM  \\\n",
       "0    11110     Jongno-gu        종로구   \n",
       "1    11140       Jung-gu         중구   \n",
       "2    11170    Yongsan-gu        용산구   \n",
       "3    11200  Seongdong-gu        성동구   \n",
       "4    11215   Gwangjin-gu        광진구   \n",
       "..     ...           ...        ...   \n",
       "247  52740    Jangsu-gun        장수군   \n",
       "248  52750     Imsil-gun        임실군   \n",
       "249  52770  Sunchang-gun        순창군   \n",
       "250  52790   Gochang-gun        고창군   \n",
       "251  52800      Buan-gun        부안군   \n",
       "\n",
       "                                              geometry  \n",
       "0    POLYGON ((127.00864 37.58047, 127.00871 37.580...  \n",
       "1    POLYGON ((127.02314 37.57196, 127.02336 37.571...  \n",
       "2    POLYGON ((126.96918 37.55566, 126.96917 37.554...  \n",
       "3    POLYGON ((127.04341 37.57234, 127.04524 37.571...  \n",
       "4    POLYGON ((127.10166 37.57240, 127.10224 37.572...  \n",
       "..                                                 ...  \n",
       "247  POLYGON ((127.61996 35.83894, 127.61996 35.838...  \n",
       "248  POLYGON ((127.10477 35.61910, 127.10510 35.619...  \n",
       "249  POLYGON ((126.97726 35.55564, 126.97780 35.555...  \n",
       "250  MULTIPOLYGON (((126.42760 35.44847, 126.42764 ...  \n",
       "251  MULTIPOLYGON (((126.35718 35.55838, 126.35702 ...  \n",
       "\n",
       "[252 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_shp"
   ]
  },
  {
   "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>노선번호</th>\n",
       "      <th>인가대수</th>\n",
       "      <th>저상대수</th>\n",
       "      <th>보유율</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>29</td>\n",
       "      <td>29</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>32</td>\n",
       "      <td>20</td>\n",
       "      <td>62.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>102</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>N72은평</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>N72중랑</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>N73구산</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>N75신림</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>N75진관</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>328 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      노선번호  인가대수  저상대수    보유율\n",
       "0        1    14    14  100.0\n",
       "1      100    29    29  100.0\n",
       "2      101    32    20   62.5\n",
       "3      102    20    20  100.0\n",
       "4      105    20    20  100.0\n",
       "..     ...   ...   ...    ...\n",
       "323  N72은평     5     3   60.0\n",
       "324  N72중랑     4     4  100.0\n",
       "325  N73구산     4     4  100.0\n",
       "326  N75신림     6     6  100.0\n",
       "327  N75진관     6     6  100.0\n",
       "\n",
       "[328 rows x 4 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the Excel file\n",
    "excel_path = \"data/저상버스.xlsx\"\n",
    "df4 = pd.read_excel(excel_path)\n",
    "\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Unnamed: 0</th>\n",
       "      <th>District</th>\n",
       "      <th>Total_Stops</th>\n",
       "      <th>High_Availability_Stops</th>\n",
       "      <th>전체 비율</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>강남구</td>\n",
       "      <td>2815</td>\n",
       "      <td>1654</td>\n",
       "      <td>0.587567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>강동구</td>\n",
       "      <td>1228</td>\n",
       "      <td>641</td>\n",
       "      <td>0.521987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>강북구</td>\n",
       "      <td>1539</td>\n",
       "      <td>551</td>\n",
       "      <td>0.358025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>강서구</td>\n",
       "      <td>2163</td>\n",
       "      <td>1077</td>\n",
       "      <td>0.497920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>관악구</td>\n",
       "      <td>1905</td>\n",
       "      <td>1160</td>\n",
       "      <td>0.608924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>광진구</td>\n",
       "      <td>1023</td>\n",
       "      <td>588</td>\n",
       "      <td>0.574780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>구로구</td>\n",
       "      <td>1587</td>\n",
       "      <td>588</td>\n",
       "      <td>0.370510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>금천구</td>\n",
       "      <td>1160</td>\n",
       "      <td>582</td>\n",
       "      <td>0.501724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>노원구</td>\n",
       "      <td>1916</td>\n",
       "      <td>827</td>\n",
       "      <td>0.431628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>도봉구</td>\n",
       "      <td>1165</td>\n",
       "      <td>481</td>\n",
       "      <td>0.412876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>동대문구</td>\n",
       "      <td>1439</td>\n",
       "      <td>972</td>\n",
       "      <td>0.675469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>동작구</td>\n",
       "      <td>1740</td>\n",
       "      <td>707</td>\n",
       "      <td>0.406322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>마포구</td>\n",
       "      <td>2192</td>\n",
       "      <td>1109</td>\n",
       "      <td>0.505931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>서대문구</td>\n",
       "      <td>2018</td>\n",
       "      <td>1119</td>\n",
       "      <td>0.554509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>서초구</td>\n",
       "      <td>2343</td>\n",
       "      <td>1007</td>\n",
       "      <td>0.429791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>성동구</td>\n",
       "      <td>1135</td>\n",
       "      <td>567</td>\n",
       "      <td>0.499559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>성북구</td>\n",
       "      <td>1988</td>\n",
       "      <td>732</td>\n",
       "      <td>0.368209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>송파구</td>\n",
       "      <td>2140</td>\n",
       "      <td>1426</td>\n",
       "      <td>0.666355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>양천구</td>\n",
       "      <td>1594</td>\n",
       "      <td>898</td>\n",
       "      <td>0.563363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>영등포구</td>\n",
       "      <td>2069</td>\n",
       "      <td>1053</td>\n",
       "      <td>0.508942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>용산구</td>\n",
       "      <td>1255</td>\n",
       "      <td>808</td>\n",
       "      <td>0.643825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>은평구</td>\n",
       "      <td>2197</td>\n",
       "      <td>1123</td>\n",
       "      <td>0.511152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>종로구</td>\n",
       "      <td>1789</td>\n",
       "      <td>950</td>\n",
       "      <td>0.531023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>중구</td>\n",
       "      <td>1393</td>\n",
       "      <td>862</td>\n",
       "      <td>0.618808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24</td>\n",
       "      <td>중랑구</td>\n",
       "      <td>1818</td>\n",
       "      <td>957</td>\n",
       "      <td>0.526403</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0 District  Total_Stops  High_Availability_Stops     전체 비율\n",
       "0            0      강남구         2815                     1654  0.587567\n",
       "1            1      강동구         1228                      641  0.521987\n",
       "2            2      강북구         1539                      551  0.358025\n",
       "3            3      강서구         2163                     1077  0.497920\n",
       "4            4      관악구         1905                     1160  0.608924\n",
       "5            5      광진구         1023                      588  0.574780\n",
       "6            6      구로구         1587                      588  0.370510\n",
       "7            7      금천구         1160                      582  0.501724\n",
       "8            8      노원구         1916                      827  0.431628\n",
       "9            9      도봉구         1165                      481  0.412876\n",
       "10          10     동대문구         1439                      972  0.675469\n",
       "11          11      동작구         1740                      707  0.406322\n",
       "12          12      마포구         2192                     1109  0.505931\n",
       "13          13     서대문구         2018                     1119  0.554509\n",
       "14          14      서초구         2343                     1007  0.429791\n",
       "15          15      성동구         1135                      567  0.499559\n",
       "16          16      성북구         1988                      732  0.368209\n",
       "17          17      송파구         2140                     1426  0.666355\n",
       "18          18      양천구         1594                      898  0.563363\n",
       "19          19     영등포구         2069                     1053  0.508942\n",
       "20          20      용산구         1255                      808  0.643825\n",
       "21          21      은평구         2197                     1123  0.511152\n",
       "22          22      종로구         1789                      950  0.531023\n",
       "23          23       중구         1393                      862  0.618808\n",
       "24          24      중랑구         1818                      957  0.526403"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "excel_path = \"results/저상버스비율1.csv\"\n",
    "df = pd.read_csv(excel_path)\n",
    "\n",
    "excel_path1 = \"results/저상버스비율2.csv\"\n",
    "df2 = pd.read_csv(excel_path1)\n",
    "\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>District</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>강남구</td>\n",
       "      <td>0.668917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>강동구</td>\n",
       "      <td>0.621336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>강북구</td>\n",
       "      <td>0.469786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>강서구</td>\n",
       "      <td>0.613500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>관악구</td>\n",
       "      <td>0.663517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>광진구</td>\n",
       "      <td>0.620723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>구로구</td>\n",
       "      <td>0.549464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>금천구</td>\n",
       "      <td>0.556897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>노원구</td>\n",
       "      <td>0.480689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>도봉구</td>\n",
       "      <td>0.496996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>동대문구</td>\n",
       "      <td>0.813760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>동작구</td>\n",
       "      <td>0.470115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>마포구</td>\n",
       "      <td>0.571624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>서대문구</td>\n",
       "      <td>0.623389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>서초구</td>\n",
       "      <td>0.477166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>성동구</td>\n",
       "      <td>0.533921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>성북구</td>\n",
       "      <td>0.444165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>송파구</td>\n",
       "      <td>0.775234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>양천구</td>\n",
       "      <td>0.712045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>영등포구</td>\n",
       "      <td>0.582407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>용산구</td>\n",
       "      <td>0.680478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>은평구</td>\n",
       "      <td>0.630405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>종로구</td>\n",
       "      <td>0.620458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>중구</td>\n",
       "      <td>0.673367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>중랑구</td>\n",
       "      <td>0.693619</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   District         0\n",
       "0       강남구  0.668917\n",
       "1       강동구  0.621336\n",
       "2       강북구  0.469786\n",
       "3       강서구  0.613500\n",
       "4       관악구  0.663517\n",
       "5       광진구  0.620723\n",
       "6       구로구  0.549464\n",
       "7       금천구  0.556897\n",
       "8       노원구  0.480689\n",
       "9       도봉구  0.496996\n",
       "10     동대문구  0.813760\n",
       "11      동작구  0.470115\n",
       "12      마포구  0.571624\n",
       "13     서대문구  0.623389\n",
       "14      서초구  0.477166\n",
       "15      성동구  0.533921\n",
       "16      성북구  0.444165\n",
       "17      송파구  0.775234\n",
       "18      양천구  0.712045\n",
       "19     영등포구  0.582407\n",
       "20      용산구  0.680478\n",
       "21      은평구  0.630405\n",
       "22      종로구  0.620458\n",
       "23       중구  0.673367\n",
       "24      중랑구  0.693619"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.008095415473797376\n",
      "0.0091671296215633\n"
     ]
    }
   ],
   "source": [
    "variance_A = df['전체 비율'].var(ddof=0)\n",
    "\n",
    "print(variance_A)\n",
    "\n",
    "variance_B = df2['0'].var(ddof=0)\n",
    "\n",
    "print(variance_B)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}