ntd_api.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Socratic API\n",
    "\n",
    "Apparently, we can also access this data using a free API endpoint with _unlimited_ usage, as long as we are using an [application token](https://dev.socrata.com/docs/app-tokens). We can query this data using this endpoint, https://data.transportation.gov/resource/8ehq-7his.json, and we can filter the data using [SoQL clauses](https://dev.socrata.com/docs/queries/). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "  {\n",
      "    \"city\": \"Albany\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Binghamton\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Brooklyn\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Buffalo\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Calverton\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Carmel\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Huntington\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Hurley\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Ithaca\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Jamaica\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Kingston\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Long Beach\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Mineola\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"New City\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"New York\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Poughkeepsie\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Rochester\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Spring Valley\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Staten Island\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Syracuse\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"White Plains\"\n",
      "  },\n",
      "  {\n",
      "    \"city\": \"Yaphank\"\n",
      "  }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "# Requires requests, pip install requests\n",
    "import requests\n",
    "import json\n",
    "\n",
    "# Define querying function\n",
    "def query_ntd(params):\n",
    "    url = \"https://data.transportation.gov/resource/8ehq-7his.json\"\n",
    "    response = requests.get(url, params=params)\n",
    "    # print(response.url)\n",
    "    if response.status_code == 200:\n",
    "        data = response.json()\n",
    "        print(json.dumps(data, indent=2))\n",
    "    else:\n",
    "        print(f\"Request failed with status code {response.status_code}\")\n",
    "\n",
    "# Define our SoQL clauses\n",
    "basic_params = {\n",
    "    '$select': 'distinct city',\n",
    "    '$where': 'state=\"NY\"',\n",
    "    '$order': 'city'\n",
    "}\n",
    "\n",
    "# Query API w/ our basic clauses\n",
    "query_ntd(basic_params)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Gathering data by UACE codes\n",
    "\n",
    "To find the different column id's that correspond to each column, visit [this](https://data.transportation.gov/Public-Transit/2022-NTD-Annual-Data-Fuel-and-Energy/8ehq-7his/data) website, click **Export**, then **SODA API**, and all of the column id's will be listed below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"MTA New York City Transit\",\n",
      "    \"city\": \"Brooklyn\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"3697741\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"15739148\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"MTA New York City Transit\",\n",
      "    \"city\": \"Brooklyn\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"23170009\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"79941725\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"MTA New York City Transit\",\n",
      "    \"city\": \"Brooklyn\",\n",
      "    \"gasoline_gal\": \"3570980\",\n",
      "    \"diesel_gal\": \"19738\",\n",
      "    \"gasoline\": \"28059319\",\n",
      "    \"diesel\": \"242940\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"MTA New York City Transit\",\n",
      "    \"city\": \"Brooklyn\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"1070814\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"3180905\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"MTA New York City Transit\",\n",
      "    \"city\": \"Brooklyn\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New Jersey Transit Corporation\",\n",
      "    \"city\": \"Newark\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New Jersey Transit Corporation\",\n",
      "    \"city\": \"Newark\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"14018352\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"2749033\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New Jersey Transit Corporation\",\n",
      "    \"city\": \"Newark\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"667776\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"1211272\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New Jersey Transit Corporation\",\n",
      "    \"city\": \"Newark\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"1887499\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"8189868\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New Jersey Transit Corporation\",\n",
      "    \"city\": \"Newark\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New Jersey Transit Corporation\",\n",
      "    \"city\": \"Newark\",\n",
      "    \"gasoline_gal\": \"1761034\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"16052645\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New Jersey Transit Corporation\",\n",
      "    \"city\": \"Newark\",\n",
      "    \"gasoline_gal\": \"44867\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"765035\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New Jersey Transit Corporation\",\n",
      "    \"city\": \"Newark\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"19011620\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"83395816\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Metro-North Commuter Railroad Company, dba: MTA Metro-North Railroad\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"192849\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"46140\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Metro-North Commuter Railroad Company, dba: MTA Metro-North Railroad\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"41741\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"205598\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Metro-North Commuter Railroad Company, dba: MTA Metro-North Railroad\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"6882694\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"MTA Bus Company\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"9476906\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"23116558\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"MTA Long Island Rail Road\",\n",
      "    \"city\": \"Jamaica\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"7310132\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"982180\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Westchester County, dba: The Bee-Line System\",\n",
      "    \"city\": \"White Plains\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Westchester County, dba: The Bee-Line System\",\n",
      "    \"city\": \"White Plains\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"2333427\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"7571675\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Westchester County, dba: The Bee-Line System\",\n",
      "    \"city\": \"White Plains\",\n",
      "    \"gasoline_gal\": \"288382\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"2981008\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"County of Nassau, dba: Nassau Inter County Express\",\n",
      "    \"city\": \"Mineola\",\n",
      "    \"gasoline_gal\": \"10905\",\n",
      "    \"diesel_gal\": \"6790\",\n",
      "    \"gasoline\": \"63972\",\n",
      "    \"diesel\": \"69461\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"County of Nassau, dba: Nassau Inter County Express\",\n",
      "    \"city\": \"Mineola\",\n",
      "    \"gasoline_gal\": \"220732\",\n",
      "    \"diesel_gal\": \"26552\",\n",
      "    \"gasoline\": \"1470302\",\n",
      "    \"diesel\": \"332839\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Port Authority Trans-Hudson Corporation\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Suffolk County , dba: Dept of Public Works - Transportation Division\",\n",
      "    \"city\": \"Yaphank\",\n",
      "    \"gasoline_gal\": \"1147047\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"9037388\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Suffolk County , dba: Dept of Public Works - Transportation Division\",\n",
      "    \"city\": \"Yaphank\",\n",
      "    \"gasoline_gal\": \"231609\",\n",
      "    \"diesel_gal\": \"1084223\",\n",
      "    \"gasoline\": \"4241833\",\n",
      "    \"diesel\": \"3735629\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Academy Lines, Inc.\",\n",
      "    \"city\": \"Hoboken\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"668954\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"3177531\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Hudson Transit Lines, Inc.\",\n",
      "    \"city\": \"Mahwah\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"944587\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"5598476\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Somerset County, dba: Somerset County Transportation\",\n",
      "    \"city\": \"Somerville\",\n",
      "    \"gasoline_gal\": \"64415\",\n",
      "    \"diesel_gal\": \"21287\",\n",
      "    \"gasoline\": \"181158\",\n",
      "    \"diesel\": \"137884\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Somerset County, dba: Somerset County Transportation\",\n",
      "    \"city\": \"Somerville\",\n",
      "    \"gasoline_gal\": \"102160\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"773482\",\n",
      "    \"diesel\": \"9783\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Hampton Jitney, Inc.\",\n",
      "    \"city\": \"Calverton\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"428924\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"3060141\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"County of Rockland , dba: Public Transportation - Transport of Rockland\",\n",
      "    \"city\": \"New City\",\n",
      "    \"gasoline_gal\": \"16744\",\n",
      "    \"diesel_gal\": \"391733\",\n",
      "    \"gasoline\": \"105512\",\n",
      "    \"diesel\": \"1920049\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"County of Rockland , dba: Public Transportation - Transport of Rockland\",\n",
      "    \"city\": \"New City\",\n",
      "    \"gasoline_gal\": \"70126\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"589713\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Bergen County, dba: Bergen County Community Transportation\",\n",
      "    \"city\": \"Hackensack\",\n",
      "    \"gasoline_gal\": \"9531\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"67360\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Bergen County, dba: Bergen County Community Transportation\",\n",
      "    \"city\": \"Hackensack\",\n",
      "    \"gasoline_gal\": \"79229\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"559956\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Staten Island Rapid Transit Operating Authority, dba:  MTA Staten Island Railway\",\n",
      "    \"city\": \"Staten Island\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Port Imperial Ferry Corporation, dba: NY Waterway\",\n",
      "    \"city\": \"Weehawken\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"1908884\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"444326\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Port Imperial Ferry Corporation, dba: NY Waterway\",\n",
      "    \"city\": \"Weehawken\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"99043\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"253908\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Lakeland Bus Lines, Inc.\",\n",
      "    \"city\": \"Dover\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"293079\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"1011075\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Orange-Newark-Elizabeth, Inc.\",\n",
      "    \"city\": \"Elizabeth\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"221969\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"903976\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Suburban Transit Corporation\",\n",
      "    \"city\": \"New Brunswick\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"772193\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"2921910\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Rockland Coaches, Inc.\",\n",
      "    \"city\": \"Westwood\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"283362\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"1563294\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Monsey New Square Trails Corporation\",\n",
      "    \"city\": \"Spring Valley\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"288710\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"841455\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New York City Economic Development Corporation\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"3856180\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"1001425\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New York City Economic Development Corporation\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"18749\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Town of Huntington, dba: Huntington Area Rapid Transit\",\n",
      "    \"city\": \"Huntington\",\n",
      "    \"gasoline_gal\": \"27755\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"955510\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Town of Huntington, dba: Huntington Area Rapid Transit\",\n",
      "    \"city\": \"Huntington\",\n",
      "    \"gasoline_gal\": \"33156\",\n",
      "    \"diesel_gal\": \"21033\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"175840\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Trans-Bridge Lines, Inc.\",\n",
      "    \"city\": \"Bethlehem\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"1269214\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"DeCamp Bus Lines\",\n",
      "    \"city\": \"Montclair\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"214773\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"1059444\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New York City Department of Transportation\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"4840205\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"192239\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Adirondack Transit Lines, Inc,\",\n",
      "    \"city\": \"Hurley\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"217501\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"3239304\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Putnam County , dba: Putnam Area Rapid Transit\",\n",
      "    \"city\": \"Carmel\",\n",
      "    \"gasoline_gal\": \"14008\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"116070\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"New York City Department of Transportation\",\n",
      "    \"city\": \"New York\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Putnam County , dba: Putnam Area Rapid Transit\",\n",
      "    \"city\": \"Carmel\",\n",
      "    \"gasoline_gal\": \"57495\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"407759\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"City of Long Beach\",\n",
      "    \"city\": \"Long Beach\",\n",
      "    \"gasoline_gal\": \"804\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"9786\",\n",
      "    \"diesel\": \"0\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"SeaStreak, LLC\",\n",
      "    \"city\": \"Atlantic Highlands\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"1701973\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"144634\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"City of Long Beach\",\n",
      "    \"city\": \"Long Beach\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"56078\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"235526\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"agency\": \"Private Transportation Corporation\",\n",
      "    \"city\": \"Brooklyn\",\n",
      "    \"gasoline_gal\": \"0\",\n",
      "    \"diesel_gal\": \"0\",\n",
      "    \"gasoline\": \"0\",\n",
      "    \"diesel\": \"0\"\n",
      "  }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "def get_data_about_uace(code, select_vals):\n",
    "    select_params = \",\".join(select_vals)\n",
    "    params = {\n",
    "        '$select': select_params,\n",
    "        '$where': 'uace_code=\"' + code +'\"'\n",
    "    }\n",
    "    query_ntd(params)\n",
    "    \n",
    "get_data_about_uace('63217', [\"uace_code\",\"agency\",\"city\",\"gasoline_gal\",\"diesel_gal\",\"gasoline\",\"diesel\"])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate data manipulation\n",
    "\n",
    "Using SoQL allows us to do some powerful data aggregation using `$group` and specific clauses found [here](https://dev.socrata.com/docs/queries/group)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Information about gasoline usage in UACE 63217\n",
      "[\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"total_gas_gals\": \"7750979\",\n",
      "    \"avg_gas_gals\": \"133637.568965517241\"\n",
      "  }\n",
      "]\n",
      "\n",
      "Top 10 UACE codes in terms of gallons of gas used\n",
      "[\n",
      "  {\n",
      "    \"uace_code\": \"63217\",\n",
      "    \"total_gas_gals\": \"7750979\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"51445\",\n",
      "    \"total_gas_gals\": \"5235725\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"57628\",\n",
      "    \"total_gas_gals\": \"4400701\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"16264\",\n",
      "    \"total_gas_gals\": \"3134130\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"56602\",\n",
      "    \"total_gas_gals\": \"3069851\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"40429\",\n",
      "    \"total_gas_gals\": \"2705990\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"92242\",\n",
      "    \"total_gas_gals\": \"2421185\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"80389\",\n",
      "    \"total_gas_gals\": \"1871475\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"4843\",\n",
      "    \"total_gas_gals\": \"1816826\"\n",
      "  },\n",
      "  {\n",
      "    \"uace_code\": \"78904\",\n",
      "    \"total_gas_gals\": \"1658109\"\n",
      "  }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "gasoline_info_params = {\n",
    "    '$select': 'uace_code,SUM(gasoline_gal) as total_gas_gals,AVG(gasoline_gal) as avg_gas_gals',\n",
    "    '$where': 'uace_code=\"63217\"',\n",
    "    '$group': 'uace_code'\n",
    "}\n",
    "\n",
    "print(\"\\nInformation about gasoline usage in UACE 63217\")\n",
    "query_ntd(gasoline_info_params)\n",
    "\n",
    "ranked_params = {\n",
    "    \"$select\": \"uace_code, SUM(gasoline_gal) as total_gas_gals\",\n",
    "    \"$group\": \"uace_code\",\n",
    "    \"$order\": \"SUM(gasoline_gal) DESC\",\n",
    "    \"$limit\": \"10\"\n",
    "}\n",
    "\n",
    "print(\"\\nTop 10 UACE codes in terms of gallons of gas used\")\n",
    "query_ntd(ranked_params)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}