如何对齐Pandas DataFrame交替列日期并重构数据结构

Got it, let's work through this problem step by step. You've got a wide DataFrame where odd-positioned columns (1st, 3rd, 5th...) are dates, even-positioned columns hold corresponding values, and dates don't line up across columns. We need to reshape this into a clean long format with a single date column and all associated values.

First, let's define the example data clearly #

Let's start with a reproducible version of your input DataFrame:

import pandas as pd

# Sample input data
raw_data = [
    ["2007-12-01", 35.6, "2007-12-05", 101.1, "2007-12-05", 89.1],
    ["2007-12-02", 36.7, "2007-12-06", 102.3, "2007-12-07", 89.3],
    ["2007-12-05", 36.7, "2007-12-07", 108.3, "2007-12-08", 89.5],
    ["2007-12-06", 36.9, "2007-12-08", 110.0, "2007-12-09", 89.3],
    ["2007-12-07", 37.1, None, None, "2007-12-10", 89.4]
]
df = pd.DataFrame(raw_data, columns=["date", "val1", "date", "val2", "date", "val3"])

Method 1: Iterate Over Date-Value Pairs (Intuitive for Beginners) #

This approach loops through each pair of date and value columns, extracts valid rows, and concatenates them into the final result. It's easy to follow and works even with messy column names:

# Initialize empty result DataFrame
final_df = pd.DataFrame(columns=["date", "value", "source_column"])

# Loop through columns in steps of 2 (date then value)
for idx in range(0, len(df.columns), 2):
    date_col = df.columns[idx]
    val_col = df.columns[idx + 1]
    
    # Extract the current pair, drop rows with missing dates/values
    temp_df = df[[date_col, val_col]].dropna()
    # Rename columns for consistency
    temp_df.columns = ["date", "value"]
    # Optional: Add a column to track which original value column this came from
    temp_df["source_column"] = val_col
    
    # Append to final result
    final_df = pd.concat([final_df, temp_df], ignore_index=True)

# Convert date column to datetime type (critical for time-series operations)
final_df["date"] = pd.to_datetime(final_df["date"])

# Preview the result
print(final_df.head())

Method 2: Use pd.wide_to_long (Cleaner, Pandas-Native) #

If you prefer a more concise method, wide_to_long is designed for this kind of wide-to-long reshaping. First, we'll rename the duplicate columns to give them a consistent suffix:

# Rename duplicate columns to add group identifiers
new_column_names = []
group_counter = 1
for col in df.columns:
    if col == "date":
        new_column_names.append(f"date_{group_counter}")
    else:
        new_column_names.append(f"val_{group_counter}")
        group_counter += 1

# Apply the new column names
renamed_df = df.rename(columns=dict(zip(df.columns, new_column_names)))

# Reshape with wide_to_long
final_df = pd.wide_to_long(
    renamed_df,
    stubnames=["date", "val"],
    i=renamed_df.index,
    j="group"
).reset_index()

# Clean up: drop invalid rows, rename columns, convert date to datetime
final_df = final_df.dropna(subset=["date", "val"])
final_df = final_df[["date", "val"]].rename(columns={"val": "value"})
final_df["date"] = pd.to_datetime(final_df["date"])

# Preview the result
print(final_df.head())

Key Notes #

• Always convert the date column to datetime type—this lets you do time-series operations like sorting, filtering by date ranges, etc.
• The source_column in Method 1 is optional but helpful if you need to track which original value column each entry came from.
• Both methods handle missing values by dropping rows where either the date or value is empty (adjust dropna parameters if you need different behavior).

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