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