请求指导：使用Python基于CSV构建层级化数据的最佳实践

Hey there! Let's tackle this problem properly. Your goal is to take a flat CSV of hierarchical department data, expand it to include every intermediate parent-child level (so no missing nodes), and make the solution work for any number of name_n/dept_code_n columns (not just 3). Here's a robust, scalable approach:

Problem Recap #

Your input CSV has rows that represent leaf or intermediate nodes in a hierarchy. You need to generate a new CSV where every possible path from root to each node is a separate row, with empty columns filled in and an "OK" at the end. For example, a row with 3 levels should generate 3 rows: root only, root + level 2, root + level2 + level3.

Limitations of Your Current Code #

Your existing code hardcodes 3 levels of hierarchy, which won't work if your CSV has name_4/dept_code_4 or more. It also outputs a nested dictionary instead of the flat CSV rows you need.

Step-by-Step Solution #

1. Dynamic Hierarchy Detection #

First, we'll automatically detect all name_n/dept_code_n column pairs in your CSV, so the code works for any number of levels.

2. Generate All Paths #

For each row in the input, we'll generate every possible prefix path (from root up to the deepest node in that row).

3. Deduplicate Paths #

Since multiple rows might share the same parent path, we'll use a set to avoid duplicate rows in the output.

4. Format Output Rows #

Each path will be converted to a CSV row with empty columns filled in and an "OK" suffix.

Complete Code Implementation #

import os
import csv

def expand_hierarchical_csv(input_path, output_path=None):
    # Step 1: Read input CSV and detect hierarchy levels
    with open(input_path, 'r', newline='', encoding='utf-8') as csv_file:
        reader = csv.DictReader(csv_file)
        fields = reader.fieldnames
        
        # Detect all name_n/dept_code_n pairs
        level_pairs = []
        level = 1
        while True:
            name_col = f'name_{level}'
            dept_col = f'dept_code_{level}'
            if name_col in fields and dept_col in fields:
                level_pairs.append((name_col, dept_col))
                level += 1
            else:
                break
        
        # Step 2: Generate all unique paths
        seen_paths = set()
        final_rows = []
        
        for row in reader:
            current_path = []
            # Build path step by step until we hit empty values
            for name_col, dept_col in level_pairs:
                name = row[name_col].strip()
                dept_code = row[dept_col].strip()
                if not name or not dept_code:
                    break  # Stop at first empty level
                current_path.append((name, dept_code))
                
                # Convert current path to a CSV row
                row_data = []
                for name_val, dept_val in current_path:
                    row_data.append(name_val)
                    row_data.append(dept_val)
                # Fill remaining columns with empty strings
                total_columns = len(level_pairs) * 2
                row_data += [''] * (total_columns - len(row_data))
                # Add the OK suffix
                row_data.append('OK')
                
                # Deduplicate using a tuple (lists can't be in sets)
                path_tuple = tuple(row_data)
                if path_tuple not in seen_paths:
                    seen_paths.add(path_tuple)
                    final_rows.append(row_data)
    
    # Step 3: Write output (or print if no output path is given)
    output_fields = fields + ['status']
    if output_path:
        with open(output_path, 'w', newline='', encoding='utf-8') as out_file:
            writer = csv.writer(out_file)
            writer.writerow(output_fields)
            writer.writerows(final_rows)
        print(f"Expanded CSV saved to {output_path}")
    else:
        # Print the result
        print(','.join(output_fields))
        for row in final_rows:
            print(','.join(row))

# Example usage
expand_hierarchical_csv('test.csv')

How This Works #

• Dynamic Level Detection: The code scans the CSV headers to find all name_n/dept_code_n pairs, so it adapts to any number of hierarchy levels.
• Path Generation: For each input row, we build paths incrementally. If a row has 3 valid levels, we generate 3 separate rows (level 1 only, level1+level2, level1+level2+level3).
• Deduplication: Using a set ensures we don't write duplicate parent rows (even if multiple child rows reference the same parent).
• Proper Formatting: Each row is padded with empty strings to match the original CSV's column count, and the "OK" status is added at the end.

Test Output #

When run with your sample input CSV, this code will produce exactly the expected output you provided:

name_1,dept_code_1,name_2,dept_code_2,name_3,dept_code_3,status
ABC,CODE1,,,,,OK
ABC,CODE1,ABC CHILD 1,CODE1-1,,,OK
ABC,CODE1,ABC CHILD 1,CODE1-1,ABC CHILD 1-1-1,CODE1-1-1,OK
ABC,CODE1,ABC CHILD 1,CODE1-1,ABC CHILD 1-1-2,CODE1-1-2,OK
ABC,CODE1,ABC CHILD 2,CODE1-2,,,OK
ABC,CODE1,ABC CHILD 2,CODE1-2,ABC CHILD 1-2-1,CODE1-2-1,OK
ABC,CODE1,ABC CHILD 2,CODE1-2,ABC CHILD 1-2-2,CODE1-2-2,OK
XYZ,CODE2,,,,,OK
XYZ,CODE2,XYZ CHILD,CODE2-2,,,OK

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