1_Census_Data_Download.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 政府統計データを使った機械学習分析\n",
    "\n",
    "国勢調査等の政府統計データを用いて市区町村を分類し、GeoPandasとPlotly Expressを使った視覚化を試みる。<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part1 Data Downloading\n",
    "\n",
    "【参照資料】\n",
    "e-Statsからのデータダウンロード：<br>\n",
    "https://note.nkmk.me/python-e-stat-api-download/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ライブラリのインポート"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "import json\n",
    "import time\n",
    "import copy\n",
    "import urllib.parse\n",
    "import urllib.request"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 利用可能データのリストを作成\n",
    "政府統計の総合窓口(e-Stat: https://www.e-stat.go.jp/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_list(param, form='json'):\n",
    "    ''' 作成されたAPIリクエストの送信とレスポンスの処理 '''\n",
    "    \n",
    "    # Create URL to get information\n",
    "    url = 'http://api.e-stat.go.jp/rest/3.0/app/'\n",
    "    if form == 'xml':\n",
    "        url += 'getStatsList?'\n",
    "    else:  # json\n",
    "        url += 'json/getStatsList?'\n",
    "    \n",
    "    url += urllib.parse.urlencode(param)\n",
    "    with urllib.request.urlopen(url) as response:\n",
    "        return response.read().decode()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_list_table_inf(param):\n",
    "    d = json.loads(get_list(param, 'json'))\n",
    "    return d['GET_STATS_LIST']['DATALIST_INF']['TABLE_INF']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_list(param, dir_path='.', filename='stats_list', form='csv',\n",
    "              replace=True, sep='_', atmark='', dollar='val', **kwargs):\n",
    "    \n",
    "    ''' フォーマットの変換とリストの保存 '''\n",
    "\n",
    "    path = os.path.join(dir_path, filename + '.' + form)\n",
    "    if form == 'csv':\n",
    "        # Load and decode json\n",
    "        l = get_list_table_inf(param)\n",
    "        if replace:\n",
    "            # Convert json to pandas.DataFrame            \n",
    "            df = pd.io.json.json_normalize(l, sep=sep)\n",
    "            # Replace some characters so that query method can be used.            \n",
    "            df.columns = [s.replace('@', atmark).replace('$', dollar) for s in df.columns]\n",
    "            # Save as a csv file            \n",
    "            df.to_csv(path, index=False)\n",
    "        else:\n",
    "            # Convert json to pandas.DataFrame            \n",
    "            pd.io.json.json_normalize(l).to_csv(path, index=False)\n",
    "    elif form == 'json':\n",
    "        # Load and decoding json        \n",
    "        d = json.loads(get_list(param, 'json'))\n",
    "        with open(path, 'w') as f:\n",
    "            # Convert into json strings            \n",
    "            json.dump(d, f, **kwargs)\n",
    "    else:  # xml\n",
    "        with open(path, 'w') as f:\n",
    "            # Load and save as a xml file            \n",
    "            f.write(get_list(param, form))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "APIリクエストに必要なID番号をjsonファイルとして、予め保存。e-statsのサイトから無料で申請可能。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('setting/app_id.json') as f:\n",
    "    p_id = json.load(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "利用可能統計データのリストをダウンロードし、csvファイルとして保存。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_list(p_id, 'download', 'all_stats_list')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "作成されたリストから、市区町村単位で集計されたデータのみをフィルターにかけ、データ名を修正して新たなcsvファイルとして保存。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "stats_df = pd.read_csv('download/all_stats.csv')\n",
    "Field_List = ['id','MAIN_CATEGORY_val','STATISTICS_NAME','SURVEY_DATE','TITLE','TITLE_val','OVERALL_TOTAL_NUMBER']\n",
    "stats_df_city = stats_df[stats_df.COLLECT_AREA == '市区町村'][Field_List]\n",
    "is_title_null = pd.isnull(stats_df_city.TITLE)\n",
    "stats_df_city.loc[is_title_null,'TITLE'] = stats_df_city.loc[is_title_null,'TITLE_val']\n",
    "stats_df_city = stats_df_city.drop(['TITLE_val'], axis=1)\n",
    "stats_df_city.loc[:,'id'] = stats_df_city['id'].apply(str).str.rjust(10,'0')\n",
    "stats_df_city.to_csv('download/all_stats_city.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 市区町村データの一部をダウンロード"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_data(param, form, path):\n",
    "    '''\n",
    "    APIリクエストの作成とレスポンスの処理。\n",
    "    10万件のレコード数が一度にダウンロードできる上限のため、それ以上のレコード数を持つデータの場合は、\n",
    "    複数回に分けてダウンロードし、appendする必要がある。\n",
    "    '''\n",
    "    \n",
    "    # Create URL to get information\n",
    "    url = 'http://api.e-stat.go.jp/rest/3.0/app/'\n",
    "    url += 'getSimpleStatsData?'\n",
    "    \n",
    "    # Open URL and get data or list.    \n",
    "    url_prm = url + urllib.parse.urlencode(param)\n",
    "    with urllib.request.urlopen(url_prm) as response:\n",
    "        data = response.read().decode()\n",
    "    \n",
    "    string = re.split(',|\\n',data.replace('\"',''))[0:100]\n",
    "    total_number = int(string[string.index('TOTAL_NUMBER') + 1])\n",
    "    num_limit = int(1e5)\n",
    "    if total_number < num_limit:\n",
    "        with open(path, 'w') as f:\n",
    "            f.write(data)    \n",
    "    else:\n",
    "        param['sectionHeaderFlg'] = 2\n",
    "        for i in range(int(total_number // num_limit)):\n",
    "            string = re.split(',|\\n',data.replace('\"',''))[0:100]\n",
    "            param['startPosition'] = (i + 1)*num_limit + 1\n",
    "            url_prm = url + urllib.parse.urlencode(param)\n",
    "            with urllib.request.urlopen(url_prm) as response:\n",
    "                data += response.read().decode()\n",
    "        with open(path, 'w') as f:\n",
    "            f.write(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_data(param, stats_data_id=None, dir_path='.', filename=None,\n",
    "              form='csv', section_header=False, skip=True, **kwargs):\n",
    "    \n",
    "    p = param.copy()\n",
    "    if stats_data_id:\n",
    "        p['statsDataId'] = stats_data_id\n",
    "\n",
    "    if not filename:\n",
    "        filename = p['statsDataId']\n",
    "\n",
    "    path = os.path.join(dir_path, filename + '.' + form)\n",
    "\n",
    "    if section_header:\n",
    "        p['sectionHeaderFlg'] = 1\n",
    "    else:\n",
    "        p['sectionHeaderFlg'] = 2\n",
    "\n",
    "    if os.path.exists(path) & skip:\n",
    "        print('skip {} ({} already exists)'.format(p['statsDataId'], path))\n",
    "    else:\n",
    "        print('download {} to {}'.format(p['statsDataId'], path))\n",
    "        get_data(p, 'csv', path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_data_multi(param, ids, dir_path='.', filename_words=None, sep='_', form='csv',\n",
    "                    section_header=False, skip=True, interval_time_sec=1, **kwargs):\n",
    "\n",
    "    if filename_words:\n",
    "        for i, *words in zip(ids, *filename_words):\n",
    "            time.sleep(interval_time_sec)\n",
    "            save_data(param, i, dir_path, sep.join([str(word) for word in words]),\n",
    "                      form, section_header, skip, **kwargs)\n",
    "    else:\n",
    "        for i in ids:\n",
    "            time.sleep(interval_time_sec)\n",
    "            save_data(param, i, dir_path, i, form, section_header, skip, **kwargs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "以下の条件に該当するデータをダウンロード。<br>\n",
    "１．市区町村毎のデータ分析を目的とするため、県あるいは地域単位のデータ以外。差し当たり、レコード数でフィルターをかける。<br>\n",
    "２．古い版のデータを避けるため、リストを見て調査年が2009年度と2013年度のデータを除外。<br>\n",
    "３．農林水作業データは扱い方も取り敢えず無視。<br>\n",
    "\n",
    "時間が非常にかかるため、中身を見ながら使えそうなものだけ拾うのがいいかも知れません。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stats_df = pd.read_csv('download/all_stats_city.csv')\n",
    "MinRecords = 4e4\n",
    "MaxRecords = 1e7\n",
    "stats_df_city = stats_df_city.loc[(stats_df_city.MAIN_CATEGORY_val != '農林水産業') &\n",
    "                                 (stats_df_city['SURVEY_DATE'].str.contains('2009') == False) &\n",
    "                                 (stats_df_city['SURVEY_DATE'].str.contains('2013') == False) & \n",
    "                                 (stats_df_city['OVERALL_TOTAL_NUMBER'] >= MinRecords) & \n",
    "                                 (stats_df_city['OVERALL_TOTAL_NUMBER'] <= MaxRecords)]\n",
    "\n",
    "id_list = stats_df_city['id'].values.tolist()\n",
    "dir_path = 'download/stats_city'\n",
    "save_data_multi(p_id, id_list, dir_path, section_header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### データのスクリーニング"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_stats_csv(path_input,file):\n",
    "    ''' ヘッダーの行数を探した後に、csvファイルを読み込む。 '''\n",
    "    path_join = os.path.join(path_input,file)\n",
    "    with open(path_join,\"r\") as file_text:\n",
    "        skiprows = file_text.read(2000).replace('\"','').split('\\n').index('VALUE') + 1\n",
    "\n",
    "    df = pd.read_csv(path_join, encoding=\"shift-jis\", skiprows=skiprows)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_by_num_records(input_path,num_min_rec=1800):\n",
    "    ''' レコード数を元に使えそうなファイルを抽出し、リストに保存。 '''\n",
    "    list_error = []\n",
    "    list_code = []\n",
    "    list_title = []\n",
    "    list_filename = []\n",
    "    list_area_code_num = []\n",
    "    for file in os.listdir(input_path):\n",
    "        try:\n",
    "            df = read_stats_csv(input_path,file)\n",
    "            num_area_code = len(df.area_code.unique())\n",
    "            list_area_code_num.append(num_area_code)\n",
    "            list_filename.append(file)\n",
    "            if num_area_code > num_min_rec:\n",
    "                flag_code = stats_df_city['id'].apply(int) == int(file.split('.csv')[0])\n",
    "                list_title.append(stats_df_city.loc[flag_code,:].TITLE.values[0])\n",
    "                list_code.append(file)\n",
    "        except:\n",
    "            list_error.append(file)\n",
    "            pass;\n",
    "    return list_code, list_title, list_area_code_num, list_filename, list_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "dir_path = 'download/stats_city'\n",
    "list_code,list_title,_,_,_ = filter_by_num_records(dir_path)\n",
    "dict_use = dict(zip(list_code,list_title))\n",
    "df_field_use = pd.DataFrame.from_dict(dict_use, orient='index')\n",
    "df_field_use.to_csv('./df_field_use.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}