Reordering Pandas Columns with a Custom Sort in Python

Introduction

The author, a Python enthusiast, shares a practical Pandas problem raised in a community group: how to reorder a DataFrame so that specific columns appear after a predefined list while also sorting columns containing "quantity" and "amount" based on embedded dates.

Problem Statement

The desired column order includes static fields such as "发料仓库", "1级物料类别", etc., followed by any columns that contain the words "数量" (quantity) or "金额" (amount). The challenge is to sort these dynamic columns correctly, for example handling names like "2024年1月份结存数量" and "对比10月份数量差异".

# Create the static column order list
column_order = [
    "发料仓库", "1级物料类别", "2级物料类别", "3级物料类别", "4级物料类别", "5级物料类别",
    "物料长代码", "物料名称", "规格型号", "单位(基本)", "单价", "仓库分类"
]

# Extract columns that contain quantity or amount
amount_and_amount_columns = [
    col for col in stock_capital.columns
    if "数量" in col or "金额" in col
]

# Reorder the DataFrame
new_columns = column_order + amount_and_amount_columns
stock_capital = stock_capital.reindex(columns=new_columns)

Solution

The author proposes a custom sorting function that extracts numeric year and month from each column name, assigns higher priority to quantity over amount, and gives precedence to comparison columns. The function returns a tuple (year, month, diff_weight, type_weight) used as the sort key.

column_order = ['发料仓库', '1级物料类别', '2级物料类别', '3级物料类别', '4级物料类别', '5级物料类别', '物料长代码', '物料名称', '规格型号', '单位(基本)', '单价', '仓库分类']

amount_and_amount_columns = [col for col in stock_capital.columns if "数量" in col or "金额" in col]

def custom_sort(col):
    # Extract numbers (year and month) from the column name
    numbers = re.findall(r'\d+', col)
    year = int(numbers[0]) if numbers else 0
    month = int(numbers[1]) if len(numbers) > 1 else 0
    # Weight: quantity > amount
    type_weight = -1 if "数量" in col else 0
    # Weight: comparison columns before non‑comparison
    diff_weight = -1 if "对比" in col else 0
    return (year, month, diff_weight, type_weight)

# Sort the dynamic columns
amount_and_amount_columns_sorted = sorted(amount_and_amount_columns, key=custom_sort)

# Append the sorted columns to the static order and reindex
column_order.extend(amount_and_amount_columns_sorted)
stock_capital = stock_capital.reindex(columns=column_order)

Conclusion

The provided code demonstrates how to combine a fixed column sequence with a dynamically sorted list of quantity and amount columns, ensuring the final DataFrame respects both business‑defined order and chronological logic.