如何按年月排序并聚合数据，实现X轴有序的年月图表？

Great question! The key here is making sure your year-month values are treated as a time-ordered type (not just plain text) so both your aggregation and plot axis behave correctly. Let's break this down step by step:

Step 1: Clean up your year-month variable (optional but better) #

Instead of extracting month/year separately and pasting, you can directly format your date into a consistent year-month string (with leading zeros for months, which helps with sorting if you stick to text) or convert it to a date object (even better for time-based operations):

# Option 1: Create a formatted year-month string (e.g., "01/2020" instead of "1/2020")
housing_data$month_year <- format(as.POSIXlt(housing_data$date, format="%Y-%m-%d"), "%m/%Y")

# Option 2: Create a date object for the first day of each month (best for time ordering)
housing_data$month_year_date <- as.Date(paste(
  year(as.POSIXlt(housing_data$date, format="%Y-%m-%d")),
  month(as.POSIXlt(housing_data$date, format="%Y-%m-%d")),
  "01",
  sep = "-"
))

The date object (month_year_date) is ideal because R inherently understands its time order—no extra sorting needed.

Step 2: Aggregate your data to the year-month level #

Let's assume you want to aggregate a numeric variable (like average housing price) by year-month. Here are two common approaches:

Using dplyr (tidyverse style)

library(dplyr)

aggregated_data <- housing_data %>%
  # Add the year-month date column if you didn't already
  mutate(month_year_date = as.Date(paste(year(date), month(date), "01", sep = "-"))) %>%
  group_by(month_year_date) %>%
  summarise(
    avg_price = mean(price, na.rm = TRUE),  # Replace with your aggregation function
    total_sales = n()  # Example of another metric you might want
  )

Using base R

# First add the month_year_date column
housing_data$month_year_date <- as.Date(paste(
  year(as.POSIXlt(housing_data$date, format="%Y-%m-%d")),
  month(as.POSIXlt(housing_data$date, format="%Y-%m-%d")),
  "01",
  sep = "-"
))

# Aggregate with aggregate()
aggregated_data <- aggregate(
  price ~ month_year_date,
  data = housing_data,
  FUN = function(x) mean(x, na.rm = TRUE)  # Replace with your function
)

Step 3: Plot with ordered X-axis #

Using ggplot2, the date object will automatically sort correctly on the X-axis. You can customize how the dates are displayed with scale_x_date:

library(ggplot2)

ggplot(aggregated_data, aes(x = month_year_date, y = avg_price)) +
  geom_line(color = "steelblue", linewidth = 1) +  # Or geom_col for bars
  scale_x_date(
    date_labels = "%m/%Y",  # Show as "01/2020"
    date_breaks = "1 month"  # Adjust based on your data density
  ) +
  labs(
    x = "Year-Month",
    y = "Average Housing Price",
    title = "Monthly Average Housing Price"
  ) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))  # Rotate labels for readability

If you prefer to use the string-based month_year instead, you need to convert it to an ordered factor first to ensure correct sorting:

# Create ordered factor using the original date order
housing_data$ordered_month_year <- factor(
  housing_data$month_year,
  levels = unique(housing_data$month_year[order(housing_data$date)]),
  ordered = TRUE
)

# Aggregate and plot
aggregated_data <- aggregate(price ~ ordered_month_year, data = housing_data, FUN = mean, na.rm = TRUE)

ggplot(aggregated_data, aes(x = ordered_month_year, y = price)) +
  geom_col(fill = "steelblue") +
  labs(x = "Year-Month", y = "Average Price") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

Key Takeaway #

The main issue with your original month_year_of_date is that it's a plain character string, so R will sort it alphabetically (e.g., "1/2020" comes before "10/2020") instead of chronologically. By converting it to a date object or ordered factor, you ensure both aggregation and plotting follow the correct time sequence.

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