US-Ignite-Fed.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# US-Ignite Fed_Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libraries from Carto.\n",
    "from cartoframes.auth import set_default_credentials, Credentials\n",
    "from cartoframes.viz import Map, Layer\n",
    "from cartoframes.data import Dataset\n",
    "from cartoframes.viz.helpers import color_continuous_layer\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import requests\n",
    "import json\n",
    "#import censusdata\n",
    "set_default_credentials(\n",
    "    base_url='https://usignite-admin.carto.com/',\n",
    "    api_key='81c922dc336bd82103d92a768816a4567c444909'\n",
    ")\n",
    "#key='0995bcf2c5a4aa42899cf705840809959c68cf75'\n",
    "#import config\n",
    "#from census import Census\n",
    "#from us import states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = requests.get(\n",
    "    'https://www.huduser.gov/hudapi/public/usps',\n",
    "    params={'type': '1', 'query': '20002'},\n",
    "    headers = {\"Authorization\": \"Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImp0aSI6IjBkYmZmNGJmMjdiZjYzZjgxM2JiOTZmMGQ4NTNjZThjNzUxNjRiNTY5MGQzYThlMzc5ZDNjMDk2ZWVhM2Y3YjFhZjI0YzY2ZDNmM2NjNGFhIn0.eyJhdWQiOiI2IiwianRpIjoiMGRiZmY0YmYyN2JmNjNmODEzYmI5NmYwZDg1M2NlOGM3NTE2NGI1NjkwZDNhOGUzNzlkM2MwOTZlZWEzZjdiMWFmMjRjNjZkM2YzY2M0YWEiLCJpYXQiOjE1NzM2Nzc1OTUsIm5iZiI6MTU3MzY3NzU5NSwiZXhwIjoxNjA1Mjk5OTk1LCJzdWIiOiIzMjkiLCJzY29wZXMiOltdfQ.VdbEhWz_XL47o_A6_xTGBDQXuImIKxd7_Ee4vsksEC1-DdfgMwnouMcOqX5GggK3jnWzhkx9hnZ9ts-PKWiSIw\"}\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'data': {'year': '2019', 'quarter': '3', 'input': '20002', 'crosswalk_type': 'zip-tract', 'results': [{'geoid': '11001010600', 'res_ratio': 0.197728542170917, 'bus_ratio': 0.26714125140502, 'oth_ratio': 0.0732815754591933, 'tot_ratio': 0.187366895734076}, {'geoid': '11001008904', 'res_ratio': 0.0733303189084235, 'bus_ratio': 0.0460846759085799, 'oth_ratio': 0.0854004923309979, 'tot_ratio': 0.0731332469756515}, {'geoid': '11001008802', 'res_ratio': 0.0562810862568821, 'bus_ratio': 0.0266017234919445, 'oth_ratio': 0.0757432304487786, 'tot_ratio': 0.0567984719081388}, {'geoid': '11001008702', 'res_ratio': 0.0543926377104556, 'bus_ratio': 0.0325964780816785, 'oth_ratio': 0.0492330997917061, 'tot_ratio': 0.0525171800557665}, {'geoid': '11001008803', 'res_ratio': 0.0458813203181104, 'bus_ratio': 0.124016485575121, 'oth_ratio': 0.0594584359022912, 'tot_ratio': 0.0520341625134476}, {'geoid': '11001008903', 'res_ratio': 0.0510413064872197, 'bus_ratio': 0.0123641813413263, 'oth_ratio': 0.0689263397083885, 'tot_ratio': 0.050848574000483}, {'geoid': '11001008804', 'res_ratio': 0.0419448360241508, 'bus_ratio': 0.0307231172723866, 'oth_ratio': 0.117023291043362, 'tot_ratio': 0.0499923156300085}, {'geoid': '11001007901', 'res_ratio': 0.0476367795302816, 'bus_ratio': 0.0236043461970775, 'oth_ratio': 0.0657072524143154, 'tot_ratio': 0.048323709574725}, {'geoid': '11001008301', 'res_ratio': 0.0454291565816421, 'bus_ratio': 0.0779318096665417, 'oth_ratio': 0.0373035409960234, 'tot_ratio': 0.0463916394054493}, {'geoid': '11001008002', 'res_ratio': 0.0419448360241508, 'bus_ratio': 0.00786811539902585, 'oth_ratio': 0.0471501609543647, 'tot_ratio': 0.0405515182119568}, {'geoid': '11001008200', 'res_ratio': 0.0369710349229991, 'bus_ratio': 0.0513300861745972, 'oth_ratio': 0.056618064760462, 'tot_ratio': 0.0400904560124706}, {'geoid': '11001008100', 'res_ratio': 0.0407479320158523, 'bus_ratio': 0.0142375421506182, 'oth_ratio': 0.045824654421511, 'tot_ratio': 0.0397830812128131}, {'geoid': '11001008302', 'res_ratio': 0.0409075192169587, 'bus_ratio': 0.0198576245784938, 'oth_ratio': 0.0344631698541942, 'tot_ratio': 0.0389268228423386}, {'geoid': '11001008402', 'res_ratio': 0.0373168071920631, 'bus_ratio': 0.0460846759085799, 'oth_ratio': 0.0291611437227797, 'tot_ratio': 0.0368849759588995}, {'geoid': '11001008001', 'res_ratio': 0.0375029922600207, 'bus_ratio': 0.0108654926938928, 'oth_ratio': 0.024616549895853, 'tot_ratio': 0.0344479329044723}, {'geoid': '11001008701', 'res_ratio': 0.0320238316886985, 'bus_ratio': 0.0232296740352191, 'oth_ratio': 0.0361673925392918, 'tot_ratio': 0.0319889345072123}, {'geoid': '11001009203', 'res_ratio': 0.0353219671782323, 'bus_ratio': 0.00786811539902585, 'oth_ratio': 0.0168528687748532, 'tot_ratio': 0.0315717829933914}, {'geoid': '11001007903', 'res_ratio': 0.0235125142963534, 'bus_ratio': 0.0168602472836268, 'oth_ratio': 0.0301079341033895, 'tot_ratio': 0.023887413001954}, {'geoid': '11001008410', 'res_ratio': 0.0223422081549059, 'bus_ratio': 0.0472086923941551, 'oth_ratio': 0.0142018557091459, 'tot_ratio': 0.0228555118888181}, {'geoid': '11001004701', 'res_ratio': 0.0137510971620076, 'bus_ratio': 0.00187336080929186, 'oth_ratio': 0.000757432304487786, 'tot_ratio': 0.0115485103299888}, {'geoid': '11001011100', 'res_ratio': 0.00773997925366385, 'bus_ratio': 0.0588235294117647, 'oth_ratio': 0.0115508426434387, 'tot_ratio': 0.0111752695018332}, {'geoid': '11001009204', 'res_ratio': 0.00954863419953719, 'bus_ratio': 0.010116148370176, 'oth_ratio': 0.00511266805529255, 'tot_ratio': 0.00906755658989615}, {'geoid': '11001003302', 'res_ratio': 0.00207463361438412, 'bus_ratio': 0.0056200824278756, 'oth_ratio': 0.00662753266426813, 'tot_ratio': 0.00281028388258282}, {'geoid': '11001003301', 'res_ratio': 0.00215442721493736, 'bus_ratio': 0.000374672161858373, 'oth_ratio': 0.00227229691346335, 'tot_ratio': 0.00206380222627176}, {'geoid': '11001004600', 'res_ratio': 0.00106391467404314, 'bus_ratio': 0.0134881978269014, 'oth_ratio': 0.00302972921795114, 'tot_ratio': 0.0020198915406064}, {'geoid': '11001009201', 'res_ratio': 0.00101071894034098, 'bus_ratio': 0, 'oth_ratio': 0.00208293883734141, 'tot_ratio': 0.00107581179880123}, {'geoid': '11001009302', 'res_ratio': 0, 'bus_ratio': 0.00899213188460097, 'oth_ratio': 0.000378716152243893, 'tot_ratio': 0.000570838913649636}, {'geoid': '11001004702', 'res_ratio': 0, 'bus_ratio': 0.00824278756088422, 'oth_ratio': 0.000378716152243893, 'tot_ratio': 0.000526928227984279}, {'geoid': '11001006804', 'res_ratio': 0.000398968002766178, 'bus_ratio': 0, 'oth_ratio': 0, 'tot_ratio': 0.000329330142490174}, {'geoid': '11001009102', 'res_ratio': 0, 'bus_ratio': 0.00449606594230048, 'oth_ratio': 0.000568074228365839, 'tot_ratio': 0.000329330142490174}, {'geoid': '11001006202', 'res_ratio': 0, 'bus_ratio': 0.00149868864743349, 'oth_ratio': 0, 'tot_ratio': 8.78213713307133e-05}]}}\n"
     ]
    }
   ],
   "source": [
    "print(response.json())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "response1 = requests.get(\n",
    "    'https://api.census.gov/data/2016/zbp',\n",
    "    params={'get':'ESTAB,EMPSZES,GEO_ID', 'for':'zipcode:20002','NAICS2012':'721',}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['ESTAB', 'EMPSZES', 'GEO_ID', 'NAICS2012', 'zipcode'], ['10', '001', '8610000US20002', '721', '20002'], ['1', '212', '8610000US20002', '721', '20002'], ['1', '220', '8610000US20002', '721', '20002'], ['4', '230', '8610000US20002', '721', '20002'], ['3', '241', '8610000US20002', '721', '20002'], ['1', '242', '8610000US20002', '721', '20002'], ['0', '251', '8610000US20002', '721', '20002'], ['0', '252', '8610000US20002', '721', '20002'], ['0', '254', '8610000US20002', '721', '20002'], ['0', '260', '8610000US20002', '721', '20002']]\n"
     ]
    }
   ],
   "source": [
    "print(response1.json())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "ddd=response1.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = response1.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "e = response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "cbp = pd.DataFrame(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "cbpx = pd.DataFrame(ddd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "hud = pd.DataFrame(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "ap= hud['data']['results']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "asa = pd.DataFrame(ap)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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>bus_ratio</th>\n",
       "      <th>geoid</th>\n",
       "      <th>oth_ratio</th>\n",
       "      <th>res_ratio</th>\n",
       "      <th>tot_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.267141</td>\n",
       "      <td>11001010600</td>\n",
       "      <td>0.073282</td>\n",
       "      <td>0.197729</td>\n",
       "      <td>0.187367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.046085</td>\n",
       "      <td>11001008904</td>\n",
       "      <td>0.085400</td>\n",
       "      <td>0.073330</td>\n",
       "      <td>0.073133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.026602</td>\n",
       "      <td>11001008802</td>\n",
       "      <td>0.075743</td>\n",
       "      <td>0.056281</td>\n",
       "      <td>0.056798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.032596</td>\n",
       "      <td>11001008702</td>\n",
       "      <td>0.049233</td>\n",
       "      <td>0.054393</td>\n",
       "      <td>0.052517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.124016</td>\n",
       "      <td>11001008803</td>\n",
       "      <td>0.059458</td>\n",
       "      <td>0.045881</td>\n",
       "      <td>0.052034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.012364</td>\n",
       "      <td>11001008903</td>\n",
       "      <td>0.068926</td>\n",
       "      <td>0.051041</td>\n",
       "      <td>0.050849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.030723</td>\n",
       "      <td>11001008804</td>\n",
       "      <td>0.117023</td>\n",
       "      <td>0.041945</td>\n",
       "      <td>0.049992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.023604</td>\n",
       "      <td>11001007901</td>\n",
       "      <td>0.065707</td>\n",
       "      <td>0.047637</td>\n",
       "      <td>0.048324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.077932</td>\n",
       "      <td>11001008301</td>\n",
       "      <td>0.037304</td>\n",
       "      <td>0.045429</td>\n",
       "      <td>0.046392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.007868</td>\n",
       "      <td>11001008002</td>\n",
       "      <td>0.047150</td>\n",
       "      <td>0.041945</td>\n",
       "      <td>0.040552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.051330</td>\n",
       "      <td>11001008200</td>\n",
       "      <td>0.056618</td>\n",
       "      <td>0.036971</td>\n",
       "      <td>0.040090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.014238</td>\n",
       "      <td>11001008100</td>\n",
       "      <td>0.045825</td>\n",
       "      <td>0.040748</td>\n",
       "      <td>0.039783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.019858</td>\n",
       "      <td>11001008302</td>\n",
       "      <td>0.034463</td>\n",
       "      <td>0.040908</td>\n",
       "      <td>0.038927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.046085</td>\n",
       "      <td>11001008402</td>\n",
       "      <td>0.029161</td>\n",
       "      <td>0.037317</td>\n",
       "      <td>0.036885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.010865</td>\n",
       "      <td>11001008001</td>\n",
       "      <td>0.024617</td>\n",
       "      <td>0.037503</td>\n",
       "      <td>0.034448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.023230</td>\n",
       "      <td>11001008701</td>\n",
       "      <td>0.036167</td>\n",
       "      <td>0.032024</td>\n",
       "      <td>0.031989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0.007868</td>\n",
       "      <td>11001009203</td>\n",
       "      <td>0.016853</td>\n",
       "      <td>0.035322</td>\n",
       "      <td>0.031572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0.016860</td>\n",
       "      <td>11001007903</td>\n",
       "      <td>0.030108</td>\n",
       "      <td>0.023513</td>\n",
       "      <td>0.023887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0.047209</td>\n",
       "      <td>11001008410</td>\n",
       "      <td>0.014202</td>\n",
       "      <td>0.022342</td>\n",
       "      <td>0.022856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0.001873</td>\n",
       "      <td>11001004701</td>\n",
       "      <td>0.000757</td>\n",
       "      <td>0.013751</td>\n",
       "      <td>0.011549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0.058824</td>\n",
       "      <td>11001011100</td>\n",
       "      <td>0.011551</td>\n",
       "      <td>0.007740</td>\n",
       "      <td>0.011175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0.010116</td>\n",
       "      <td>11001009204</td>\n",
       "      <td>0.005113</td>\n",
       "      <td>0.009549</td>\n",
       "      <td>0.009068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0.005620</td>\n",
       "      <td>11001003302</td>\n",
       "      <td>0.006628</td>\n",
       "      <td>0.002075</td>\n",
       "      <td>0.002810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0.000375</td>\n",
       "      <td>11001003301</td>\n",
       "      <td>0.002272</td>\n",
       "      <td>0.002154</td>\n",
       "      <td>0.002064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0.013488</td>\n",
       "      <td>11001004600</td>\n",
       "      <td>0.003030</td>\n",
       "      <td>0.001064</td>\n",
       "      <td>0.002020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>11001009201</td>\n",
       "      <td>0.002083</td>\n",
       "      <td>0.001011</td>\n",
       "      <td>0.001076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0.008992</td>\n",
       "      <td>11001009302</td>\n",
       "      <td>0.000379</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0.008243</td>\n",
       "      <td>11001004702</td>\n",
       "      <td>0.000379</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>11001006804</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000399</td>\n",
       "      <td>0.000329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0.004496</td>\n",
       "      <td>11001009102</td>\n",
       "      <td>0.000568</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>0.001499</td>\n",
       "      <td>11001006202</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000088</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    bus_ratio        geoid  oth_ratio  res_ratio  tot_ratio\n",
       "0    0.267141  11001010600   0.073282   0.197729   0.187367\n",
       "1    0.046085  11001008904   0.085400   0.073330   0.073133\n",
       "2    0.026602  11001008802   0.075743   0.056281   0.056798\n",
       "3    0.032596  11001008702   0.049233   0.054393   0.052517\n",
       "4    0.124016  11001008803   0.059458   0.045881   0.052034\n",
       "5    0.012364  11001008903   0.068926   0.051041   0.050849\n",
       "6    0.030723  11001008804   0.117023   0.041945   0.049992\n",
       "7    0.023604  11001007901   0.065707   0.047637   0.048324\n",
       "8    0.077932  11001008301   0.037304   0.045429   0.046392\n",
       "9    0.007868  11001008002   0.047150   0.041945   0.040552\n",
       "10   0.051330  11001008200   0.056618   0.036971   0.040090\n",
       "11   0.014238  11001008100   0.045825   0.040748   0.039783\n",
       "12   0.019858  11001008302   0.034463   0.040908   0.038927\n",
       "13   0.046085  11001008402   0.029161   0.037317   0.036885\n",
       "14   0.010865  11001008001   0.024617   0.037503   0.034448\n",
       "15   0.023230  11001008701   0.036167   0.032024   0.031989\n",
       "16   0.007868  11001009203   0.016853   0.035322   0.031572\n",
       "17   0.016860  11001007903   0.030108   0.023513   0.023887\n",
       "18   0.047209  11001008410   0.014202   0.022342   0.022856\n",
       "19   0.001873  11001004701   0.000757   0.013751   0.011549\n",
       "20   0.058824  11001011100   0.011551   0.007740   0.011175\n",
       "21   0.010116  11001009204   0.005113   0.009549   0.009068\n",
       "22   0.005620  11001003302   0.006628   0.002075   0.002810\n",
       "23   0.000375  11001003301   0.002272   0.002154   0.002064\n",
       "24   0.013488  11001004600   0.003030   0.001064   0.002020\n",
       "25   0.000000  11001009201   0.002083   0.001011   0.001076\n",
       "26   0.008992  11001009302   0.000379   0.000000   0.000571\n",
       "27   0.008243  11001004702   0.000379   0.000000   0.000527\n",
       "28   0.000000  11001006804   0.000000   0.000399   0.000329\n",
       "29   0.004496  11001009102   0.000568   0.000000   0.000329\n",
       "30   0.001499  11001006202   0.000000   0.000000   0.000088"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "scrolled": true
   },
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ESTAB</td>\n",
       "      <td>EMPSZES</td>\n",
       "      <td>GEO_ID</td>\n",
       "      <td>NAICS2012</td>\n",
       "      <td>zipcode</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>232</td>\n",
       "      <td>001</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>81</td>\n",
       "      <td>212</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34</td>\n",
       "      <td>220</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>55</td>\n",
       "      <td>230</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>48</td>\n",
       "      <td>241</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>14</td>\n",
       "      <td>242</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>251</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>254</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0</td>\n",
       "      <td>260</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>72</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        0        1               2          3        4\n",
       "0   ESTAB  EMPSZES          GEO_ID  NAICS2012  zipcode\n",
       "1     232      001  8610000US20002         72    20002\n",
       "2      81      212  8610000US20002         72    20002\n",
       "3      34      220  8610000US20002         72    20002\n",
       "4      55      230  8610000US20002         72    20002\n",
       "5      48      241  8610000US20002         72    20002\n",
       "6      14      242  8610000US20002         72    20002\n",
       "7       0      251  8610000US20002         72    20002\n",
       "8       0      252  8610000US20002         72    20002\n",
       "9       0      254  8610000US20002         72    20002\n",
       "10      0      260  8610000US20002         72    20002"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cbp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ESTAB</td>\n",
       "      <td>EMPSZES</td>\n",
       "      <td>GEO_ID</td>\n",
       "      <td>NAICS2012</td>\n",
       "      <td>zipcode</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>001</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>212</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>220</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>230</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>241</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>242</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>251</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>254</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0</td>\n",
       "      <td>260</td>\n",
       "      <td>8610000US20002</td>\n",
       "      <td>721</td>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        0        1               2          3        4\n",
       "0   ESTAB  EMPSZES          GEO_ID  NAICS2012  zipcode\n",
       "1      10      001  8610000US20002        721    20002\n",
       "2       1      212  8610000US20002        721    20002\n",
       "3       1      220  8610000US20002        721    20002\n",
       "4       4      230  8610000US20002        721    20002\n",
       "5       3      241  8610000US20002        721    20002\n",
       "6       1      242  8610000US20002        721    20002\n",
       "7       0      251  8610000US20002        721    20002\n",
       "8       0      252  8610000US20002        721    20002\n",
       "9       0      254  8610000US20002        721    20002\n",
       "10      0      260  8610000US20002        721    20002"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cbpx\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "scrolled": true
   },
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>quarter</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>input</th>\n",
       "      <td>20002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>crosswalk_type</th>\n",
       "      <td>zip-tract</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>results</th>\n",
       "      <td>[{'geoid': '11001010600', 'res_ratio': 0.19772...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                0\n",
       "year                                                         2019\n",
       "quarter                                                         3\n",
       "input                                                       20002\n",
       "crosswalk_type                                          zip-tract\n",
       "results         [{'geoid': '11001010600', 'res_ratio': 0.19772..."
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "tt = 'eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImp0aSI6IjBkYmZmNGJmMjdiZjYzZjgxM2JiOTZmMGQ4NTNjZThjNzUxNjRiNTY5MGQzYThlMzc5ZDNjMDk2ZWVhM2Y3YjFhZjI0YzY2ZDNmM2NjNGFhIn0.eyJhdWQiOiI2IiwianRpIjoiMGRiZmY0YmYyN2JmNjNmODEzYmI5NmYwZDg1M2NlOGM3NTE2NGI1NjkwZDNhOGUzNzlkM2MwOTZlZWEzZjdiMWFmMjRjNjZkM2YzY2M0YWEiLCJpYXQiOjE1NzM2Nzc1OTUsIm5iZiI6MTU3MzY3NzU5NSwiZXhwIjoxNjA1Mjk5OTk1LCJzdWIiOiIzMjkiLCJzY29wZXMiOltdfQ.VdbEhWz_XL47o_A6_xTGBDQXuImIKxd7_Ee4vsksEC1-DdfgMwnouMcOqX5GggK3jnWzhkx9hnZ9ts-PKWiSIw'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImp0aSI6IjBkYmZmNGJmMjdiZjYzZjgxM2JiOTZmMGQ4NTNjZThjNzUxNjRiNTY5MGQzYThlMzc5ZDNjMDk2ZWVhM2Y3YjFhZjI0YzY2ZDNmM2NjNGFhIn0.eyJhdWQiOiI2IiwianRpIjoiMGRiZmY0YmYyN2JmNjNmODEzYmI5NmYwZDg1M2NlOGM3NTE2NGI1NjkwZDNhOGUzNzlkM2MwOTZlZWEzZjdiMWFmMjRjNjZkM2YzY2M0YWEiLCJpYXQiOjE1NzM2Nzc1OTUsIm5iZiI6MTU3MzY3NzU5NSwiZXhwIjoxNjA1Mjk5OTk1LCJzdWIiOiIzMjkiLCJzY29wZXMiOltdfQ.VdbEhWz_XL47o_A6_xTGBDQXuImIKxd7_Ee4vsksEC1-DdfgMwnouMcOqX5GggK3jnWzhkx9hnZ9ts-PKWiSIw\n"
     ]
    }
   ],
   "source": [
    "print(Bearer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "base = 'http://api.census.gov/data/'\n",
    "years = ['2012']#['2009', '2012', '2015']\n",
    "variables = {'NAME':'Name',\n",
    "             'B01001_001E': 'Population total',\n",
    "             'B19013_001E': 'Real Median Income',}\n",
    "v = ','.join(variables.keys())\n",
    "c = '*'\n",
    "s = '*'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://www.huduser.gov/hudapi/public/usps?type=1&query=22031/Authorization:\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\thttps://api.census.gov/data/2017/acs/acs1?get=NAME,B00001_001E&for=us:*&key=YOUR_KEY_GOES_HERE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame()\n",
    "for y in years:\n",
    "    url = '{}{}/acs/acs5?get={}&for=county:{}&in=state:{}&key={}'.format(\n",
    "        base, y, v, c, s, key)\n",
    "    r = requests.get(url).json()\n",
    "    dft = pd.DataFrame(r[1:], columns=r[0])\n",
    "    dft['Year'] = y\n",
    "    df = df.append(dft)\n",
    "df = df.rename(columns=variables).set_index(\n",
    "    ['Name', 'Year']).sort_index(level='Name')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#load .env with credentials\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "import os\n",
    "BASE_URL = os.getenv(\"BASE_URL\")\n",
    "API_KEY = os.getenv(\"API_KEY\")\n",
    "USERNAME = os.getenv(\"USERNAME\")\n",
    "\n",
    "set_default_credentials(\n",
    "    base_url=BASE_URL,\n",
    "    api_key=API_KEY\n",
    ")\n",
    "\n",
    "credentials = Credentials(USERNAME, API_KEY)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract: Bring GeoJson into Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "transit_stop_peak_arrival_gpdf = gpd.read_file('https://opendata.arcgis.com/datasets/0e7e79b428804da392abbb2f64cba7b2_253.geojson')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform\n",
    "Add here some code to do data transformation (remove outliers, null values, etc.). It's probably best to do this type of work on the geopandas dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load: Upload to CARTO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "transit_stop_peak_arrival_d = Dataset(transit_stop_peak_arrival_gpdf)\n",
    "transit_stop_peak_arrival_d.upload(table_name='portland_transit_stop_peak_arrival', if_exists='replace', credentials=credentials)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make a quick map using one of CARTO's helper functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "transit_stop_peak_arrival_CARTO = Dataset('portland_transit_stop_peak_arrival')\n",
    "Map(\n",
    "    color_continuous_layer(transit_stop_peak_arrival_CARTO, 'pmpeakarrivals','PM Peak Arrivals')\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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}