Python中如何将列表转换为带列标题的行列格式

Got it, let's break down how to convert that messy string into a structured table in Python. The main challenge here is handling company names with spaces, but we can use the 9-character CUSIP code as a reliable marker to split each record correctly.

Here's a step-by-step solution:

Step 1: Parse the Raw Input #

First, we'll split the input string into individual tokens, then group them into records (each ending with two zeros). For each record, we'll extract the company name (all tokens before the CUSIP), the CUSIP itself, and the remaining fields.

Step 2: Map to Column Headers #

We'll use the headers you mentioned (CUSIP, VALUE, SHARES, DETAILS, TYPE, MG...) plus additional columns to handle variable fields in some records.

Full Python Code #

# Your raw input string
input_str = "A D C TELECOMMUNICATIONS COM NEW 000886309 39 6290 SH SOLE 6290 0 0 A D C TELECOMMUNICATIONS COM NEW 000886309 156 25100 SH DEFINED 2 25100 0 0 AAR CORP COM 000361105 7 305 SH SOLE 6 305 0 0 ATMOS ENERGY CORP COM 049560105 186 6342 SH SOLE 6342 0 0 CLEAR CHANNEL OUTDOOR HLDGS CL A 18451C109 6 609 SH SOLE 6 609 0 0"

# Split into individual tokens
tokens = input_str.split()

records = []
current_record = []

for token in tokens:
    current_record.append(token)
    # Check if we've reached the end of a record (ends with two zeros)
    if len(current_record) >= 2 and current_record[-2] == "0" and current_record[-1] == "0":
        # Find the CUSIP (9-character token)
        try:
            cusip_index = next(i for i, t in enumerate(current_record) if len(t) == 9)
        except StopIteration:
            current_record = []
            continue
        
        # Extract company name and remaining fields
        company_name = " ".join(current_record[:cusip_index])
        remaining = current_record[cusip_index:]
        
        if len(remaining) >= 7:
            cusip = remaining[0]
            value = remaining[1]
            shares = remaining[2]
            details = remaining[3]
            type_ = remaining[4]
            rest_fields = remaining[5:-2]
            zero1 = remaining[-2]
            zero2 = remaining[-1]
            
            # Map to structured dictionary
            record_dict = {
                "Company Name": company_name,
                "CUSIP": cusip,
                "VALUE": value,
                "SHARES": shares,
                "DETAILS": details,
                "TYPE": type_,
                "MG": rest_fields[0] if len(rest_fields) >= 1 else "-",
                "EXTRA_FIELD": rest_fields[1] if len(rest_fields) >= 2 else "-",
                "FIELD1": zero1,
                "FIELD2": zero2
            }
            records.append(record_dict)
        
        # Reset for next record
        current_record = []

# Generate Markdown table (using pandas for simplicity)
import pandas as pd

df = pd.DataFrame(records)
# Reorder columns to prioritize your requested headers
columns_order = ["CUSIP", "VALUE", "SHARES", "DETAILS", "TYPE", "MG", "EXTRA_FIELD", "Company Name", "FIELD1", "FIELD2"]
df = df[columns_order]

# Print the Markdown table
print(df.to_markdown(index=False, na_rep="-"))

Resulting Markdown Table #

Notes #

• If you don't have pandas installed, you can replace the pandas part with a manual markdown table builder (just ask if you need that code).
• The EXTRA_FIELD column handles the extra value in the "DEFINED" type record—you can rename this to something more meaningful if you know what it represents.

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