notebooks/census_reporter_quick_queries.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "zips = [90049, 60608, 60615]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "API_URL=\"http://api.censusreporter.org/1.0/data/show/{release}?table_ids={table_ids}&geo_ids={geoids}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_data(tables=None, geoids=None, release='latest'):\n",
    "    if geoids is None:\n",
    "        geoids = ['040|01000US']\n",
    "    if tables is None:\n",
    "        tables = ['B01001']\n",
    "\n",
    "    url = API_URL.format(table_ids=','.join(tables).upper(), \n",
    "                         geoids=','.join(geoids), \n",
    "                         release=release)\n",
    "    response = requests.get(url)\n",
    "    return response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def prep_for_pandas(json_data,include_moe=False):\n",
    "    \"\"\"Given a dict of dicts as they come from a Census Reporter API call, set it up to be amenable \n",
    "    to pandas.DataFrame.from_dict\"\"\"\n",
    "    result = {}\n",
    "    for geoid, tables in json_data.items():\n",
    "        flat = {}\n",
    "        for table,values in tables.items():\n",
    "            for kind, columns in values.items():\n",
    "                if kind == 'estimate':\n",
    "                    flat.update(columns)\n",
    "                elif kind == 'error' and include_moe:\n",
    "                    renamed = dict((k+\"_moe\",v) for k,v in columns.items())\n",
    "                    flat.update(renamed)\n",
    "        result[geoid] = flat\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "response = get_data(geoids = [\"86000US\" + str(z) for z in zips], tables = ['B17001'])\n",
    "df = pd.DataFrame.from_dict(prep_for_pandas(response['data']),orient='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B17001010</th>\n",
       "      <th>B17001049</th>\n",
       "      <th>B17001033</th>\n",
       "      <th>B17001053</th>\n",
       "      <th>B17001031</th>\n",
       "      <th>B17001018</th>\n",
       "      <th>B17001005</th>\n",
       "      <th>B17001020</th>\n",
       "      <th>B17001057</th>\n",
       "      <th>B17001041</th>\n",
       "      <th>...</th>\n",
       "      <th>B17001052</th>\n",
       "      <th>B17001035</th>\n",
       "      <th>B17001008</th>\n",
       "      <th>B17001024</th>\n",
       "      <th>B17001042</th>\n",
       "      <th>B17001014</th>\n",
       "      <th>B17001059</th>\n",
       "      <th>B17001055</th>\n",
       "      <th>B17001017</th>\n",
       "      <th>B17001037</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>86000US60608</th>\n",
       "      <td>1329</td>\n",
       "      <td>1662</td>\n",
       "      <td>1721</td>\n",
       "      <td>1857</td>\n",
       "      <td>50017</td>\n",
       "      <td>1123</td>\n",
       "      <td>230</td>\n",
       "      <td>1229</td>\n",
       "      <td>2658</td>\n",
       "      <td>3988</td>\n",
       "      <td>...</td>\n",
       "      <td>494</td>\n",
       "      <td>1845</td>\n",
       "      <td>102</td>\n",
       "      <td>1376</td>\n",
       "      <td>3085</td>\n",
       "      <td>802</td>\n",
       "      <td>1336</td>\n",
       "      <td>3076</td>\n",
       "      <td>10976</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86000US60615</th>\n",
       "      <td>1776</td>\n",
       "      <td>653</td>\n",
       "      <td>966</td>\n",
       "      <td>1157</td>\n",
       "      <td>30199</td>\n",
       "      <td>341</td>\n",
       "      <td>54</td>\n",
       "      <td>378</td>\n",
       "      <td>2245</td>\n",
       "      <td>2008</td>\n",
       "      <td>...</td>\n",
       "      <td>198</td>\n",
       "      <td>925</td>\n",
       "      <td>24</td>\n",
       "      <td>948</td>\n",
       "      <td>1504</td>\n",
       "      <td>256</td>\n",
       "      <td>975</td>\n",
       "      <td>2214</td>\n",
       "      <td>5029</td>\n",
       "      <td>257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86000US90049</th>\n",
       "      <td>85</td>\n",
       "      <td>794</td>\n",
       "      <td>671</td>\n",
       "      <td>1060</td>\n",
       "      <td>32561</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>2353</td>\n",
       "      <td>2365</td>\n",
       "      <td>...</td>\n",
       "      <td>204</td>\n",
       "      <td>916</td>\n",
       "      <td>0</td>\n",
       "      <td>205</td>\n",
       "      <td>1878</td>\n",
       "      <td>102</td>\n",
       "      <td>1618</td>\n",
       "      <td>2509</td>\n",
       "      <td>1313</td>\n",
       "      <td>247</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              B17001010  B17001049  B17001033  B17001053  B17001031  \\\n",
       "86000US60608       1329       1662       1721       1857      50017   \n",
       "86000US60615       1776        653        966       1157      30199   \n",
       "86000US90049         85        794        671       1060      32561   \n",
       "\n",
       "              B17001018  B17001005  B17001020  B17001057  B17001041  \\\n",
       "86000US60608       1123        230       1229       2658       3988   \n",
       "86000US60615        341         54        378       2245       2008   \n",
       "86000US90049          0          0         18       2353       2365   \n",
       "\n",
       "                ...      B17001052  B17001035  B17001008  B17001024  \\\n",
       "86000US60608    ...            494       1845        102       1376   \n",
       "86000US60615    ...            198        925         24        948   \n",
       "86000US90049    ...            204        916          0        205   \n",
       "\n",
       "              B17001042  B17001014  B17001059  B17001055  B17001017  B17001037  \n",
       "86000US60608       3085        802       1336       3076      10976        203  \n",
       "86000US60615       1504        256        975       2214       5029        257  \n",
       "86000US90049       1878        102       1618       2509       1313        247  \n",
       "\n",
       "[3 rows x 59 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B17001010</th>\n",
       "      <th>B17001049</th>\n",
       "      <th>B17001033</th>\n",
       "      <th>B17001053</th>\n",
       "      <th>B17001031</th>\n",
       "      <th>B17001018</th>\n",
       "      <th>B17001005</th>\n",
       "      <th>B17001020</th>\n",
       "      <th>B17001057</th>\n",
       "      <th>B17001041</th>\n",
       "      <th>...</th>\n",
       "      <th>B17001052</th>\n",
       "      <th>B17001035</th>\n",
       "      <th>B17001008</th>\n",
       "      <th>B17001024</th>\n",
       "      <th>B17001042</th>\n",
       "      <th>B17001014</th>\n",
       "      <th>B17001059</th>\n",
       "      <th>B17001055</th>\n",
       "      <th>B17001017</th>\n",
       "      <th>B17001037</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1063.333333</td>\n",
       "      <td>1036.333333</td>\n",
       "      <td>1119.333333</td>\n",
       "      <td>1358.000000</td>\n",
       "      <td>37592.333333</td>\n",
       "      <td>488.000000</td>\n",
       "      <td>94.666667</td>\n",
       "      <td>541.666667</td>\n",
       "      <td>2418.666667</td>\n",
       "      <td>2787.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>298.666667</td>\n",
       "      <td>1228.666667</td>\n",
       "      <td>42.000000</td>\n",
       "      <td>843.000000</td>\n",
       "      <td>2155.666667</td>\n",
       "      <td>386.666667</td>\n",
       "      <td>1309.666667</td>\n",
       "      <td>2599.666667</td>\n",
       "      <td>5772.666667</td>\n",
       "      <td>235.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>876.244448</td>\n",
       "      <td>546.410407</td>\n",
       "      <td>541.533317</td>\n",
       "      <td>434.859748</td>\n",
       "      <td>10824.694792</td>\n",
       "      <td>575.750814</td>\n",
       "      <td>120.271914</td>\n",
       "      <td>621.868421</td>\n",
       "      <td>214.187612</td>\n",
       "      <td>1055.302326</td>\n",
       "      <td>...</td>\n",
       "      <td>169.190228</td>\n",
       "      <td>533.779293</td>\n",
       "      <td>53.329167</td>\n",
       "      <td>592.519198</td>\n",
       "      <td>826.265292</td>\n",
       "      <td>367.838733</td>\n",
       "      <td>322.307824</td>\n",
       "      <td>438.093978</td>\n",
       "      <td>4874.235564</td>\n",
       "      <td>28.728615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>85.000000</td>\n",
       "      <td>653.000000</td>\n",
       "      <td>671.000000</td>\n",
       "      <td>1060.000000</td>\n",
       "      <td>30199.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>2245.000000</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>198.000000</td>\n",
       "      <td>916.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>205.000000</td>\n",
       "      <td>1504.000000</td>\n",
       "      <td>102.000000</td>\n",
       "      <td>975.000000</td>\n",
       "      <td>2214.000000</td>\n",
       "      <td>1313.000000</td>\n",
       "      <td>203.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>707.000000</td>\n",
       "      <td>723.500000</td>\n",
       "      <td>818.500000</td>\n",
       "      <td>1108.500000</td>\n",
       "      <td>31380.000000</td>\n",
       "      <td>170.500000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>198.000000</td>\n",
       "      <td>2299.000000</td>\n",
       "      <td>2186.500000</td>\n",
       "      <td>...</td>\n",
       "      <td>201.000000</td>\n",
       "      <td>920.500000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>576.500000</td>\n",
       "      <td>1691.000000</td>\n",
       "      <td>179.000000</td>\n",
       "      <td>1155.500000</td>\n",
       "      <td>2361.500000</td>\n",
       "      <td>3171.000000</td>\n",
       "      <td>225.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1329.000000</td>\n",
       "      <td>794.000000</td>\n",
       "      <td>966.000000</td>\n",
       "      <td>1157.000000</td>\n",
       "      <td>32561.000000</td>\n",
       "      <td>341.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>378.000000</td>\n",
       "      <td>2353.000000</td>\n",
       "      <td>2365.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>204.000000</td>\n",
       "      <td>925.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>948.000000</td>\n",
       "      <td>1878.000000</td>\n",
       "      <td>256.000000</td>\n",
       "      <td>1336.000000</td>\n",
       "      <td>2509.000000</td>\n",
       "      <td>5029.000000</td>\n",
       "      <td>247.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1552.500000</td>\n",
       "      <td>1228.000000</td>\n",
       "      <td>1343.500000</td>\n",
       "      <td>1507.000000</td>\n",
       "      <td>41289.000000</td>\n",
       "      <td>732.000000</td>\n",
       "      <td>142.000000</td>\n",
       "      <td>803.500000</td>\n",
       "      <td>2505.500000</td>\n",
       "      <td>3176.500000</td>\n",
       "      <td>...</td>\n",
       "      <td>349.000000</td>\n",
       "      <td>1385.000000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>1162.000000</td>\n",
       "      <td>2481.500000</td>\n",
       "      <td>529.000000</td>\n",
       "      <td>1477.000000</td>\n",
       "      <td>2792.500000</td>\n",
       "      <td>8002.500000</td>\n",
       "      <td>252.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1776.000000</td>\n",
       "      <td>1662.000000</td>\n",
       "      <td>1721.000000</td>\n",
       "      <td>1857.000000</td>\n",
       "      <td>50017.000000</td>\n",
       "      <td>1123.000000</td>\n",
       "      <td>230.000000</td>\n",
       "      <td>1229.000000</td>\n",
       "      <td>2658.000000</td>\n",
       "      <td>3988.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>494.000000</td>\n",
       "      <td>1845.000000</td>\n",
       "      <td>102.000000</td>\n",
       "      <td>1376.000000</td>\n",
       "      <td>3085.000000</td>\n",
       "      <td>802.000000</td>\n",
       "      <td>1618.000000</td>\n",
       "      <td>3076.000000</td>\n",
       "      <td>10976.000000</td>\n",
       "      <td>257.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         B17001010    B17001049    B17001033    B17001053     B17001031  \\\n",
       "count     3.000000     3.000000     3.000000     3.000000      3.000000   \n",
       "mean   1063.333333  1036.333333  1119.333333  1358.000000  37592.333333   \n",
       "std     876.244448   546.410407   541.533317   434.859748  10824.694792   \n",
       "min      85.000000   653.000000   671.000000  1060.000000  30199.000000   \n",
       "25%     707.000000   723.500000   818.500000  1108.500000  31380.000000   \n",
       "50%    1329.000000   794.000000   966.000000  1157.000000  32561.000000   \n",
       "75%    1552.500000  1228.000000  1343.500000  1507.000000  41289.000000   \n",
       "max    1776.000000  1662.000000  1721.000000  1857.000000  50017.000000   \n",
       "\n",
       "         B17001018   B17001005    B17001020    B17001057    B17001041  \\\n",
       "count     3.000000    3.000000     3.000000     3.000000     3.000000   \n",
       "mean    488.000000   94.666667   541.666667  2418.666667  2787.000000   \n",
       "std     575.750814  120.271914   621.868421   214.187612  1055.302326   \n",
       "min       0.000000    0.000000    18.000000  2245.000000  2008.000000   \n",
       "25%     170.500000   27.000000   198.000000  2299.000000  2186.500000   \n",
       "50%     341.000000   54.000000   378.000000  2353.000000  2365.000000   \n",
       "75%     732.000000  142.000000   803.500000  2505.500000  3176.500000   \n",
       "max    1123.000000  230.000000  1229.000000  2658.000000  3988.000000   \n",
       "\n",
       "          ...       B17001052    B17001035   B17001008    B17001024  \\\n",
       "count     ...        3.000000     3.000000    3.000000     3.000000   \n",
       "mean      ...      298.666667  1228.666667   42.000000   843.000000   \n",
       "std       ...      169.190228   533.779293   53.329167   592.519198   \n",
       "min       ...      198.000000   916.000000    0.000000   205.000000   \n",
       "25%       ...      201.000000   920.500000   12.000000   576.500000   \n",
       "50%       ...      204.000000   925.000000   24.000000   948.000000   \n",
       "75%       ...      349.000000  1385.000000   63.000000  1162.000000   \n",
       "max       ...      494.000000  1845.000000  102.000000  1376.000000   \n",
       "\n",
       "         B17001042   B17001014    B17001059    B17001055     B17001017  \\\n",
       "count     3.000000    3.000000     3.000000     3.000000      3.000000   \n",
       "mean   2155.666667  386.666667  1309.666667  2599.666667   5772.666667   \n",
       "std     826.265292  367.838733   322.307824   438.093978   4874.235564   \n",
       "min    1504.000000  102.000000   975.000000  2214.000000   1313.000000   \n",
       "25%    1691.000000  179.000000  1155.500000  2361.500000   3171.000000   \n",
       "50%    1878.000000  256.000000  1336.000000  2509.000000   5029.000000   \n",
       "75%    2481.500000  529.000000  1477.000000  2792.500000   8002.500000   \n",
       "max    3085.000000  802.000000  1618.000000  3076.000000  10976.000000   \n",
       "\n",
       "        B17001037  \n",
       "count    3.000000  \n",
       "mean   235.666667  \n",
       "std     28.728615  \n",
       "min    203.000000  \n",
       "25%    225.000000  \n",
       "50%    247.000000  \n",
       "75%    252.000000  \n",
       "max    257.000000  \n",
       "\n",
       "[8 rows x 59 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}