Python实现CSV按值解析：2行转4行格式转换方法

Hey there! Let's break down how to solve this problem—what you need is to convert a "wide" table format into a "long" one (often called an unpivot operation), which is ideal for loading into SQL. Since you're new to Python, I'll walk you through the steps clearly with simple, annotated code examples.

Step-by-Step Approach #

• Parse the raw data: Your input is space-separated text, so we'll use Python's built-in split() method to break each line into individual values.
• Extract key components:
  • From the first line: Grab the header 1 label and the list of values that will become header 2.
  • From the second line: Get the actual entry for header 1 (in your example, that's a) and its corresponding values.
• Pair and format the data: Loop through each header 2 value and its matching value, then combine them with the header 1 entry to create each new row.
• Output the result: Print the formatted rows or save them to a file for easy SQL import.

Example Code #

First, let's handle the exact data you provided:

# Define your original space-separated data
original_data = """header 1 0 1 2 3
a 0 10 10 10"""

# Split the data into separate lines
lines = original_data.split('\n')

# Parse the first line (header row)
header_line = lines[0].split()
header1_label = header_line[0]  # Gets "header 1"
header2_values = header_line[1:]  # Gets ["0", "1", "2", "3"]

# Parse the second line (data row)
data_line = lines[1].split()
header1_value = data_line[0]  # Gets "a"
values = data_line[1:]  # Gets ["0", "10", "10", "10"]

# Print the new header row
print(f"{header1_label} header 2 value")

# Loop through paired values and print each new row
for h2_val, val in zip(header2_values, values):
    print(f"{header1_value} {h2_val} {val}")

This code will output exactly the format you need:

header 1 header 2 value
a 0 0
a 1 10
a 2 10
a 3 10

Key Code Explanations #

• split('\n') breaks the original text into separate lines.
• split() with no arguments splits each line into a list of strings (handling any number of spaces automatically).
• zip(header2_values, values) pairs each header 2 value with its matching value—a simple way to loop through two lists at the same time.

Handling File Input/Output #

If your data is stored in a file (like input.txt), you can read it directly instead of hardcoding:

# Read data from a file
with open('input.txt', 'r') as input_file:
    # Skip empty lines and strip extra whitespace
    lines = [line.strip() for line in input_file if line.strip()]

# Parse headers and data (same as before)
header_line = lines[0].split()
header1_label = header_line[0]
header2_values = header_line[1:]

data_line = lines[1].split()
header1_value = data_line[0]
values = data_line[1:]

# Save the result to a file for SQL import
with open('output_for_sql.txt', 'w') as output_file:
    output_file.write(f"{header1_label} header 2 value\n")
    for h2_val, val in zip(header2_values, values):
        output_file.write(f"{header1_value} {h2_val} {val}\n")

This saved file can be imported into most SQL databases using commands like LOAD DATA INFILE (MySQL) or COPY (PostgreSQL).

Quick Tips for New Python Devs #

• Always use with open(...) when working with files—it automatically closes the file for you, avoiding bugs.
• If you have multiple data rows (not just the a row), add an extra loop to process each data line one by one.
• split() works for any whitespace (spaces, tabs, etc.), so it's flexible for messy input.

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