使用Python将深度嵌套XML转换为CSV的方法求助

Alright, let's figure out how to convert your XML household data into a CSV using Python. Based on the snippet you shared, here's a step-by-step solution tailored to your structure.

First, let's recap the key parts of your XML:

• Root element: <households> with metadata attributes
• Child elements: <household> (each has an id attribute)
• Each household has <members> containing <member> entries (with id attribute)
• Members have a <member_process> element with result and vacation attributes

Solution 1: Using built-in xml.etree.ElementTree and csv modules #

This approach gives you full control over data extraction, which is great for custom XML structures.

1. Import the required modules:

import xml.etree.ElementTree as ET
import csv

2. Parse the XML file and extract data:

# Parse your XML file (replace with your actual file path)
tree = ET.parse('households.xml')
root = tree.getroot()

# Define the CSV columns we want to capture
csv_columns = [
    'household_id',
    'member_id',
    'member_process_result',
    'member_process_vacation'
]

# Write data to CSV
with open('households_output.csv', 'w', newline='', encoding='utf-8') as csv_file:
    writer = csv.DictWriter(csv_file, fieldnames=csv_columns)
    writer.writeheader()
    
    # Loop through each household
    for household in root.findall('.//household'):
        household_id = household.get('id')
        
        # Loop through each member in the household
        for member in household.findall('.//member'):
            member_id = member.get('id')
            
            # Extract member_process attributes (handle cases where it might be missing)
            member_process = member.find('.//member_process')
            result = member_process.get('result') if member_process is not None else None
            vacation = member_process.get('vacation') if member_process is not None else None
            
            # Write the row to CSV
            writer.writerow({
                'household_id': household_id,
                'member_id': member_id,
                'member_process_result': result,
                'member_process_vacation': vacation
            })

Solution 2: Using pandas for a more concise approach #

If you're comfortable with pandas, this method is quicker and handles DataFrame operations easily.

1. Install pandas if you haven't already:

pip install pandas

2. Code to convert XML to CSV:

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

tree = ET.parse('households.xml')
root = tree.getroot()

# Collect data into a list of dictionaries
data = []
for household in root.findall('.//household'):
    hh_id = household.get('id')
    for member in household.findall('.//member'):
        m_id = member.get('id')
        mp = member.find('.//member_process')
        
        row = {
            'household_id': hh_id,
            'member_id': m_id,
            'member_process_result': mp.get('result') if mp else None,
            'member_process_vacation': mp.get('vacation') if mp else None
        }
        # Add any additional fields from <individual_audience_tvset> or other elements here
        data.append(row)

# Convert to DataFrame and save as CSV
df = pd.DataFrame(data)
df.to_csv('households_output.csv', index=False, encoding='utf-8')

Important Notes: #

• Adjust fields as needed: Your XML snippet cuts off at <channel...—if there are additional fields (like TV set/channel data), simply add them to the csv_columns list (or the row dictionary) and extract them using find()/get() like we did with member_process.
• Handle missing data: The code checks if elements exist before accessing their attributes to avoid errors.
• File paths: Replace households.xml with your actual XML file path, and households_output.csv with your desired output file name.
• Encoding: Using encoding='utf-8' ensures special characters are handled correctly.

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