CSV合并资产列拆分重排：百万级数据高效实现方案咨询（优先Python）

Hey there! Let’s tackle this data reshaping problem you’ve got—turning that messy single-column asset allocation into a clean, wide-format table makes total sense, especially with potentially millions of rows. Here’s how to approach it, starting with Python (since you have experience) and then some even faster alternatives for huge datasets.

Basic Approach (Great for Small to Medium Datasets) #

If your dataset isn’t yet hitting the million-row mark, pandas is the easiest way to get this done quickly. It handles the splitting and pivoting logic with minimal code:

import pandas as pd

# Read the raw CSV, handling the | separator and extra spaces
df = pd.read_csv('your_data.csv', sep='|', skipinitialspace=True)

# Split the Asset Allocation column into individual asset entries, then stack them into rows
asset_rows = df['Asset Allocation'].str.split('+++', expand=True).stack().reset_index(level=1, drop=True)

# Split each asset entry into name and value, then convert values to integers
asset_df = asset_rows.str.split(':', expand=True).rename(columns={0: 'Asset', 1: 'Value'})
asset_df['Value'] = asset_df['Value'].astype(int)

# Merge back with the Day column, then pivot to wide format (fill missing values with 0)
result = df[['Day']].join(asset_df).pivot(index='Day', columns='Asset', values='Value').fillna(0).reset_index()

# Reorder columns to match your desired output
result = result[['Day', 'NYSE', 'FTSE', 'DAX', 'STOXX']]

# Save the final output
result.to_csv('reshaped_data.csv', sep='|', index=False)

Optimized Python for Large Datasets (Millions of Rows) #

When dealing with millions of rows, pandas’ in-memory operations can get slow or use too much RAM. Instead, use the built-in csv module to process rows one at a time (low memory footprint) or leverage parallel processing with Dask.

Option 1: Low-Memory CSV Processing #

This approach reads the file twice—once to collect all unique assets, then again to build the wide-format table:

import csv

# First pass: collect all unique asset types from the dataset
unique_assets = set()
with open('your_data.csv', 'r') as infile:
    reader = csv.DictReader(infile, delimiter='|', skipinitialspace=True)
    for row in reader:
        allocations = row['Asset Allocation'].split('+++')
        for alloc in allocations:
            asset_name = alloc.split(':')[0]
            unique_assets.add(asset_name)

# Define your desired column order (add any extra assets found to the end)
target_columns = ['NYSE', 'FTSE', 'DAX', 'STOXX']
for asset in unique_assets:
    if asset not in target_columns:
        target_columns.append(asset)

# Second pass: build the reshaped CSV
with open('reshaped_data.csv', 'w', newline='') as outfile:
    writer = csv.writer(outfile, delimiter='|')
    # Write the header row
    writer.writerow(['Day'] + target_columns)
    
    with open('your_data.csv', 'r') as infile:
        reader = csv.DictReader(infile, delimiter='|', skipinitialspace=True)
        for row in reader:
            day = row['Day']
            # Initialize all asset values to 0
            asset_values = {asset: 0 for asset in target_columns}
            # Populate values from the current row's allocations
            allocations = row['Asset Allocation'].split('+++')
            for alloc in allocations:
                asset, value = alloc.split(':')
                asset_values[asset] = int(value)
            # Write the row in the correct order
            output_row = [day] + [asset_values[col] for col in target_columns]
            writer.writerow(output_row)

Option 2: Parallel Processing with Dask #

Dask handles out-of-core data processing (meaning it works with data larger than your RAM) by splitting the dataset into chunks and processing them in parallel. It uses a pandas-like API, so the code feels familiar:

import dask.dataframe as dd

# Read the CSV with Dask
dask_df = dd.read_csv('your_data.csv', sep='|', skipinitialspace=True)

# Split and stack asset allocations (parallelized across chunks)
asset_rows = dask_df['Asset Allocation'].str.split('+++', expand=True).stack().reset_index(level=1, drop=True)
asset_df = asset_rows.str.split(':', expand=True).rename(columns={0: 'Asset', 1: 'Value'}).astype({'Value': int})

# Merge and pivot to wide format
result = dask_df[['Day']].join(asset_df).pivot_table(
    index='Day', columns='Asset', values='Value', aggfunc='first'
).fillna(0).reset_index()

# Reorder columns to match your desired output
result = result[['Day', 'NYSE', 'FTSE', 'DAX', 'STOXX']]

# Save the result as a single CSV file
result.to_csv('reshaped_data.csv', sep='|', index=False, single_file=True)

If Python still isn’t fast enough for your dataset, these tools are built for speed with massive text data:

Command Line Tools (Awk + Datamash) #

Awk is a lightning-fast text processing tool written in C, and datamash simplifies pivoting. This pipeline converts your data in two steps:

# Step 1: Convert the raw CSV to a "long" format (Day, Asset, Value)
awk -F'|' '{
    # Clean up whitespace around Day and Asset Allocation
    gsub(/^[ \t]+|[ \t]+$/, "", $1);
    gsub(/^[ \t]+|[ \t]+$/, "", $2);
    # Split allocations into individual assets
    n = split($2, assets, /\+\+\+/);
    for (i=1; i<=n; i++) {
        split(assets[i], kv, /:/);
        print $1 "," kv[1] "," kv[2];
    }
}' your_data.csv > long_format.csv

# Step 2: Pivot from long to wide format, sum values (we use sum since each Day+Asset has one entry)
datamash -t',' --header-in groupby 1 pivot 2 sum 3 < long_format.csv > reshaped_data.csv

# Optional: Replace commas with | and reorder columns to match your desired output
sed 's/,/|/g' reshaped_data.csv | awk -F'|' '{print $1 "|" $2 "|" $3 "|" $4 "|" $5}' > final_reshaped.csv

Database Processing (SQLite/PostgreSQL) #

Databases excel at handling large datasets and have built-in pivot logic. Here’s how to do it with SQLite (no server required):

1. Import your raw data into SQLite:

-- Create a table for raw data
CREATE TABLE raw_data (Day INTEGER, Asset_Allocation TEXT);

-- Switch to CSV mode and import the file
.mode csv
.separator |
.import your_data.csv raw_data

2. Run a query to pivot the data:

SELECT 
    Day,
    MAX(CASE WHEN Asset = 'NYSE' THEN Value ELSE 0 END) AS NYSE,
    MAX(CASE WHEN Asset = 'FTSE' THEN Value ELSE 0 END) AS FTSE,
    MAX(CASE WHEN Asset = 'DAX' THEN Value ELSE 0 END) AS DAX,
    MAX(CASE WHEN Asset = 'STOXX' THEN Value ELSE 0 END) AS STOXX
FROM (
    -- Split Asset_Allocation into individual Asset-Value pairs
    SELECT 
        Day,
        SUBSTR(alloc.value, 1, INSTR(alloc.value, ':')-1) AS Asset,
        CAST(SUBSTR(alloc.value, INSTR(alloc.value, ':')+1) AS INTEGER) AS Value
    FROM raw_data,
         json_each('["' || REPLACE(Asset_Allocation, '+++', '","') || '"]') AS alloc
)
GROUP BY Day
ORDER BY Day;

3. Export the result to CSV:

.mode csv
.separator |
.output reshaped_data.csv
-- Re-run the pivot query above
.output stdout

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