Python：DataFrame多工作表存储问题及代码报错排查

Hey there! Let's break down your two requirements and fix that error you're hitting step by step.

This one's straightforward—pd.ExcelWriter is exactly designed for this use case. You can use either the xlsxwriter or openpyxl engine to write multiple DataFrames to separate tabs in a single Excel file. Here's a working example tailored to your stock data use case:

import pandas as pd
import yfinance as yf  # Using yfinance to fetch stock data (install via pip if needed)

# Fetch 30-day data for multiple tickers
tickers = ["AAPL", "MSFT", "AMZN"]
stock_dfs = {}
for ticker in tickers:
    stock_dfs[ticker] = yf.download(ticker, period="30d")

# Write each DataFrame to a separate worksheet
with pd.ExcelWriter("stock_portfolio.xlsx", engine="xlsxwriter") as writer:
    for ticker, df in stock_dfs.items():
        # Use the ticker symbol as the worksheet name
        df.to_excel(writer, sheet_name=ticker)

This will create an Excel file where each stock's data lives in its own named tab.

First off, let's clear up a critical misunderstanding: CSV files do not support multiple worksheets. CSV is a plain-text format that only stores a single table of rows and columns—there's no built-in structure for separate tabs like Excel. This is why your code threw a ValueError when trying to use pd.ExcelWriter with a .csv file:

The error happens because pd.ExcelWriter is built exclusively for Excel formats (.xlsx/.xls), and the xlsxwriter engine can't handle CSV files. It expects a file extension that matches Excel's format rules.

Instead of forcing CSV to do something it can't, here are two practical alternatives:

Option 1: Split Data into Multiple CSV Files #

If you need to stick with CSV, split your data (based on sub-headers) into separate CSV files, one per "virtual worksheet":

# Example: Assume your main DataFrame has multi-level headers (sub-headers)
# Load your data first (adjust header argument to match your file's structure)
main_df = pd.read_csv("raw_stock_data.csv", header=[0, 1])

# Split by the top-level header (your "sub-header group")
for header_group in main_df.columns.get_level_values(0).unique():
    # Extract the subset of columns for this group
    subset_df = main_df[header_group]
    # Save to a dedicated CSV file
    subset_df.to_csv(f"{header_group}_data.csv", index=False)

Option 2: Use Excel for Sub-Header Based Worksheets #

If you prefer a single file with separate tabs (like your original "worksheet" idea), stick with Excel but split the data based on your sub-headers:

with pd.ExcelWriter("subheader_stock_data.xlsx", engine="xlsxwriter") as writer:
    for header_group in main_df.columns.get_level_values(0).unique():
        subset_df = main_df[header_group]
        # Use the sub-header group name as the worksheet tab
        subset_df.to_excel(writer, sheet_name=header_group)

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