Va_Open_Data_Portal_API_Example.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Va Open Data Portal API Example",
      "provenance": [],
      "collapsed_sections": [],
      "mount_file_id": "1klzxCBv87vQOVEfUt7Xz38pL7l3qpuHa",
      "authorship_tag": "ABX9TyMjtjRBoEgOWe059srr9d4R",
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/odu-cs625-datavis/public/blob/main/Va_Open_Data_Portal_API_Example.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B8VA0graSZg7"
      },
      "source": [
        "## Using the SODA API to access data from the [Virginia Open Data Portal](https://data.virginia.gov)\n",
        "\n",
        "For more information on using the API, visit the dataset page you're interested in and click the **API** button.  It should provide links to the API docs for that dataset and the [Developer Portal (Socrata.com)](https://dev.socrata.com/).\n",
        "\n",
        "In this example, we'll access the VDH COVID-19 Public Use Dataset - Cases, available at <https://data.virginia.gov/Government/VDH-COVID-19-PublicUseDataset-Cases/bre9-aqqr>.  The dataset name, which we'll need for the API, is the last part of the URI, `bre9-aqqr`.\n",
        "\n",
        "As of Sep 29, 2021, this dataset had 74.7k rows and 7 columns, with each row representing the overall count of COVID-19 cases, hospitalizations, deaths for each locality in Virginia by report date since reporting began for this dataset.\n",
        "\n",
        "The following example is based the API docs for this dataset, available at https://dev.socrata.com/foundry/data.virginia.gov/bre9-aqqr"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lUPX-ic-RALN"
      },
      "source": [
        "First, install the `sodapy` package to use the API in Python"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6MwcKAnWRPam",
        "outputId": "95de21c3-fcf4-423d-b619-59036c1e3713"
      },
      "source": [
        "!pip install sodapy"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting sodapy\n",
            "  Downloading sodapy-2.1.0-py2.py3-none-any.whl (14 kB)\n",
            "Requirement already satisfied: requests>=2.20.0 in /usr/local/lib/python3.7/dist-packages (from sodapy) (2.23.0)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests>=2.20.0->sodapy) (3.0.4)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.7/dist-packages (from requests>=2.20.0->sodapy) (2021.5.30)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests>=2.20.0->sodapy) (2.10)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests>=2.20.0->sodapy) (1.24.3)\n",
            "Installing collected packages: sodapy\n",
            "Successfully installed sodapy-2.1.0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B_jWFvGZRQNy"
      },
      "source": [
        "Then import `pandas` and the `Socrata` package from the `sodapy` library.\n",
        "\n",
        "Next, we need to specify that we'll be using the data.virginia.gov client.  We are accessing public datasets, so we don't need to provide an authentication token."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5kGN03CGa0ep",
        "outputId": "82698a39-a873-473d-c623-9b24563adec1"
      },
      "source": [
        "import pandas as pd\n",
        "from sodapy import Socrata\n",
        "\n",
        "# Unauthenticated client only works with public data sets. Note 'None'\n",
        "# in place of application token, and no username or password:\n",
        "client = Socrata(\"data.virginia.gov\", None)"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DBvIOjsjSAUx"
      },
      "source": [
        "Then we specify which dataset we want to access.  This corresponds to the set of characters at end of the dataset URI.\n",
        "\n",
        "Note that the `get()` function only returns 1000 results by default.  To access more, use the `limit=` option.\n",
        "\n",
        "And then we'll convert that to a Pandas data frame for ease of use."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "N8gTpkllal9O"
      },
      "source": [
        "# data = client.get(\"bre9-aqqr\")         # Gets 1000 records\n",
        "data = client.get(\"bre9-aqqr\", limit=75000)  # We know there are 74.7k records, set max at 75,000\n",
        "df = pd.DataFrame.from_records(data)   # Convert to pandas DataFrame"
      ],
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ci1o7TuDTm89",
        "outputId": "30eda502-9fe0-488c-dc97-ac53fd8c46e9"
      },
      "source": [
        "df.info()"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 74746 entries, 0 to 74745\n",
            "Data columns (total 7 columns):\n",
            " #   Column               Non-Null Count  Dtype \n",
            "---  ------               --------------  ----- \n",
            " 0   report_date          74746 non-null  object\n",
            " 1   fips                 74746 non-null  object\n",
            " 2   locality             74746 non-null  object\n",
            " 3   vdh_health_district  74746 non-null  object\n",
            " 4   total_cases          74746 non-null  object\n",
            " 5   hospitalizations     74746 non-null  object\n",
            " 6   deaths               74746 non-null  object\n",
            "dtypes: object(7)\n",
            "memory usage: 4.0+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "72bPenoDGu9a"
      },
      "source": [
        "# convert to proper datatypes\n",
        "df['report_date'] = pd.to_datetime(df['report_date'])\n",
        "df['total_cases'] = pd.to_numeric(df['total_cases'])\n",
        "df['hospitalizations'] = pd.to_numeric(df['hospitalizations'])\n",
        "df['deaths'] = pd.to_numeric(df['hospitalizations'])"
      ],
      "execution_count": 11,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "asOm6HXdTfAY",
        "outputId": "7383762f-f1c7-4f68-eec8-4c5496b84d32"
      },
      "source": [
        "df.info()"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 74746 entries, 0 to 74745\n",
            "Data columns (total 7 columns):\n",
            " #   Column               Non-Null Count  Dtype         \n",
            "---  ------               --------------  -----         \n",
            " 0   report_date          74746 non-null  datetime64[ns]\n",
            " 1   fips                 74746 non-null  object        \n",
            " 2   locality             74746 non-null  object        \n",
            " 3   vdh_health_district  74746 non-null  object        \n",
            " 4   total_cases          74746 non-null  int64         \n",
            " 5   hospitalizations     74746 non-null  int64         \n",
            " 6   deaths               74746 non-null  int64         \n",
            "dtypes: datetime64[ns](1), int64(3), object(3)\n",
            "memory usage: 4.0+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "lSDAFZ9yUHZV",
        "outputId": "0b3ced96-7b5e-41c5-ae62-71bd09cd74b9"
      },
      "source": [
        "df.head(10)"
      ],
      "execution_count": 13,
      "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>report_date</th>\n",
              "      <th>fips</th>\n",
              "      <th>locality</th>\n",
              "      <th>vdh_health_district</th>\n",
              "      <th>total_cases</th>\n",
              "      <th>hospitalizations</th>\n",
              "      <th>deaths</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51001</td>\n",
              "      <td>Accomack</td>\n",
              "      <td>Eastern Shore</td>\n",
              "      <td>3768</td>\n",
              "      <td>286</td>\n",
              "      <td>286</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51003</td>\n",
              "      <td>Albemarle</td>\n",
              "      <td>Blue Ridge</td>\n",
              "      <td>7504</td>\n",
              "      <td>307</td>\n",
              "      <td>307</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51005</td>\n",
              "      <td>Alleghany</td>\n",
              "      <td>Alleghany</td>\n",
              "      <td>1938</td>\n",
              "      <td>79</td>\n",
              "      <td>79</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51007</td>\n",
              "      <td>Amelia</td>\n",
              "      <td>Piedmont</td>\n",
              "      <td>1299</td>\n",
              "      <td>59</td>\n",
              "      <td>59</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51009</td>\n",
              "      <td>Amherst</td>\n",
              "      <td>Central Virginia</td>\n",
              "      <td>4071</td>\n",
              "      <td>198</td>\n",
              "      <td>198</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51011</td>\n",
              "      <td>Appomattox</td>\n",
              "      <td>Central Virginia</td>\n",
              "      <td>2217</td>\n",
              "      <td>104</td>\n",
              "      <td>104</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51013</td>\n",
              "      <td>Arlington</td>\n",
              "      <td>Arlington</td>\n",
              "      <td>17750</td>\n",
              "      <td>906</td>\n",
              "      <td>906</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51015</td>\n",
              "      <td>Augusta</td>\n",
              "      <td>Central Shenandoah</td>\n",
              "      <td>9396</td>\n",
              "      <td>265</td>\n",
              "      <td>265</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51017</td>\n",
              "      <td>Bath</td>\n",
              "      <td>Central Shenandoah</td>\n",
              "      <td>416</td>\n",
              "      <td>14</td>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2021-09-29</td>\n",
              "      <td>51019</td>\n",
              "      <td>Bedford</td>\n",
              "      <td>Central Virginia</td>\n",
              "      <td>9143</td>\n",
              "      <td>353</td>\n",
              "      <td>353</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  report_date   fips    locality  ... total_cases  hospitalizations  deaths\n",
              "0  2021-09-29  51001    Accomack  ...        3768               286     286\n",
              "1  2021-09-29  51003   Albemarle  ...        7504               307     307\n",
              "2  2021-09-29  51005   Alleghany  ...        1938                79      79\n",
              "3  2021-09-29  51007      Amelia  ...        1299                59      59\n",
              "4  2021-09-29  51009     Amherst  ...        4071               198     198\n",
              "5  2021-09-29  51011  Appomattox  ...        2217               104     104\n",
              "6  2021-09-29  51013   Arlington  ...       17750               906     906\n",
              "7  2021-09-29  51015     Augusta  ...        9396               265     265\n",
              "8  2021-09-29  51017        Bath  ...         416                14      14\n",
              "9  2021-09-29  51019     Bedford  ...        9143               353     353\n",
              "\n",
              "[10 rows x 7 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    }
  ]
}