如何使用Python基于表格映射复杂结构的CSV数据？

Got it, let's break down how to map this tricky CSV into a usable, structured format. The file has a unique setup where the first 5 rows act as metadata headers (defining id, company, ticker, etc. for each column), and the rest of the rows are dates paired with values for each indicator. Here's a step-by-step solution using pandas (the go-to for tabular data in Python):

Step 1: Understand the CSV Structure #

First, let's recap the structure to make sure we're aligned:

• Rows 0-4: Metadata for each column (id, company, ticker, industry code, indicator)
• Rows 5+: Date as the first value, followed by corresponding metric values for each column defined in the metadata

Step 2: Python Code to Process the CSV #

import pandas as pd

# Read the CSV with semicolon delimiter (matches your data's separator)
df_raw = pd.read_csv('your_file.csv', delimiter=';', header=None)

# Extract the first 5 rows which hold our column metadata
header_rows = df_raw.iloc[:5]

# Create a dictionary to map each column index to its full metadata
column_metadata = {}
for col_idx in range(header_rows.shape[1]):
    # Strip whitespace from each metadata value to clean up the data
    attrs = [str(val).strip() for val in header_rows[col_idx].values]
    column_metadata[col_idx] = {
        'id': attrs[0],
        'company': attrs[1],
        'ticker': attrs[2],
        'industry_code': attrs[3],
        'indicator': attrs[4]
    }

# Pull out the actual data rows (starting from row 5)
data_rows = df_raw.iloc[5:]

# Prepare a list to collect our structured, analysis-ready records
structured_records = []

# Iterate over each date row
for _, data_row in data_rows.iterrows():
    date = str(data_row[0]).strip()
    # Loop through each value column (skip the first column which is the date)
    for col_idx in range(1, data_row.shape[0]):
        raw_value = data_row[col_idx]
        # Skip empty values if present (adjust this logic if your data has edge cases)
        if pd.isna(raw_value):
            continue
        # Clean and convert the value to a numeric type
        try:
            value = float(str(raw_value).strip())
        except ValueError:
            # Handle non-numeric values here if needed (e.g., leave as string)
            value = str(raw_value).strip()
        # Grab the metadata for this column
        meta = column_metadata[col_idx]
        # Build the structured record and add it to our list
        structured_records.append({
            'date': date,
            'id': meta['id'],
            'company': meta['company'],
            'ticker': meta['ticker'],
            'industry_code': meta['industry_code'],
            'indicator': meta['indicator'],
            'value': value
        })

# Convert the list of records to a clean DataFrame
final_df = pd.DataFrame(structured_records)

# Optional: Save the structured data to a new CSV for easy access
final_df.to_csv('structured_output.csv', index=False)

# Preview the first few rows to verify
print(final_df.head())

Step 3: What This Does #

• Reads raw CSV: Uses pd.read_csv with semicolon delimiter to match your data's format.
• Builds column metadata: Maps each column index to its associated id, company, ticker, industry code, and indicator by pulling values from the first 5 rows.
• Flattens the data: For each date row, it pairs every value with its metadata, creating a flat, row-based structure where each row represents a single metric (e.g., "Fox century's Share Price on 2011-11-04 is 2.72").
• Cleans values: Strips whitespace from strings and converts numeric values to floats, making the data ready for analysis, visualization, or database loading.

Example Output #

The resulting final_df will look like this (simplified):

内容的提问来源于stack exchange，提问作者chandra shekhar das