R语言技术问询：如何正确分析并绘制酒店多公寓预订量的时间趋势图

Hey Sofia, let's break this down step by step—you’ve got great data to work with, and we’ll turn it into those clean line charts you want in no time!

Your core challenge here is transforming your wide-format data into a structure that ggplot can easily handle for grouped trends, then aggregating to get your "booking volume" metric. Let's walk through each step:

1. Load Required Packages #

First, we'll use the tidyverse suite—it includes everything we need for data wrangling and visualization:

library(tidyverse)
# Optional: For easier date handling with German month abbreviations
library(lubridate)

2. Reshape Data from Wide to Long #

Your data has apartment types split across multiple columns (ost, west, sued, etc.), with only one column having a non-zero value per booking. We need to collapse these into two columns: one for apartment type, one for the associated nights. This makes grouping and aggregation simple.

# Replace `hotel_data` with your actual data frame name
hotel_long <- hotel_data %>%
  pivot_longer(
    cols = c(ost, west, sued, ost.west, sued.west, sued.ost, gesamtes_haus),
    names_to = "wohnung_type",  # New column for apartment type
    values_to = "nights"        # New column for stay length
  ) %>%
  filter(nights != 0)  # Remove rows where no apartment was booked

Now each row represents a single booking tied to one apartment type—perfect for trend analysis.

3. Aggregate Booking Volume by Time & Apartment #

Next, we'll calculate your "booking volume" over time. You have two common options here:

Option 1: Count of Bookings (Number of Reservations) #

If you want to track how many times each apartment was booked per month:

booking_counts <- hotel_long %>%
  # Create a clean year-month date (fixes sorting issues with German months)
  mutate(year_month = lubridate::my(paste(month, year))) %>%
  # Group by time and apartment type, then count bookings
  group_by(year_month, wohnung_type) %>%
  summarise(booking_count = n(), .groups = "drop")

Option 2: Total Stay Nights (Sum of All Nights Booked) #

If you want to track total occupancy nights per month instead:

total_stay_nights <- hotel_long %>%
  mutate(year_month = lubridate::my(paste(month, year))) %>%
  group_by(year_month, wohnung_type) %>%
  summarise(total_nights = sum(nights), .groups = "drop")

4. Build the Line Chart with ggplot2 #

Now let's visualize the trends. We'll use booking counts as an example—swap in total_nights if that's your preferred metric.

All Apartments in One Chart #

ggplot(booking_counts, aes(x = year_month, y = booking_count, color = wohnung_type)) +
  geom_line(linewidth = 1) +  # Thick lines for readability
  geom_point(size = 2) +      # Add data points to highlight monthly values
  labs(
    title = "Hotel Apartment Booking Trends (2018-2022)",
    x = "Month/Year",
    y = "Number of Bookings",
    color = "Apartment Type"
  ) +
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),  # Rotate x-labels to avoid overlap
    plot.title = element_text(hjust = 0.5)              # Center the title
  )

Individual Charts for Each Apartment #

If you want to compare trends without overlapping lines, use facets:

ggplot(booking_counts, aes(x = year_month, y = booking_count)) +
  geom_line(color = "#2c3e50", linewidth = 1) +
  geom_point(color = "#e74c3c", size = 2) +
  facet_wrap(~wohnung_type) +  # Split into one chart per apartment
  labs(
    title = "Booking Trends by Apartment Type",
    x = "Month/Year",
    y = "Number of Bookings"
  ) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

5. Overall Hotel Booking Trend #

If you want a single line for the entire hotel's booking volume:

overall_bookings <- hotel_long %>%
  mutate(year_month = lubridate::my(paste(month, year))) %>%
  group_by(year_month) %>%
  summarise(total_bookings = n(), .groups = "drop")

ggplot(overall_bookings, aes(x = year_month, y = total_bookings)) +
  geom_line(color = "#3498db", linewidth = 1.2) +
  geom_point(color = "#3498db", size = 2.5) +
  labs(
    title = "Overall Hotel Booking Trends (2018-2022)",
    x = "Month/Year",
    y = "Total Number of Bookings"
  ) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

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