基于Pandas结合字典列表操作提取Registration与经纬度数据咨询

Alright, let's work through this problem together. You have a flat list of key-value strings repeating in a record pattern, and you want to extract Registration, Latitude, and Longitude into a Pandas DataFrame using a row-by-row approach. Here's a practical, step-by-step solution:

Step 1: Understand the Data Structure #

Your input is a single list where each full record starts with a Registration: entry, followed by other attributes (FileNumber, Status, etc.) until the next Registration: entry. First, we need to split this flat list into individual record groups.

Step 2: Implement the Solution #

1. Split the List into Record Groups #

We'll iterate through the list and group entries by their parent Registration record:

import pandas as pd

# Sample input data (replace with your full dataset)
sample_data = [
    "Registration:1005227",
    "FileNumber:A0485456",
    "Status:Terminated",
    "Locatedin: ABINGTON,MALat/Long:42-06-48.0N070-56-58.0W ",
    "Registration:1015227",
    "FileNumber:A0485451",
    "Locatedin: BOSTON,MALat/Long:42-21-30.0N071-03-45.0W ",
    # ... thousands more entries
]

# Split flat list into individual record groups
record_groups = []
current_group = []

for item in sample_data:
    # Start a new group when we hit a Registration entry
    if item.startswith("Registration:"):
        if current_group:
            record_groups.append(current_group)
        current_group = [item]
    else:
        current_group.append(item)
# Add the final group to the list
if current_group:
    record_groups.append(current_group)

2. Extract Target Fields from Each Group #

Next, we'll loop through each record group to pull out the fields we need. For Latitude/Longitude, we'll parse the degree-minute-second (DMS) format into decimal coordinates for easier analysis:

# Helper function to convert DMS (e.g., 42-06-48.0N) to decimal coordinates
def dms_to_decimal(dms_str):
    direction = dms_str[-1]
    degrees, minutes, seconds = map(float, dms_str[:-1].split("-"))
    decimal = degrees + (minutes / 60) + (seconds / 3600)
    # Apply negative sign for southern/western coordinates
    if direction in ["S", "W"]:
        decimal *= -1
    return decimal

# Extract target fields from each record group
extracted_records = []

for group in record_groups:
    record = {}
    for entry in group:
        # Extract Registration number
        if entry.startswith("Registration:"):
            record["Registration"] = entry.split(":", 1)[1].strip()
        # Extract Latitude and Longitude from Locatedin entry
        elif "Lat/Long:" in entry:
            lat_long_segment = entry.split("Lat/Long:", 1)[1].strip()
            # Split latitude (ends with N) and longitude (ends with W)
            lat_end_idx = lat_long_segment.index("N") + 1
            lat_str = lat_long_segment[:lat_end_idx]
            long_str = lat_long_segment[lat_end_idx:]
            
            record["Latitude"] = dms_to_decimal(lat_str)
            record["Longitude"] = dms_to_decimal(long_str)
    # Only add records that have a Registration (skip empty/incomplete entries)
    if "Registration" in record:
        extracted_records.append(record)

3. Convert to Pandas DataFrame #

Finally, turn our list of extracted records into a structured DataFrame:

df = pd.DataFrame(extracted_records)
print(df.head())

Key Notes #

• If some records lack a Lat/Long entry, the corresponding columns will show NaN — you can add default values (e.g., record["Latitude"] = None) if needed.
• The DMS-to-decimal conversion is optional; if you prefer to keep the original string format, skip that helper function and store lat_str/long_str directly.
• This approach efficiently handles thousands of records while maintaining readability and control over the extraction logic.

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