如何在R中智能排序含月份及Delta列的数据框？

Great question! This is a super common scenario when building recurring monthly reports in R, and we can put together a flexible, scalable solution that automatically handles new columns as they’re added each month. The key is to parse metadata from your column names, then sort based on that structured information.

Here’s a step-by-step implementation using tidyverse tools (they make string parsing and sorting much cleaner):

1. Load Required Packages #

First, make sure you have these installed and loaded:

library(tidyverse)

2. Parse Column Metadata #

We’ll convert your column names into a structured table that extracts key details: month abbreviation, column type (yearly value or delta), and year (for the monthly value columns). This lets us sort consistently regardless of how new columns are named (as long as they follow your existing pattern):

# Get the current column names from your data frame
col_names <- colnames(df)

# Create a metadata table to organize column details
col_metadata <- tibble(col_name = col_names) %>%
  mutate(
    # Extract the month abbreviation (Jan, Feb, etc.) from any column name
    month = str_extract(col_name, "(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)"),
    # Classify columns as either "Year" (e.g., Jan-2018) or "Delta"
    column_type = case_when(
      str_detect(col_name, "\\d{4}") & !str_detect(col_name, "Delta") ~ "Year",
      str_detect(col_name, "Delta") ~ "Delta",
      TRUE ~ NA_character_
    ),
    # Extract the year for "Year" type columns
    year = ifelse(column_type == "Year", str_extract(col_name, "\\d{4}"), NA_character_),
    # Convert month to an ordered factor to enforce chronological order (Jan → Dec)
    month_ordered = factor(month, levels = month.abb)
  )

3. Sort Columns & Reorder the Data Frame #

Now we’ll sort the metadata to match your desired order:

• First, by chronological month (Jan, Feb, Mar...)
• Within each month: first the 2018 column, then 2019, then the Delta column

Then we’ll use this sorted list to reorder your original data frame:

# Generate the sorted column list
sorted_columns <- col_metadata %>%
  arrange(month_ordered, year, column_type) %>%
  pull(col_name)

# Reorder your data frame with the sorted columns
df_sorted <- df %>%
  select(all_of(sorted_columns))

Why This Works for Monthly Updates #

This solution is "smart" because:

• It automatically detects new months (e.g., Mar-2018, Mar-2019, Delta Mar) as you add them each month
• It doesn’t hardcode column names—so you don’t have to update the script every month
• It handles minor variations in Delta column names (like "Delta Jan 2018" vs "Delta Feb") since it only cares about extracting the month abbreviation

Customization Notes #

• If your reports use non-English month abbreviations, replace month.abb with a vector of your month abbreviations in chronological order (e.g., c("Janv", "Févr", ...) for French)
• If you start adding more years (e.g., 2020), the code will automatically sort them in ascending order (2018 → 2019 → 2020) before the Delta column—adjust the arrange() call if you need a different year order

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