institution_master.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# KAKENの研究機関マスタをローカルのMariaDBに保存するプログラム"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 事前準備\n",
    "- KAKENマスタデータは、git のリポジトリで管理されており、最新のデータを利用可能。\n",
    "- https://bitbucket.org/niijp/grants_masterxml_kaken/ からリポジトリを pull して、ローカルの ./grants_masterxml_kaken フォルダに同期しておく。\n",
    "- ローカルで MariaDB を動かしておく。\n",
    "- MariaDB のユーザ名、パスワード、データベース名は、configparserで ./config.ini として保存しておく。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ここから本編\n",
    "bitbucketから読み込んだマスタのXMLファイルをelementTreeに変換"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# encoding: utf-8\n",
    "from lxml import etree\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>institution_niicode</th>\n",
       "      <th>institution_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0010101</td>\n",
       "      <td>北海道大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0010102</td>\n",
       "      <td>北海道教育大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0010103</td>\n",
       "      <td>室蘭工業大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0010104</td>\n",
       "      <td>小樽商科大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0010105</td>\n",
       "      <td>帯広畜産大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0010106</td>\n",
       "      <td>北見工業大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0010107</td>\n",
       "      <td>旭川医科大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0011101</td>\n",
       "      <td>弘前大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0011201</td>\n",
       "      <td>岩手大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0011301</td>\n",
       "      <td>東北大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0011302</td>\n",
       "      <td>宮城教育大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0011401</td>\n",
       "      <td>秋田大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0011501</td>\n",
       "      <td>山形大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0011601</td>\n",
       "      <td>福島大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0012101</td>\n",
       "      <td>茨城大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0012201</td>\n",
       "      <td>宇都宮大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0012301</td>\n",
       "      <td>群馬大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0012401</td>\n",
       "      <td>埼玉大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0012501</td>\n",
       "      <td>千葉大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0012601</td>\n",
       "      <td>東京大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0012602</td>\n",
       "      <td>東京医科歯科大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0012603</td>\n",
       "      <td>東京外国語大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0012604</td>\n",
       "      <td>東京学芸大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0012605</td>\n",
       "      <td>東京農工大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0012606</td>\n",
       "      <td>東京芸術大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0012608</td>\n",
       "      <td>東京工業大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0012610</td>\n",
       "      <td>東京水産大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0012611</td>\n",
       "      <td>お茶の水女子大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0012612</td>\n",
       "      <td>電気通信大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0012613</td>\n",
       "      <td>一橋大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72084</th>\n",
       "      <td>0062401</td>\n",
       "      <td>国立女性教育会館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72085</th>\n",
       "      <td>0062602</td>\n",
       "      <td>国立科学博物館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72086</th>\n",
       "      <td>0062604</td>\n",
       "      <td>国立国語研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72087</th>\n",
       "      <td>0062605</td>\n",
       "      <td>東京国立博物館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72088</th>\n",
       "      <td>0062606</td>\n",
       "      <td>京都国立博物館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72089</th>\n",
       "      <td>0062607</td>\n",
       "      <td>東京国立文化財研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72090</th>\n",
       "      <td>0062609</td>\n",
       "      <td>東京国立近代美術館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72091</th>\n",
       "      <td>0062610</td>\n",
       "      <td>国立西洋美術館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72092</th>\n",
       "      <td>0062612</td>\n",
       "      <td>大学入試センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72093</th>\n",
       "      <td>0062613</td>\n",
       "      <td>メディア教育開発センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72094</th>\n",
       "      <td>0062617</td>\n",
       "      <td>国立オリンピック記念青少年総合センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72095</th>\n",
       "      <td>0062701</td>\n",
       "      <td>国立特殊教育総合研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72096</th>\n",
       "      <td>0062702</td>\n",
       "      <td>大学評価・学位授与機構</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72097</th>\n",
       "      <td>0063901</td>\n",
       "      <td>岡崎国立共同研究機構</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72098</th>\n",
       "      <td>0064301</td>\n",
       "      <td>京都国立近代美術館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72099</th>\n",
       "      <td>0064402</td>\n",
       "      <td>国立国際美術館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72100</th>\n",
       "      <td>0064601</td>\n",
       "      <td>奈良国立博物館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72101</th>\n",
       "      <td>0064602</td>\n",
       "      <td>奈良国立文化財研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72102</th>\n",
       "      <td>0081402</td>\n",
       "      <td>秋田県高度技術研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72103</th>\n",
       "      <td>0081403</td>\n",
       "      <td>秋田県工業技術センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72104</th>\n",
       "      <td>0082605</td>\n",
       "      <td>東京都神経科学総合研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72105</th>\n",
       "      <td>0082608</td>\n",
       "      <td>国立多摩研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72106</th>\n",
       "      <td>0082615</td>\n",
       "      <td>日本進路指導協会</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72107</th>\n",
       "      <td>0083202</td>\n",
       "      <td>富山市科学文化センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72108</th>\n",
       "      <td>382757</td>\n",
       "      <td>国立障害者リハビリテーションセンター(研究所)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72109</th>\n",
       "      <td>382761</td>\n",
       "      <td>国立研究開発法人国立国際医療研究センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72110</th>\n",
       "      <td>382773</td>\n",
       "      <td>独立行政法人国立病院機構(名古屋医療センター臨床研究センター)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72111</th>\n",
       "      <td>382783</td>\n",
       "      <td>熊本県産業技術センター(ものづくり室、材料・地域資源室、食品加工室)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72112</th>\n",
       "      <td>382868</td>\n",
       "      <td>奈良県農業研究開発センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72113</th>\n",
       "      <td>382699</td>\n",
       "      <td>国際連合大学サステイナビリティ高等研究所</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72114 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      institution_niicode                    institution_name\n",
       "0                 0010101                               北海道大学\n",
       "1                 0010102                             北海道教育大学\n",
       "2                 0010103                              室蘭工業大学\n",
       "3                 0010104                              小樽商科大学\n",
       "4                 0010105                              帯広畜産大学\n",
       "5                 0010106                              北見工業大学\n",
       "6                 0010107                              旭川医科大学\n",
       "7                 0011101                                弘前大学\n",
       "8                 0011201                                岩手大学\n",
       "9                 0011301                                東北大学\n",
       "10                0011302                              宮城教育大学\n",
       "11                0011401                                秋田大学\n",
       "12                0011501                                山形大学\n",
       "13                0011601                                福島大学\n",
       "14                0012101                                茨城大学\n",
       "15                0012201                               宇都宮大学\n",
       "16                0012301                                群馬大学\n",
       "17                0012401                                埼玉大学\n",
       "18                0012501                                千葉大学\n",
       "19                0012601                                東京大学\n",
       "20                0012602                            東京医科歯科大学\n",
       "21                0012603                             東京外国語大学\n",
       "22                0012604                              東京学芸大学\n",
       "23                0012605                              東京農工大学\n",
       "24                0012606                              東京芸術大学\n",
       "25                0012608                              東京工業大学\n",
       "26                0012610                              東京水産大学\n",
       "27                0012611                            お茶の水女子大学\n",
       "28                0012612                              電気通信大学\n",
       "29                0012613                                一橋大学\n",
       "...                   ...                                 ...\n",
       "72084             0062401                            国立女性教育会館\n",
       "72085             0062602                             国立科学博物館\n",
       "72086             0062604                             国立国語研究所\n",
       "72087             0062605                             東京国立博物館\n",
       "72088             0062606                             京都国立博物館\n",
       "72089             0062607                          東京国立文化財研究所\n",
       "72090             0062609                           東京国立近代美術館\n",
       "72091             0062610                             国立西洋美術館\n",
       "72092             0062612                            大学入試センター\n",
       "72093             0062613                        メディア教育開発センター\n",
       "72094             0062617                 国立オリンピック記念青少年総合センター\n",
       "72095             0062701                         国立特殊教育総合研究所\n",
       "72096             0062702                         大学評価・学位授与機構\n",
       "72097             0063901                          岡崎国立共同研究機構\n",
       "72098             0064301                           京都国立近代美術館\n",
       "72099             0064402                             国立国際美術館\n",
       "72100             0064601                             奈良国立博物館\n",
       "72101             0064602                          奈良国立文化財研究所\n",
       "72102             0081402                          秋田県高度技術研究所\n",
       "72103             0081403                         秋田県工業技術センター\n",
       "72104             0082605                        東京都神経科学総合研究所\n",
       "72105             0082608                             国立多摩研究所\n",
       "72106             0082615                            日本進路指導協会\n",
       "72107             0083202                         富山市科学文化センター\n",
       "72108              382757             国立障害者リハビリテーションセンター(研究所)\n",
       "72109              382761                国立研究開発法人国立国際医療研究センター\n",
       "72110              382773     独立行政法人国立病院機構(名古屋医療センター臨床研究センター)\n",
       "72111              382783  熊本県産業技術センター(ものづくり室、材料・地域資源室、食品加工室)\n",
       "72112              382868                       奈良県農業研究開発センター\n",
       "72113              382699                国際連合大学サステイナビリティ高等研究所\n",
       "\n",
       "[72114 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tree = etree.parse('grants_masterxml_kaken/institution_master_kakenhi.xml')\n",
    "\n",
    "institutionlist = []\n",
    "for institution_table in tree.iterfind(\"institution_table\"):\n",
    "    for institution in institution_table.iterfind(\"institution\"):\n",
    "        institution_name = institution.find(\"name[@lang='ja']\").text\n",
    "        institution_niicode = institution.find(\"code[@type='nii']\").text\n",
    "\n",
    "        row = [\n",
    "            institution_niicode,\n",
    "            institution_name,\n",
    "        ]\n",
    "        \n",
    "        institutionlist.append(row)\n",
    "            \n",
    "df = pd.DataFrame(institutionlist)\n",
    "df.columns = ['institution_niicode', 'institution_name']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "重複のデータがあるかどうか"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "重複データがあった。dropしておく。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop_duplicates()\n",
    "df.duplicated().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0082679    1\n",
       "0042651    1\n",
       "0082699    1\n",
       "0082201    1\n",
       "0037602    1\n",
       "0034318    1\n",
       "0031101    1\n",
       "0027401    1\n",
       "0094601    1\n",
       "0043941    1\n",
       "0030110    1\n",
       "0054601    1\n",
       "0056401    1\n",
       "382897     1\n",
       "0033943    1\n",
       "0037408    1\n",
       "0051201    1\n",
       "0074325    1\n",
       "0042677    1\n",
       "0092720    1\n",
       "0037117    1\n",
       "0032105    1\n",
       "0035313    1\n",
       "0032635    1\n",
       "0032812    1\n",
       "0083803    1\n",
       "0024303    1\n",
       "0035409    1\n",
       "0021601    1\n",
       "0044435    1\n",
       "          ..\n",
       "0046104    1\n",
       "0183102    1\n",
       "0382657    1\n",
       "0023103    1\n",
       "0086401    1\n",
       "382724     1\n",
       "0023701    1\n",
       "0032629    1\n",
       "0174302    1\n",
       "0014701    1\n",
       "0037120    1\n",
       "0034528    1\n",
       "0034409    1\n",
       "0047202    1\n",
       "0084422    1\n",
       "0046304    1\n",
       "0043945    1\n",
       "0134520    1\n",
       "0042727    1\n",
       "0235505    1\n",
       "0042514    1\n",
       "0037107    1\n",
       "0033936    1\n",
       "0030108    1\n",
       "0042721    1\n",
       "0042694    1\n",
       "0035415    1\n",
       "382766     1\n",
       "0092105    1\n",
       "0171301    1\n",
       "Name: institution_niicode, Length: 2618, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.institution_niicode.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2618 entries, 0 to 2617\n",
      "Data columns (total 2 columns):\n",
      "institution_niicode    2618 non-null int32\n",
      "institution_name       2618 non-null object\n",
      "dtypes: int32(1), object(1)\n",
      "memory usage: 30.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df = df.astype({\n",
    "    'institution_niicode': int,\n",
    "})\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "niicodeがユニークなので、インデックスに設定する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>institution_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>institution_niicode</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10101</th>\n",
       "      <td>北海道大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10102</th>\n",
       "      <td>北海道教育大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10103</th>\n",
       "      <td>室蘭工業大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10104</th>\n",
       "      <td>小樽商科大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10105</th>\n",
       "      <td>帯広畜産大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10106</th>\n",
       "      <td>北見工業大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10107</th>\n",
       "      <td>旭川医科大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11101</th>\n",
       "      <td>弘前大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11201</th>\n",
       "      <td>岩手大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11301</th>\n",
       "      <td>東北大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11302</th>\n",
       "      <td>宮城教育大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11401</th>\n",
       "      <td>秋田大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11501</th>\n",
       "      <td>山形大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11601</th>\n",
       "      <td>福島大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12101</th>\n",
       "      <td>茨城大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12201</th>\n",
       "      <td>宇都宮大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12301</th>\n",
       "      <td>群馬大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12401</th>\n",
       "      <td>埼玉大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12501</th>\n",
       "      <td>千葉大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12601</th>\n",
       "      <td>東京大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12602</th>\n",
       "      <td>東京医科歯科大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12603</th>\n",
       "      <td>東京外国語大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12604</th>\n",
       "      <td>東京学芸大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12605</th>\n",
       "      <td>東京農工大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12606</th>\n",
       "      <td>東京芸術大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12608</th>\n",
       "      <td>東京工業大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12610</th>\n",
       "      <td>東京水産大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12611</th>\n",
       "      <td>お茶の水女子大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12612</th>\n",
       "      <td>電気通信大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12613</th>\n",
       "      <td>一橋大学</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62612</th>\n",
       "      <td>大学入試センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62613</th>\n",
       "      <td>メディア教育開発センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62617</th>\n",
       "      <td>国立オリンピック記念青少年総合センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62701</th>\n",
       "      <td>国立特殊教育総合研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62702</th>\n",
       "      <td>大学評価・学位授与機構</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63901</th>\n",
       "      <td>岡崎国立共同研究機構</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64301</th>\n",
       "      <td>京都国立近代美術館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64402</th>\n",
       "      <td>国立国際美術館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64601</th>\n",
       "      <td>奈良国立博物館</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64602</th>\n",
       "      <td>奈良国立文化財研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81402</th>\n",
       "      <td>秋田県高度技術研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81403</th>\n",
       "      <td>秋田県工業技術センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82404</th>\n",
       "      <td>国立身体障害者リハビリテーションセンター研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82605</th>\n",
       "      <td>東京都神経科学総合研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82608</th>\n",
       "      <td>国立多摩研究所</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82610</th>\n",
       "      <td>国立病院医療センター(臨床研究部)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82615</th>\n",
       "      <td>日本進路指導協会</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83202</th>\n",
       "      <td>富山市科学文化センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83904</th>\n",
       "      <td>国立名古屋病院(臨床研究センター)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84605</th>\n",
       "      <td>奈良県農業技術センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87402</th>\n",
       "      <td>熊本県工業技術センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382759</th>\n",
       "      <td>国立国際医療センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382773</th>\n",
       "      <td>独立行政法人国立病院機構(名古屋医療センター臨床研究センター)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382867</th>\n",
       "      <td>奈良県農業総合センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382757</th>\n",
       "      <td>国立障害者リハビリテーションセンター(研究所)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382783</th>\n",
       "      <td>熊本県産業技術センター(ものづくり室、材料・地域資源室、食品加工室)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382760</th>\n",
       "      <td>独立行政法人国立国際医療研究センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382868</th>\n",
       "      <td>奈良県農業研究開発センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382761</th>\n",
       "      <td>国立研究開発法人国立国際医療研究センター</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382699</th>\n",
       "      <td>国際連合大学サステイナビリティ高等研究所</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2618 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       institution_name\n",
       "institution_niicode                                    \n",
       "10101                                             北海道大学\n",
       "10102                                           北海道教育大学\n",
       "10103                                            室蘭工業大学\n",
       "10104                                            小樽商科大学\n",
       "10105                                            帯広畜産大学\n",
       "10106                                            北見工業大学\n",
       "10107                                            旭川医科大学\n",
       "11101                                              弘前大学\n",
       "11201                                              岩手大学\n",
       "11301                                              東北大学\n",
       "11302                                            宮城教育大学\n",
       "11401                                              秋田大学\n",
       "11501                                              山形大学\n",
       "11601                                              福島大学\n",
       "12101                                              茨城大学\n",
       "12201                                             宇都宮大学\n",
       "12301                                              群馬大学\n",
       "12401                                              埼玉大学\n",
       "12501                                              千葉大学\n",
       "12601                                              東京大学\n",
       "12602                                          東京医科歯科大学\n",
       "12603                                           東京外国語大学\n",
       "12604                                            東京学芸大学\n",
       "12605                                            東京農工大学\n",
       "12606                                            東京芸術大学\n",
       "12608                                            東京工業大学\n",
       "12610                                            東京水産大学\n",
       "12611                                          お茶の水女子大学\n",
       "12612                                            電気通信大学\n",
       "12613                                              一橋大学\n",
       "...                                                 ...\n",
       "62612                                          大学入試センター\n",
       "62613                                      メディア教育開発センター\n",
       "62617                               国立オリンピック記念青少年総合センター\n",
       "62701                                       国立特殊教育総合研究所\n",
       "62702                                       大学評価・学位授与機構\n",
       "63901                                        岡崎国立共同研究機構\n",
       "64301                                         京都国立近代美術館\n",
       "64402                                           国立国際美術館\n",
       "64601                                           奈良国立博物館\n",
       "64602                                        奈良国立文化財研究所\n",
       "81402                                        秋田県高度技術研究所\n",
       "81403                                       秋田県工業技術センター\n",
       "82404                           国立身体障害者リハビリテーションセンター研究所\n",
       "82605                                      東京都神経科学総合研究所\n",
       "82608                                           国立多摩研究所\n",
       "82610                                 国立病院医療センター(臨床研究部)\n",
       "82615                                          日本進路指導協会\n",
       "83202                                       富山市科学文化センター\n",
       "83904                                 国立名古屋病院(臨床研究センター)\n",
       "84605                                       奈良県農業技術センター\n",
       "87402                                       熊本県工業技術センター\n",
       "382759                                       国立国際医療センター\n",
       "382773                  独立行政法人国立病院機構(名古屋医療センター臨床研究センター)\n",
       "382867                                      奈良県農業総合センター\n",
       "382757                          国立障害者リハビリテーションセンター(研究所)\n",
       "382783               熊本県産業技術センター(ものづくり室、材料・地域資源室、食品加工室)\n",
       "382760                               独立行政法人国立国際医療研究センター\n",
       "382868                                    奈良県農業研究開発センター\n",
       "382761                             国立研究開発法人国立国際医療研究センター\n",
       "382699                             国際連合大学サステイナビリティ高等研究所\n",
       "\n",
       "[2618 rows x 1 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.set_index('institution_niicode')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ローカルのmariaDBに関する設定ファイルを読み込み（config.iniはgitに上げていないが、事前準備で作られているはず）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import configparser\n",
    "\n",
    "config = configparser.ConfigParser()\n",
    "config.read('config.ini')\n",
    "username = config['mariadb']['username']\n",
    "password = config['mariadb']['password']\n",
    "database = config['mariadb']['database']\n",
    "url = 'mysql+pymysql://' + username + ':' + password + '@localhost:3306/' + database + '?charset=utf8'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "データベースにテーブル構造とデータの中身を書き込む"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-05-09 11:52:23,385 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'\n",
      "2018-05-09 11:52:23,386 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,387 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()\n",
      "2018-05-09 11:52:23,387 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,388 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'\n",
      "2018-05-09 11:52:23,389 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,390 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1\n",
      "2018-05-09 11:52:23,390 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,391 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1\n",
      "2018-05-09 11:52:23,392 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,392 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1\n",
      "2018-05-09 11:52:23,393 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,394 INFO sqlalchemy.engine.base.Engine DESCRIBE `kaken_master_institution`\n",
      "2018-05-09 11:52:23,394 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,409 INFO sqlalchemy.engine.base.Engine DESCRIBE `kaken_master_institution`\n",
      "2018-05-09 11:52:23,410 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,424 INFO sqlalchemy.engine.base.Engine SHOW FULL TABLES FROM `data_analysis`\n",
      "2018-05-09 11:52:23,425 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,439 INFO sqlalchemy.engine.base.Engine SHOW CREATE TABLE `kaken_master_institution`\n",
      "2018-05-09 11:52:23,439 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,441 INFO sqlalchemy.engine.base.Engine \n",
      "DROP TABLE kaken_master_institution\n",
      "2018-05-09 11:52:23,442 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,455 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2018-05-09 11:52:23,456 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE kaken_master_institution (\n",
      "\tinstitution_niicode INTEGER, \n",
      "\tinstitution_name VARCHAR(256)\n",
      ")\n",
      "\n",
      "\n",
      "2018-05-09 11:52:23,456 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,475 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2018-05-09 11:52:23,476 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_kaken_master_institution_institution_niicode ON kaken_master_institution (institution_niicode)\n",
      "2018-05-09 11:52:23,476 INFO sqlalchemy.engine.base.Engine {}\n",
      "2018-05-09 11:52:23,485 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2018-05-09 11:52:23,487 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2018-05-09 11:52:23,495 INFO sqlalchemy.engine.base.Engine INSERT INTO kaken_master_institution (institution_niicode, institution_name) VALUES (%(institution_niicode)s, %(institution_name)s)\n",
      "2018-05-09 11:52:23,496 INFO sqlalchemy.engine.base.Engine ({'institution_niicode': 10101, 'institution_name': '北海道大学'}, {'institution_niicode': 10102, 'institution_name': '北海道教育大学'}, {'institution_niicode': 10103, 'institution_name': '室蘭工業大学'}, {'institution_niicode': 10104, 'institution_name': '小樽商科大学'}, {'institution_niicode': 10105, 'institution_name': '帯広畜産大学'}, {'institution_niicode': 10106, 'institution_name': '北見工業大学'}, {'institution_niicode': 10107, 'institution_name': '旭川医科大学'}, {'institution_niicode': 11101, 'institution_name': '弘前大学'}  ... displaying 10 of 2618 total bound parameter sets ...  {'institution_niicode': 382761, 'institution_name': '国立研究開発法人国立国際医療研究センター'}, {'institution_niicode': 382699, 'institution_name': '国際連合大学サステイナビリティ高等研究所'})\n",
      "2018-05-09 11:52:23,524 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.types import String, Integer\n",
    "\n",
    "engine = create_engine(url, echo=True)\n",
    "\n",
    "df.to_sql('kaken_master_institution', engine, if_exists='replace',\n",
    "          dtype={\n",
    "              'institution_niicode': Integer,\n",
    "              'institution_name': String(256),\n",
    "                })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### おしまい\n",
    "データがコミットされていれば終了。HeidiSQLなどで、上記ドロップ済みデータフレームの件数が登録されているか確認する。"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}