如何对Pandas数据框中含年月标识的列按年月顺序重新排序

Got it, let's tackle this problem step by step. You want to reorder only the columns that contain month-year details (like Jan-22, Feb-22Rate) while keeping your non-time-related columns in their original order. Here's a practical, scalable solution that works even with 100+ columns:

Step 1: Separate Columns into Non-Time and Time-Related Groups #

First, we'll use a regular expression to identify which columns include the MMM-YY (e.g., Jan-22) pattern. We'll split our columns into two lists: one for columns without time info, and one for those that have it.

Step 2: Sort Time-Related Columns Chronologically #

For each time-related column, we'll extract the month-year string, convert it to a datetime object (so we can sort chronologically), then reorder the columns based on this converted date.

Step 3: Reconstruct the DataFrame with New Column Order #

Combine the original non-time columns with the sorted time columns to get your final ordered DataFrame.

Full Code Implementation #

import pandas as pd
import re
from datetime import datetime

# Your original dataset
data = [[11, 1, 6, 8, 45, 67, '30-06-2021', 43578, 3.4, '30-04-2022', 6.7, 5000, 6744, 8.9, 8978, '31-03-2022', '31-01-2022', '28-02-2022', 5.6]]
dat = pd.DataFrame(data, columns = ['a', 'b', 't', 'u', 'g', 'd', 'Start', 'Apr-22Total', 'Mar-22Rate', 'Apr-22', 'Feb-22Rate', 'Feb-22Total', 'Jan-22Total', 'Apr-22Rate', 'Mar-22Total', 'Mar-22', 'Jan-22', 'Feb-22', 'Jan-22Rate'])

# Regex pattern to match MMM-YY format (e.g., Jan-22, Apr-22)
date_pattern = re.compile(r'([A-Za-z]{3}-\d{2})')

# Split columns into non-time and time-related groups
non_time_cols = []
time_cols = []

for col in dat.columns:
    if date_pattern.search(col):
        time_cols.append(col)
    else:
        non_time_cols.append(col)

# Function to extract and convert month-year string to datetime
def extract_month_year(col_name):
    match = date_pattern.search(col_name)
    if match:
        # Convert "Apr-22" to datetime (assumes 20XX for the year)
        return datetime.strptime(match.group(1), '%b-%y')
    return None

# Sort time-related columns by their chronological order
sorted_time_cols = sorted(time_cols, key=lambda x: extract_month_year(x))

# Create the new column order: original non-time cols + sorted time cols
new_column_order = non_time_cols + sorted_time_cols

# Reorder the DataFrame
sorted_dat = dat[new_column_order]

# Verify the result
print("New column order:")
print(sorted_dat.columns.tolist())

Output Column Order #

After running the code, your columns will be ordered like this:

['a', 'b', 't', 'u', 'g', 'd', 'Start', 'Jan-22Total', 'Jan-22', 'Jan-22Rate', 'Feb-22Rate', 'Feb-22Total', 'Feb-22', 'Mar-22Rate', 'Mar-22Total', 'Mar-22', 'Apr-22Total', 'Apr-22', 'Apr-22Rate']

Key Notes #

• This solution works across years too (e.g., Dec-21 will come before Jan-22 because the datetime conversion handles cross-year ordering correctly).
• The regex is flexible enough to match the month-year pattern anywhere in the column name (whether it's at the start, middle, or end).
• Non-time columns stay in their original position, so you don't lose your initial column structure for unrelated data.

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