从DataFrame文本列提取村庄名并进行地理编码的技术需求

Got it, let's break this down clearly. You've got two DataFrames to work with: event (with District and City columns, where City is messy text hiding village names—even with typos like "atvillage2") and villages (with District and Village columns). Here's a step-by-step solution to extract those village names and geocode them properly:

Step 1: Extract Village Names (Even with Typos) #

First, we’ll use the villages DataFrame to target valid village names, then use regex to pull them out of the City text—even when they’re stuck to preceding words. We’ll also tie matches to the correct District to avoid cross-district mismatches.

import pandas as pd
import re

# Group villages by their District for targeted, accurate matching
village_groups = villages.groupby('District')['Village'].apply(list).to_dict()

def extract_village(row):
    # Get all villages associated with the row's District
    district_villages = village_groups.get(row['District'], [])
    if not district_villages:
        return None
    
    # Build a regex pattern to match any village in the district (case-insensitive)
    # Re.escape handles villages with special characters
    pattern = re.compile(r'(' + '|'.join(re.escape(v) for v in district_villages) + r')', re.IGNORECASE)
    
    # Search the messy City text for a matching village
    match = pattern.search(row['City'])
    return match.group(1) if match else None

# Apply the extraction to the event DataFrame
event['Extracted_Village'] = event.apply(extract_village, axis=1)

This will pull out village2 from "Regional gatherting atvillage2" because the regex targets the exact village name, regardless of what’s immediately before it.

Step 2: Geocode the Extracted Villages #

Once we have clean village names paired with their districts, we can use a geocoding service (like OpenStreetMap’s Nominatim) to get latitude/longitude coordinates. We’ll add a rate limiter to avoid hitting API limits.

from geopy.geocoders import Nominatim
from geopy.extra.rate_limiter import RateLimiter

# Initialize geocoder with a custom user agent (required by Nominatim's terms)
geolocator = Nominatim(user_agent="your_village_geocoder_app")
# Add a delay between requests to comply with API rules
geocode = RateLimiter(geolocator.geocode, min_delay_seconds=1)

# Create a full address string for better geocoding accuracy
event['Full_Address'] = event.apply(lambda row: f"{row['Extracted_Village']}, {row['District']}", axis=1)

# Geocode each address
event['Geocode_Result'] = event['Full_Address'].apply(geocode)

# Extract latitude and longitude into separate, usable columns
event['Latitude'] = event['Geocode_Result'].apply(lambda x: x.latitude if x else None)
event['Longitude'] = event['Geocode_Result'].apply(lambda x: x.longitude if x else None)

Step 3: Handle Edge Cases #

• If some City entries don’t have a matching village, Extracted_Village will be None—you can filter these out with event = event.dropna(subset=['Extracted_Village']) if needed.
• If geocoding fails (e.g., the village name is too vague), Latitude/Longitude will be None—you can add fallback logic or manually verify these entries.

内容的提问来源于stack exchange，提问作者Adarsha Murthy