I am creating an API to retrieve census data, but I am having problems formatting the output. My question is really one of two things:
1) How can I improve my API call so that the result is more beautiful (ideally, a data framework)
or
2) How can I manipulate the list that I am currently getting to be in the pandas data frame?
Here is what I still have:
import requests import pandas as pd import numpy as np mytoken = "numbersandletters" # this is my API key, so unfortunately I can't provide it def state_data(token, variables, year = 2010, state = "*", survey = "sf1"): state = [str(i) for i in state] # make sure the input for state (integers) are strings variables = ",".join(variables) # squish all the variables into one string year = str(year) combine = ["http://api.census.gov/data/", year, "/", survey, "?key=", mytoken, "&get=", variables, "&for=state:"] # make a list of all the components to construct a URL incomplete_url = "".join(combine) # the URL without the state tackd on to the end complete_url = map(lambda i: incomplete_url + i, state) # now the state is tacked on to the end; one URL per state or for "*" r = [] r = map(lambda i: requests.get(i), complete_url) # make an API call to each complete_url data = map(lambda i: i.json(), r) print r print data print type(data) df = pd.DataFrame(data) print df
An example of a function call is this, with the output below.
state_data(token = mytoken, state = [47, 48, 49, 50], variables = ["P0010001", "P0010001"])
as a result of:
[<Response [200]>, <Response [200]>, <Response [200]>, <Response [200]>] [[[u'P0010001', u'P0010001', u'state'], [u'6346105', u'6346105', u'47']], [[u'P0010001', u'P0010001', u'state'], [u'25145561', u'25145561', u'48']], [[u'P0010001', u'P0010001', u'state'], [u'2763885', u'2763885', u'49']], [[u'P0010001', u'P0010001', u'state'], [u'625741', u'625741', u'50']]] <type 'list'> 0 1 0 [P0010001, P0010001, state] [6346105, 6346105, 47] 1 [P0010001, P0010001, state] [25145561, 25145561, 48] 2 [P0010001, P0010001, state] [2763885, 2763885, 49] 3 [P0010001, P0010001, state] [625741, 625741, 50]
While the desired result:
P0010001 P0010001 state 0 6346105 6346105 47 1 25145561 25145561 48 2 2763885 2763885 49 3 625741 625741 50
Fwiw, similar code in R below. I am rewriting a library written in R in Python:
state.data = function(token, state = "*", variables, year = 2010, survey = "sf1"){ state = as.character(state) variables = paste(variables, collapse = ",") year = as.character(year) my.url = matrix(paste("http://api.census.gov/data/", year, "/", survey, "?key=", token, "&get=",variables, "&for=state:", state, sep = ""), ncol = 1) process.url = apply(my.url, 1, function(x) process.api.data(fromJSON(file=url(x)))) rbind.dat = data.frame(rbindlist(process.url)) rbind.dat = rbind.dat[, c(tail(seq_len(ncol(rbind.dat)), 1), seq_len(ncol(rbind.dat) - 1))] rbind.dat }