如何使用R或Python从美国政府公开数据源的XML文件中提取特定字段并解析同节点多字段以生成规整的关系型数据库/表格

Got it, let's tackle this XML parsing problem step by step. The core issue here is that your target fields (like Last Name, First Name) are stored as key-value pairs inside a single XML node, instead of being separate child nodes. Here's how you can split them into a structured table using Python and R respectively:

Python Implementation #

We'll use the built-in xml.etree.ElementTree for XML parsing and pandas to build our final table. Let's assume your XML structure looks something like this (matching the key-value pairs you mentioned):

<Record>
  <Details>
    Last Name: Doe
    First Name: John
    Mid Name: Bob
    Location: New York
  </Details>
</Record>

Step-by-Step Code #

import xml.etree.ElementTree as ET
import pandas as pd

# 1. Parse the XML file
tree = ET.parse("your_government_data.xml")
root = tree.getroot()

# 2. Initialize a list to hold all parsed records
parsed_records = []

# 3. Iterate through each record in the XML
for record in root.findall(".//Record"):
    # Get the text content from the target node (e.g., <Details>)
    details_text = record.find("Details").text.strip()
    
    # 4. Split the text into individual lines, filter out empty lines
    lines = [line.strip() for line in details_text.split("\n") if line.strip()]
    
    # 5. Convert lines into a key-value dictionary
    record_dict = {}
    for line in lines:
        # Split each line into key and value (handle cases where values might have colons)
        key, value = line.split(":", 1)
        # Clean up key and value, standardize column names (e.g., uppercase)
        clean_key = key.strip().replace(" ", "_").upper()
        record_dict[clean_key] = value.strip()
    
    # 6. Add the dictionary to our records list
    parsed_records.append(record_dict)

# 7. Convert to a structured DataFrame (table)
final_df = pd.DataFrame(parsed_records)
print(final_df)

This will output a table with columns like LAST_NAME, FIRST_NAME, MID_NAME, LOCATION and corresponding values.

R Implementation #

For R, we'll use the xml2 package for XML handling and the tidyverse suite to reshape the data into a table. Using the same XML structure as above:

Step-by-Step Code #

library(xml2)
library(tidyverse)

# 1. Read and parse the XML file
xml_doc <- read_xml("your_government_data.xml")

# 2. Extract all <Details> node texts
details_texts <- xml_doc %>%
  xml_find_all(".//Record/Details") %>%
  xml_text() %>%
  str_squish()  # Remove extra whitespace

# 3. Process each text block into key-value pairs
parsed_records <- map_dfr(details_texts, function(text) {
  # Split into lines
  lines <- str_split(text, "\n")[[1]] %>% str_trim() %>% discard(~. == "")
  
  # Split each line into key and value
  key_value_pairs <- str_split_fixed(lines, ":", 2) %>%
    as_tibble() %>%
    rename(key = V1, value = V2) %>%
    mutate(
      key = str_trim(key) %>% str_replace_all(" ", "_") %>% str_to_upper(),
      value = str_trim(value)
    )
  
  # Reshape from long to wide format
  key_value_pairs %>%
    pivot_wider(names_from = key, values_from = value)
})

# 4. View the final table
print(parsed_records)

Notes for Edge Cases #

• If some records are missing certain fields (e.g., no Mid Name), both implementations will automatically fill those with NaN (Python) or NA (R), which is standard for relational tables.
• If your XML uses different node names (not <Record> or <Details>), adjust the XPath queries (.//Record, .//Record/Details) to match your actual XML structure.
• For values that might contain colons (e.g., a Location like "Washington D.C.: Downtown"), the split(":", 1) (Python) and str_split_fixed(lines, ":", 2) (R) ensure we only split on the first colon, preserving the full value.

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