This code reads an Excel file, filters the data to keep only the facilities we would like the geocoordinates for. It then uses Google's Geocoding API to find the latitude and longitude for each address. These coordinates are added to the DataFrame and are saved back to an Excel file.
The following demonstration is using the dataset found here
# Import required libraries
import requests
import json
import pandas as pd
import time
# Function to use Google's Geocoding API
def geocode(address, api_key):
# Define API endpoint
url = "https://maps.googleapis.com/maps/api/geocode/json"
# Parameters for API request
params = {
'address': address, # Address to geocode
'key': api_key # API Key
}
# Make the GET request
response = requests.get(url, params=params)
# If the request is successful
if response.status_code == 200:
# Load the JSON response
data = json.loads(response.text)
# If 'results' are present in response
if 'results' in data and len(data['results']) > 0:
# Extract latitude and longitude
location = data['results'][0]['geometry']['location']
return location['lat'], location['lng']
# Return None if request unsuccessful or results not found
return None, None
# Load the data from the Excel file
data = pd.read_excel("PATH_TO_YOUR_FILE") # replace with your file path
# Filter the data for facilities with Component/Service type of 'CAPS infanto-juvenil'
filtered_data = data[data['\nComponent / Service'] == 'CAPS infanto-juvenil'].copy()
# Create columns for latitude and longitude in the DataFrame
filtered_data['LATITUDE'] = None
filtered_data['LONGITUDE'] = None
# Google Cloud API Key
api_key = "YOUR_API_KEY" # replace with your actual API key
# Iterate over the rows of the DataFrame
for idx, row in filtered_data.iterrows():
# Create the full address string
address = f"{row['STREET NAME']} {row['ADDRESS NUMBER']}, {row['CITY']}, Brazil"
# Use the geocode function to get the latitude and longitude
latitude, longitude = geocode(address, api_key)
# If valid latitude and longitude are returned
if latitude and longitude:
# Store the latitude and longitude in the DataFrame
filtered_data.at[idx, 'LATITUDE'] = latitude
filtered_data.at[idx, 'LONGITUDE'] = longitude
# Sleep for 1 second to avoid hitting the API rate limit
time.sleep(1)
# Save the DataFrame with latitude and longitude data to an Excel file
filtered_data.to_excel("PATH_TO_SAVE_YOUR_FILE", index=False) # replace with your desired save path