bangkok open data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# [Bangkok open data](https://data.bangkok.go.th/dataset/)\n",
    "\n",
    "---\n",
    "* author:  [Prasert Kanawattanachai](prasert.k@chula.ac.th)\n",
    "* YouTube: https://www.youtube.com/prasertcbs\n",
    "* github: https://github.com/prasertcbs/\n",
    "* kaggle: https://www.kaggle.com/prasertk/\n",
    "* [Chulalongkorn Business School](https://www.cbs.chula.ac.th/en/)\n",
    "---\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>station_area</th>\n",
       "      <th>pm10_min</th>\n",
       "      <th>pm10_max</th>\n",
       "      <th>pm10_overstd</th>\n",
       "      <th>pm10_count</th>\n",
       "      <th>no2_min</th>\n",
       "      <th>no2_max</th>\n",
       "      <th>no2_overstd</th>\n",
       "      <th>no2_count</th>\n",
       "      <th>...</th>\n",
       "      <th>o38h_min</th>\n",
       "      <th>o38h_max</th>\n",
       "      <th>o38h_overstd</th>\n",
       "      <th>o38h_count</th>\n",
       "      <th>pm2_5_min</th>\n",
       "      <th>pm2_5_max</th>\n",
       "      <th>pm2_5_overstd</th>\n",
       "      <th>pm2_5_count</th>\n",
       "      <th>adddate</th>\n",
       "      <th>updatedate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>ค่ามาตรฐาน</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>120</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>170</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>70</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>50</td>\n",
       "      <td>-</td>\n",
       "      <td>2020-05-23</td>\n",
       "      <td>2020-09-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>สรุป</td>\n",
       "      <td>13</td>\n",
       "      <td>185</td>\n",
       "      <td>59</td>\n",
       "      <td>1434</td>\n",
       "      <td>0</td>\n",
       "      <td>158.4</td>\n",
       "      <td>0</td>\n",
       "      <td>33142</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>79.4</td>\n",
       "      <td>9</td>\n",
       "      <td>8665</td>\n",
       "      <td>9</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>354</td>\n",
       "      <td>2020-05-23</td>\n",
       "      <td>2020-09-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>ราชเทวี</td>\n",
       "      <td>18</td>\n",
       "      <td>125</td>\n",
       "      <td>1</td>\n",
       "      <td>354</td>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>8372</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>79.4</td>\n",
       "      <td>9</td>\n",
       "      <td>8665</td>\n",
       "      <td>9</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>354</td>\n",
       "      <td>2020-05-23</td>\n",
       "      <td>2020-09-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>ราษฎร์บูรณะ</td>\n",
       "      <td>36</td>\n",
       "      <td>165</td>\n",
       "      <td>16</td>\n",
       "      <td>361</td>\n",
       "      <td>0</td>\n",
       "      <td>116.2</td>\n",
       "      <td>0</td>\n",
       "      <td>8355</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>2020-05-23</td>\n",
       "      <td>2020-09-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>พระโขนง</td>\n",
       "      <td>13</td>\n",
       "      <td>155</td>\n",
       "      <td>14</td>\n",
       "      <td>362</td>\n",
       "      <td>0</td>\n",
       "      <td>158.4</td>\n",
       "      <td>0</td>\n",
       "      <td>8286</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>2020-05-23</td>\n",
       "      <td>2020-09-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>ดินแดง</td>\n",
       "      <td>29</td>\n",
       "      <td>185</td>\n",
       "      <td>28</td>\n",
       "      <td>357</td>\n",
       "      <td>0</td>\n",
       "      <td>136.6</td>\n",
       "      <td>0</td>\n",
       "      <td>8129</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>2020-05-23</td>\n",
       "      <td>2020-10-15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id station_area pm10_min pm10_max  pm10_overstd pm10_count no2_min no2_max  \\\n",
       "0   6   ค่ามาตรฐาน        -        -           120          -       -       -   \n",
       "1   5         สรุป       13      185            59       1434       0   158.4   \n",
       "2   4      ราชเทวี       18      125             1        354       2     100   \n",
       "3   3  ราษฎร์บูรณะ       36      165            16        361       0   116.2   \n",
       "4   2      พระโขนง       13      155            14        362       0   158.4   \n",
       "5   1       ดินแดง       29      185            28        357       0   136.6   \n",
       "\n",
       "   no2_overstd no2_count  ... o38h_min o38h_max  o38h_overstd o38h_count  \\\n",
       "0          170         -  ...        -        -            70          -   \n",
       "1            0     33142  ...        0     79.4             9       8665   \n",
       "2            0      8372  ...        0     79.4             9       8665   \n",
       "3            0      8355  ...        -        -             -          -   \n",
       "4            0      8286  ...        -        -             -          -   \n",
       "5            0      8129  ...        -        -             -          -   \n",
       "\n",
       "  pm2_5_min pm2_5_max  pm2_5_overstd pm2_5_count     adddate  updatedate  \n",
       "0         -         -             50           -  2020-05-23  2020-09-15  \n",
       "1         9        76             16         354  2020-05-23  2020-09-15  \n",
       "2         9        76             16         354  2020-05-23  2020-09-15  \n",
       "3         -         -              -           -  2020-05-23  2020-09-15  \n",
       "4         -         -              -           -  2020-05-23  2020-09-15  \n",
       "5         -         -              -           -  2020-05-23  2020-10-15  \n",
       "\n",
       "[6 rows x 32 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url=\"https://data.bangkok.go.th/dataset/52a5da69-c086-425a-bcb3-fccfadd824f5/resource/1aecc616-c570-4efb-9398-7dd4e39356b2/download/env_5ec8dad38033f.csv\"\n",
    "df=pd.read_csv(url)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>No.</th>\n",
       "      <th>Province</th>\n",
       "      <th>แหล่งน้ำ</th>\n",
       "      <th>WQI score</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2561</td>\n",
       "      <td>64</td>\n",
       "      <td>กรุงเทพมหานคร</td>\n",
       "      <td>แม่น้ำเจ้าพระยา</td>\n",
       "      <td>35</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2562</td>\n",
       "      <td>64</td>\n",
       "      <td>กรุงเทพมหานคร</td>\n",
       "      <td>แม่น้ำเจ้าพระยา</td>\n",
       "      <td>36</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2563</td>\n",
       "      <td>64</td>\n",
       "      <td>กรุงเทพมหานคร</td>\n",
       "      <td>แม่น้ำเจ้าพระยา</td>\n",
       "      <td>38</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  No.       Province         แหล่งน้ำ  WQI score  Type\n",
       "0  2561   64  กรุงเทพมหานคร  แม่น้ำเจ้าพระยา         35     4\n",
       "1  2562   64  กรุงเทพมหานคร  แม่น้ำเจ้าพระยา         36     4\n",
       "2  2563   64  กรุงเทพมหานคร  แม่น้ำเจ้าพระยา         38     4"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url=\"https://data.bangkok.go.th/dataset/d7139135-3988-40fa-8703-d546eb480960/resource/70c4f872-6e4b-42db-b8c4-474ef29e0245/download/wq-4-wqi-..xlsx\"\n",
    "df=pd.read_excel(url)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>drag_g_id</th>\n",
       "      <th>dcode</th>\n",
       "      <th>scode</th>\n",
       "      <th>community_name</th>\n",
       "      <th>num_volunteers</th>\n",
       "      <th>population</th>\n",
       "      <th>amount_waste_kg</th>\n",
       "      <th>general_waste_kg</th>\n",
       "      <th>organic_waste_kg</th>\n",
       "      <th>toxic_waste_kg</th>\n",
       "      <th>infectious_waste_kg</th>\n",
       "      <th>recycle_waste_kg</th>\n",
       "      <th>sum_month</th>\n",
       "      <th>sum_year</th>\n",
       "      <th>remark</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1025001</td>\n",
       "      <td>1025</td>\n",
       "      <td>102504</td>\n",
       "      <td>ชุมชนวัดดาวดึงษาราม</td>\n",
       "      <td>2</td>\n",
       "      <td>2095</td>\n",
       "      <td>36266</td>\n",
       "      <td>17408</td>\n",
       "      <td>10880</td>\n",
       "      <td>725</td>\n",
       "      <td>0</td>\n",
       "      <td>7253</td>\n",
       "      <td>7</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1025002</td>\n",
       "      <td>1025</td>\n",
       "      <td>102501</td>\n",
       "      <td>ชุมชนวัดเทพนารี</td>\n",
       "      <td>1</td>\n",
       "      <td>990</td>\n",
       "      <td>20425</td>\n",
       "      <td>9804</td>\n",
       "      <td>6128</td>\n",
       "      <td>409</td>\n",
       "      <td>0</td>\n",
       "      <td>4085</td>\n",
       "      <td>7</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1025003</td>\n",
       "      <td>1025</td>\n",
       "      <td>102502</td>\n",
       "      <td>ชุมชนเติมสุข</td>\n",
       "      <td>1</td>\n",
       "      <td>440</td>\n",
       "      <td>20115</td>\n",
       "      <td>9655</td>\n",
       "      <td>6035</td>\n",
       "      <td>402</td>\n",
       "      <td>0</td>\n",
       "      <td>4023</td>\n",
       "      <td>7</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1025004</td>\n",
       "      <td>1025</td>\n",
       "      <td>102502</td>\n",
       "      <td>ชุมชนริมคลองบางพลัด</td>\n",
       "      <td>1</td>\n",
       "      <td>534</td>\n",
       "      <td>25548</td>\n",
       "      <td>12263</td>\n",
       "      <td>7664</td>\n",
       "      <td>511</td>\n",
       "      <td>0</td>\n",
       "      <td>5110</td>\n",
       "      <td>7</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1025005</td>\n",
       "      <td>1025</td>\n",
       "      <td>102502</td>\n",
       "      <td>ชุมชนวัดฉัตรแก้วจงกลนี</td>\n",
       "      <td>1</td>\n",
       "      <td>1498</td>\n",
       "      <td>19215</td>\n",
       "      <td>9223</td>\n",
       "      <td>5765</td>\n",
       "      <td>384</td>\n",
       "      <td>0</td>\n",
       "      <td>3843</td>\n",
       "      <td>7</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>1025006</td>\n",
       "      <td>1025</td>\n",
       "      <td>102502</td>\n",
       "      <td>ชุมชนแสงทอง</td>\n",
       "      <td>1</td>\n",
       "      <td>1922</td>\n",
       "      <td>37914</td>\n",
       "      <td>18199</td>\n",
       "      <td>11374</td>\n",
       "      <td>758</td>\n",
       "      <td>0</td>\n",
       "      <td>7583</td>\n",
       "      <td>7</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>1025007</td>\n",
       "      <td>1025</td>\n",
       "      <td>102501</td>\n",
       "      <td>ชุมชนสวนปรก</td>\n",
       "      <td>1</td>\n",
       "      <td>276</td>\n",
       "      <td>18900</td>\n",
       "      <td>9072</td>\n",
       "      <td>5670</td>\n",
       "      <td>378</td>\n",
       "      <td>0</td>\n",
       "      <td>3780</td>\n",
       "      <td>7</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _id  drag_g_id  dcode   scode          community_name  num_volunteers  \\\n",
       "0    1    1025001   1025  102504     ชุมชนวัดดาวดึงษาราม               2   \n",
       "1    2    1025002   1025  102501         ชุมชนวัดเทพนารี               1   \n",
       "2    3    1025003   1025  102502            ชุมชนเติมสุข               1   \n",
       "3    4    1025004   1025  102502     ชุมชนริมคลองบางพลัด               1   \n",
       "4    5    1025005   1025  102502  ชุมชนวัดฉัตรแก้วจงกลนี               1   \n",
       "5    6    1025006   1025  102502             ชุมชนแสงทอง               1   \n",
       "6    7    1025007   1025  102501             ชุมชนสวนปรก               1   \n",
       "\n",
       "   population  amount_waste_kg  general_waste_kg  organic_waste_kg  \\\n",
       "0        2095            36266             17408             10880   \n",
       "1         990            20425              9804              6128   \n",
       "2         440            20115              9655              6035   \n",
       "3         534            25548             12263              7664   \n",
       "4        1498            19215              9223              5765   \n",
       "5        1922            37914             18199             11374   \n",
       "6         276            18900              9072              5670   \n",
       "\n",
       "   toxic_waste_kg  infectious_waste_kg  recycle_waste_kg  sum_month  sum_year  \\\n",
       "0             725                    0              7253          7      2564   \n",
       "1             409                    0              4085          7      2564   \n",
       "2             402                    0              4023          7      2564   \n",
       "3             511                    0              5110          7      2564   \n",
       "4             384                    0              3843          7      2564   \n",
       "5             758                    0              7583          7      2564   \n",
       "6             378                    0              3780          7      2564   \n",
       "\n",
       "   remark  \n",
       "0       0  \n",
       "1       0  \n",
       "2       0  \n",
       "3       0  \n",
       "4       0  \n",
       "5       0  \n",
       "6       0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def bkkapi(resource_id):\n",
    "\turl=f\"https://data.bangkok.go.th/api/3/action/datastore_search?resource_id={resource_id}\"\n",
    "\tr=requests.get(url)\n",
    "\tj=r.json()\n",
    "\t# j['result']['records']\n",
    "\tdf=pd.DataFrame(j['result']['records'])\n",
    "\treturn df\n",
    "\n",
    "# https://data.bangkok.go.th/dataset/65eb2ee6-efc6-4045-8e3e-ef451b844953/resource/95bd1379-177e-4dd8-9374-c0ce307bede9/download/-..-64.csv\n",
    "dt=bkkapi(\"95bd1379-177e-4dd8-9374-c0ce307bede9\")\n",
    "dt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## read CSV\n",
    "- ข้อมูลสถิติการจัดเก็บภาษีเปรียบเทียบยอดประมาณการ\n",
    "  -   https://data.bangkok.go.th/dataset/statvat0864/resource/5cd749fe-11a8-457d-b88f-2151ac33fe27\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://data.bangkok.go.th/dataset/c051d4a7-8418-49c1-86b0-5a5ea8511c33/resource/5cd749fe-11a8-457d-b88f-2151ac33fe27/download/fin_600ea1e3c81a7.csv\"\n",
    "df = pd.read_csv(url)\n",
    "df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## read Excel\n",
    "\n",
    "-   รายงานปริมาณน้ำที่ผ่านการบำบัดแล้วกลับมาใช้ประโยชน์ (พ.ศ.2561-2563)\n",
    "    -   https://data.bangkok.go.th/dataset/kpisbangkok_1100/resource/272b8fbc-6146-40d6-be7c-335ea30c2f7e\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://data.bangkok.go.th/dataset/e53ef05f-32b2-4b06-b800-cad2702f426e/resource/272b8fbc-6146-40d6-be7c-335ea30c2f7e/download/wq-2-reused_water-2561-2563.xlsx\"\n",
    "print(url)\n",
    "df = pd.read_excel(url)\n",
    "df[:5]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download(url):\n",
    "\tif (url[-4:] == \".xls\") or (url[-5:] == \".xlsx\"):\n",
    "\t\tdf = pd.read_excel(url)\n",
    "\telse:\n",
    "\t\tdf = pd.read_csv(url)\n",
    "\treturn df\n",
    "\n",
    "# url = \"https://data.bangkok.go.th/dataset/c051d4a7-8418-49c1-86b0-5a5ea8511c33/resource/5cd749fe-11a8-457d-b88f-2151ac33fe27/download/fin_600ea1e3c81a7.csv\"\n",
    "url = \"https://data.bangkok.go.th/dataset/e53ef05f-32b2-4b06-b800-cad2702f426e/resource/272b8fbc-6146-40d6-be7c-335ea30c2f7e/download/wq-2-reused_water-2561-2563.xlsx\"\n",
    "df=download(url)\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CKAN API\n",
    "-   รายงานปริมาณน้ำที่ผ่านการบำบัดแล้วกลับมาใช้ประโยชน์ (พ.ศ.2561-2563)\n",
    "    -   https://data.bangkok.go.th/dataset/kpisbangkok_1100/resource/272b8fbc-6146-40d6-be7c-335ea30c2f7e\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# resource_id=\"5cd749fe-11a8-457d-b88f-2151ac33fe27\"\n",
    "resource_id = \"272b8fbc-6146-40d6-be7c-335ea30c2f7e\"\n",
    "url = f\"https://data.bangkok.go.th/api/3/action/datastore_search?resource_id={resource_id}\"\n",
    "print(url)\n",
    "r = requests.get(url)\n",
    "\n",
    "j = r.json()\n",
    "# j\n",
    "j[\"result\"][\"records\"]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(j[\"result\"][\"records\"])\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, 'Year':]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.12 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "629cb9f199f624aadf5cdcebfeb0fdc9652b7a7e432d484384b677cab7914fce"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}