多工作表中姓名、事件日期及模态码提取：split函数使用困境

Hey Jordan, sounds like you're stuck trying to wrangle that spreadsheet data into a clean master dataset—let's break this down and fix it! The split() function probably isn't the right tool here because you're dealing with a wide-format table (dates as columns) that needs to be converted to a long-format table (each row is a single event with name, date, and modality).

理解你的数据结构 #

From your example, it looks like each worksheet has:

• A header row with the month/year (e.g., Feb-15)
• A second row with column labels: NAME followed by day numbers (1, 2, ..., 10)
• Rows below with names, and cells containing modalities like H or HI under the corresponding day columns

方法1：用Python Pandas（最适合批量处理多工作表） #

Pandas makes it easy to read all sheets at once and reshape your data. Here's a step-by-step code example:

import pandas as pd
from datetime import datetime

# Step 1: Load all worksheets from your Excel file
excel_path = "your_spreadsheet_file.xlsx"
excel_file = pd.ExcelFile(excel_path)
all_worksheets = {sheet: excel_file.parse(sheet) for sheet in excel_file.sheet_names}

# Step 2: Process each sheet and build the master dataset
master_records = []

for sheet_name, df in all_worksheets.items():
    # Grab the month/year from the first cell (e.g., Feb-15)
    month_year = df.iloc[0, 0]
    # Set the second row as the official column headers
    df.columns = df.iloc[1]
    # Keep only the actual data rows (skip first two header rows)
    df = df.iloc[2:].reset_index(drop=True)
    
    # Convert wide table to long table: turn day columns into rows
    melted_df = df.melt(
        id_vars=["NAME"],  # Keep NAME as the identifier column
        var_name="day",    # Name for the new day column
        value_name="modality"  # Name for the modality values
    )
    
    # Combine day + month/year into a full date
    melted_df["date"] = melted_df.apply(
        lambda row: datetime.strptime(f"{row['day']} {month_year}", "%d %b-%y"),
        axis=1
    )
    
    # Filter out rows where there's no modality (empty cells)
    melted_df = melted_df.dropna(subset=["modality"])
    
    # Keep only the columns we need for the master dataset
    cleaned_data = melted_df[["NAME", "date", "modality"]]
    master_records.append(cleaned_data)

# Step 3: Combine all processed sheets into one master dataset
final_master = pd.concat(master_records, ignore_index=True)

# Check the result
print(final_master.head())

为什么这比split()好？

split() is designed to break single strings into parts, but your problem is about reshaping an entire table. The melt() function is purpose-built for converting wide tables (dates as columns) to long tables (each row is a single event)—exactly what you need to build your master dataset.

方法2：用Excel Power Query（无需代码） #

If you prefer to stick with Excel, Power Query is perfect for this task:

1. Go to Data > Get Data > From File > From Workbook
2. Select your spreadsheet, then choose Select Multiple Items and check all your worksheets
3. Click Combine & Load > Combine & Edit
4. In the Power Query editor:
   • Remove the first row (the month/year row)
   • Select the NAME column, then go to Transform > Unpivot Columns > Unpivot Other Columns
   • Rename the new columns to Day and Modality
   • Add a custom column to combine Day with the month/year (you can extract the month/year from the sheet name or original header if needed)
   • Filter out rows where Modality is blank
   • Load the cleaned data back to Excel as your master dataset

Either of these approaches should get you the structured master dataset you need—no more struggling with split()!

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