ECSP

# CrossCompute
ready_table_path = 'Ready table with geometry points.csv'
search_radius_in_miles = 5

user_address = '28-10 Jackson Ave'    # this address can be located
#user_address = '236-238 25TH STREET' # this is an address geocode can't locate
target_folder = '/tmp'

from geopy import GoogleV3
geocode = GoogleV3('AIzaSyDNqc0tWzXHx_wIp1w75-XTcCk4BSphB5w').geocode 
x = geocode(user_address)
if x is None:
    print("No location!")
else:
    user_coor = x.longitude, x.latitude 

x

x.longitude

x.latitude

type(user_coor)

# Load inputs
import pandas as pd
import numpy as np
ready_table = pd.read_csv(ready_table_path)
ready_table=ready_table.drop([#'Total Tree Count within 0.5 Mile','Periodic Savings within 0.5 Mile',
                            'BIN','Latitude','Longitude',
                            'Unnamed: 0','Unnamed: 0.1','Unnamed: 0.1.1'], axis=1)
ready_table[:3]

addresses = ready_table['Address'].tolist()
addresses[:3]

import subprocess
subprocess.call('pip install geopandas'.split())

import pandas as pd
import requests
import logging
import time

logger = logging.getLogger('root')
logger.setLevel(logging.DEBUG)
# create console handler
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
logger.addHandler(ch)

#------------------ CONFIGURATION -------------------------------

# Set your Google API key here. 
# Even if using the free 2500 queries a day, its worth getting an API key since the rate limit is 50 / second.
# With API_KEY = None, you will run into a 2 second delay every 10 requests or so.
# With a 'Google Maps Geocoding API' key from https://console.developers.google.com/apis/, 
# the daily limit will be 2500, but at a much faster rate.

API_KEY = 'AIzaSyDNqc0tWzXHx_wIp1w75-XTcCk4BSphB5w'
# Backoff time sets how many minutes to wait between google pings when your API limit is hit
BACKOFF_TIME = 30
# Set your output file name here.
output_filename = 'Ready table with convertable addresses.csv'
# Set your input file here
input_filename = 'Ready table with geometry points.csv'
# Specify the column name in your input data that contains addresses here
address_column_name = 'Address'
# Return Full Google Results? If True, full JSON results from Google are included in output
RETURN_FULL_RESULTS = False

#------------------ DATA LOADING --------------------------------

# Read the data to a Pandas Dataframe
data = pd.read_csv(input_filename, encoding='utf8')

if address_column_name not in data.columns:
    raise ValueError('Missing Address column in input data')

addresses = data['Address'].tolist()

#------------------	FUNCTION DEFINITIONS ------------------------

def get_google_results(address, api_key='AIzaSyDNqc0tWzXHx_wIp1w75-XTcCk4BSphB5w', return_full_response=False):

    # Set up your Geocoding url
    geocode_url = 'https://maps.googleapis.com/maps/api/geocode/json?address={}'.format(address)
    if api_key is not None:
        geocode_url = geocode_url + '&key={}'.format(api_key)
        
    # Ping google for the reuslts:
    results = requests.get(geocode_url)
    # Results will be in JSON format - convert to dict using requests functionality
    results = results.json()
    
    # if there's no results or an error, return empty results.
    if len(results['results']) == 0:
        output = {
            'formatted_address' : None,
            'latitude': None,
            'longitude': None,
        }
    else:    
        answer = results['results'][0]
        output = {
            'formatted_address' : answer.get('formatted_address'),
            'latitude': answer.get('geometry').get('location').get('lat'),
            'longitude': answer.get('geometry').get('location').get('lng'),
}
        
    # Append some other details:    
    output['input_string'] = address
    #output['number_of_results'] = len(results['results'])
    output['status'] = results.get('status')
    if return_full_response is True:
        output['response'] = results
    
    return output

#------------------ PROCESSING LOOP -----------------------------

# Create a list to hold results
results = []
# Go through each address in turn
for address in addresses:
    # While the address geocoding is not finished:
    geocoded = False
    while geocoded is not True:
        # Geocode the address with google
        try:
            geocode_result = get_google_results(address, API_KEY, return_full_response=RETURN_FULL_RESULTS)
        except Exception as e:
            logger.exception(e)
            logger.error('Major error with {}'.format(address))
            logger.error('Skipping!')
            geocoded = True
            
        # If we're over the API limit, backoff for a while and try again later.
        if geocode_result['status'] == 'OVER_QUERY_LIMIT':
            logger.info('Hit Query Limit! Backing off for a bit.')
            time.sleep(BACKOFF_TIME * 60) # sleep for 30 minutes
            geocoded = False
        else:
            # If we're ok with API use, save the results
            # Note that the results might be empty / non-ok - log this
            if geocode_result['status'] != 'OK':
                logger.warning('Error geocoding {}: {}'.format(address, geocode_result['status']))
            logger.debug('Geocoded: {}: {}'.format(address, geocode_result['status']))
            results.append(geocode_result)           
            geocoded = True

    # Print status every 100 addresses
    if len(results) % 100 == 0:
        logger.info('Completed {} of {} address'.format(len(results), len(addresses)))
            
    # Every 500 addresses, save progress to file(in case of a failure so you have something!)
    if len(results) % 500 == 0:
        pd.DataFrame(results).to_csv('{}_bak'.format(output_filename))

# All done
logger.info('Finished geocoding all addresses')
# Write the full results to csv using the pandas library.
pd.DataFrame(results).to_csv(output_filename, encoding='utf8')

geocoded_table = pd.read_csv('Ready table with convertable addresses.csv')
#geocoded_table=pd.DataFrame(results)
geocoded_table = geocoded_table.rename(columns={'input_string': 'Address',
                                               'input_string': 'Address'}) 
len(geocoded_table)

cannot_converted_table=geocoded_table[geocoded_table['status'] != 'OK'][['Address','formatted_address']]
cannot_converted_table

ready_table.head()

merged_table = pd.merge(ready_table,
                 geocoded_table[['Address','formatted_address','status']],
                 on='Address', how='right')
df2 = merged_table[merged_table.status == 'OK']
df2 = df2.drop(df2[(df2.status != 'OK')].index)
df2 = df2.drop_duplicates()
df2

# Save your output files in target_folder
target_folder = '/tmp'
target_path = target_folder + '/Ready Table with 490 rows.csv'
df2.to_csv(target_path, index=False)

# Render the file as a table
print('final_table_path = %s' % target_path)

df2.to_csv('Ready Table with 490 rows.csv', sep=',')