Nov-08-2020, 09:52 PM
(This post was last modified: Nov-08-2020, 10:20 PM by SAS2PYTHON.)
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
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:
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"