使用Pandas按年份拆分Excel日期数据至列的方法问询

Absolutely! Pandas is ideal for this kind of time-series data transformation. Let's walk through exactly how to split your data into year-based columns, even with custom date ranges, using code tailored to your specific data structure.

1. Read and Reshape Raw Data #

Your data is structured as a continuous stream of [date, time, value] triples, so first we need to convert this unstructured format into a clean 3-column DataFrame:

import pandas as pd

# Read the Excel file (use read_csv if it's a text file; set header=None since there's no column names)
raw_data = pd.read_excel("your_data_file.xlsx", header=None)

# Reshape the 1D data into rows of 3 elements each
structured_df = pd.DataFrame(
    raw_data[0].values.reshape(-1, 3),
    columns=["date_str", "time_str", "value"]
)

# Convert the value column to numeric type for calculations
structured_df["value"] = structured_df["value"].astype(float)

2. Combine and Parse DateTime #

Merge the date and time strings into a single datetime object—this is critical for filtering and grouping later:

# Merge date and time columns into a full datetime string
structured_df["datetime"] = pd.to_datetime(
    structured_df["date_str"] + " " + structured_df["time_str"],
    format="%m/%d/%Y %I:%M:%S %p"  # Explicit format to avoid parsing errors
)

# Extract year and month-day (for row indexing when splitting by year)
structured_df["year"] = structured_df["datetime"].dt.year
structured_df["month_day"] = structured_df["datetime"].dt.strftime("%m-%d")

3. Filter Custom Date Ranges #

You can easily narrow down to specific date ranges (like 1920-2020 January or 1940-2020 June) using boolean masks:

Example 1: 1920-2020 January Data #

filter_mask = (structured_df["datetime"].dt.year.between(1920, 2020)) & (structured_df["datetime"].dt.month == 1)
filtered_data = structured_df[filter_mask]

Example 2: 1940-2020 June Data #

filter_mask = (structured_df["datetime"].dt.year.between(1940, 2020)) & (structured_df["datetime"].dt.month == 6)
filtered_data = structured_df[filter_mask]

4. Split Data into Year-Based Columns #

Use pivot to transform years into columns, with month-day as the row index and values filling the corresponding cells:

# Create a pivot table where each column represents a year
year_split_df = filtered_data.pivot(
    index="month_day",
    columns="year",
    values="value"
)

# Optional: Reset index to make month-day a regular column instead of the index
year_split_df = year_split_df.reset_index()

# Optional: Fill missing values (use 0, pd.NA, or another value based on your needs)
year_split_df = year_split_df.fillna(pd.NA)

Bonus Tips #

• For large datasets (~36k rows for 100 years of daily data), use usecols in pd.read_excel to only load the necessary column and speed up reading.
• Export the final result to Excel with year_split_df.to_excel("year_split_data.xlsx", index=False)

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