如何使用正则表达式在Python中重命名MM/yyyy格式的列名

Alright, let's solve this column renaming problem with Python and regex—this is a common scenario when dealing with monthly sales data files. Here's a step-by-step solution using pandas (the go-to library for tabular data) and regular expressions:

Step 1: Identify Date Columns with Regex #

First, we need to spot all columns that follow the MM/yyyy format. The regex pattern r'^\d{2}/\d{4}$' will perfectly match this:

• ^\d{2}: Starts with exactly 2 digits (month)
• /: The separator
• \d{4}$: Ends with exactly 4 digits (year)

Step 2: Build a Rename Function #

We'll create a reusable function to scan dataframe columns, match the date pattern, and rename them to a consistent format (you can customize this format to fit your workflow):

import pandas as pd
import re

def standardize_date_columns(df):
    # Regex pattern to match MM/yyyy column names
    date_regex = re.compile(r'^(\d{2})/(\d{4})$')
    
    # Create a mapping of old column names to new standardized names
    rename_map = {}
    for col in df.columns:
        match = date_regex.match(col)
        if match:
            # Extract month and year from the match groups
            month, year = match.groups()
            # Customize your new column name here—examples below:
            # Option 1: "Sales_YYYYMM" (e.g., Sales_201804)
            new_col = f"Sales_{year}{month}"
            # Option 2: "YYYY-MM" (e.g., 2018-04)
            # new_col = f"{year}-{month}"
            rename_map[col] = new_col
    
    # Apply the rename mapping to the dataframe
    return df.rename(columns=rename_map)

Step 3: Use the Function on Your New Files #

Simply load your monthly file, run the function, and you'll have standardized column names:

# Load your new monthly data file (adjust the read method for your file type: excel, etc.)
monthly_data = pd.read_csv("april_2018_sales.csv")

# Standardize the date columns
cleaned_data = standardize_date_columns(monthly_data)

# Verify the result
print("Original columns:", monthly_data.columns.tolist())
print("Cleaned columns:", cleaned_data.columns.tolist())

Customization Tips #

• Handle messy column names: If your date columns have extra spaces (e.g., 04/2018), adjust the regex to r'^\s*\d{2}/\d{4}\s*$' to trim whitespace.
• Support single-digit months: If some files use M/yyyy (e.g., 4/2018), update the regex to r'^\d{1,2}/\d{4}$' to match 1 or 2-digit months.
• Adjust the new name format: Tweak the new_col line to fit your needs—add prefixes, suffixes, or even convert to a datetime object if that makes sense for your analysis.

This approach ensures that no matter which month's file you receive, all the MM/yyyy sales columns get renamed to a consistent, predictable format, making it easy to merge or analyze data across months.

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