如何按日期顺序重排DataFrame列？代码执行结果不符预期求助

Hey there! The problem you're running into is that when you use sort_values() directly on these date strings, pandas is doing lexicographical (dictionary) sorting instead of sorting by actual date logic. That's why "Dec. 31, 2009A" ends up before "Dec. 31, 2010"—dictionary order compares characters one by one, and "2009A" is considered "smaller" than "2010" because the third character in the year segment is '0' vs '1'.

To fix this, we need to define a custom sorting key that understands your date format (including the "A" suffix) and sorts them the way you expect. Here are a couple of solutions depending on what the "A" suffix means for your data:

Solution 1: Treat "A" as an adjusted version of the same year #

If "2009A" is an adjusted/updated dataset for 2009 that should come right after the original 2009 data (but before 2010), use this approach:

def date_sort_key(column_name):
    # Extract the year segment from the column name (e.g., "2009A" from "Dec. 31, 2009A")
    year_segment = column_name.split(', ')[-1]
    if year_segment.endswith('A'):
        # Assign a value slightly higher than the base year to place it after the original
        return int(year_segment[:-1]) + 0.5
    else:
        return int(year_segment)

# Sort columns using the custom key
sorted_columns = sorted(oval_df.columns, key=date_sort_key)
# Reindex your DataFrame with the sorted columns
oval_df = oval_df.reindex(columns=sorted_columns)

This will give you the order:
['Dec. 31, 2008', 'Dec. 31, 2009', 'Dec. 31, 2009A', 'Dec. 31, 2010', 'Dec. 31, 2010A', 'Dec. 31, 2011']

Solution 2: Treat "A" as a post-year update (e.g., 2009A comes after 2010) #

If the "A" suffix means the data is an adjustment that belongs after the following year, adjust the key to add 1 to the base year:

def date_sort_key(column_name):
    year_segment = column_name.split(', ')[-1]
    if year_segment.endswith('A'):
        return int(year_segment[:-1]) + 1
    else:
        return int(year_segment)

sorted_columns = sorted(oval_df.columns, key=date_sort_key)
oval_df = oval_df.reindex(columns=sorted_columns)

This will produce the order:
['Dec. 31, 2008', 'Dec. 31, 2009', 'Dec. 31, 2010', 'Dec. 31, 2009A', 'Dec. 31, 2011', 'Dec. 31, 2010A']

Solution 3: Parse as actual datetime objects (most precise) #

For a more date-centric approach, parse the column names into datetime objects, then shift the "A" entries by a day to place them after the original date:

import pandas as pd

def parse_date_col(col_name):
    # Remove the "A" suffix first, then parse the date
    cleaned_name = col_name.rstrip('A')
    return pd.to_datetime(cleaned_name)

def date_sort_key(column_name):
    base_date = parse_date_col(column_name)
    if column_name.endswith('A'):
        # Add one day to place the adjusted date right after the original
        return base_date + pd.Timedelta(days=1)
    else:
        return base_date

sorted_columns = sorted(oval_df.columns, key=date_sort_key)
oval_df = oval_df.reindex(columns=sorted_columns)

This method ensures you're sorting by actual chronological order, with "Dec. 31, 2009A" treated as January 1, 2010 (placing it between 2009 and 2010 data).

Pick the solution that matches what the "A" suffix represents for your dataset, and you'll get the correct date-based sorting you need!

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