使用geopy库实现Pandas数据框地址转经纬度时遇错误求助

Hey there! Let's work through your geopy + Pandas geocoding issue together. First, let's fix up the incomplete code you shared, then break down the most common errors you might be facing and how to resolve them.

Your code is missing a few critical pieces—like importing Pandas, completing the proxy setup, and configuring the GoogleV3 geocoder properly. Here's a cleaned-up, functional version:

import time
import pandas as pd
from geopy.geocoders import GoogleV3
import os

# Set up proxy (skip if you don't need one)
def set_proxy():
    proxy_addr = 'http://{user}:{passwd}@{address}:{port}'.format(
        user='usuario',
        passwd='senha',
        address='IP',
        port=int('PORTA')
    )
    # Configure both HTTP and HTTPS proxies
    os.environ['http_proxy'] = proxy_addr
    os.environ['https_proxy'] = proxy_addr

# Initialize GoogleV3 (REQUIRES a valid API key)
set_proxy()  # Comment this line if no proxy is needed
# Replace with your actual Google Geocoding API key
geolocator = GoogleV3(api_key='YOUR_GOOGLE_API_KEY')

# Load Excel data
arquivo = pd.ExcelFile('path/to/your/file.xlsx')
df = arquivo.parse("Table1")

# Function to get lat/long with error handling and rate limiting
def get_lat_lon(address):
    try:
        time.sleep(1)  # Avoid hitting Google's rate limits (free tier has quotas)
        location = geolocator.geocode(address)
        if location:
            return (location.latitude, location.longitude)
        else:
            return (None, None)
    except Exception as e:
        print(f"Failed to process address '{address}': {str(e)}")
        return (None, None)

# Add lat/long columns to your DataFrame
# Replace 'your_address_column' with the actual column name containing addresses
df['latitude'], df['longitude'] = zip(*df['your_address_column'].apply(get_lat_lon))

# Save the results
df.to_excel('geocoded_results.xlsx', index=False)

Here are the most likely issues causing your error, plus fixes:

• Invalid/Missing Google API Key

  Typical error: GeocoderAuthenticationError: 403 Forbidden
  Fix: You need to enable the Google Geocoding API in the Google Cloud Console, generate a valid API key, and ensure the key has no IP restrictions or exhausted quotas.

• Proxy Connection Failures

  Typical error: GeocoderUnavailable: HTTPSConnectionPool(...)
  Fix: Double-check your proxy address, username, and password. If your proxy blocks Google domains, add os.environ['NO_PROXY'] = 'google.com' to your proxy setup function. Or skip the proxy entirely if your network allows direct access.

• Rate Limit Exceeded

  Typical error: GeocoderQuotaExceeded: 429 Too Many Requests
  Fix: Increase the time.sleep() duration (try 2-3 seconds), upgrade your Google API quota, or use batch geocoding to reduce the number of requests.

• Address Parsing Failures

  Symptom: Lat/long values are all None
  Fix: Ensure your addresses are formatted correctly (include city, country, etc., for better accuracy). Add preprocessing steps to clean addresses (e.g., remove extra spaces, standardize abbreviations).

If you're working with a big dataset, using apply can be slow. Try parallel processing (just don't overdo it to avoid rate limits):

from concurrent.futures import ThreadPoolExecutor

def get_lat_lon_parallel(address):
    try:
        location = geolocator.geocode(address)
        return (location.latitude if location else None, location.longitude if location else None)
    except Exception as e:
        print(f"Error with address '{address}': {e}")
        return (None, None)

# Use 5 threads to balance speed and rate limits
with ThreadPoolExecutor(max_workers=5) as executor:
    results = list(executor.map(get_lat_lon_parallel, df['your_address_column']))

df[['latitude', 'longitude']] = pd.DataFrame(results, index=df.index)

内容的提问来源于stack exchange，提问作者Clayton Tosatti