hyougo_json.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "hyougo_json.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/imabari/hyougo/blob/master/hyougo_json.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "l9PRH1px56P3",
        "colab_type": "code",
        "outputId": "7a8e9b51-ed10-487a-9ae4-6bafa428569b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "!pip install jsonschema"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: jsonschema in /usr/local/lib/python3.6/dist-packages (2.6.0)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9JQQ0bZHBL33",
        "colab_type": "text"
      },
      "source": [
        "# ダウンロード"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ODvuvDOVA-sg",
        "colab_type": "code",
        "outputId": "f86390ab-58f8-40f4-b3ca-d31ce52e1549",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "source": [
        "!wget --tries=3 https://web.pref.hyogo.lg.jp/kk03/documents/pcr.xlsx -O pcr.xlsx"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-03-30 14:22:13--  https://web.pref.hyogo.lg.jp/kk03/documents/pcr.xlsx\n",
            "Resolving web.pref.hyogo.lg.jp (web.pref.hyogo.lg.jp)... 210.130.236.38\n",
            "Connecting to web.pref.hyogo.lg.jp (web.pref.hyogo.lg.jp)|210.130.236.38|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 10090 (9.9K) [application/vnd.openxmlformats-officedocument.spreadsheetml.sheet]\n",
            "Saving to: ‘pcr.xlsx’\n",
            "\n",
            "pcr.xlsx            100%[===================>]   9.85K  --.-KB/s    in 0s      \n",
            "\n",
            "2020-03-30 14:22:14 (126 MB/s) - ‘pcr.xlsx’ saved [10090/10090]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FrlAcxvhA_F-",
        "colab_type": "code",
        "outputId": "60d9503d-29af-456c-a4bb-ed1a6141726f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "source": [
        "!wget --tries=3 https://web.pref.hyogo.lg.jp/kk03/documents/yousei.xlsx -O yousei.xlsx"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-03-30 14:22:15--  https://web.pref.hyogo.lg.jp/kk03/documents/yousei.xlsx\n",
            "Resolving web.pref.hyogo.lg.jp (web.pref.hyogo.lg.jp)... 210.130.236.38\n",
            "Connecting to web.pref.hyogo.lg.jp (web.pref.hyogo.lg.jp)|210.130.236.38|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 11793 (12K) [application/vnd.openxmlformats-officedocument.spreadsheetml.sheet]\n",
            "Saving to: ‘yousei.xlsx’\n",
            "\n",
            "yousei.xlsx         100%[===================>]  11.52K  --.-KB/s    in 0s      \n",
            "\n",
            "2020-03-30 14:22:16 (106 MB/s) - ‘yousei.xlsx’ saved [11793/11793]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GEm7dmpSA_Ix",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#!wget --tries=3 https://web.pref.hyogo.lg.jp/kk03/documents/$(date +%m%d --date '1 day ago')coronakanjya.xlsx"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NhHHMpUbGIFp",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import requests\n",
        "from bs4 import BeautifulSoup\n",
        "\n",
        "from urllib.parse import urljoin"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TV9G4gNfGIKp",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "url = \"https://web.pref.hyogo.lg.jp/kk03/corona_kanjyajyokyo.html\"\n",
        "\n",
        "headers = {\n",
        "    \"User-Agent\": \"Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko\"\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-SPH14ZeGIOv",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "r = requests.get(url, headers=headers)\n",
        "\n",
        "r.raise_for_status()\n",
        "\n",
        "soup = BeautifulSoup(r.content, \"html.parser\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qe3TuPuKGISO",
        "colab_type": "code",
        "outputId": "ddc218dd-aa36-4e2d-c4c5-f591a28b13d0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "tag = soup.select_one(\"a.icon_excel\")\n",
        "\n",
        "s = tag.get_text(strip=True)\n",
        "\n",
        "link = urljoin(url, tag.get(\"href\"))\n",
        "\n",
        "link"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'https://web.pref.hyogo.lg.jp/kk03/documents/0329corona_kanjya.xlsx'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "StrHpo63GIe3",
        "colab_type": "code",
        "outputId": "0e8fe030-38cb-4962-96ac-1b8318d3f8bf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "source": [
        "!wget --tries=3 $link -O kanjya.xlsx"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-03-30 14:22:18--  https://web.pref.hyogo.lg.jp/kk03/documents/0329corona_kanjya.xlsx\n",
            "Resolving web.pref.hyogo.lg.jp (web.pref.hyogo.lg.jp)... 210.130.236.38\n",
            "Connecting to web.pref.hyogo.lg.jp (web.pref.hyogo.lg.jp)|210.130.236.38|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 32514 (32K) [application/vnd.openxmlformats-officedocument.spreadsheetml.sheet]\n",
            "Saving to: ‘kanjya.xlsx’\n",
            "\n",
            "kanjya.xlsx         100%[===================>]  31.75K   193KB/s    in 0.2s    \n",
            "\n",
            "2020-03-30 14:22:19 (193 KB/s) - ‘kanjya.xlsx’ saved [32514/32514]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qhcpLl5vDICg",
        "colab_type": "text"
      },
      "source": [
        "# SCHEMA"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iT0ZZYuJDPwh",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "INSPECTIONS_SUMMARY_SCHEMA = {\n",
        "    \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n",
        "    \"type\": \"object\",\n",
        "    \"additionalProperties\": False,\n",
        "    \"properties\": {\n",
        "        \"data\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": {\n",
        "                \"type\": \"array\",\n",
        "                \"items\": {\"default\": 0, \"type\": \"integer\"},\n",
        "            },\n",
        "        },\n",
        "        \"labels\": {\n",
        "            \"type\": \"array\",\n",
        "            \"items\": {\"pattern\": r\"^[0-9]{2}/[0-9]{2}$\", \"type\": \"string\"},\n",
        "        },\n",
        "        \"last_update\": {\n",
        "            \"pattern\": r\"^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$\",\n",
        "            \"type\": \"string\",\n",
        "        },\n",
        "    },\n",
        "    \"required\": [\"data\", \"labels\", \"last_update\"],\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BgOCH2VhDPzr",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "PATIENTS_SUMMARY_SCHEMA = {\n",
        "    \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n",
        "    \"type\": \"object\",\n",
        "    \"additionalProperties\": False,\n",
        "    \"properties\": {\n",
        "        \"data\": {\n",
        "            \"type\": \"array\",\n",
        "            \"items\": {\n",
        "                \"type\": \"object\",\n",
        "                \"additionalProperties\": False,\n",
        "                \"properties\": {\n",
        "                    \"日付\": {\"type\": \"string\", \"format\": \"date\"},\n",
        "                    \"小計\": {\"default\": 0, \"type\": \"integer\"},\n",
        "                },\n",
        "                \"required\": [\"小計\", \"日付\"],\n",
        "            },\n",
        "        },\n",
        "        \"last_update\": {\n",
        "            \"pattern\": r\"^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$\",\n",
        "            \"type\": \"string\",\n",
        "        },\n",
        "    },\n",
        "    \"required\": [\"data\", \"last_update\"],\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Xoc8NSP4DP2t",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "AGE_SCHEMA = {\n",
        "    \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n",
        "    \"type\": \"object\",\n",
        "    \"properties\": {\n",
        "        \"data\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": {\n",
        "                \"default\": 0,\n",
        "                \"type\": \"integer\"\n",
        "            }\n",
        "        },\n",
        "        \"last_update\": {\n",
        "            \"pattern\": r\"^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$\",\n",
        "            \"type\": \"string\"\n",
        "        }\n",
        "    },\n",
        "    \"required\": [\n",
        "        \"data\",\n",
        "        \"last_update\"\n",
        "    ],\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TlJpnZuUDP5k",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "AGE_SUMMARY_SCHEMA = {\n",
        "    \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n",
        "    \"type\": \"object\",\n",
        "    \"additionalProperties\": False,\n",
        "    \"properties\": {\n",
        "        \"data\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": {\n",
        "                \"type\": \"array\",\n",
        "                \"items\": {\"default\": 0, \"type\": \"integer\"},\n",
        "            },\n",
        "        },\n",
        "        \"labels\": {\n",
        "            \"type\": \"array\",\n",
        "            \"items\": {\"pattern\": \"^[0-9]{2}/[0-9]{2}$\", \"type\": \"string\"},\n",
        "        },\n",
        "        \"last_update\": {\n",
        "            \"pattern\": r\"^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$\",\n",
        "            \"type\": \"string\",\n",
        "        },\n",
        "    },\n",
        "    \"required\": [\"data\", \"labels\", \"last_update\"],\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_b3I-Z95DP8s",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "PATIENTS_SUMMARY_SCHEMA = {\n",
        "    \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n",
        "    \"type\": \"object\",\n",
        "    \"additionalProperties\": False,\n",
        "    \"properties\": {\n",
        "        \"data\": {\n",
        "            \"type\": \"array\",\n",
        "            \"items\": {\n",
        "                \"type\": \"object\",\n",
        "                \"additionalProperties\": False,\n",
        "                \"properties\": {\n",
        "                    \"日付\": {\"type\": \"string\", \"format\": \"date\"},\n",
        "                    \"小計\": {\"default\": 0, \"type\": \"integer\"},\n",
        "                },\n",
        "                \"required\": [\"小計\", \"日付\"],\n",
        "            },\n",
        "        },\n",
        "        \"last_update\": {\n",
        "            \"pattern\": r\"^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$\",\n",
        "            \"type\": \"string\",\n",
        "        },\n",
        "    },\n",
        "    \"required\": [\"data\", \"last_update\"],\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hBEq8Iv4DP_2",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "PATIENTS_SCHEMA = {\n",
        "    \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n",
        "    \"$ref\": \"#/definitions/Main\",\n",
        "    \"definitions\": {\n",
        "        \"Main\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": False,\n",
        "            \"properties\": {\n",
        "                \"data\": {\"type\": \"array\", \"items\": {\"$ref\": \"#/definitions/Datum\"}},\n",
        "                \"last_update\": {\"type\": \"string\"},\n",
        "            },\n",
        "            \"required\": [\"data\", \"last_update\"],\n",
        "            \"title\": \"Main\",\n",
        "        },\n",
        "        \"Datum\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": False,\n",
        "            \"properties\": {\n",
        "                \"No\": {\"type\": \"integer\"},\n",
        "                \"居住地\": {\"type\": \"string\"},\n",
        "                \"年代\": {\"$ref\": \"#/definitions/Age\"},\n",
        "                \"性別\": {\"$ref\": \"#/definitions/Sex\"},\n",
        "                \"備考\": {\"type\": \"string\"},\n",
        "                \"退院\": {\"type\": \"null\"},\n",
        "                \"date\": {\"type\": \"string\", \"format\": \"date\"},\n",
        "                \"リリース日\": {\"type\": \"string\", \"format\": \"date\"},\n",
        "                \"曜日\": {\"$ref\": \"#/definitions/Week\"},\n",
        "            },\n",
        "            \"required\": [\"date\", \"リリース日\", \"備考\", \"居住地\", \"年代\", \"性別\", \"曜日\", \"No\", \"退院\"],\n",
        "            \"title\": \"Datum\",\n",
        "        },\n",
        "        \"Age\": {\n",
        "            \"type\": \"string\",\n",
        "            \"enum\": [\n",
        "                \"0代\",\n",
        "                \"10代\",\n",
        "                \"20代\",\n",
        "                \"30代\",\n",
        "                \"40代\",\n",
        "                \"50代\",\n",
        "                \"60代\",\n",
        "                \"70代\",\n",
        "                \"80代\",\n",
        "                \"90代\",\n",
        "            ],\n",
        "            \"title\": \"Age\",\n",
        "        },\n",
        "        \"Sex\": {\"type\": \"string\", \"enum\": [\"男性\", \"女性\"], \"title\": \"Sex\"},\n",
        "        \"Week\": {\n",
        "            \"type\": \"string\",\n",
        "            \"enum\": [\"月\", \"火\", \"水\", \"木\", \"金\", \"土\", \"日\"],\n",
        "            \"title\": \"Week\",\n",
        "        },\n",
        "    },\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "--P3YaJDDQDE",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "CLUSTERS_SUMMARY_SCHEMA = {\n",
        "    \"$schema\": \"http://json-schema.org/draft-07/schema#\",\n",
        "    \"type\": \"object\",\n",
        "    \"additionalProperties\": False,\n",
        "    \"properties\": {\n",
        "        \"data\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": {\"default\": 0, \"type\": \"integer\"},\n",
        "        },\n",
        "        \"last_update\": {\n",
        "            \"pattern\": r\"^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$\",\n",
        "            \"type\": \"string\",\n",
        "        },\n",
        "    },\n",
        "    \"required\": [\"data\", \"last_update\"],\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8mEE9K1mC20P",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "MAIN_SUMMARY_SCHEMA = {\n",
        "    \"$schema\": \"http://json-schema.org/draft-06/schema#\",\n",
        "    \"$ref\": \"#/definitions/Main\",\n",
        "    \"definitions\": {\n",
        "        \"Main\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": False,\n",
        "            \"properties\": {\n",
        "                \"attr\": {\"type\": \"string\"},\n",
        "                \"value\": {\"type\": \"integer\", \"default\": 0},\n",
        "                \"children\": {\n",
        "                    \"type\": \"array\",\n",
        "                    \"items\": {\"$ref\": \"#/definitions/Inspections\"},\n",
        "                },\n",
        "                \"last_update\": {\n",
        "                    \"pattern\": \"^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$\",\n",
        "                    \"type\": \"string\",\n",
        "                },\n",
        "            },\n",
        "            \"required\": [\"attr\", \"children\", \"last_update\", \"value\"],\n",
        "            \"title\": \"Main\",\n",
        "        },\n",
        "        \"Inspections\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": False,\n",
        "            \"properties\": {\n",
        "                \"attr\": {\"type\": \"string\"},\n",
        "                \"value\": {\"type\": \"integer\", \"default\": 0},\n",
        "                \"children\": {\n",
        "                    \"type\": \"array\",\n",
        "                    \"items\": {\"$ref\": \"#/definitions/Patients\"},\n",
        "                },\n",
        "            },\n",
        "            \"required\": [\"attr\", \"children\", \"value\"],\n",
        "            \"title\": \"Inspections\",\n",
        "        },\n",
        "        \"Patients\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": False,\n",
        "            \"properties\": {\n",
        "                \"attr\": {\"type\": \"string\"},\n",
        "                \"value\": {\"type\": \"integer\", \"default\": 0},\n",
        "                \"children\": {\n",
        "                    \"type\": \"array\",\n",
        "                    \"items\": {\"$ref\": \"#/definitions/Symptoms\"},\n",
        "                },\n",
        "            },\n",
        "            \"required\": [\"attr\", \"value\"],\n",
        "            \"title\": \"Patients\",\n",
        "        },\n",
        "        \"Symptoms\": {\n",
        "            \"type\": \"object\",\n",
        "            \"additionalProperties\": False,\n",
        "            \"properties\": {\n",
        "                \"attr\": {\"type\": \"string\"},\n",
        "                \"value\": {\"type\": \"integer\", \"default\": 0},\n",
        "            },\n",
        "            \"required\": [\"attr\", \"value\"],\n",
        "            \"title\": \"Symptoms\",\n",
        "        },\n",
        "    },\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "O7HqGFL7BbdW",
        "colab_type": "text"
      },
      "source": [
        "# データラングリング"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pD01Cc3nA_Lz",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import datetime\n",
        "import json\n",
        "import re\n",
        "from urllib.parse import urljoin\n",
        "\n",
        "import jsonschema\n",
        "import pandas as pd"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "E02siZKr5663",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def dumps_json(file_name, json_data):\n",
        "\n",
        "    with open(file_name, \"w\") as fw:\n",
        "        json.dump(json_data, fw, ensure_ascii=False, indent=4)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UDR_y1oO57D8",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def json_check(fn, d):\n",
        "\n",
        "    r = requests.get(\n",
        "        \"https://raw.githubusercontent.com/stop-covid19-hyogo/covid19/development/data/\"\n",
        "        + fn\n",
        "    )\n",
        "\n",
        "    result = d == r.json()\n",
        "\n",
        "    if not result:\n",
        "\n",
        "        print(fn)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bU2P5-qEBz0-",
        "colab_type": "code",
        "outputId": "9496ee53-9836-4714-ffc5-00ad92418e32",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "# 最終更新日\n",
        "last_update = datetime.date.today()\n",
        "last_update"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "datetime.date(2020, 3, 30)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_1pbVpuxCDXm",
        "colab_type": "text"
      },
      "source": [
        "## pcr.xlsx"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "E3FQSlBaBz4N",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df_pcr = pd.read_excel(\"pcr.xlsx\", index_col=\"年月日\").fillna(0).astype(int)\n",
        "\n",
        "df_pcr[\"日付\"] = df_pcr.index.strftime(\"%Y-%m-%d\")\n",
        "\n",
        "df_pcr.rename(columns={\"PCR検査件数\": \"検査検体数\", \"陽性件数\": \"陽性確認\"}, inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BKjuTqsaBz6_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# inspections_summary\n",
        "\n",
        "df_insp = df_pcr.loc[:, [\"検査検体数\", \"陽性確認\"]].copy()\n",
        "\n",
        "labels = df_insp.index.strftime(\"%m/%d\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Bat9wxoSCfWf",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "inspections_summary = {\n",
        "    \"data\": df_insp.to_dict(orient=\"list\"),\n",
        "    \"labels\": labels.tolist(),\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rYnfrPiYCVOb",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "jsonschema.validate(inspections_summary, INSPECTIONS_SUMMARY_SCHEMA)\n",
        "\n",
        "dumps_json(\"inspections_summary.json\", inspections_summary)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "r1MYAu9yBz97",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# parent_summary\n",
        "\n",
        "df_pts = df_pcr.loc[:, [\"日付\", \"陽性確認\"]].copy()\n",
        "\n",
        "df_pts.rename(columns={\"陽性確認\": \"小計\"}, inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IpH_cQcECj24",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "patients_summary = {\n",
        "    \"data\": df_pts.to_dict(orient=\"records\"),\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nkGEW1SaCj83",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "jsonschema.validate(patients_summary, PATIENTS_SUMMARY_SCHEMA)\n",
        "\n",
        "dumps_json(\"patients_summary.json\", patients_summary)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EXKm4d_sDzrI",
        "colab_type": "text"
      },
      "source": [
        "## kanjya.xlsx"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qxpb3vYZCkAP",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df_tmp = pd.read_excel(\"kanjya.xlsx\", skiprows=3)\n",
        "\n",
        "df_kanja = df_tmp[df_tmp[\"番号\"].notnull()].copy()\n",
        "\n",
        "df_kanja.dropna(how=\"all\", axis=1, inplace=True)\n",
        "\n",
        "df_kanja.columns = df_kanja.columns.map(lambda s: s.replace(\"\\n\", \"\"))\n",
        "\n",
        "df_kanja[\"番号\"] = df_kanja[\"番号\"].astype(int)\n",
        "df_kanja[\"年代\"] = df_kanja[\"年代\"].astype(int)\n",
        "\n",
        "df_kanja[\"確認日\"] = df_kanja[\"確認日\"].apply(\n",
        "    lambda date: pd.to_datetime(date, unit=\"D\", origin=pd.Timestamp(\"1899/12/30\"))\n",
        ")\n",
        "\n",
        "df_kanja[\"備考欄\"] = df_kanja[\"備考欄\"].str.replace(\"\\n\", \"\")\n",
        "\n",
        "df_kanja.set_index(\"番号\", inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EOcy78R2Dwv3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df_kanja.to_csv(\"kanja.tsv\", sep=\"\\t\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wgIuFlPBDwy8",
        "colab_type": "code",
        "outputId": "5a871762-54f6-42fb-c135-f15af42a6814",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "# 陽性患者数（累計）\n",
        "\n",
        "len(df_kanja)"
      ],
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "133"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hNahpt-pDw2L",
        "colab_type": "code",
        "outputId": "105d91ed-2283-4531-d31b-942056a8f829",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "source": [
        "# 陽性患者数（日別）\n",
        "\"\"\"\n",
        "df_pts = (\n",
        "    df_kanja[\"確認日\"]\n",
        "    .value_counts()\n",
        "    .sort_index()\n",
        "    .asfreq(\"D\", fill_value=0)\n",
        "    .reset_index()\n",
        ")\n",
        "\n",
        "df_pts[\"日付\"] = df_pts[\"index\"].dt.strftime(\"%Y-%m-%d\")\n",
        "\n",
        "df_pts.rename(columns={\"確認日\": \"小計\"}, inplace=True)\n",
        "\n",
        "df_pts.drop(\"index\", axis=1, inplace=True)\n",
        "\n",
        "df_pts\n",
        "\"\"\""
      ],
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'\\ndf_pts = (\\n    df_kanja[\"確認日\"]\\n    .value_counts()\\n    .sort_index()\\n    .asfreq(\"D\", fill_value=0)\\n    .reset_index()\\n)\\n\\ndf_pts[\"日付\"] = df_pts[\"index\"].dt.strftime(\"%Y-%m-%d\")\\n\\ndf_pts.rename(columns={\"確認日\": \"小計\"}, inplace=True)\\n\\ndf_pts.drop(\"index\", axis=1, inplace=True)\\n\\ndf_pts\\n'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6C55vNCAEU9X",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "0a605837-712a-4bb9-de85-b94d477f95a4"
      },
      "source": [
        "\"\"\"\n",
        "patients_summary = {\n",
        "    \"data\": df_pts.to_dict(orient=\"records\"),\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}\n",
        "\"\"\""
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'\\npatients_summary = {\\n    \"data\": df_pts.to_dict(orient=\"records\"),\\n    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\\n}\\n'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iIkDdxn5Dw5i",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# jsonschema.validate(patients_summary, PATIENTS_SUMMARY_SCHEMA)\n",
        "\n",
        "# dumps_json(\"patients_summary.json\", patients_summary)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "E48gD9ZtDw8Z",
        "colab_type": "code",
        "outputId": "5ae0c2aa-59c8-4f57-8184-3916f79f0438",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 419
        }
      },
      "source": [
        "# 陽性患者情報\n",
        "\n",
        "df_pt = (\n",
        "    df_kanja.loc[:, [\"確認日\", \"居住地\", \"年代\", \"性別\", \"備考欄\"]].sort_index().reset_index()\n",
        ")\n",
        "df_pt.head(10)\n",
        "\n",
        "df_pt[\"退院\"] = None\n",
        "\n",
        "df_pt[\"date\"] = df_pt[\"確認日\"].dt.strftime(\"%Y-%m-%d\")\n",
        "df_pt[\"リリース日\"] = df_pt[\"確認日\"].dt.strftime(\"%Y-%m-%d\")\n",
        "\n",
        "week = [\"月\", \"火\", \"水\", \"木\", \"金\", \"土\", \"日\"]\n",
        "\n",
        "df_pt[\"曜日\"] = df_pt[\"確認日\"].dt.dayofweek.apply(lambda x: week[x])\n",
        "\n",
        "df_pt[\"年代\"] = df_pt[\"年代\"].astype(\"str\") + \"代\"\n",
        "\n",
        "df_pt[\"備考欄\"] = df_pt[\"備考欄\"].str.replace(\"NO.|N0.|NO,|N0,|No,\", \"No.\")\n",
        "df_pt[\"備考欄\"] = df_pt[\"備考欄\"].str.replace(\"・\", \"、\")\n",
        "\n",
        "df_pt.rename(columns={\"番号\": \"No\", \"備考欄\": \"備考\"}, inplace=True)\n",
        "\n",
        "df_pt.drop(\"確認日\", axis=1, inplace=True)\n",
        "\n",
        "df_pt"
      ],
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>No</th>\n",
              "      <th>居住地</th>\n",
              "      <th>年代</th>\n",
              "      <th>性別</th>\n",
              "      <th>備考</th>\n",
              "      <th>退院</th>\n",
              "      <th>date</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>西宮市</td>\n",
              "      <td>40代</td>\n",
              "      <td>男性</td>\n",
              "      <td>特定できず</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-01</td>\n",
              "      <td>2020-03-01</td>\n",
              "      <td>日</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>神戸市</td>\n",
              "      <td>40代</td>\n",
              "      <td>男性</td>\n",
              "      <td>特定できず</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-03</td>\n",
              "      <td>2020-03-03</td>\n",
              "      <td>火</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>神戸市</td>\n",
              "      <td>40代</td>\n",
              "      <td>女性</td>\n",
              "      <td>2/15、16に大阪のライブハウスArcに参加</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-03</td>\n",
              "      <td>2020-03-03</td>\n",
              "      <td>火</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>福崎町</td>\n",
              "      <td>50代</td>\n",
              "      <td>女性</td>\n",
              "      <td>2/15、16に大阪のライブハウスArcに参加</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-05</td>\n",
              "      <td>2020-03-05</td>\n",
              "      <td>木</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>姫路市</td>\n",
              "      <td>40代</td>\n",
              "      <td>男性</td>\n",
              "      <td>2/19に大阪のSoap operaライブに参加</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-06</td>\n",
              "      <td>2020-03-06</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>128</th>\n",
              "      <td>129</td>\n",
              "      <td>神戸市</td>\n",
              "      <td>70代</td>\n",
              "      <td>男性</td>\n",
              "      <td>海外からの帰国者</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>日</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>129</th>\n",
              "      <td>130</td>\n",
              "      <td>神戸市</td>\n",
              "      <td>60代</td>\n",
              "      <td>男性</td>\n",
              "      <td>海外からの帰国者</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>日</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>130</th>\n",
              "      <td>131</td>\n",
              "      <td>神戸市</td>\n",
              "      <td>60代</td>\n",
              "      <td>女性</td>\n",
              "      <td>海外からの帰国者</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>日</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>131</th>\n",
              "      <td>132</td>\n",
              "      <td>姫路市</td>\n",
              "      <td>70代</td>\n",
              "      <td>女性</td>\n",
              "      <td>行動歴調査中</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>日</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>132</th>\n",
              "      <td>133</td>\n",
              "      <td>尼崎市</td>\n",
              "      <td>30代</td>\n",
              "      <td>男性</td>\n",
              "      <td>行動歴調査中</td>\n",
              "      <td>None</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>2020-03-29</td>\n",
              "      <td>日</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>133 rows × 9 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "      No  居住地   年代  性別  ...    退院        date       リリース日 曜日\n",
              "0      1  西宮市  40代  男性  ...  None  2020-03-01  2020-03-01  日\n",
              "1      2  神戸市  40代  男性  ...  None  2020-03-03  2020-03-03  火\n",
              "2      3  神戸市  40代  女性  ...  None  2020-03-03  2020-03-03  火\n",
              "3      4  福崎町  50代  女性  ...  None  2020-03-05  2020-03-05  木\n",
              "4      5  姫路市  40代  男性  ...  None  2020-03-06  2020-03-06  金\n",
              "..   ...  ...  ...  ..  ...   ...         ...         ... ..\n",
              "128  129  神戸市  70代  男性  ...  None  2020-03-29  2020-03-29  日\n",
              "129  130  神戸市  60代  男性  ...  None  2020-03-29  2020-03-29  日\n",
              "130  131  神戸市  60代  女性  ...  None  2020-03-29  2020-03-29  日\n",
              "131  132  姫路市  70代  女性  ...  None  2020-03-29  2020-03-29  日\n",
              "132  133  尼崎市  30代  男性  ...  None  2020-03-29  2020-03-29  日\n",
              "\n",
              "[133 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hsejNrMkEOIo",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "patients = {\n",
        "    \"data\": df_pt.to_dict(orient=\"records\"),\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OCAWwZpVEOLo",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "jsonschema.validate(patients, PATIENTS_SCHEMA)\n",
        "\n",
        "dumps_json(\"patients.json\", patients)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ekQLzuX3EOOh",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# 年代集計\n",
        "\n",
        "age_list = [i for i in range(0, 100, 10)]\n",
        "\n",
        "df_age = df_kanja[\"年代\"].value_counts().sort_index().reindex(age_list, fill_value=0)\n",
        "\n",
        "df_age.index = df_age.index.map(lambda s: str(s) + \"代\")\n",
        "df_age.rename(index={\"0代\": \"10代未満\", \"90代\": \"90代以上\"}, inplace=True)\n",
        "\n",
        "df_age = df_age.astype(int)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1mAAXEhjEORq",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "age = {\n",
        "    \"data\": df_age.to_dict(),\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bWEsrIOKEOUq",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "jsonschema.validate(age, AGE_SCHEMA)\n",
        "\n",
        "dumps_json(\"age.json\", age)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "o4sLD-zdE4Pn",
        "colab_type": "code",
        "outputId": "fa988211-6b25-42f0-b852-27f7ee469317",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 979
        }
      },
      "source": [
        "df_ages = pd.crosstab(df_kanja[\"確認日\"], df_kanja[\"年代\"]).reindex(\n",
        "    age_list, axis=1, fill_value=0\n",
        ")\n",
        "\n",
        "df_ages.columns = df_ages.columns.map(lambda s: str(s) + \"代\")\n",
        "df_ages.rename(columns={\"0代\": \"10代未満\", \"90代\": \"90代以上\"}, inplace=True)\n",
        "\n",
        "df_ages = df_ages.astype(int)\n",
        "\n",
        "df_agesum = df_ages.asfreq(\"D\", fill_value=0)\n",
        "\n",
        "df_agesum"
      ],
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>10代未満</th>\n",
              "      <th>10代</th>\n",
              "      <th>20代</th>\n",
              "      <th>30代</th>\n",
              "      <th>40代</th>\n",
              "      <th>50代</th>\n",
              "      <th>60代</th>\n",
              "      <th>70代</th>\n",
              "      <th>80代</th>\n",
              "      <th>90代以上</th>\n",
              "    </tr>\n",
              "    <tr>\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>2020-03-01</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-02</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-03</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-04</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-05</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-06</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-07</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-08</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-09</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-10</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>3</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-11</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-12</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-13</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-14</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>4</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-15</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>6</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-16</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-17</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-18</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-19</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-20</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-21</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-22</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-23</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-24</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-25</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-26</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-27</th>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-28</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-29</th>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>3</td>\n",
              "      <td>2</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "年代          10代未満  10代  20代  30代  40代  50代  60代  70代  80代  90代以上\n",
              "確認日                                                             \n",
              "2020-03-01      0    0    0    0    1    0    0    0    0      0\n",
              "2020-03-02      0    0    0    0    0    0    0    0    0      0\n",
              "2020-03-03      0    0    0    0    2    0    0    0    0      0\n",
              "2020-03-04      0    0    0    0    0    0    0    0    0      0\n",
              "2020-03-05      0    0    0    0    0    1    0    0    0      0\n",
              "2020-03-06      0    0    0    0    3    1    0    0    0      0\n",
              "2020-03-07      0    0    0    0    1    0    0    0    1      0\n",
              "2020-03-08      0    0    0    0    0    1    0    0    1      0\n",
              "2020-03-09      0    0    0    1    1    1    0    0    1      0\n",
              "2020-03-10      0    0    1    0    2    1    1    3    1      0\n",
              "2020-03-11      0    0    2    1    2    1    2    3    1      0\n",
              "2020-03-12      0    0    0    0    1    3    0    3    1      1\n",
              "2020-03-13      0    0    0    1    2    3    0    2    2      0\n",
              "2020-03-14      0    0    2    0    0    4    1    1    3      0\n",
              "2020-03-15      0    0    0    0    1    2    0    1    6      1\n",
              "2020-03-16      0    0    0    0    1    1    0    1    1      0\n",
              "2020-03-17      0    0    0    0    1    1    0    1    1      0\n",
              "2020-03-18      0    0    0    1    0    1    0    2    2      0\n",
              "2020-03-19      0    0    0    0    0    1    0    0    0      0\n",
              "2020-03-20      0    0    1    0    0    0    2    2    3      0\n",
              "2020-03-21      0    0    0    0    1    0    1    1    2      1\n",
              "2020-03-22      0    0    0    0    1    0    1    2    0      0\n",
              "2020-03-23      0    0    0    1    0    0    0    0    1      0\n",
              "2020-03-24      0    0    1    0    1    0    0    1    2      0\n",
              "2020-03-25      0    0    1    0    0    0    0    0    0      0\n",
              "2020-03-26      0    0    0    0    0    0    0    1    0      0\n",
              "2020-03-27      0    1    0    1    0    1    0    0    0      0\n",
              "2020-03-28      0    0    0    1    1    1    0    0    0      0\n",
              "2020-03-29      0    0    1    1    0    0    3    2    0      0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 41
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-7IURnJTE4Sr",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "labels = df_agesum.index.map(lambda d: d.strftime(\"%m/%d\"))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fxzc9U1VE4Vm",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "age_summary = {\n",
        "    \"data\": df_agesum.to_dict(orient=\"list\"),\n",
        "    \"labels\": labels.tolist(),\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aM6yKRUGE4Ym",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "jsonschema.validate(age_summary, AGE_SUMMARY_SCHEMA)\n",
        "\n",
        "dumps_json(\"age_summary.json\", age_summary)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C6g7-wHoE4cO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# クラスタ概要\n",
        "\n",
        "df_cluster_sum = df_kanja.loc[:, \"認定こども園\":].copy().notnull().sum()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wQ5pZ66YFMzb",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "clusters_summary = {\n",
        "    \"data\": df_cluster_sum.to_dict(),\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kYoLUbXiFM2o",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "jsonschema.validate(clusters_summary, CLUSTERS_SUMMARY_SCHEMA)\n",
        "\n",
        "dumps_json(\"clusters_summary.json\", clusters_summary)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "56_J6al5FM5n",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# クラスタ\n",
        "\n",
        "df_clusters = df_kanja.loc[:, \"認定こども園\":].copy().fillna(0)\n",
        "\n",
        "df_clusters[\"確認日\"] = df_kanja[\"確認日\"]\n",
        "\n",
        "df_clusters.replace(\"〇\", 1, inplace=True)\n",
        "\n",
        "pv_clusters = df_clusters.pivot_table(index=\"確認日\", aggfunc=\"sum\").asfreq(\"D\", fill_value=0)\n",
        "\n",
        "pv_clusters[\"日付\"] = pv_clusters.index.strftime(\"%Y-%m-%d\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uztyhc-JFM8-",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "clusters = {\n",
        "    \"data\": pv_clusters.to_dict(orient=\"recodes\"),\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2jzMYwd0FUmB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dumps_json(\"clusters.json\", clusters)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xKR55Q97Dw_Z",
        "colab_type": "code",
        "outputId": "32d6f2e8-51df-4e02-a64a-0ae083c5013c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "# 重複者\n",
        "\n",
        "(df_kanja.loc[:, \"認定こども園\":].copy().notnull().sum(axis=1) > 1).sum()"
      ],
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "4"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 51
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gug1ffSBFixf",
        "colab_type": "text"
      },
      "source": [
        "## yousei.xlsx"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FgEnLr8pFhWI",
        "colab_type": "code",
        "outputId": "0fdc1fe5-594b-426a-e8c0-c88aad4486f3",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 700
        }
      },
      "source": [
        "df_yousei = pd.read_excel(\"yousei.xlsx\", index_col=\"発表年月日\")\n",
        "\n",
        "df_yousei.columns = df_yousei.columns.map(lambda s: s.replace(\"（累計）\", \"\").strip())\n",
        "\n",
        "df_yousei.index += pd.to_timedelta(\"1 days\")\n",
        "\n",
        "df_yousei.rename(columns={\"中等症以下\": \"軽症・中等症\", \"陽性者数\": \"陽性患者数\"}, inplace=True)\n",
        "\n",
        "df_yousei.drop(\"発表時間\", axis=1, inplace=True)\n",
        "\n",
        "df_yousei"
      ],
      "execution_count": 52,
      "outputs": [
        {
          "output_type": "execute_result",
          "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",
              "    </tr>\n",
              "    <tr>\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>2020-03-11</th>\n",
              "      <td>444</td>\n",
              "      <td>25</td>\n",
              "      <td>24</td>\n",
              "      <td>20</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-12</th>\n",
              "      <td>540</td>\n",
              "      <td>37</td>\n",
              "      <td>35</td>\n",
              "      <td>31</td>\n",
              "      <td>4</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-13</th>\n",
              "      <td>650</td>\n",
              "      <td>46</td>\n",
              "      <td>44</td>\n",
              "      <td>40</td>\n",
              "      <td>4</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-14</th>\n",
              "      <td>744</td>\n",
              "      <td>56</td>\n",
              "      <td>54</td>\n",
              "      <td>50</td>\n",
              "      <td>4</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-15</th>\n",
              "      <td>824</td>\n",
              "      <td>67</td>\n",
              "      <td>64</td>\n",
              "      <td>57</td>\n",
              "      <td>7</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-16</th>\n",
              "      <td>887</td>\n",
              "      <td>78</td>\n",
              "      <td>75</td>\n",
              "      <td>68</td>\n",
              "      <td>7</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-17</th>\n",
              "      <td>951</td>\n",
              "      <td>82</td>\n",
              "      <td>78</td>\n",
              "      <td>72</td>\n",
              "      <td>6</td>\n",
              "      <td>2</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-18</th>\n",
              "      <td>1024</td>\n",
              "      <td>86</td>\n",
              "      <td>81</td>\n",
              "      <td>75</td>\n",
              "      <td>6</td>\n",
              "      <td>2</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-19</th>\n",
              "      <td>1127</td>\n",
              "      <td>91</td>\n",
              "      <td>84</td>\n",
              "      <td>78</td>\n",
              "      <td>6</td>\n",
              "      <td>3</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-20</th>\n",
              "      <td>1310</td>\n",
              "      <td>93</td>\n",
              "      <td>85</td>\n",
              "      <td>80</td>\n",
              "      <td>5</td>\n",
              "      <td>4</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-21</th>\n",
              "      <td>1434</td>\n",
              "      <td>101</td>\n",
              "      <td>88</td>\n",
              "      <td>83</td>\n",
              "      <td>5</td>\n",
              "      <td>4</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-22</th>\n",
              "      <td>1518</td>\n",
              "      <td>107</td>\n",
              "      <td>94</td>\n",
              "      <td>88</td>\n",
              "      <td>6</td>\n",
              "      <td>4</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-23</th>\n",
              "      <td>1607</td>\n",
              "      <td>111</td>\n",
              "      <td>96</td>\n",
              "      <td>91</td>\n",
              "      <td>5</td>\n",
              "      <td>6</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-24</th>\n",
              "      <td>1722</td>\n",
              "      <td>113</td>\n",
              "      <td>92</td>\n",
              "      <td>86</td>\n",
              "      <td>6</td>\n",
              "      <td>6</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-25</th>\n",
              "      <td>1855</td>\n",
              "      <td>118</td>\n",
              "      <td>96</td>\n",
              "      <td>89</td>\n",
              "      <td>7</td>\n",
              "      <td>6</td>\n",
              "      <td>16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-26</th>\n",
              "      <td>2031</td>\n",
              "      <td>119</td>\n",
              "      <td>96</td>\n",
              "      <td>89</td>\n",
              "      <td>7</td>\n",
              "      <td>6</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-27</th>\n",
              "      <td>2121</td>\n",
              "      <td>120</td>\n",
              "      <td>96</td>\n",
              "      <td>88</td>\n",
              "      <td>8</td>\n",
              "      <td>7</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-28</th>\n",
              "      <td>2282</td>\n",
              "      <td>123</td>\n",
              "      <td>97</td>\n",
              "      <td>91</td>\n",
              "      <td>6</td>\n",
              "      <td>9</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-29</th>\n",
              "      <td>2382</td>\n",
              "      <td>126</td>\n",
              "      <td>99</td>\n",
              "      <td>93</td>\n",
              "      <td>6</td>\n",
              "      <td>10</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2020-03-30</th>\n",
              "      <td>2399</td>\n",
              "      <td>133</td>\n",
              "      <td>106</td>\n",
              "      <td>100</td>\n",
              "      <td>6</td>\n",
              "      <td>10</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "            検査実施人数  陽性患者数  入院中  軽症・中等症  重症  死亡  退院\n",
              "発表年月日                                             \n",
              "2020-03-11     444     25   24      20   4   0   1\n",
              "2020-03-12     540     37   35      31   4   1   1\n",
              "2020-03-13     650     46   44      40   4   1   1\n",
              "2020-03-14     744     56   54      50   4   1   1\n",
              "2020-03-15     824     67   64      57   7   1   2\n",
              "2020-03-16     887     78   75      68   7   1   2\n",
              "2020-03-17     951     82   78      72   6   2   2\n",
              "2020-03-18    1024     86   81      75   6   2   3\n",
              "2020-03-19    1127     91   84      78   6   3   4\n",
              "2020-03-20    1310     93   85      80   5   4   4\n",
              "2020-03-21    1434    101   88      83   5   4   9\n",
              "2020-03-22    1518    107   94      88   6   4   9\n",
              "2020-03-23    1607    111   96      91   5   6   9\n",
              "2020-03-24    1722    113   92      86   6   6  15\n",
              "2020-03-25    1855    118   96      89   7   6  16\n",
              "2020-03-26    2031    119   96      89   7   6  17\n",
              "2020-03-27    2121    120   96      88   8   7  17\n",
              "2020-03-28    2282    123   97      91   6   9  17\n",
              "2020-03-29    2382    126   99      93   6  10  17\n",
              "2020-03-30    2399    133  106     100   6  10  17"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xlI_55c5FiM_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "d = df_yousei.iloc[-1].to_dict()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YSv_sHnJFoRf",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "main_summary = {\n",
        "    \"attr\": \"検査実施人数\",\n",
        "    \"value\": d[\"検査実施人数\"],\n",
        "    \"children\": [\n",
        "        {\n",
        "            \"attr\": \"陽性患者数\",\n",
        "            \"value\": d[\"陽性患者数\"],\n",
        "            \"children\": [\n",
        "                {\n",
        "                    \"attr\": \"入院中\",\n",
        "                    \"value\": d[\"入院中\"],\n",
        "                    \"children\": [\n",
        "                        {\"attr\": \"軽症・中等症\", \"value\": d[\"軽症・中等症\"]},\n",
        "                        {\"attr\": \"重症\", \"value\": d[\"重症\"]},\n",
        "                    ],\n",
        "                },\n",
        "                {\"attr\": \"死亡\", \"value\": d[\"死亡\"]},\n",
        "                {\"attr\": \"退院\", \"value\": d[\"退院\"]},\n",
        "            ],\n",
        "        }\n",
        "    ],\n",
        "    \"last_update\": last_update.strftime(\"%Y-%m-%d %H:%M\"),\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FTxI8eGt57NM",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "jsonschema.validate(main_summary, MAIN_SUMMARY_SCHEMA)\n",
        "\n",
        "dumps_json(\"main_summary.json\", main_summary)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HfVk5o1rF1a7",
        "colab_type": "text"
      },
      "source": [
        "# チェック"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2MAgx7EfCux3",
        "colab_type": "code",
        "outputId": "7dc5be44-983b-4d91-9f12-9007fe4ce8b4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 51
        }
      },
      "source": [
        "json_check(\"patients_summary.json\", patients_summary)\n",
        "json_check(\"inspections_summary.json\", inspections_summary)"
      ],
      "execution_count": 56,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "patients_summary.json\n",
            "inspections_summary.json\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YGURxqLmCu1H",
        "colab_type": "code",
        "outputId": "f3e590d2-bd11-4f4e-db27-aa0bf0ad4245",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 119
        }
      },
      "source": [
        "json_check(\"patients_summary.json\", patients_summary)\n",
        "json_check(\"age.json\", age)\n",
        "json_check(\"age_summary.json\", age_summary)\n",
        "json_check(\"clusters.json\", clusters)\n",
        "json_check(\"clusters_summary.json\", clusters_summary)\n",
        "json_check(\"patients.json\", patients)"
      ],
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "patients_summary.json\n",
            "age.json\n",
            "age_summary.json\n",
            "clusters.json\n",
            "clusters_summary.json\n",
            "patients.json\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iurioM6cCu4c",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "29492379-e7ea-4624-a553-fe6dcab69781"
      },
      "source": [
        "json_check(\"main_summary.json\", main_summary)"
      ],
      "execution_count": 58,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "main_summary.json\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}