Python Forum
Census geocoding services API. How do I import output into dataframe?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Census geocoding services API. How do I import output into dataframe?
#1
I'm new to python and I'm trying to use the census geocoding services API to geocode addresses then convert the output to a dataframe. I've been able to read in my address file and I can see the output, but I can't seem to figure out how to import it into a dataframe. I provided the code I used below as well as the contents of the address file.

The output does not appear to be in JSON format, but rather CSV. I tried to import the output as I would a CSV file, but I couldn't figure out how to import the variable as I would a CSV file and I couldn't figure out how to export the output to a CSV file that I could import.

The URL describing the API is https://geocoding.geo.census.gov/geocode...es_API.pdf

import requests
import pandas as pd
import json
#url = 'https://geocoding.geo.census.gov/geocoder/locations/addressbatch'
url = 'https://geocoding.geo.census.gov/geocoder/geographies/addressbatch'
#payload = {'benchmark':'Public_AR_Current','vintage':'ACS2013_Current'}
payload = {'benchmark':'Public_AR_Current','vintage':'Current_Current'}
files = {'addressFile': ('C:\PYTHON_CLASS\CSV\ADDRESS_SAMPLE.csv', open('C:\PYTHON_CLASS\CSV\ADDRESS_SAMPLE.csv', 'rb'), 'text/csv')}
response = requests.post(url, files=files, data = payload)
type(response)
print(response.text)
-I tried

jsondata = response.json()
-and

from pandas import json_normalize
json_normalize(response.text)
-Address file

id,address,city,state,zipcode
1,1600 Pennsylvania Avenue NW, Washington,DC,20500
2,4 S Market St,Boston,MA,02109
3,1200 Getty Center Drive,Los Angeles,CA,90049
4,1800 Congress Ave,Austin,TX,78701
5,One Caesars Palace Drive,Las Vegas,NV,89109
6,1060 West Addison,Chicago,IL,60613
7,One East 161st Street,Bronx,NY,10451
8,201 E Jefferson St,Phoenix,AZ,85004
9,600 N 1st Ave,Minneapolis,MN,55403
10,400 W Church St,Orlando,FL,32801

-The output looks like this:
print(response.text)
Output:
"1","1600 Pennsylvania Avenue NW, Washington, DC, 20500","Match","Non_Exact","1600 PENNSYLVANIA AVE NW, WASHINGTON, DC, 20006","-77.03535,38.898754","76225813","L","11","001","006202","1031" "2","4 S Market St, Boston, MA, 02109","Match","Exact","4 S MARKET ST, BOSTON, MA, 02109","-71.05566,42.359936","85723841","R","25","025","030300","2017" "3","1200 Getty Center Drive, Los Angeles, CA, 90049","Match","Exact","1200 GETTY CENTER DR, LOS ANGELES, CA, 90049","-118.47564,34.08857","142816014","L","06","037","262302","1005" "4","1800 Congress Ave, Austin, TX, 78701","Match","Exact","1800 CONGRESS AVE, AUSTIN, TX, 78701","-97.73847,30.279745","63946318","L","48","453","000700","1007" "5","One Caesars Palace Drive, Las Vegas, NV, 89109","No_Match" "6","1060 West Addison, Chicago, IL, 60613","Match","Non_Exact","1060 W ADDISON ST, CHICAGO, IL, 60613","-87.65581,41.947227","111863716","R","17","031","061100","1014" "7","One East 161st Street, Bronx, NY, 10451","No_Match" "8","201 E Jefferson St, Phoenix, AZ, 85004","Match","Exact","201 E JEFFERSON ST, PHOENIX, AZ, 85004","-112.07113,33.44675","128300920","L","04","013","114100","1058" "9","600 N 1st Ave, Minneapolis, MN, 55403","No_Match" "id","address, city, state, zipcode","No_Match" "10","400 W Church St, Orlando, FL, 32801","Match","Exact","400 W CHURCH ST, ORLANDO, FL, 32801","-81.38436,28.540176","94416807","L","12","095","010500","1002"
Reply
#2
google "openstreetmap geocoding api python"
I think you'll be pleasantly surprised.
Reply
#3
(Nov-09-2020, 03:31 AM)Larz60+ Wrote: google "openstreetmap geocoding api python"
I think you'll be pleasantly surprised.

Thanks, Larz60+. I'm sure your suggestion would be great. However, I'm validating an IT process that uses Census data so I'm really looking for a census data solution.
Reply
#4
This was answered in Stack Overflow. The full code working code can be found below.

import requests
import pandas as pd
import io
import csv
url = 'https://geocoding.geo.census.gov/geocoder/geographies/addressbatch'
payload = {'benchmark':'Public_AR_Current','vintage':'Current_Current'}
files = {'addressFile': ('C:\PYTHON_CLASS\CSV\ADDRESS_SAMPLE.csv', open('C:\PYTHON_CLASS\CSV\ADDRESS_SAMPLE.csv', 'rb'), 'text/csv')}
s = requests.post(url, files=files, data = payload)
df = pd.read_csv(io.StringIO(s.text), sep=',', header=None, quoting=csv.QUOTE_ALL)
with pd.option_context(
    'display.width', None, 
    'display.max_columns', None,
    'display.max_colwidth', -1,
    'display.colheader_justify', 'left'):
    print(df)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Import output from python script to another varietyjones 1 1,877 Oct-12-2020, 09:07 PM
Last Post: bowlofred
  Import Text, output curve geometry Alyner 0 1,932 Feb-03-2020, 03:05 AM
Last Post: Alyner
  Suggestions Needed For Free Hosting Services for my non-Django App Loophole16 2 6,335 Oct-05-2016, 06:04 PM
Last Post: Loophole16

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020