create_Proc_Block_Master.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "prec_noとblock_noのマスタの作成"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['.ipynb_checkpoints',\n",
       " 'chromedriver.exe',\n",
       " 'create_Proc_Block_Master.ipynb',\n",
       " 'LocationMaster.csv',\n",
       " 'LocationMaster.jmp',\n",
       " 'prec_no__Master.csv',\n",
       " 'Sample2.ipynb',\n",
       " 'Sample3.ipynb',\n",
       " 'smaster.index',\n",
       " '気象庁天候データ.msg']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1時間ごとのデータ取得  \n",
    "https://www.data.jma.go.jp/obd/stats/etrn/view/hourly_s1.php?prec_no=44&block_no=47662&year=2020&month=1&day=1&view=  \n",
    "  \n",
    "パラメータ\n",
    "- prec_no=44　（都道府県）\n",
    "- block_no=47662　（都市）\n",
    "- year=2020\n",
    "- month=1\n",
    "- day=1\n",
    "- view="
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### prec_noに関して\n",
    "https://www.jma.go.jp/jma/kishou/know/amedas/ame_master.pdf  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "こちらは手作業で成型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(63, 2)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prec_mst = pd.read_csv('prec_no__Master.csv')\n",
    "prec_mst.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>宗谷総合振興局</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>上川総合振興局</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>留萌振興局</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>石狩振興局</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>空知総合振興局</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>愛媛県</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>高知県</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>山口県</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>福岡県</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>大分県</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        府県名  番号\n",
       "0   宗谷総合振興局  11\n",
       "1   上川総合振興局  12\n",
       "2     留萌振興局  13\n",
       "3     石狩振興局  14\n",
       "4   空知総合振興局  15\n",
       "..      ...  ..\n",
       "58      愛媛県  73\n",
       "59      高知県  74\n",
       "60      山口県  81\n",
       "61      福岡県  82\n",
       "62      大分県  83\n",
       "\n",
       "[63 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prec_mst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### block_noに関して\n",
    "https://www.data.jma.go.jp/obd/stats/data/mdrr/man/kansoku_gaiyou.html  \n",
    "Download smaster.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2999"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('smaster.index', 'r') as f:\n",
    "    #all_line = f.read()\n",
    "    lines = f.readlines()\n",
    "    #print(lines)\n",
    "\n",
    "len(lines)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'532 8810732   7 ｼｮｳﾜ    SYOWA       6900300393480  113  217    02016020199999999昭和\\u3000\\u3000\\u3000\\u3000昭和基地\\u3000\\u3000\\u3000\\u3000\\u3000南極\\u3000\\u3000  184            00110      \\n'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lines[2998]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "地点番号 401\n",
      "管区コード 1\n",
      "アメダス府県コード   \n",
      "観測所区分  \n",
      "カナ地点名 ﾜﾂｶﾅｲ   \n",
      "緯度 452490\n",
      "経度 1414070\n",
      "漢字地点名 　　　　　　\n",
      "漢字官署名 　　　　　　　　\n",
      "都道府県振興局名 　　　　\n"
     ]
    }
   ],
   "source": [
    "line = lines[2]\n",
    "\n",
    "print(\"地点番号\", line[0:3])\n",
    "print(\"管区コード\", line[4])\n",
    "print(\"アメダス府県コード\", line[12:14])\n",
    "print(\"観測所区分\", line[14])\n",
    "print(\"カナ地点名\", line[16:24])\n",
    "\n",
    "\n",
    "print(\"緯度\", line[36:42])\n",
    "print(\"経度\", line[42:49])\n",
    "\n",
    "print(\"漢字地点名\", line[80:86])\n",
    "print(\"漢字官署名\", line[86:94])\n",
    "print(\"都道府県振興局名\", line[95:99])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "val_Chiten= []\n",
    "val_Kanku = []\n",
    "val_Amedas = []\n",
    "val_Kasokujyo = []\n",
    "val_KanaChiku = []\n",
    "val_Ido = []\n",
    "val_Keido = []\n",
    "val_KjChiten =[]\n",
    "val_KjKansho = []\n",
    "val_Ken= []\n",
    "\n",
    "for line in lines:\n",
    "    val_Chiten.append(line[0:3].strip(' '))\n",
    "    val_Kanku.append(line[4].strip(' '))\n",
    "    val_Amedas.append(line[12:14].strip(' '))\n",
    "    val_Kasokujyo.append(line[14].strip(' '))\n",
    "    val_KanaChiku.append(line[16:24].strip(' '))\n",
    "    val_Ido.append(line[36:42].strip(' '))\n",
    "    val_Keido.append(line[42:49].strip(' '))\n",
    "    val_KjChiten.append(line[80:86].strip('　'))\n",
    "    val_KjKansho.append(line[86:94].strip('　'))\n",
    "    val_Ken.append(line[95:99].strip('　'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2999, 10)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_mst = pd.DataFrame({\"地点番号\":val_Chiten, \n",
    "                          \"管区コード\":val_Kanku, \n",
    "                          \"アメダス府県コード\":val_Amedas,\n",
    "                          \"観測所区分\":val_Kasokujyo,\n",
    "                          \"カナ地点名\":val_KanaChiku,\n",
    "                          \"緯度\":val_Ido,\n",
    "                          \"経度\":val_Keido,\n",
    "                          \"漢字地点名\":val_KjChiten,\n",
    "                          \"漢字官署名\":val_KjKansho,\n",
    "                          \"都道府県振興局名\":val_Ken})\n",
    "block_mst.shape"
   ]
  },
  {
   "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>地点番号</th>\n",
       "      <th>管区コード</th>\n",
       "      <th>アメダス府県コード</th>\n",
       "      <th>観測所区分</th>\n",
       "      <th>カナ地点名</th>\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>401</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  地点番号 管区コード アメダス府県コード 観測所区分  カナ地点名      緯度       経度 漢字地点名 漢字官署名 都道府県振興局名\n",
       "0  401     1                  ﾜﾂｶﾅｲ  452490  1414070                     \n",
       "1  401     1                  ﾜﾂｶﾅｲ  452490  1414070                     \n",
       "2  401     1                  ﾜﾂｶﾅｲ  452490  1414070                     \n",
       "3  401     1                  ﾜﾂｶﾅｲ  452490  1414070                     \n",
       "4  401     1                  ﾜﾂｶﾅｲ  452490  1414070                     "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_mst.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1871, 10)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_mst = block_mst[block_mst[\"漢字地点名\"] != '']\n",
    "block_mst.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(338, 10)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_mst = block_mst.drop_duplicates()\n",
    "block_mst.shape"
   ]
  },
  {
   "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",
       "      <th>カナ地点名</th>\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>7</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td>稚内</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷支庁</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td>稚内</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷総合</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>北見枝幸測候所</td>\n",
       "      <td>宗谷支庁</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷支庁</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷総合</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>2955</th>\n",
       "      <td>945</td>\n",
       "      <td>6</td>\n",
       "      <td>92</td>\n",
       "      <td>4</td>\n",
       "      <td>ﾐﾅﾐﾀﾞｲﾄｳ</td>\n",
       "      <td>254970</td>\n",
       "      <td>1311370</td>\n",
       "      <td>南大東島</td>\n",
       "      <td>南大東島地方気象</td>\n",
       "      <td>沖縄県</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2966</th>\n",
       "      <td>971</td>\n",
       "      <td>7</td>\n",
       "      <td>44</td>\n",
       "      <td>6</td>\n",
       "      <td>ﾁﾁｼﾞﾏ</td>\n",
       "      <td>270550</td>\n",
       "      <td>1421140</td>\n",
       "      <td>父島</td>\n",
       "      <td>父島気象観測所</td>\n",
       "      <td>東京都</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2978</th>\n",
       "      <td>991</td>\n",
       "      <td>7</td>\n",
       "      <td>44</td>\n",
       "      <td>6</td>\n",
       "      <td>ﾐﾅﾐﾄﾘｼﾏ</td>\n",
       "      <td>241820</td>\n",
       "      <td>1535770</td>\n",
       "      <td>南鳥島</td>\n",
       "      <td>南鳥島気象観測所</td>\n",
       "      <td>東京都</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2982</th>\n",
       "      <td>991</td>\n",
       "      <td>7</td>\n",
       "      <td>44</td>\n",
       "      <td>6</td>\n",
       "      <td>ﾐﾅﾐﾄﾘｼﾏ</td>\n",
       "      <td>241730</td>\n",
       "      <td>1535900</td>\n",
       "      <td>南鳥島</td>\n",
       "      <td>南鳥島気象観測所</td>\n",
       "      <td>東京都</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2989</th>\n",
       "      <td>532</td>\n",
       "      <td>8</td>\n",
       "      <td></td>\n",
       "      <td>7</td>\n",
       "      <td>ｼｮｳﾜ</td>\n",
       "      <td>690030</td>\n",
       "      <td>0393480</td>\n",
       "      <td>昭和</td>\n",
       "      <td>昭和基地</td>\n",
       "      <td>南極</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>338 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     地点番号 管区コード アメダス府県コード 観測所区分     カナ地点名      緯度       経度 漢字地点名     漢字官署名  \\\n",
       "7     401     1        11     4     ﾜﾂｶﾅｲ  452490  1414070    稚内   稚内地方気象台   \n",
       "16    401     1        11     4     ﾜﾂｶﾅｲ  452490  1414070    稚内   稚内地方気象台   \n",
       "27    402     1        11     5    ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸   北見枝幸測候所   \n",
       "34    402     1        11     8    ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸   稚内地方気象台   \n",
       "36    402     1        11     8    ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸   稚内地方気象台   \n",
       "...   ...   ...       ...   ...       ...     ...      ...   ...       ...   \n",
       "2955  945     6        92     4  ﾐﾅﾐﾀﾞｲﾄｳ  254970  1311370  南大東島  南大東島地方気象   \n",
       "2966  971     7        44     6     ﾁﾁｼﾞﾏ  270550  1421140    父島   父島気象観測所   \n",
       "2978  991     7        44     6   ﾐﾅﾐﾄﾘｼﾏ  241820  1535770   南鳥島  南鳥島気象観測所   \n",
       "2982  991     7        44     6   ﾐﾅﾐﾄﾘｼﾏ  241730  1535900   南鳥島  南鳥島気象観測所   \n",
       "2989  532     8               7      ｼｮｳﾜ  690030  0393480    昭和      昭和基地   \n",
       "\n",
       "     都道府県振興局名  \n",
       "7        宗谷支庁  \n",
       "16       宗谷総合  \n",
       "27       宗谷支庁  \n",
       "34       宗谷支庁  \n",
       "36       宗谷総合  \n",
       "...       ...  \n",
       "2955      沖縄県  \n",
       "2966      東京都  \n",
       "2978      東京都  \n",
       "2982      東京都  \n",
       "2989       南極  \n",
       "\n",
       "[338 rows x 10 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_mst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "block_mst['block_no'] = ['47'+str(no) for no in block_mst['地点番号']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#(block_mst['緯度'].astype(int)/10000).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#(block_mst['緯度'].astype(int)/10000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#(block_mst['緯度'].astype(int)%10000)/6000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#(block_mst['緯度'].astype(int)/10000).astype(int) + (block_mst['緯度'].astype(int)%10000)/6000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#block_mst['latitude'] = block_mst['緯度'].astype(int)/10000\n",
    "#block_mst['longtitude'] = block_mst['経度'].astype(int)/10000\n",
    "\n",
    "block_mst['latitude'] = (block_mst['緯度'].astype(int)/10000).astype(int) + (block_mst['緯度'].astype(int)%10000)/6000\n",
    "block_mst['longtitude'] = (block_mst['経度'].astype(int)/10000).astype(int) + (block_mst['経度'].astype(int)%10000)/6000"
   ]
  },
  {
   "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>地点番号</th>\n",
       "      <th>管区コード</th>\n",
       "      <th>アメダス府県コード</th>\n",
       "      <th>観測所区分</th>\n",
       "      <th>カナ地点名</th>\n",
       "      <th>緯度</th>\n",
       "      <th>経度</th>\n",
       "      <th>漢字地点名</th>\n",
       "      <th>漢字官署名</th>\n",
       "      <th>都道府県振興局名</th>\n",
       "      <th>block_no</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longtitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td>稚内</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷支庁</td>\n",
       "      <td>47401</td>\n",
       "      <td>45.415</td>\n",
       "      <td>141.678333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td>稚内</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷総合</td>\n",
       "      <td>47401</td>\n",
       "      <td>45.415</td>\n",
       "      <td>141.678333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>北見枝幸測候所</td>\n",
       "      <td>宗谷支庁</td>\n",
       "      <td>47402</td>\n",
       "      <td>44.940</td>\n",
       "      <td>142.585000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷支庁</td>\n",
       "      <td>47402</td>\n",
       "      <td>44.940</td>\n",
       "      <td>142.585000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷総合</td>\n",
       "      <td>47402</td>\n",
       "      <td>44.940</td>\n",
       "      <td>142.585000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   地点番号 管区コード アメダス府県コード 観測所区分   カナ地点名      緯度       経度 漢字地点名    漢字官署名  \\\n",
       "7   401     1        11     4   ﾜﾂｶﾅｲ  452490  1414070    稚内  稚内地方気象台   \n",
       "16  401     1        11     4   ﾜﾂｶﾅｲ  452490  1414070    稚内  稚内地方気象台   \n",
       "27  402     1        11     5  ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸  北見枝幸測候所   \n",
       "34  402     1        11     8  ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸  稚内地方気象台   \n",
       "36  402     1        11     8  ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸  稚内地方気象台   \n",
       "\n",
       "   都道府県振興局名 block_no  latitude  longtitude  \n",
       "7      宗谷支庁    47401    45.415  141.678333  \n",
       "16     宗谷総合    47401    45.415  141.678333  \n",
       "27     宗谷支庁    47402    44.940  142.585000  \n",
       "34     宗谷支庁    47402    44.940  142.585000  \n",
       "36     宗谷総合    47402    44.940  142.585000  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_mst.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(338, 15)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_prec_mst = pd.merge(block_mst, prec_mst, left_on='都道府県振興局名', right_on='府県名', how='left')\n",
    "block_prec_mst.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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",
       "      <th>カナ地点名</th>\n",
       "      <th>緯度</th>\n",
       "      <th>経度</th>\n",
       "      <th>漢字地点名</th>\n",
       "      <th>漢字官署名</th>\n",
       "      <th>都道府県振興局名</th>\n",
       "      <th>block_no</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longtitude</th>\n",
       "      <th>府県名</th>\n",
       "      <th>番号</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td>稚内</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷支庁</td>\n",
       "      <td>47401</td>\n",
       "      <td>45.415</td>\n",
       "      <td>141.678333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>ﾜﾂｶﾅｲ</td>\n",
       "      <td>452490</td>\n",
       "      <td>1414070</td>\n",
       "      <td>稚内</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷総合</td>\n",
       "      <td>47401</td>\n",
       "      <td>45.415</td>\n",
       "      <td>141.678333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>北見枝幸測候所</td>\n",
       "      <td>宗谷支庁</td>\n",
       "      <td>47402</td>\n",
       "      <td>44.940</td>\n",
       "      <td>142.585000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷支庁</td>\n",
       "      <td>47402</td>\n",
       "      <td>44.940</td>\n",
       "      <td>142.585000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>402</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>ｷﾀﾐｴｻｼ</td>\n",
       "      <td>445640</td>\n",
       "      <td>1423510</td>\n",
       "      <td>北見枝幸</td>\n",
       "      <td>稚内地方気象台</td>\n",
       "      <td>宗谷総合</td>\n",
       "      <td>47402</td>\n",
       "      <td>44.940</td>\n",
       "      <td>142.585000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  地点番号 管区コード アメダス府県コード 観測所区分   カナ地点名      緯度       経度 漢字地点名    漢字官署名 都道府県振興局名  \\\n",
       "0  401     1        11     4   ﾜﾂｶﾅｲ  452490  1414070    稚内  稚内地方気象台     宗谷支庁   \n",
       "1  401     1        11     4   ﾜﾂｶﾅｲ  452490  1414070    稚内  稚内地方気象台     宗谷総合   \n",
       "2  402     1        11     5  ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸  北見枝幸測候所     宗谷支庁   \n",
       "3  402     1        11     8  ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸  稚内地方気象台     宗谷支庁   \n",
       "4  402     1        11     8  ｷﾀﾐｴｻｼ  445640  1423510  北見枝幸  稚内地方気象台     宗谷総合   \n",
       "\n",
       "  block_no  latitude  longtitude  府県名  番号  \n",
       "0    47401    45.415  141.678333  NaN NaN  \n",
       "1    47401    45.415  141.678333  NaN NaN  \n",
       "2    47402    44.940  142.585000  NaN NaN  \n",
       "3    47402    44.940  142.585000  NaN NaN  \n",
       "4    47402    44.940  142.585000  NaN NaN  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "block_prec_mst.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "block_prec_mst.to_csv('LocationMaster.csv', index=False)"
   ]
  },
  {
   "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}