按列拆分含1500+公司股票数据的CSV文件技术问询

Hey there, let's solve this CSV splitting problem for your stock data. I've put together two solutions—one using pandas for simplicity, and a low-memory alternative for extra large files—that exactly meet your requirement of keeping the date column in every split file and grouping 30 company columns per output.

Pandas makes tabular data operations a breeze, and it's perfect for this task if your system has enough memory to load the full CSV. First, install pandas if you haven't already:

pip install pandas

Here's the script:

import pandas as pd
import math

# Configure these values to match your file
input_csv = "your_large_stock_data.csv"  # Replace with your actual file path
companies_per_split = 30
date_column_name = "date"  # Update this if your date column has a different name

# Load the full dataset
full_df = pd.read_csv(input_csv)

# Separate date column from company columns
all_company_columns = [col for col in full_df.columns if col != date_column_name]
total_companies = len(all_company_columns)
total_split_files = math.ceil(total_companies / companies_per_split)

# Split and save each chunk
for file_num in range(total_split_files):
    # Calculate which company columns to include in this split
    start_idx = file_num * companies_per_split
    end_idx = start_idx + companies_per_split
    selected_companies = all_company_columns[start_idx:end_idx]
    
    # Combine date column with selected companies
    chunk_df = full_df[[date_column_name] + selected_companies]
    
    # Save to a new CSV
    output_filename = f"stock_data_chunk_{file_num + 1}.csv"
    chunk_df.to_csv(output_filename, index=False)
    print(f"Saved {output_filename} (contains {len(selected_companies)} companies + date column)")

Key Details: #

• The script automatically calculates how many split files are needed (using math.ceil to handle the final chunk, which might have fewer than 30 companies if your total isn't a perfect multiple of 30).
• Each output CSV will always include the date column as the first column, followed by up to 30 company columns.
• You can tweak the output_filename pattern to fit your naming preferences.

If your CSV is extremely large and loading the entire file into memory causes issues, use this row-by-row processing script with Python's built-in csv module (no extra dependencies needed):

import csv
import math

input_csv = "your_large_stock_data.csv"
companies_per_split = 30

# First, read the header row to map columns
with open(input_csv, 'r', newline='', encoding='utf-8') as input_file:
    reader = csv.reader(input_file)
    headers = next(reader)
    date_header = headers[0]
    company_headers = headers[1:]
    total_companies = len(company_headers)
    total_split_files = math.ceil(total_companies / companies_per_split)

    # Set up writers for all output files
    output_handles = []
    csv_writers = []
    for file_num in range(total_split_files):
        # Define which headers go into this split
        start = file_num * companies_per_split
        end = start + companies_per_split
        chunk_headers = [date_header] + company_headers[start:end]
        
        # Create output file and writer
        output_filename = f"stock_data_chunk_{file_num + 1}.csv"
        output_file = open(output_filename, 'w', newline='', encoding='utf-8')
        output_handles.append(output_file)
        writer = csv.writer(output_file)
        writer.writerow(chunk_headers)
        csv_writers.append(writer)

    # Process each row and write to the correct split files
    for row in reader:
        date_value = row[0]
        for idx, writer in enumerate(csv_writers):
            start = idx * companies_per_split
            end = start + companies_per_split
            company_values = row[1:][start:end]
            writer.writerow([date_value] + company_values)

# Clean up: close all output files
for handle in output_handles:
    handle.close()

print(f"Done! Split into {total_split_files} separate CSV files.")

Notes: #

• This script reads one row at a time, so it uses minimal memory even for huge CSV files.
• It automatically uses the first column as the date column (no need to specify a name).

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