Census geocoding services API. How do I import output into dataframe?

Census geocoding services API. How do I import output into dataframe? - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Census geocoding services API. How do I import output into dataframe? (/thread-30828.html)

Census geocoding services API. How do I import output into dataframe? - SAS2PYTHON - Nov-08-2020

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/geocoder/Geocoding_Services_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"

RE: Census geocoding services API. How do I import output into dataframe? - Larz60+ - Nov-09-2020

google "openstreetmap geocoding api python"
I think you'll be pleasantly surprised.

RE: Census geocoding services API. How do I import output into dataframe? - SAS2PYTHON - Nov-09-2020

(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.

RE: Census geocoding services API. How do I import output into dataframe? - SAS2PYTHON - Nov-10-2020

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)