如何用Python将Excel日期命名的工作表名导入为日期列？

Hey there, let's work through this problem together. I've done a lot of Excel data wrangling with Python, so here's a robust, general approach that should handle your large dataset smoothly.

Step 1: Install Required Libraries #

First, make sure you have the right tools installed. We'll use pandas for data manipulation and openpyxl to read/write modern Excel files (.xlsx). If you're dealing with older .xls files, swap openpyxl for xlrd (note: xlrd no longer supports .xlsx, so stick to openpyxl for newer files).

Run this in your terminal:

pip install pandas openpyxl

Step 2: Full Code Implementation #

Here's the complete script, with comments explaining each part. I'll break it down after so you understand how it works.

import pandas as pd
from pandas import ExcelFile

# Replace this with your actual Excel file path
excel_file_path = "your_large_dataset.xlsx"

# Load the Excel file and get all sheet names
xls = ExcelFile(excel_file_path)
all_sheet_names = xls.sheet_names

# Initialize an empty list to store data from each sheet
processed_data = []

# Loop through each sheet
for sheet_name in all_sheet_names:
    # Read the sheet into a DataFrame
    # For extra large sheets, add chunksize=10000 (adjust number as needed) to process in batches
    sheet_df = pd.read_excel(xls, sheet_name=sheet_name)
    
    # Convert sheet name to a proper datetime column
    # Adjust the format parameter if your sheet names use a non-standard date format
    # Examples: "%Y-%m-%d" for "2023-12-31", "%b-%Y" for "Dec-2023", "%d/%m/%Y" for "31/12/2023"
    sheet_df["日期"] = pd.to_datetime(sheet_name, format="%Y-%m-%d", errors="coerce")
    
    # Add the processed sheet data to our list
    processed_data.append(sheet_df)

# Combine all sheets into a single DataFrame
combined_df = pd.concat(processed_data, ignore_index=True)

# Save the result to a new Excel file (or do further analysis here)
combined_df.to_excel("dataset_with_date_column.xlsx", index=False, engine="openpyxl")

# Optional: Check for any unparseable dates (NaT values)
unparseable_count = combined_df["日期"].isna().sum()
if unparseable_count > 0:
    print(f"Warning: {unparseable_count} sheets had unrecognizable date formats. Check your sheet names!")

Key Details to Customize #

• Date Format Adjustment: If your sheet names use a format like "20230101" (YYYYMMDD) or "Jan-2023", update the format parameter in pd.to_datetime() to match. For example:
  • format="%Y%m%d" for "20230101"
  • format="%b-%Y" for "Jan-2023"
• Large Dataset Handling: If your file is massive (100k+ rows per sheet), use the chunksize parameter in pd.read_excel() to process data in smaller batches, preventing memory issues.
• Error Handling: The errors="coerce" flag turns unparseable sheet names into NaT (Not a Time) values instead of crashing the script. You can later filter these out or fix the sheet names.

What This Does #

• Reads every sheet in your Excel file
• Adds a new "日期" column to each sheet's data, populated with the sheet's name converted to a proper datetime value
• Merges all sheets into one big DataFrame (so you can analyze all data together with the date context)
• Saves the merged data to a new Excel file, ready for use

Hope this solves your problem! If you hit snags with specific date formats or super large files, feel free to follow up.

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